Пример #1
0
  public static void main(String[] args) {
    // by default each and every query is auto-committed
    Connection con = MyConnection.getConnection();
    Statement ps = null;
    ResultSet rs = null;
    String insertQuery1 = "insert into test.emp values(124,'Neeta',56756.90)";
    String insertQuery2 = "insert into test.emp values(125,'Meeta',23534.90)";
    String insertQuery3 = "insert into test.emp values(123,'Reeta',37547.90)";
    try {
      // enablr transactions
      con.setAutoCommit(false); // here tx boundary begins
      ps = con.createStatement();
      ps.addBatch(insertQuery1);
      ps.addBatch(insertQuery2);
      ps.addBatch(insertQuery3);
      int rec[] = ps.executeBatch();
      System.out.println("batch Executed!!");
      con.commit(); // tx done or completed

    } catch (Exception e) {
      e.printStackTrace();
    } // end of catch
    finally {
      try {
        if (ps != null) ps.close();
        if (con != null) con.close();
      } catch (Exception e2) {
        e2.printStackTrace();
      } // end of catch
    } // end of finally
  } // end of main
Пример #2
0
  private void updateCardToDecks(final List<Deck> decks)
      throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
    final Map<Integer, CardApearancesInDecks> cardDBIdToNewApearances =
        createCardToNewApearances(decks);

    try (final Statement statement = connection.createStatement()) {
      for (final Map.Entry<Integer, CardApearancesInDecks> entry :
          cardDBIdToNewApearances.entrySet()) {
        final Integer cardDBId = entry.getKey();
        final CardApearancesInDecks newAppearances = entry.getValue();

        final String updateMainboard =
            String.format(
                "UPDATE cdi_card_decks_index SET cdi_mainboard_appearances = CONCAT(IF(cdi_mainboard_appearances IS NULL, '', cdi_mainboard_appearances), \"%s\") WHERE cdi_card_id=%d",
                newAppearances.getMainboardAppearancesString(), cardDBId);
        statement.addBatch(updateMainboard);
        if (newAppearances.sideboardApearences != null
            && newAppearances.sideboardApearences.size() > 0) {
          final String updateSideboard =
              String.format(
                  "UPDATE cdi_card_decks_index SET cdi_sideboard_appearances = CONCAT(IF(cdi_sideboard_appearances IS NULL, '', cdi_sideboard_appearances), \"%s\") WHERE cdi_card_id=%d",
                  newAppearances.getSideboardAppearancesString(), cardDBId);
          statement.addBatch(updateSideboard);
        }
      }
      final int[] updatedRows = statement.executeBatch();
    }
  }
Пример #3
0
 @Test
 public void testAddBatchSql() throws SQLException {
   TGroupConnection conn = null;
   Statement stat = null;
   try {
     conn = tgds.getConnection();
     stat = conn.createStatement();
     stat.addBatch("update t set name = 'newName' ");
     stat.addBatch("update t set type = 2 ");
     int[] affectedRow = stat.executeBatch();
     System.out.println(Arrays.toString(affectedRow));
     MockDataSource.showTrace();
     Assert.assertTrue(MockDataSource.hasMethod("db", "db1", "executeBatch"));
   } finally {
     if (conn != null) {
       try {
         conn.close();
       } catch (SQLException e) {
       }
       if (stat != null) {
         try {
           stat.close();
         } catch (SQLException e) {
         }
       }
     }
   }
 }
Пример #4
0
 private void testExecuteBatch04() throws SQLException {
   trace("testExecuteBatch04");
   int i = 0;
   int[] retValue = {0, 0, 0};
   int updCountLength = 0;
   String sUpdCoffee = COFFEE_UPDATE1;
   String sInsCoffee = COFFEE_INSERT1;
   String sDelCoffee = COFFEE_DELETE1;
   stat.addBatch(sUpdCoffee);
   stat.addBatch(sDelCoffee);
   stat.addBatch(sInsCoffee);
   int[] updateCount = stat.executeBatch();
   updCountLength = updateCount.length;
   trace("Successfully Updated");
   trace("updateCount Length:" + updCountLength);
   if (updCountLength != 3) {
     fail("executeBatch");
   } else {
     trace("executeBatch executes the Batch of SQL statements");
   }
   String query1 = "SELECT COUNT(*) FROM TEST WHERE TYPE_ID=1";
   ResultSet rs = stat.executeQuery(query1);
   rs.next();
   retValue[i++] = rs.getInt(1);
   // 1 as Delete Statement will delete only one row
   retValue[i++] = 1;
   // 1 as Insert Statement will insert only one row
   retValue[i++] = 1;
   for (int j = 0; j < updateCount.length; j++) {
     trace("Update Count : " + updateCount[j]);
     if (updateCount[j] != retValue[j]) {
       fail("j=" + j + " right:" + retValue[j]);
     }
   }
 }
Пример #5
0
 private void testAddBatch02() throws SQLException {
   trace("testAddBatch02");
   int i = 0;
   int[] retValue = {0, 0, 0};
   int updCountLength = 0;
   String sUpdCoffee = COFFEE_UPDATE1;
   String sDelCoffee = COFFEE_DELETE1;
   String sInsCoffee = COFFEE_INSERT1;
   stat.addBatch(sUpdCoffee);
   stat.addBatch(sDelCoffee);
   stat.addBatch(sInsCoffee);
   int[] updateCount = stat.executeBatch();
   updCountLength = updateCount.length;
   trace("updateCount Length:" + updCountLength);
   assertEquals(3, updCountLength);
   String query1 = "SELECT COUNT(*) FROM TEST WHERE TYPE_ID=1";
   ResultSet rs = stat.executeQuery(query1);
   rs.next();
   retValue[i++] = rs.getInt(1);
   // 1 as delete Statement will delete only one row
   retValue[i++] = 1;
   // 1 as insert Statement will insert only one row
   retValue[i++] = 1;
   trace("ReturnValue count : " + retValue.length);
   for (int j = 0; j < updateCount.length; j++) {
     trace("Update Count:" + updateCount[j]);
     trace("Returned Value : " + retValue[j]);
     assertEquals("j:" + j, retValue[j], updateCount[j]);
   }
 }
Пример #6
0
  public void batchUpdate() throws SQLException {

    Statement stmt = null;
    try {

      this.con.setAutoCommit(false);
      stmt = this.con.createStatement();

      stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Amaretto', 49, 9.99, 0, 0)");
      stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Hazelnut', 49, 9.99, 0, 0)");
      stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Amaretto_decaf', 49, 10.99, 0, 0)");
      stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Hazelnut_decaf', 49, 10.99, 0, 0)");

      int[] updateCounts = stmt.executeBatch();
      this.con.commit();

    } catch (BatchUpdateException b) {
      JDBCTutorialUtilities.printBatchUpdateException(b);
    } catch (SQLException ex) {
      JDBCTutorialUtilities.printSQLException(ex);
    } finally {
      if (stmt != null) {
        stmt.close();
      }
      this.con.setAutoCommit(true);
    }
  }
Пример #7
0
  @Override
  protected void addDatabaseCreateStatementsToBatch(Statement statement) throws SQLException {
    LOGGER.info("setup raft log");

    statement.addBatch(
        "CREATE TABLE IF NOT EXISTS entries(log_index BIGINT PRIMARY KEY, term BIGINT NOT NULL, type TINYINT NOT NULL, data BLOB DEFAULT NULL)");
    statement.addBatch("CREATE INDEX IF NOT EXISTS entries_index ON entries(log_index DESC)");
  }
Пример #8
0
 /**
  * The first statement in this batch does not return an update count. SUCCESS_NO_INFO is expected
  * instead.
  */
 public void testNoCount() throws Exception {
   Statement stmt = con.createStatement();
   stmt.addBatch("create table #testbatch (id int, data varchar(255))");
   stmt.addBatch("insert into #testbatch VALUES(1, 'Test line')");
   int x[] = stmt.executeBatch();
   assertEquals(2, x.length);
   assertEquals(SUCCESS_NO_INFO, x[0]);
   assertEquals(1, x[1]);
 }
Пример #9
0
 private void testClearBatch02() throws SQLException {
   trace("testClearBatch02");
   String sUpdCoffee = COFFEE_UPDATE1;
   String sInsCoffee = COFFEE_INSERT1;
   String sDelCoffee = COFFEE_DELETE1;
   stat.addBatch(sUpdCoffee);
   stat.addBatch(sDelCoffee);
   stat.addBatch(sInsCoffee);
   stat.clearBatch();
   assertEquals(0, stat.executeBatch().length);
 }
Пример #10
0
 @Override
 public void traceMarker() throws Exception {
   Statement statement = connection.createStatement();
   try {
     statement.addBatch("insert into employee (name) values ('huckle')");
     statement.addBatch("insert into employee (name) values ('sally')");
     statement.executeBatch();
     // intentionally not calling statement.clearBatch()
     statement.addBatch("insert into employee (name) values ('lowly')");
     statement.addBatch("insert into employee (name) values ('pig will')");
     statement.executeBatch();
   } finally {
     statement.close();
   }
 }
Пример #11
0
 /** Test batched statements. */
 public void testBatch() throws Exception {
   Statement stmt = con.createStatement();
   stmt.execute("create table #testbatch (id int, data varchar(255))");
   for (int i = 0; i < 5; i++) {
     if (i == 2) {
       // This statement will generate an error
       stmt.addBatch("INSERT INTO #testbatch VALUES ('xx', 'This is line " + i + "')");
     } else {
       stmt.addBatch("INSERT INTO #testbatch VALUES (" + i + ", 'This is line " + i + "')");
     }
   }
   int x[];
   try {
     x = stmt.executeBatch();
   } catch (BatchUpdateException e) {
     x = e.getUpdateCounts();
   }
   if (con.getMetaData().getDatabaseProductName().toLowerCase().startsWith("microsoft")
       && ((JtdsDatabaseMetaData) con.getMetaData()).getDatabaseMajorVersion() > 6) {
     assertEquals(5, x.length);
     assertEquals(1, x[0]);
     assertEquals(1, x[1]);
     assertEquals(EXECUTE_FAILED, x[2]);
     assertEquals(EXECUTE_FAILED, x[3]);
     assertEquals(EXECUTE_FAILED, x[4]);
   } else {
     // Sybase or SQL Server 6.5 - Entire batch fails due to data conversion error
     // detected in statement 3
     assertEquals(5, x.length);
     assertEquals(EXECUTE_FAILED, x[0]);
     assertEquals(EXECUTE_FAILED, x[1]);
     assertEquals(EXECUTE_FAILED, x[2]);
     assertEquals(EXECUTE_FAILED, x[3]);
     assertEquals(EXECUTE_FAILED, x[4]);
   }
   // Now without errors
   stmt.execute("TRUNCATE TABLE #testbatch");
   for (int i = 0; i < 5; i++) {
     stmt.addBatch("INSERT INTO #testbatch VALUES (" + i + ", 'This is line " + i + "')");
   }
   x = stmt.executeBatch();
   assertEquals(5, x.length);
   assertEquals(1, x[0]);
   assertEquals(1, x[1]);
   assertEquals(1, x[2]);
   assertEquals(1, x[3]);
   assertEquals(1, x[4]);
 }
  private void uploadTempTable(
      Statement tempStmt, String tempTableName, List<String> patientNumList) throws SQLException {

    // smuniraju: Extended to include POSTGRES
    String serverType = dataSourceLookup.getServerType();
    String createTempInputListTable = "";
    if (serverType.equalsIgnoreCase(DAOFactoryHelper.SQLSERVER)) {
      createTempInputListTable = "create table " + tempTableName + " ( char_param1 varchar(100) )";
    } else if (serverType.equalsIgnoreCase(DAOFactoryHelper.POSTGRES)) {
      createTempInputListTable =
          "create temporary table " + tempTableName + " ( char_param1 varchar(100) )";
    }
    tempStmt.executeUpdate(createTempInputListTable);
    log.debug("created temp table" + tempTableName);
    // load to temp table
    // TempInputListInsert inputListInserter = new
    // TempInputListInsert(dataSource,TEMP_PDO_INPUTLIST_TABLE);
    // inputListInserter.setBatchSize(100);
    int i = 0;
    for (String singleValue : patientNumList) {
      tempStmt.addBatch("insert into " + tempTableName + " values ('" + singleValue + "' )");
      log.debug("adding batch" + singleValue);
      i++;
      if (i % 100 == 0) {
        log.debug("batch insert");
        tempStmt.executeBatch();
      }
    }
    log.debug("batch insert1");
    tempStmt.executeBatch();
  }
Пример #13
0
  private static CurVO executeBatchNoBindSQL(String sql, List voList, Connection connect)
      throws Exception {
    CurVO resultVO;
    if (voList == null || voList.size() == 0) {
      resultVO = new CurVO();
      resultVO.setBatchBesult(new int[0]);
    }
    Statement stm = null;

    try {
      stm = connect.createStatement();
      for (int i = 0; i < voList.size(); i++) {
        // P.print(DBAssistantTools.GetJointSQL(sql, voList.get(i),
        // null, false));
        /** ---------------------打印------------------------- */
        P.printSQL(sql, voList.get(i));
        /** ---------------------打印------------------------- */
        // 添加SQL字符串
        stm.addBatch(SqlTool.GetJointSQL(sql, voList.get(i), null, false));
      }
      // 该数组可查看每次SQL处理的整形结果
      resultVO = new CurVO();
      resultVO.setBatchBesult(stm.executeBatch());
      resultVO.setResult(stm.getUpdateCount());
      return resultVO;
    } catch (Exception e) {
      throw e;
    } finally {
      DBTool.close(null, stm, null);
    }
  }
Пример #14
0
  public boolean createAllTable() {
    Statement pstmt = null;
    boolean statusFinal = false;
    try {
      List<String> queries = getAllTabelQuaries();

      conn = DBConnection.dbConnection();
      pstmt = conn.createStatement();
      for (String query : queries) {
        pstmt.addBatch(query);
      }
      int[] status = pstmt.executeBatch();
      for (int s : status) {
        if (s >= 0) {
          statusFinal = true;
        }
        System.out.println(s);
      }

      return statusFinal;
    } catch (Exception e) {
      e.printStackTrace();
      return false;
    } finally {
      try {
        if (pstmt != null) pstmt.close();
        conn.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
Пример #15
0
 /**
  * Issue multiple SQL updates on a single JDBC Statement using batching. Will fall back to
  * separate updates on a single Statement if the JDBC driver does not support batch updates.
  *
  * @param sqls
  * @return
  * @throws SQLException
  */
 public int[] batchUpdate(final String[] sqls) {
   validateSqlParameter(sqls);
   Connection conn = null;
   Statement stmt = null;
   try {
     conn = getConnection();
     conn.setAutoCommit(false);
     applyQueryTimeOut(stmt);
     stmt = conn.createStatement();
     for (String sql : sqls) {
       if (showSql) {
         logger.info(getFinalSql(sql));
       }
       stmt.addBatch(sql);
     }
     int[] result = stmt.executeBatch();
     conn.commit();
     return result;
   } catch (SQLException e) {
     try {
       JdbcUtils.rollback(conn);
     } catch (Exception unused) {
     }
     throw new RuntimeException(e);
   } finally {
     JdbcUtils.closeQuietly(stmt);
     JdbcUtils.closeQuietly(conn);
   }
 }
Пример #16
0
  public boolean execSQL(String[] sqls) {
    Statement statement = null;
    Connection connection = null;
    try {
      connection = DBManager.getConnection();
      connection.setAutoCommit(false);
      statement = connection.createStatement();
      for (String s : sqls) {
        statement.addBatch(s);
      }
      statement.executeBatch();
      connection.commit();

      return true;
    } catch (Exception e) {
      // TODO: handle exception
      try {
        connection.rollback();
      } catch (SQLException e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
      }
      return false;
    } finally {

      try {
        connection.setAutoCommit(true);
        statement.close();
      } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
      DBManager.freeConnection(connection);
    }
  }
Пример #17
0
 private int[] batch(Config config, Connection conn, List<String> sqlList, int batchSize)
     throws SQLException {
   if (sqlList == null || sqlList.size() == 0)
     throw new IllegalArgumentException("The sqlList length must more than 0.");
   if (batchSize < 1) throw new IllegalArgumentException("The batchSize must more than 0.");
   int counter = 0;
   int pointer = 0;
   int size = sqlList.size();
   int[] result = new int[size];
   Statement st = conn.createStatement();
   for (int i = 0; i < size; i++) {
     st.addBatch(sqlList.get(i));
     if (++counter >= batchSize) {
       counter = 0;
       int[] r = st.executeBatch();
       conn.commit();
       for (int k = 0; k < r.length; k++) result[pointer++] = r[k];
     }
   }
   int[] r = st.executeBatch();
   conn.commit();
   for (int k = 0; k < r.length; k++) result[pointer++] = r[k];
   DbKit.closeQuietly(st);
   return result;
 }
Пример #18
0
  public void insertGrandprizePoint() throws IOException, SQLException {
    recordCounter = 0;
    System.out.println("Starting inserting Grand Prize point records into points' table..");
    LogLoader.setInfo(
        PointUpdaterDaemon.class.getSimpleName(),
        "[INSERT GRANDPRIZE] Started: " + getCurrentTimeStamp());
    // INSERT MONTHLY - Supposed to be executed on INIT w/CREATE TABLE function
    // Binding a single input stream CSV reader & its buffer
    CSVReader grandprizeReader =
        new CSVReader(
            new FileReader(workingDir + grandprizeFile), recSeparator, escChar, headerLine);
    String[] grandprize;
    String insertGrandprizeQuery;

    if (con == null) {
      db_object.openConnection();
      con = db_object.getConnection();
    }

    try {
      stm = con.createStatement();
      while ((grandprize = grandprizeReader.readNext()) != null) {
        // System.out.println(monthly[0] + monthly[1] + monthly[2]);
        insertGrandprizeQuery =
            "INSERT INTO tbl_points_grandprize (point_cardno, point_grandprize) VALUES ('"
                + grandprize[1].trim()
                + "', "
                + grandprize[2].trim()
                + ");";
        stm.addBatch(insertGrandprizeQuery);
        // stm 	     = con.createStatement();
        // affectedRow  = stm.executeUpdate(updateGrandprizeQuery);
        recordCounter++;
      }
      stm.executeBatch();
    } catch (FileNotFoundException e) {
      e.printStackTrace();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      stm.close();
      if (con != null) {
        try {
          db_object.closeConnection();
        } catch (SQLException e) {
          e.printStackTrace();
        } finally {
          con = null;
        }
      }
    }

    statGrandprize = true;
    System.out.println("Grand Prize Records Inserted:" + recordCounter);
    System.out.println("Grand Prize Records Finished Time: " + getCurrentTimeStamp());
    LogLoader.setInfo(
        PointUpdaterDaemon.class.getSimpleName(),
        "[INSERT GRANDPRIZE] Finished: " + getCurrentTimeStamp());
  }
  @Override
  public void insertBatchUser() throws SQLException {
    // TODO Auto-generated method stub

    Statement st = con.createStatement();
    String sql = "insert into User (username,password) values('abc','def')";
    st.addBatch(sql);
    String sql1 = "insert into User (username,password) values('abc11','def1')";
    st.addBatch(sql1);
    String sql2 = "insert into User (username,password) values('abc12','def2')";
    st.addBatch(sql2);

    int[] data = st.executeBatch();
    for (int count : data) {
      System.out.println(count);
    }
  }
Пример #20
0
 /**
  * This test should generate an error as the second statement in the batch returns a result set.
  */
 public void testResultSetError() throws Exception {
   Statement stmt = con.createStatement();
   stmt.addBatch("create table #testbatch (id int, data varchar(255))");
   stmt.addBatch("insert into #testbatch VALUES(1, 'Test line')");
   stmt.addBatch("SELECT 'This is an error'");
   int x[];
   try {
     x = stmt.executeBatch();
     fail("Expecting BatchUpdateException");
   } catch (BatchUpdateException e) {
     x = e.getUpdateCounts();
   }
   assertEquals(3, x.length);
   assertEquals(SUCCESS_NO_INFO, x[0]);
   assertEquals(1, x[1]);
   assertEquals(EXECUTE_FAILED, x[2]);
 }
Пример #21
0
 /** Test for bug [1180169] JDBC escapes not allowed with Sybase addBatch. */
 public void testBatchEsc() throws Exception {
   Statement stmt = con.createStatement();
   stmt.execute("CREATE TABLE #TESTBATCH (ts datetime)");
   stmt.addBatch("INSERT INTO #TESTBATCH VALUES ({ts '1999-01-01 23:50:00'})");
   int counts[] = stmt.executeBatch();
   assertEquals(1, counts[0]);
   stmt.close();
 }
Пример #22
0
 public void deleteRowsByColumns(List<Map<String, Object>> otherEntities, Statement statement)
     throws SQLException {
   if (otherEntities.size() == 0) {
     return;
   }
   List<String> columns = new ArrayList<String>(otherEntities.get(0).keySet());
   String deleteStatement = constructDeleteStatement(columns, otherEntities);
   statement.addBatch(deleteStatement);
 }
Пример #23
0
  public void updateMonthlyPoint() throws IOException, SQLException {
    recordCounter = 0;
    System.out.println("Starting updating monthly point records into points' table..");
    LogLoader.setInfo(
        PointUpdaterDaemon.class.getSimpleName(),
        "[UPDATE MONTHLY] Started: " + getCurrentTimeStamp());
    // UPDATE MONTHLY
    // Binding a single input stream CSV reader & its buffer
    CSVReader monthlyReader =
        new CSVReader(new FileReader(workingDir + monthlyFile), recSeparator, escChar, headerLine);
    String[] monthly;
    String updateMonthlyQuery;

    if (con == null) {
      db_object.openConnection();
      con = db_object.getConnection();
    }

    try {
      stm = con.createStatement();
      while ((monthly = monthlyReader.readNext()) != null) {
        // System.out.println(monthly[0] + monthly[1] + monthly[2]);
        updateMonthlyQuery =
            "UPDATE tbl_points SET point_monthly = "
                + monthly[2].trim()
                + " WHERE  point_cardno = '"
                + monthly[1].trim()
                + "';";
        stm.addBatch(updateMonthlyQuery);
        recordCounter++;
      }
      stm.executeBatch();
    } catch (FileNotFoundException e) {
      e.printStackTrace();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      stm.close();
      if (con != null) {
        try {
          db_object.closeConnection();
        } catch (SQLException e) {
          e.printStackTrace();
        } finally {
          con = null;
        }
      }
    }

    statMonthly = true;
    System.out.println("Updated Monthly Records:" + recordCounter);
    System.out.println("Monthly Records Update Finished Time: " + getCurrentTimeStamp());
    LogLoader.setInfo(
        PointUpdaterDaemon.class.getSimpleName(),
        "[UPDATE MONTHLY] Finished: " + getCurrentTimeStamp());
  }
Пример #24
0
  public void updateGrandprizePoint() throws IOException, SQLException {
    recordCounter = 0;
    System.out.println("Starting updating grand prize point records into points' table..");
    LogLoader.setInfo(
        PointUpdaterDaemon.class.getSimpleName(),
        "[UPDATE GRANDPRIZE] Started: " + getCurrentTimeStamp());
    // UPDATE GRANDPRIZE
    CSVReader grandprizeReader =
        new CSVReader(
            new FileReader(workingDir + grandprizeFile), recSeparator, escChar, headerLine);
    String[] grandprize;
    String updateGrandprizeQuery;

    if (con == null) {
      db_object.openConnection();
      con = db_object.getConnection();
    }

    try {
      stm = con.createStatement();
      while ((grandprize = grandprizeReader.readNext()) != null) {
        // System.out.println(grandprize[0] + grandprize[1] + grandprize[2]);
        updateGrandprizeQuery =
            "UPDATE tbl_points SET point_grandprize = "
                + grandprize[2].trim()
                + " WHERE point_cardno = '"
                + grandprize[1].trim()
                + "';";
        stm.addBatch(updateGrandprizeQuery);
        recordCounter++;
      }
      stm.executeBatch();
    } catch (FileNotFoundException e) {
      e.printStackTrace();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      stm.close();
      if (con != null) {
        try {
          db_object.closeConnection();
        } catch (SQLException e) {
          e.printStackTrace();
        } finally {
          con = null;
        }
      }
    }

    statGrandprize = true;
    System.out.println("Updated Grand Prize Records: " + recordCounter);
    System.out.println("Grand Prize Records Finished Time: " + getCurrentTimeStamp());
    LogLoader.setInfo(
        PointUpdaterDaemon.class.getSimpleName(),
        "[UPDATE GRANDPRIZE] Finished: " + getCurrentTimeStamp());
  }
Пример #25
0
  @Before
  public void setUp() throws SQLException {
    // Create a table in H2 and put some data in it for querying.
    connection = DriverManager.getConnection(h2ConnectionString, username, password);
    try (Statement statement = connection.createStatement()) {
      // Setup table
      statement.addBatch("CREATE SCHEMA IF NOT EXISTS TEST;");
      statement.addBatch(
          "CREATE TABLE IF NOT EXISTS TEST.TEST_TABLE "
              + "(P_ID INT NOT NULL PRIMARY KEY, FIRST_NAME "
              + "VARCHAR(255), LAST_NAME VARCHAR(255));");
      statement.addBatch("INSERT INTO TEST.TEST_TABLE VALUES (1, 'Adam', 'Kunicki')");
      statement.addBatch("INSERT INTO TEST.TEST_TABLE VALUES (2, 'Jon', 'Natkins')");
      statement.addBatch("INSERT INTO TEST.TEST_TABLE VALUES (3, 'Jon', 'Daulton')");
      statement.addBatch("INSERT INTO TEST.TEST_TABLE VALUES (4, 'Girish', 'Pancha')");

      statement.executeBatch();
    }
  }
  /* goodG2B1() - use goodsource and badsink by changing first IO.STATIC_FINAL_FIVE==5 to IO.STATIC_FINAL_FIVE!=5 */
  private void goodG2B1() throws Throwable {
    String data;
    if (IO.STATIC_FINAL_FIVE != 5) {
      /* INCIDENTAL: CWE 561 Dead Code, the code below will never run
       * but ensure data is inititialized before the Sink to avoid compiler errors */
      data = null;
    } else {

      /* FIX: Use a hardcoded string */
      data = "foo";
    }

    if (IO.STATIC_FINAL_FIVE == 5) {
      if (data != null) {
        String names[] = data.split("-");
        int successCount = 0;
        Connection dbConnection = null;
        Statement sqlStatement = null;
        try {
          dbConnection = IO.getDBConnection();
          sqlStatement = dbConnection.createStatement();
          for (int i = 0; i < names.length; i++) {
            /* POTENTIAL FLAW: data concatenated into SQL statement used in executeBatch(), which could result in SQL Injection */
            sqlStatement.addBatch(
                "update users set hitcount=hitcount+1 where name='" + names[i] + "'");
          }
          int resultsArray[] = sqlStatement.executeBatch();
          for (int i = 0; i < names.length; i++) {
            if (resultsArray[i] > 0) {
              successCount++;
            }
          }
          IO.writeLine("Succeeded in " + successCount + " out of " + names.length + " queries.");
        } catch (SQLException exceptSql) {
          IO.logger.log(Level.WARNING, "Error getting database connection", exceptSql);
        } finally {
          try {
            if (sqlStatement != null) {
              sqlStatement.close();
            }
          } catch (SQLException exceptSql) {
            IO.logger.log(Level.WARNING, "Error closing Statament", exceptSql);
          }

          try {
            if (dbConnection != null) {
              dbConnection.close();
            }
          } catch (SQLException exceptSql) {
            IO.logger.log(Level.WARNING, "Error closing Connection", exceptSql);
          }
        }
      }
    }
  }
  /* goodG2B() - use goodsource and badsink */
  private void goodG2B() throws Throwable {
    String dataCopy;
    {
      String data;

      /* FIX: Use a hardcoded string */
      data = "foo";

      dataCopy = data;
    }
    {
      String data = dataCopy;

      if (data != null) {
        String names[] = data.split("-");
        int successCount = 0;
        Connection dbConnection = null;
        Statement sqlStatement = null;
        try {
          dbConnection = IO.getDBConnection();
          sqlStatement = dbConnection.createStatement();
          for (int i = 0; i < names.length; i++) {
            /* POTENTIAL FLAW: data concatenated into SQL statement used in executeBatch(), which could result in SQL Injection */
            sqlStatement.addBatch(
                "update users set hitcount=hitcount+1 where name='" + names[i] + "'");
          }
          int resultsArray[] = sqlStatement.executeBatch();
          for (int i = 0; i < names.length; i++) {
            if (resultsArray[i] > 0) {
              successCount++;
            }
          }
          IO.writeLine("Succeeded in " + successCount + " out of " + names.length + " queries.");
        } catch (SQLException exceptSql) {
          IO.logger.log(Level.WARNING, "Error getting database connection", exceptSql);
        } finally {
          try {
            if (sqlStatement != null) {
              sqlStatement.close();
            }
          } catch (SQLException exceptSql) {
            IO.logger.log(Level.WARNING, "Error closing Statament", exceptSql);
          }

          try {
            if (dbConnection != null) {
              dbConnection.close();
            }
          } catch (SQLException exceptSql) {
            IO.logger.log(Level.WARNING, "Error closing Connection", exceptSql);
          }
        }
      }
    }
  }
Пример #28
0
  // 插入注册类型
  public int insertZclx(ArrayList al) {
    int result1 = 1;
    Connection conn = null;

    if (al != null && !al.isEmpty()) {
      try {
        conn = DBConnection.getConnection();
        if (conn != null) {
          conn.setAutoCommit(false);
          Statement stsm = conn.createStatement();

          Iterator it = al.iterator();
          while (it.hasNext()) {
            HashMap hm = (HashMap) it.next();
            String sql =
                "insert into SKQ_ZCLX(ZCLXBM,ZCLXMC,ZCLXJC,STATUS) values('"
                    + (String) hm.get("zclxbm")
                    + "','"
                    + (String) hm.get("zclxmc")
                    + "','"
                    + (String) hm.get("zclxjc")
                    + "',1)";
            stsm.addBatch(sql);
          }

          int[] result = stsm.executeBatch();
          conn.commit();
          for (int i = 0; i < result.length; i++) {
            if (result[i] == Statement.EXECUTE_FAILED || result[i] == Statement.SUCCESS_NO_INFO) {
              conn.rollback();
              stsm.close();
              result1 = -1;
            }
            break;
          }
        }
      } catch (SQLException e) {
        // TODO Auto-generated catch block
        result1 = -1;
        // e.getMessage();
        e.printStackTrace();
      } finally {
        if (conn != null) {
          try {
            conn.close();
          } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
          }
        }
      }
    }
    return result1;
  }
Пример #29
0
 public static void main(String[] args) throws Exception {
   Class.forName(DBDRIVER);
   Connection conn = DriverManager.getConnection(DBURL, USER, PASSWORD);
   Statement stmt = conn.createStatement();
   conn.setAutoCommit(false); // 取消自动提交
   try {
     stmt.addBatch("INSERT INTO member(mid,name) VALUES (myseq.nextval, '测试A')");
     stmt.addBatch("INSERT INTO member(mid,name) VALUES (myseq.nextval, '测试B')");
     stmt.addBatch("INSERT INTO member(mid,name) VALUES (myseq.nextval, '测试C')");
     stmt.addBatch("INSERT INTO member(mid,name) VALUES (myseq.nextval, '测试D')");
     stmt.addBatch("INSERT INTO member(mid,name) VALUES (myseq.nextval, '测试E')");
     int result[] = stmt.executeBatch(); // 执行处理
     System.out.println(Arrays.toString(result));
     conn.commit(); // 如果没有错误,进行提交
   } catch (Exception e) {
     e.printStackTrace();
     conn.rollback(); // 如果出现异常则进行回滚
   }
   conn.close();
 }
Пример #30
0
  public void executeBatch() {
    int statusCode[];
    init();
    statusCode = new int[0];
    try {
      int count = 0;
      for (int i = 0; i < sb.size(); i++) {
        count++;
        stmt.addBatch((new StringBuilder()).append(sb.get(i)).append(" ").toString());
        if (count > 30) {
          count = 0;
          statusCode = stmt.executeBatch();
          stmt.clearBatch();
        }
      }

      System.out.println((new StringBuilder("sql:")).append(sb).toString());
      if (count > 0) statusCode = stmt.executeBatch();
      message = (new StringBuilder(String.valueOf(statusCode.length))).toString();
      dbConn.commit();
      sb.clear();
      success = true;
      stmt.close();
      dbConn.close();
    } catch (Exception ex) {
      success = false;
      message = ex.getMessage();
      try {
        dbConn.rollback();
        stmt.close();
        dbConn.close();
      } catch (Exception e) {
        dbConn = null;
      }
      println(statusCode);
    }
    try {
      dbConn.close();
    } catch (Exception e) {
      dbConn = null;
    }

    try {
      dbConn.close();
    } catch (Exception e) {
      dbConn = null;
    }

    try {
      dbConn.close();
    } catch (Exception e) {
      dbConn = null;
    }
  }