private void initFromDatabase() throws SQLException, BlockStoreException {
    Statement s = conn.get().createStatement();
    ResultSet rs;

    rs = s.executeQuery("SELECT value FROM settings WHERE name = '" + CHAIN_HEAD_SETTING + "'");
    if (!rs.next()) {
      throw new BlockStoreException("corrupt Postgres block store - no chain head pointer");
    }
    Sha256Hash hash = new Sha256Hash(rs.getBytes(1));
    rs.close();
    this.chainHeadBlock = get(hash);
    this.chainHeadHash = hash;
    if (this.chainHeadBlock == null) {
      throw new BlockStoreException("corrupt Postgres block store - head block not found");
    }

    rs =
        s.executeQuery(
            "SELECT value FROM settings WHERE name = '" + VERIFIED_CHAIN_HEAD_SETTING + "'");
    if (!rs.next()) {
      throw new BlockStoreException(
          "corrupt Postgres block store - no verified chain head pointer");
    }
    hash = new Sha256Hash(rs.getBytes(1));
    rs.close();
    s.close();
    this.verifiedChainHeadBlock = get(hash);
    this.verifiedChainHeadHash = hash;
    if (this.verifiedChainHeadBlock == null) {
      throw new BlockStoreException("corrupt Postgres block store - verified head block not found");
    }
  }
  public void testDelete() {

    try {
      insertData(con);

      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery("SELECT COUNT(EIACODXA) FROM CA");

      rs.next();

      int origCount = rs.getInt(1);

      rs.close();
      deleteXBRecord(con);

      rs = stmt.executeQuery("SELECT COUNT(EIACODXA) FROM CA");

      rs.next();

      int newCount = rs.getInt(1);

      rs.close();
      stmt.close();
      assertEquals(9, newCount);
    } catch (SQLException e) {
      this.assertTrue("SQLException thrown", false);
    }
  }
  /**
   * Test that we recover from self locking in the auto-create nested transaction (cf solution for
   * DERBY-48).
   */
  public void testDerby48SelfLockingRecovery() throws SQLException {
    Connection c1 = openUserConnection("newuser");
    c1.setAutoCommit(false);
    c1.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
    Statement s1 = c1.createStatement();

    // Set read locks in parent transaction
    s1.executeQuery("select count(*) from sys.sysschemas");

    // ..which conflicts with the auto-create in a subtransaction
    // which will self-lock here, but should recover to try again
    // in outer transaction:
    s1.executeUpdate("create table t1(i int)");

    JDBC.assertSingleValueResultSet(
        s1.executeQuery("select schemaname from sys.sysschemas " + "where schemaname='NEWUSER'"),
        "NEWUSER");

    c1.rollback();

    // Since the fallback does the auto-create of the schema in
    // the outer transaction, a rollback will remove it:
    JDBC.assertEmpty(s1.executeQuery("select * from sys.sysschemas where schemaname='NEWUSER'"));

    c1.rollback();
  }
Example #4
0
 public static void namejudge(Socket socket, Connection conn, BufferedReader is) {
   System.out.println("用户查询存在模块");
   try {
     PrintWriter os = new PrintWriter(socket.getOutputStream());
     String rec;
     rec = is.readLine();
     // 执行SQL语句
     Statement stmt = conn.createStatement(); // 创建语句对象,用以执行sql语言
     if (rec.charAt(0) == '1') {
       ResultSet rs = stmt.executeQuery("select * from teacher_table where logname=" + rec);
       if (rs.first()) {
         os.println(rec);
         os.flush();
       } else {
         os.println("no" + rec);
         os.flush();
       }
     } else if (rec.charAt(0) == '2') {
       ResultSet rs = stmt.executeQuery("select * from student_table where logname=" + rec);
       if (rs.first()) {
         os.println(rec);
         os.flush();
       } else {
         os.println("no" + rec);
         os.flush();
       }
     } else {
       os.println("no" + rec);
       os.flush();
     }
   } catch (Exception e) {
     System.out.println("Error:" + e);
   }
 }
 /**
  * Test support for JDBC 1 style positioned updates with named cursors.
  *
  * <p>When running on SQL Server this test will exercise MSCursorResultSet. When running on Sybase
  * this test will exercise CachedResultSet.
  */
 public void testPositionedUpdate() throws Exception {
   assertTrue(con.getMetaData().supportsPositionedDelete());
   assertTrue(con.getMetaData().supportsPositionedUpdate());
   Statement stmt = con.createStatement();
   stmt.execute("CREATE TABLE #TESTPOS (id INT primary key, data VARCHAR(255))");
   for (int i = 1; i < 5; i++) {
     stmt.execute("INSERT INTO #TESTPOS VALUES(" + i + ", 'This is line " + i + "')");
   }
   stmt.setCursorName("curname");
   ResultSet rs = stmt.executeQuery("SELECT * FROM #TESTPOS FOR UPDATE");
   Statement stmt2 = con.createStatement();
   while (rs.next()) {
     if (rs.getInt(1) == 1) {
       stmt2.execute("UPDATE #TESTPOS SET data = 'Updated' WHERE CURRENT OF curname");
     } else if (rs.getInt(1) == 3) {
       stmt2.execute("DELETE FROM #TESTPOS WHERE CURRENT OF curname");
     }
   }
   rs.close();
   stmt.setFetchSize(100);
   rs = stmt.executeQuery("SELECT * FROM #TESTPOS");
   while (rs.next()) {
     int id = rs.getInt(1);
     assertTrue(id != 3); // Should have been deleted
     if (id == 1) {
       assertEquals("Updated", rs.getString(2));
     }
   }
   stmt2.close();
   stmt.close();
 }
Example #6
0
 public void deleteOption(String modelName, String optionSetName, String option) {
   try {
     int autoid = 0;
     String sql = "select id from automobile where name ='" + modelName + "';";
     ResultSet rs;
     rs = statement.executeQuery(sql);
     while (rs.next()) {
       autoid = rs.getInt("id"); // get auto_id
     }
     int opsid = 0;
     sql =
         "select id from optionset where name ='"
             + optionSetName
             + "' and auto_id= "
             + autoid
             + ";";
     rs = statement.executeQuery(sql);
     while (rs.next()) {
       opsid = rs.getInt("id"); // get option_id
     }
     sql = "delete from options where name= '" + option + "' and option_id= " + opsid;
     statement.executeUpdate(sql); // delete it using name and option_id
   } catch (SQLException e) {
     e.printStackTrace();
   }
 }
Example #7
0
  /**
   * fine the co-authors of given author name
   *
   * @param author's name
   * @return return a string of all co-authors
   * @throws Exception
   */
  public String findCoAuthor(String name) throws Exception {

    Statement stmt = conn.createStatement();
    String useDB = "USE dblp;";
    stmt.executeUpdate(useDB);
    String sql = "SELECT id FROM authorInfo WHERE name = \"" + name + "\";";
    ResultSet rs = stmt.executeQuery(sql);
    ArrayList<String> id = new ArrayList<String>();
    Set<String> coAuthor = new HashSet<>();
    String ret = "Co-Authors of ".concat(name).concat(":\n");
    while (rs.next()) {
      id.add(rs.getString("id"));
    }
    if (id.size() == 0) {
      return "No such author";
    }
    for (String s : id) {
      sql = "SELECT name FROM authorInfo WHERE id=\"" + s + "\";";
      rs = stmt.executeQuery(sql);
      while (rs.next()) {
        coAuthor.add(rs.getString("name"));
      }
    }
    coAuthor.remove(name);
    for (String s : coAuthor) {
      ret = ret.concat(s + "\n");
    }

    if (ret.equals("Co-Authors of ".concat(name).concat(":\n"))) ret = "No Co-Author found";

    if (stmt != null) {
      stmt.close();
    }
    return ret;
  }
  public static void createDefaultShippingRegion(Connection connection) throws SQLException {
    String sql =
        "insert  "
            + "into    shipping_region "
            + "        (published, rec_create_by, rec_create_datetime, rec_update_by, rec_update_datetime, shipping_region_name, site_id, system_record) "
            + "values  ('Y', 'admin', now(), 'admin', now(), 'default', ?, 'Y')";
    PreparedStatement insertStatement = connection.prepareStatement(sql);

    Statement select = connection.createStatement();
    sql =
        "select  site_id "
            + "from    site "
            + "where   site_id != '_system' "
            + "and     system_record = 'Y'";
    ResultSet result = select.executeQuery(sql);
    while (result.next()) {
      String siteId = result.getString("site_id");

      sql =
          "select  count(*) "
              + "from    shipping_region "
              + "where   site_id = '"
              + siteId
              + "' "
              + "and     system_record = 'Y'";
      Statement countStatement = connection.createStatement();
      ResultSet shippingRegionResult = countStatement.executeQuery(sql);
      shippingRegionResult.first();
      int count = shippingRegionResult.getInt(1);
      if (count == 0) {
        insertStatement.setString(1, siteId);
        insertStatement.executeUpdate();
      }
    }
  }
 private boolean validateConnectiontoDBMS() throws SQLException {
   int NumberOfRows, Response;
   VDBMSC = DriverManager.getConnection(ConnectionStringNoDB, Username, Password);
   VDBMSS = VDBMSC.createStatement();
   VDBMSRS = VDBMSS.executeQuery("SHOW DATABASES LIKE 'salesdata'");
   if (!VDBMSRS.next())
     for (String sql : SQL) {
       VDBMSS.executeUpdate(sql);
     }
   else {
     VDBMSS.executeUpdate("USE salesdata;");
     VDBMSRS = VDBMSS.executeQuery("SHOW TABLES");
     //		    VDBMSRS.last();
     //		    NumberOfRows = VDBMSRS.getRow();
     //		    if(NumberOfRows!=1)
     //		      {
     //		    	if(JOptionPane.showConfirmDialog(null, "Unauthorized change has been found in
     // database : termresultcalculator .\nProbably, one or more Tables have been dropped which may
     // stop the application to work correctly.\nDo you want to create the database again? ",
     // "Change in Database Table", JOptionPane.YES_NO_OPTION,
     // JOptionPane.QUESTION_MESSAGE)==JOptionPane.YES_OPTION)
     //		    	  {
     //		    		for(int i = 1; i<5; i++)
     //			    	  VDBMSS.executeUpdate(SQL[i]);
     //		    		JOptionPane.showMessageDialog(null, "Changes have been made.","Success",
     // JOptionPane.INFORMATION_MESSAGE);
     //		          }
     //		      }
   }
   closeVDBMS();
   return true;
 }
  /**
   * Remove a student from a particular course. Also Deletes all the quiz vizualisation files in the
   * student's directory which relates to the course. Caution: vizualisation file will be deleted
   * eventhough it also relates to another course if the student is also registered to that course.
   * (FIX ME!) Throws InvalidDBRequestException if the student is not registered in the course,
   * error occured during deletion, or other exception occured.
   *
   * @param username student's user name
   * @param courseID course id (course number + instructor name)
   * @throws InvalidDBRequestException
   */
  public void deleteStudent(String username, String courseID) throws InvalidDBRequestException {
    try {
      Class.forName(GaigsServer.DBDRIVER);
      db =
          DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);

      Statement stmt = db.createStatement();
      ResultSet rs;

      int count = 0;

      // check if student registered to the course
      rs =
          stmt.executeQuery(
              "select * from courseRoster where course_id = '"
                  + courseID
                  + "' and user_login = '"
                  + username
                  + "'");
      if (!rs.next())
        throw new InvalidDBRequestException("Student is not registered to the course");

      // remove student from the course
      count =
          stmt.executeUpdate(
              "delete from courseRoster where course_id = '"
                  + courseID
                  + "' and user_login = '"
                  + username
                  + "'");
      if (count != 1) throw new InvalidDBRequestException("Error occured during deletion!");

      // delete the quiz visualization files
      rs =
          stmt.executeQuery(
              "select distinct unique_id, s.test_name from scores s, courseTest t "
                  + "where s.test_name = t.test_name "
                  + "and course_id = '"
                  + courseID
                  + "' "
                  + "and user_login = '"
                  + username
                  + "'");
      while (rs.next()) {
        deleteVisualization(rs.getString(1), username, rs.getString(2));
        count =
            stmt.executeUpdate("delete from scores where unique_id = " + rs.getString(1).trim());
      }

      rs.close();
      stmt.close();
      db.close();
    } catch (SQLException e) {
      System.err.println("Invalid SQL in addstudent: " + e.getMessage());
      throw new InvalidDBRequestException("???");
    } catch (ClassNotFoundException e) {
      System.err.println("Driver Not Loaded");
      throw new InvalidDBRequestException("Internal Server Error");
    }
  }
  private void okActionPerformed(
      java.awt.event.ActionEvent evt) { // GEN-FIRST:event_okActionPerformed

    try {

      if (txtuser.getText().equals("") || txtpassword.getText().equals("")) {

        JOptionPane.showMessageDialog(this, "Please Enter Username & Password", "Message", WIDTH);
      } else { // connect
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn =
            DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/student_information", "root", "123");
        Statement stm = conn.createStatement();
        String qry = "select password from login where username = '" + txtuser.getText() + "' ;";
        ResultSet rst = stm.executeQuery(qry);

        Statement stm2 = conn.createStatement();
        String qry2 = "select state from login where username = '" + txtuser.getText() + "'; ";
        ResultSet rst2 = stm2.executeQuery(qry2);

        if (!rst.next()) { // validate username

          JOptionPane.showMessageDialog(this, "Invalid Username", "Error", WIDTH);
        } else if (rst.getString("password").equals(txtpassword.getText())) { // check password

          MainWindow m = new MainWindow(); // open main
          m.setVisible(true);

          m.lbluser.setText(txtuser.getText());

          if (rst2.next() && rst2.getInt("State") == 1) { // block lecturer

            m.lblState.setText("Admin");
          } else {

            m.lblState.setText("Lecturer");
            /* MainWindow mw1 = new MainWindow();
            mw1.btnStudentm.setVisible(false);
            mw1.btnCoursem.setVisible(false);
            mw1.btnLecturerm.setVisible(false);
            mw1.btnAdmin.setVisible(false);
            mw1.lbllec.setVisible(false);*/
          }

          this.setVisible(false);
          txtuser.setText("");
          txtpassword.setText("");

        } else {

          JOptionPane.showMessageDialog(this, "Invalid Password", "Error", WIDTH);
        }
      }

    } catch (Exception e) {

      JOptionPane.showMessageDialog(this, "Error in Excecution " + e, "Error", WIDTH);
    }
  } // GEN-LAST:event_okActionPerformed
  private void testUpdateLob() throws SQLException {
    Connection conn = getConnection();
    Statement stat = conn.createStatement();
    stat.execute(
        "CREATE TABLE object_index " + "(id integer primary key, object other, number integer)");

    PreparedStatement prep =
        conn.prepareStatement("INSERT INTO object_index (id,object)  VALUES (1,?)");
    prep.setObject(1, "hello", Types.JAVA_OBJECT);
    prep.execute();

    ResultSet rs = stat.executeQuery("SELECT object,id,number FROM object_index WHERE id =1");
    rs.next();
    assertEquals("hello", rs.getObject(1).toString());
    stat = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
    rs = stat.executeQuery("SELECT object,id,number FROM object_index WHERE id =1");
    rs.next();
    assertEquals("hello", rs.getObject(1).toString());
    rs.updateInt(2, 1);
    rs.updateRow();
    rs.close();
    stat = conn.createStatement();
    rs = stat.executeQuery("SELECT object,id,number FROM object_index WHERE id =1");
    rs.next();
    assertEquals("hello", rs.getObject(1).toString());
    conn.close();
  }
Example #13
0
 /**
  * This method gets the moments happening after the reference moments, in particular the moments
  * that occur in the range ]referenceMoment, referenceMoment + range{
  *
  * @param referenceMoment the reference moment to be used to as boundary
  * @param range how many minutes after we want to capture (remember the ranges are not inclusive
  *     of the boundaries)
  * @return a vector of moments that happened after the referenceMoment
  */
 public Vector<Moment> getMomentsAfter(Moment referenceMoment, int range) throws SQLException {
   Vector<Moment> before = new Vector<Moment>();
   String query =
       "SELECT * FROM minute WHERE id > "
           + referenceMoment.getMinute()
           + "AND id < "
           + String.valueOf(Integer.parseInt(referenceMoment.getMinute()) + range);
   Statement st = this.connection.createStatement();
   ResultSet rs = st.executeQuery(query);
   while (rs.next()) {
     Moment moment = new Moment();
     moment.setMinute(rs.getString("id"));
     moment.setDate(rs.getString("date"));
     moment.setLocation(rs.getString("location"));
     moment.setActivity(rs.getString("activity"));
     moment.setImagepath(rs.getString("image_path"));
     String queryimages = "SELECT `image-path` FROM image WHERE id='" + moment.getMinute() + "'";
     Statement subst = this.connection.createStatement();
     ResultSet subrs = subst.executeQuery(queryimages);
     while (subrs.next()) {
       moment.getImages().add(subrs.getString("image-path"));
     }
   }
   return before;
 }
Example #14
0
  public Moment queryMoment(String image_path) throws SQLException {
    Moment moment = new Moment();
    String queryminute = "SELECT id FROM image WHERE `image-path`='" + image_path + "'";
    Statement st = this.connection.createStatement();
    ResultSet rs = st.executeQuery(queryminute);
    String minute = "";
    while (rs.next()) {
      minute = rs.getString("id");
    }

    String query = "SELECT * FROM minute WHERE id='" + minute + "'";
    st = this.connection.createStatement();
    rs = st.executeQuery(query);
    int counter = 0;
    while (rs.next()) {
      moment.setMinute(rs.getString("id"));
      moment.setDate(rs.getString("date"));
      moment.setLocation(rs.getString("location"));
      moment.setActivity(rs.getString("activity"));
      moment.setImagepath(image_path);
      String queryimages = "SELECT `image-path` FROM image WHERE id='" + moment.getMinute() + "'";
      Statement subst = this.connection.createStatement();
      ResultSet subrs = subst.executeQuery(queryimages);
      while (subrs.next()) {
        moment.getImages().add(subrs.getString("image-path"));
      }
      counter++;
    }
    if (counter > 1)
      System.err.println(
          "Attention! More than a moment associated to image "
              + image_path
              + ". Returning only the last moment");
    return moment;
  }
 private void testTriggerBeforeSelect() throws SQLException {
   Connection conn;
   Statement stat;
   conn = getConnection("trigger");
   stat = conn.createStatement();
   stat.execute("drop table if exists meta_tables");
   stat.execute("create table meta_tables(name varchar)");
   stat.execute(
       "create trigger meta_tables_select "
           + "before select on meta_tables call \""
           + TestSelect.class.getName()
           + "\"");
   ResultSet rs;
   rs = stat.executeQuery("select * from meta_tables");
   assertTrue(rs.next());
   assertFalse(rs.next());
   stat.execute("create table test(id int)");
   rs = stat.executeQuery("select * from meta_tables");
   assertTrue(rs.next());
   assertTrue(rs.next());
   assertFalse(rs.next());
   conn.close();
   if (!config.memory) {
     conn = getConnection("trigger");
     stat = conn.createStatement();
     stat.execute("create table test2(id int)");
     rs = stat.executeQuery("select * from meta_tables");
     assertTrue(rs.next());
     assertTrue(rs.next());
     assertTrue(rs.next());
     assertFalse(rs.next());
     conn.close();
   }
 }
 private void testTriggerAdapter() throws SQLException {
   Connection conn;
   Statement stat;
   conn = getConnection("trigger");
   stat = conn.createStatement();
   stat.execute("drop table if exists test");
   stat.execute("create table test(id int, c clob, b blob)");
   stat.execute("create table message(name varchar)");
   stat.execute(
       "create trigger test_insert before insert, update, delete on test "
           + "for each row call \""
           + TestTriggerAdapter.class.getName()
           + "\"");
   stat.execute("insert into test values(1, 'hello', 'abcd')");
   ResultSet rs;
   rs = stat.executeQuery("select * from test");
   rs.next();
   assertEquals(10, rs.getInt(1));
   stat.execute("update test set id = 2");
   rs = stat.executeQuery("select * from test");
   rs.next();
   assertEquals(20, rs.getInt(1));
   stat.execute("delete from test");
   rs = stat.executeQuery("select * from message");
   assertTrue(rs.next());
   assertEquals("+1;", rs.getString(1));
   assertTrue(rs.next());
   assertEquals("-10;+2;", rs.getString(1));
   assertTrue(rs.next());
   assertEquals("-20;", rs.getString(1));
   assertFalse(rs.next());
   stat.execute("drop table test, message");
   conn.close();
 }
  public Formation get(String s) {
    try {
      Connection connection = DataBaseManager.getConnection();
      Statement statement = connection.createStatement();
      ResultSet rs =
          statement.executeQuery("SELECT * FROM FORMATIONS \n" + "WHERE codeForm = '" + s + "'");

      while (rs.next()) {
        TeachingUnitManager tum = new TeachingUnitManager();
        TeacherManager tm = new TeacherManager();
        Formation form =
            new Formation(
                rs.getString("Title"), rs.getString("Grade"), tm.get(rs.getString("Director")));
        ResultSet rstu =
            statement.executeQuery(
                "SELECT * FROM FORMATIONS_TUS \n" + "WHERE codeForm = '" + s + "'");
        while (rstu.next()) {
          TeachingUnit tu = tum.get(rstu.getString("codeTU"));
          form.addTeachingUnits(tu);
        }
        DataBaseManager.releaseConnection(connection);
        return form;
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return null;
  }
Example #18
0
  // Methode für den Insert einer Adresse
  private int insertAdresse(final Adresse adr) throws SQLException {
    try {
      Statement stm = createStatement();
      StringBuilder sb = new StringBuilder();
      sb.append(
          "INSERT INTO dbsys20.Adresse (AdressId, Landesid, plz, strasze, hausnummer) VALUES (");
      sb.append("dbsys20.SQAdressId.nextval").append(", ");
      sb.append(Integer.toString(adr.getLand().getLandesId())).append(", ");
      sb.append("'").append(adr.getPLZ()).append("', ");
      sb.append("'").append(adr.getStrasze()).append("', ");
      sb.append("'").append(adr.getHausnummer()).append("') ");

      String myInsertQuery = sb.toString();
      System.out.println(myInsertQuery); // FIXME REMOVE
      stm.executeQuery(myInsertQuery);

      String select = "Select dbsys20.SQAdressID.currVal from dual";

      ResultSet resultSet = stm.executeQuery(select);
      if (!resultSet.next()) throw new SQLException("no result");

      int adressID = resultSet.getInt(1);

      // new Adresse(adressID, adr.getStrasze(), adr.getHausnummer(), adr.getOrt(), adr.getPLZ(),
      // adr.getLand());

      stm.close();
      return adressID;
      // con.commit(); must not commit. will be commited in createNewUser
    } catch (SQLException e) {
      System.err.println("Exception while inserting new adress");
      throw e;
    }
  }
Example #19
0
  public LinkedList<Trigger> listaTrigger(String nombreT, Connection con) throws SQLException {
    Statement s = con.createStatement();
    LinkedList<Trigger> lT = new LinkedList();
    LinkedList<String> nombTrigger = nombreTrigger(nombreT, s);

    ResultSet rs;
    String disparador = null;
    for (int i = 0; i < nombTrigger.size(); i++) {
      LinkedList<String> condTrigger = new LinkedList();
      rs =
          s.executeQuery(
              "SELECT event_manipulation,action_timing FROM information_schema.triggers where trigger_name ='"
                  + nombTrigger.get(i)
                  + "' order by event_manipulation");
      int u = 0;
      while (rs.next()) {
        u++;
      }
      rs =
          s.executeQuery(
              "SELECT event_manipulation,action_timing FROM information_schema.triggers where trigger_name ='"
                  + nombTrigger.get(i)
                  + "' order by event_manipulation");
      while (rs.next()) {
        disparador = rs.getObject("action_timing").toString();
        condTrigger.add(rs.getObject("event_manipulation").toString());
        // System.out.println("COND "+nombTrigger.get(i)+" -
        // "+rs.getObject("event_manipulation").toString());
      }
      lT.add(new Trigger(nombTrigger.get(i), condTrigger, disparador));
    }
    s.close();
    return lT;
  }
  public static void deleteThought(int index) {
    try {
      java.sql.Connection con = Database.getConnection();
      java.sql.Statement s =
          con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
      int indext = index + 1;
      ResultSet thoughts;
      thoughts = s.executeQuery("select * from thoughts");
      thoughts.absolute(indext);
      thoughts.deleteRow();

      int size = aantalID("thoughts");
      ResultSet thoughts2;
      thoughts2 = s.executeQuery("select * from thoughts");
      if (thoughts2.next()) {
        while (indext < size) {
          thoughts2.absolute(indext);
          thoughts2.updateInt("id", indext);
          thoughts2.updateRow();
          indext++;
          thoughts2.next();
        }
      }

    } catch (SQLException ex) {
      Logger.getLogger(DataLayer.class.getName()).log(Level.SEVERE, null, ex);
    }
  }
  public static void createCustomerClass(Connection connection) throws SQLException {
    String sql =
        "insert  "
            + "into    customer_class "
            + "        (cust_class_name, rec_create_by, rec_create_datetime, rec_update_by, rec_update_datetime, site_id, system_record) "
            + "values  ('Regular', 'admin', now(), 'admin', now(), ?, 'Y')";
    PreparedStatement insertStatement = connection.prepareStatement(sql);

    Statement select = connection.createStatement();
    sql =
        "select  site_id "
            + "from    site "
            + "where   site_id != '_system' "
            + "and     system_record = 'Y'";
    ResultSet result = select.executeQuery(sql);
    while (result.next()) {
      String siteId = result.getString("site_id");

      sql = "select  count(*) " + "from    customer_class " + "where   site_id = '" + siteId + "'";
      Statement countStatement = connection.createStatement();
      ResultSet customerClassResult = countStatement.executeQuery(sql);
      customerClassResult.first();
      int count = customerClassResult.getInt(1);
      if (count == 0) {
        insertStatement.setString(1, siteId);
        insertStatement.executeUpdate();
      }
    }
  }
  public static void deleteProject(int id) {
    try {
      java.sql.Connection con = Database.getConnection();
      java.sql.Statement s =
          con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

      ResultSet projects;
      projects = s.executeQuery("select * from projects");
      projects.absolute(id);
      projects.deleteRow();

      int size = aantalID("projects");
      projects = s.executeQuery("select * from projects");
      if (projects.next()) {
        while (id < size) {
          projects.absolute(id);
          projects.updateInt("id", id);
          projects.updateRow();
          id++;
          projects.next();
        }
      }
    } catch (SQLException ex) {
      Logger.getLogger(DataLayer.class.getName()).log(Level.SEVERE, null, ex);
    }
  }
Example #23
0
  public void updateOptionPrice(
      String modelName, String optionSetName, String optionName, float newPrice) {
    try {
      int autoid = 0;
      String sql = "select id from automobile where name ='" + modelName + "';";
      ResultSet rs = statement.executeQuery(sql);
      while (rs.next()) {
        autoid = rs.getInt("id"); // get auto_id
      }
      int opsid = 0;
      sql =
          "select id from optionset where name ='"
              + optionSetName
              + "' and auto_id= "
              + autoid
              + ";";
      rs = statement.executeQuery(sql);
      while (rs.next()) {
        opsid = rs.getInt("id"); // get option_id
      }
      sql =
          "update options set price = "
              + newPrice
              + " where name= '"
              + optionName
              + "' and option_id= "
              + opsid
              + ";";
      statement.executeUpdate(sql); // update it with name and option_id

    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
Example #24
0
 public static List<Document> getDocumentsBySerie(String serie, Connection conn) {
   Statement stmt;
   List<Document> resultat = new ArrayList<Document>();
   try {
     stmt = conn.createStatement();
     int serieId;
     ResultSet rs = stmt.executeQuery("SELECT S_ID FROM SERIE WHERE NOM=\"" + serie + "\"");
     rs.next();
     serieId = rs.getInt(1);
     rs =
         stmt.executeQuery(
             "SELECT * FROM IMAGE D WHERE EXISTS(SELECT * FROM IMSERIE I WHERE I.I_ID = D.I_ID AND S_ID="
                 + serieId
                 + ")");
     while (rs.next()) {
       resultat.add(
           new Document(
               rs.getInt("I_ID"),
               rs.getDate("I_DATE"),
               rs.getInt("SIZE"),
               rs.getString("NOM"),
               rs.getString("CHEMIN"),
               rs.getInt("WIDTH"),
               rs.getInt("HEIGHT"),
               rs.getInt("NOTE"),
               rs.getString("DESCRIPTION")));
     }
   } catch (SQLException e) {
     e.printStackTrace();
   }
   return resultat;
 }
Example #25
0
  @Test
  public void testImport() throws Exception {
    MemoryDatabase memDb = new MemoryDatabase("sample");
    memDb.start();

    final String jsonFileName = "/sample.json";
    memDb.importJSON(getClass(), jsonFileName);

    JSONParser jsonParser = new JSONParser();
    JSONArray tables =
        (JSONArray)
            jsonParser.parse(new InputStreamReader(getClass().getResourceAsStream(jsonFileName)));
    assertEquals(2, tables.size());

    Connection conn = memDb.getConnection();
    Statement stmt = conn.createStatement();

    JSONObject employee = (JSONObject) tables.get(0);
    ResultSet rs = stmt.executeQuery("SELECT * FROM \"employee\"");
    verifyTableEquals(employee, rs);
    rs.close();

    JSONObject team = (JSONObject) tables.get(1);
    rs = stmt.executeQuery("SELECT * FROM \"team\"");
    verifyTableEquals(team, rs);
    rs.close();

    stmt.close();
    conn.close();

    memDb.stop();
  }
Example #26
0
 public static Document executeSelectDocument(Connection conn, int id) {
   Statement stmt;
   Document d = new Document();
   try {
     stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery("SELECT * FROM IMAGE WHERE I_ID=" + id);
     rs.next();
     d =
         new Document(
             rs.getInt("I_ID"),
             rs.getDate("I_DATE"),
             rs.getInt("SIZE"),
             rs.getString("NOM"),
             rs.getString("CHEMIN"),
             rs.getInt("WIDTH"),
             rs.getInt("HEIGHT"),
             rs.getInt("NOTE"),
             rs.getString("DESCRIPTION"));
     rs = stmt.executeQuery("SELECT NOM FROM IMTAG I, TAG T WHERE I.T_ID=T.T_ID AND I.I_ID=" + id);
     while (rs.next()) {
       d.addTag(rs.getString("NOM"));
     }
     rs =
         stmt.executeQuery(
             "SELECT NOM FROM IMSERIE I, SERIE S WHERE I.S_ID=S.S_ID AND I.I_ID=" + id);
     while (rs.next()) {
       d.addSerie(rs.getString("NOM"));
     }
   } catch (SQLException e) {
     e.printStackTrace();
   }
   return d;
 }
  private void testColumnLength() throws SQLException {
    trace("testColumnDisplayLength");
    ResultSet rs;
    ResultSetMetaData meta;

    stat.execute("CREATE TABLE one (ID INT, NAME VARCHAR(255))");
    rs = stat.executeQuery("select * from one");
    meta = rs.getMetaData();
    assertEquals("ID", meta.getColumnLabel(1));
    assertEquals(11, meta.getColumnDisplaySize(1));
    assertEquals("NAME", meta.getColumnLabel(2));
    assertEquals(255, meta.getColumnDisplaySize(2));
    stat.execute("DROP TABLE one");

    rs = stat.executeQuery("select 1, 'Hello' union select 2, 'Hello World!'");
    meta = rs.getMetaData();
    assertEquals(11, meta.getColumnDisplaySize(1));
    assertEquals(12, meta.getColumnDisplaySize(2));

    rs = stat.executeQuery("explain select * from dual");
    meta = rs.getMetaData();
    assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(1));
    assertEquals(Integer.MAX_VALUE, meta.getPrecision(1));

    rs = stat.executeQuery("script");
    meta = rs.getMetaData();
    assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(1));
    assertEquals(Integer.MAX_VALUE, meta.getPrecision(1));

    rs = stat.executeQuery("select group_concat(table_name) " + "from information_schema.tables");
    rs.next();
    meta = rs.getMetaData();
    assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(1));
    assertEquals(Integer.MAX_VALUE, meta.getPrecision(1));
  }
Example #28
0
 // Checken ob ein User an der Reihe ist in diesem Spiel.
 private boolean isUserNextPlayer(int userID, int gameID) {
   if (!isUserInGame(userID, gameID)) {
     return false;
   }
   Connection con = DatabaseConnector.getConnection();
   Statement stmt;
   ResultSet rs;
   try {
     stmt = con.createStatement();
     // Checken ob Spieler am Spiel teilnimmt
     int nextStationType = -1, stationType = -2;
     rs = stmt.executeQuery("SELECT next_station_type FROM games WHERE game_id=" + gameID);
     if (rs.next()) {
       nextStationType = rs.getInt("next_station_type");
       con.commit();
     }
     rs =
         stmt.executeQuery(
             "SELECT station_type FROM stations NATURAL JOIN users WHERE game_id="
                 + gameID
                 + " AND user_id="
                 + userID);
     if (rs.next()) {
       stationType = rs.getInt("station_type");
       con.commit();
     }
     if (nextStationType == stationType) return true;
     else return false;
   } catch (SQLException e) {
     e.printStackTrace();
     return false;
   }
 }
  @Override
  public void pushToDB(Connection con) {
    Statement stmt;

    try {
      stmt = con.createStatement();
      StringBuilder sqlString = new StringBuilder("INSERT INTO question_response VALUES(null,");
      sqlString.append(statement);
      sqlString.append("\",\" ");
      for (String string : answers) {
        sqlString.append(string);
        sqlString.append(" &&& ");
      }
      sqlString.replace(sqlString.length() - 5, sqlString.length(), "");
      sqlString.append("\" ");

      System.out.print(sqlString.toString());
      ResultSet resultSet = stmt.executeQuery(sqlString.toString());

      stmt = con.createStatement();
      sqlString = new StringBuilder("SELECT * FROM question_response WHERE statement=\"");
      sqlString.append(statement);
      sqlString.append("\" ");

      System.out.print(sqlString.toString());
      resultSet = stmt.executeQuery(sqlString.toString());

      while (resultSet.next()) {
        this.setqID(resultSet.getInt("question_id")); // will always be the last one
      }
    } catch (Exception e) {

    }
  }
  public int miseAJourStatutCommande() {

    ConnexionBD bd = new ConnexionBD();
    Statement st = bd.createStatement();
    int rs = 0;
    try {

      // on regarde combie de colis à la commande correspondant au colis controle
      ResultSet i =
          st.executeQuery(
              "SELECT id_commande, count(id_colis) FROM "
                  + Constantes.base_colis
                  + " WHERE id_colis = "
                  + id
                  + " GROUP BY id_commande;");

      if (i.next()) {

        int numCommande = i.getInt(1);
        int nbColis = i.getInt(2);

        // i.close();

        // on récupère les colis donc le statut à été controle, cad passer par la douane et approuvé
        ResultSet g =
            st.executeQuery(
                " SELECT id_colis FROM "
                    + Constantes.base_colis
                    + " WHERE id_commande = "
                    + numCommande
                    + " AND statut IN ('controle', 'emballe') GROUP BY id_colis;");
        if (g.next()) {
          g.last();
          int nombreColis = g.getRow();
          g.beforeFirst();
          // si le nombre de colis de la commande est égal au nombre de colis controle de la
          // commande, on peut changer le statut de la commande a expedie
          if (nbColis == nombreColis) {
            rs =
                st.executeUpdate(
                    "UPDATE "
                        + Constantes.base_commande
                        + " SET statut = 'expediee' WHERE id_commande = "
                        + numCommande
                        + ";");
          }

        } else {
          System.out.println("NOOOON");
        }

      } else {
        System.out.println("RIEEEEN");
      }

    } catch (SQLException e) {
      e.printStackTrace();
    }
    return rs;
  }