Java Code Examples for java.sql.PreparedStatement#clearBatch()

@Test
public void testBatch() throws SQLException, MalformedURLException {
    PreparedStatement preparedStatement = multiHostConnection.prepareStatement("UPDATE test SET a = ? WHERE b = ?");
    preparedStatement.setShort(1, (short) 12);
    preparedStatement.setTime(2, new Time(System.currentTimeMillis()));
    preparedStatement.addBatch();
    int[] resultSet = preparedStatement.executeBatch();
    preparedStatement.clearBatch();

    verify(mysqlPreparedStatement).executeBatch();
    verify(mysqlPreparedStatement).addBatch();
    verify(mysqlPreparedStatement).clearBatch();

    TraceSegment traceSegment = segmentStorage.getTraceSegments().get(0);
    List<AbstractTracingSpan> spans = SegmentHelper.getSpans(traceSegment);
    assertThat(spans.size(), is(1));
    assertDBSpan(spans.get(0), "Mysql/JDBI/PreparedStatement/executeBatch", "");

}
 

private static void deleteResponses(final PreparedStatement stmtDelResponse, final List<String> responseIds2delete, final int batchSize) throws SQLException {
    int count = 0;
    for (String id : responseIds2delete) {
        stmtDelResponse.setString(1, id);
        stmtDelResponse.addBatch();
        count++;
        if (count == batchSize) {
            stmtDelResponse.executeBatch();
            stmtDelResponse.clearBatch();
            count = 0;
        }
    }
    if (count > 0) {
        stmtDelResponse.executeBatch();
        stmtDelResponse.clearBatch();
        count = 0;
    }
}
 

private long insert(Connection con, List<Map<String, String>> list)
		throws SQLException {
	PreparedStatement ps;

	String sql = "insert into travelrecord (id,user_id,traveldate,fee,days) values(?,?,?,?,?)";
	ps = con.prepareStatement(sql);
	for (Map<String, String> map : list) {
		ps.setLong(1, Long.parseLong(map.get("id")));
		ps.setString(2, (String) map.get("user_id"));
		ps.setString(3, (String) map.get("traveldate"));
		ps.setString(4, (String) map.get("fee"));
		ps.setString(5, (String) map.get("days"));
		ps.addBatch();
	}
	ps.executeBatch();
	con.commit();
	ps.clearBatch();
	ps.close();
	return list.size();
}
 

private int insert(Connection con, List<Map<String, String>> list)
        throws SQLException {
    PreparedStatement ps;

    String sql = "insert into travelrecord (user_id,traveldate,fee,days) values(?,?,?,?,?)";
    ps = con.prepareStatement(sql);
    for (Map<String, String> map : list) {
        //ps.setLong(1, Long.parseLong(map.get("id")));
        ps.setString(1, (String) map.get("user_id"));
        ps.setString(2, (String) map.get("traveldate"));
        ps.setString(3, (String) map.get("fee"));
        ps.setString(4, (String) map.get("days"));
        ps.addBatch();

    }
    ps.executeBatch();
    con.commit();
    ps.clearBatch();
    ps.close();
    return list.size();
}
 

/**
 * 写BAttribute的内容
 * @param attrs
 * @param parentName
 * @param parentId
 * @throws SQLException
 *             ;
 */
public void insertBAttribute(Map<String, String> attrs, String parentName, int parentId) throws SQLException {
	if (attrs != null) {
		PreparedStatement stmt = null;
		String sql = dbConfig.getOperateDbSQL("insert-battribute");
		Iterator<Entry<String, String>> iter = attrs.entrySet().iterator();
		try {
			while (iter.hasNext()) {
				Entry<String, String> entry = iter.next();
				String attrName = entry.getKey();
				String attrValue = entry.getValue();
				stmt = conn.prepareStatement(sql);
				stmt.setInt(1, parentId);
				stmt.setString(2, attrName);
				stmt.setString(3, attrValue);
				stmt.setString(4, parentName);
				stmt.addBatch();
			}
			stmt.executeBatch();
			stmt.clearBatch();
		} finally {
			if (stmt != null) {
				stmt.close();
			}
		}
	}
}
 

/** Submit and clear the batch, or roll back on error */
private void checkBatchExecution(final PreparedStatement insert) throws Exception
{
    try
    {   // Try to perform the inserts
        // In principle this could return update counts for
        // each batched insert, but Oracle 10g and 11g just throw
        // an exception
        insert.executeBatch();
        connection.commit();
    }
    catch (final Exception ex)
    {
        try
        {
            // On failure, roll back.
            // With Oracle 10g, the BatchUpdateException doesn't
            // indicate which of the batched commands faulted...
            insert.clearBatch();
            // Still: Commit what's committable.
            // Unfortunately no way to know what failed,
            // and no way to re-submit the 'remaining' inserts.
            connection.commit();
        }
        catch (Exception nested)
        {
            logger.log(Level.WARNING, "clearBatch(), commit() error after batch issue", nested);
        }
        throw ex;
    }
}
 

public int fillAndExecutePreparedInsertStatements(List pstmts, List stmts, int sid)
    throws SQLException {
  int numInstrumentsPerSector = SectorPrms.getNumInstrumentsPerSector();
  PreparedStatement pstmt = (PreparedStatement) pstmts.get(0);
  String stmt = (String)stmts.get(0);
  int results = 0;
  for (int i = 0; i < numInstrumentsPerSector; i++) {
    int id = sid * numInstrumentsPerSector + i; // unique
    String typeName = Instrument.getInstrument(id);
    pstmt.setString(1, typeName);
    pstmt.setInt(2, sid);
    //pstmt.setString(3, typeName);
    if (logQueries) {
      Log.getLogWriter().info("EXECUTING: " + stmt + " with id=" + typeName
         + " sector_id=" + sid);
    }
    results += positionQueryFactory.fillAndExecutePreparedInsertStatements(
        (List)pstmts.get(1), (List)stmts.get(1), id);
    pstmt.addBatch();
    results++;
  }
  pstmt.executeBatch();
  pstmt.clearBatch();
  Connection c = pstmt.getConnection();
  if (c.getTransactionIsolation() != Connection.TRANSACTION_NONE
      && SectorPrms.commitBatches()) {
    c.commit();
  }
  return results;
}
 

/**
 * 写BAttribute的内容
 * @param attrs
 * @param parentName
 * @param parentId
 * @throws SQLException
 *             ;
 */
public void insertBAttribute(Map<String, String> attrs, String parentName, int parentId) throws SQLException {
	if (attrs != null) {
		PreparedStatement stmt = null;
		String sql = dbConfig.getOperateDbSQL("insert-battribute");
		Iterator<Entry<String, String>> iter = attrs.entrySet().iterator();
		try {
			while (iter.hasNext()) {
				Entry<String, String> entry = iter.next();
				String attrName = entry.getKey();
				String attrValue = entry.getValue();
				stmt = conn.prepareStatement(sql);
				stmt.setInt(1, parentId);
				stmt.setString(2, attrName);
				stmt.setString(3, attrValue);
				stmt.setString(4, parentName);
				stmt.addBatch();
			}
			stmt.executeBatch();
			stmt.clearBatch();
		} finally {
			if (stmt != null) {
				stmt.close();
			}
		}
	}
}
 

private void generate_SECT_CHANNEL_DATA(Connection conn) throws SQLException {
  if (SECT_CHANNEL_DATA_PS == null) {
    SECT_CHANNEL_DATA_PS = conn.prepareStatement(SECT_CHANNEL_DATA_SQL);
  }
  PreparedStatement stmt = SECT_CHANNEL_DATA_PS;

  int batchSize = UseCase1Prms.getBatchSize();
  int numSectChannelDataRows = UseCase1Prms.getNumSectChannelDataRows();

  String tgname = RemoteTestModule.getCurrentThread().getThreadGroupName();
  int tgthreads = TestConfig.getInstance().getThreadGroup(tgname).getTotalThreads();
  int totalBatches = numSectChannelDataRows/(batchSize * tgthreads);
  Log.getLogWriter().info("Generating " + (totalBatches * batchSize)
                    + " rows of data using " + tgthreads + " threads");
  if (totalBatches <= 0) {
    String s = "Cannot generate data with only " + (totalBatches * batchSize)
             + " rows. Fix" +
               " UseCase1Prms.numSectChannelDataRows=" + numSectChannelDataRows +
               " UseCase1Prms.batchSize=" + batchSize +
               " this.numThreads=" + this.numThreads;
    throw new QueryPerfException(s);
  }
  for (int i = 0; i < totalBatches; i++) {
    long start = this.useCase1stats.startStmt(Stmt.insertSectChannelData);
    for (int j = 0; j < batchSize; j++) {
      fillSectChannelDataStmt(conn, stmt);
      stmt.addBatch();
    }
    stmt.executeBatch();
    conn.commit();
    stmt.clearBatch();
    this.useCase1stats.endStmt(Stmt.insertSectChannelData, start, batchSize, null);
  }
  stmt.close();
}
 

public int fillAndExecutePreparedInsertStatements(List pstmts, List stmts, int iid)
    throws SQLException {

  String instrument = Instrument.getInstrument(iid);
  int numPositionsPerInstrument = SectorPrms.getNumPositionsPerInstrument();
  int numBookValues = SectorPrms.getNumBookValues();
  PreparedStatement pstmt = (PreparedStatement) pstmts.get(0);
  String stmt = (String)stmts.get(0);
  int synthetic = 0;
  int results = 0;
  for (int i = 0; i < numPositionsPerInstrument; i++) {
    int id = iid * numPositionsPerInstrument + i; // unique
    int amount = id;
    int bookId = id % numBookValues;
    String owner = Position.getOwner(id);
    synthetic = 1 - synthetic;
    String symbol = Position.getSymbol(id);
    pstmt.setInt(1, id);
    pstmt.setInt(2, bookId);
    pstmt.setString(3, instrument);
    pstmt.setInt(4, amount);
    pstmt.setInt(5, synthetic);
    pstmt.setString(6, owner);
    pstmt.setString(7, symbol);
    if (logQueries) {
      Log.getLogWriter().info("EXECUTING: " + stmt + " with id=" + id
         + " book_id=" + bookId + " instrument=" + instrument
         + " amount=" + amount + " synthetic=" + synthetic
         + " owner=" + owner + " symbol=" + symbol);
    }
    results += riskQueryFactory.fillAndExecutePreparedInsertStatements((List) pstmts
        .get(1), (List)stmts.get(1), id);
    pstmt.addBatch();
    results++;
  }
  pstmt.executeBatch();
  pstmt.clearBatch();
  List rpstmts = (List)pstmts.get(1);
  PreparedStatement rpstmt = (PreparedStatement)rpstmts.get(rpstmts.size() - 1);
  rpstmt.executeBatch();
  rpstmt.clearBatch();
  Connection c = pstmt.getConnection();
  if (c.getTransactionIsolation() != Connection.TRANSACTION_NONE
      && SectorPrms.commitBatches()) {
    c.commit();
  }
  return results;
}
 

@Override
protected void onEnd(PreparedStatement statement) throws SQLException {
    int[] updateCount = statement.executeBatch();
    statement.clearBatch();
}
 

/**
 * TABLE XML_DOC_1
 *   XML_DOC_ID_NBR DECIMAL(19) NOT NULL,
 *   STRUCTURE_ID_NBR DECIMAL(22) NOT NULL,
 *   CREATE_MINT_CD CHAR(1) NOT NULL,
 *   MSG_PAYLOAD_QTY DECIMAL(22) NOT NULL,
 *   MSG_PAYLOAD1_IMG BLOB(2000) NOT NULL,
 *   MSG_PAYLOAD2_IMG BLOB(2000),
 *   MSG_PAYLOAD_SIZE_NBR DECIMAL(22),
 *   MSG_PURGE_DT DATE,
 *   DELETED_FLG CHAR(1) NOT NULL,
 *   LAST_UPDATE_SYSTEM_NM VARCHAR(30),
 *   LAST_UPDATE_TMSTP TIMESTAMP NOT NULL,
 *   MSG_MAJOR_VERSION_NBR DECIMAL(22),
 *   MSG_MINOR_VERSION_NBR DECIMAL(22),
 *   OPT_LOCK_TOKEN_NBR DECIMAL(22) DEFAULT 1,
 *   PRESET_DICTIONARY_ID_NBR DECIMAL(22) DEFAULT 0 NOT NULL
 */
private void useCase2LoadData() throws SQLException {
  long currentTime = System.currentTimeMillis();
  Date date = new Date(currentTime);
  Timestamp timestamp = new Timestamp(currentTime);
  SerialBlob blob = new SerialBlob("12345678452984560289456029847609487234785012934857109348156034650234560897628900985760289207856027895602785608560786085602857602985760206106110476191087345601456105610478568347562686289765927868972691785634975604562056104762978679451308956205620437861508561034756028475180756917856190348756012876510871789546913485620720476107856479238579385923847934".getBytes(Charset.forName("UTF-8")));

  String stmt = "INSERT INTO XML_DOC_1 ("
              + " LAST_UPDATE_SYSTEM_NM,"    //  1
              + " XML_DOC_ID_NBR,"           //  2
              + " MSG_PAYLOAD1_IMG,"         //  3
              + " MSG_PAYLOAD_QTY,"          //  4
              + " MSG_MINOR_VERSION_NBR,"    //  5
              + " PRESET_DICTIONARY_ID_NBR," //  6
              + " CREATE_MINT_CD,"           //  7
              + " MSG_PAYLOAD_SIZE_NBR,"     //  8
              + " DELETED_FLG,"              //  9
              + " STRUCTURE_ID_NBR,"         // 10
              + " MSG_PURGE_DT,"             // 11
              + " OPT_LOCK_TOKEN_NBR,"       // 12
              + " MSG_MAJOR_VERSION_NBR,"    // 13
              + " LAST_UPDATE_TMSTP"         // 14
              + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
  PreparedStatement pstmt = this.connection.prepareStatement(stmt);

  int numBatches = POCPrms.getNumBatches();
  int batchSize = POCPrms.getBatchSize();

  Log.getLogWriter().info("Generating " + numBatches + " batches of " + batchSize);
  for (int i = 0; i < numBatches; i++) {
    for (int j = 0; j < batchSize; j++) {
      pstmt.setString(1, USECASE2_APP);
      pstmt.setInt(2, i * batchSize + j);
      pstmt.setBlob(3, blob);
      pstmt.setBigDecimal(4, BIG_DECIMAL_1);
      pstmt.setBigDecimal(5, BIG_DECIMAL_1);
      pstmt.setBigDecimal(6, BIG_DECIMAL_1);
      pstmt.setString(7, N_FLAG);
      pstmt.setBigDecimal(8, BIG_DECIMAL_1);
      pstmt.setString(9, N_FLAG);
      pstmt.setBigDecimal(10, BIG_DECIMAL_9);
      pstmt.setDate(11, date);
      pstmt.setBigDecimal(12, BIG_DECIMAL_1);
      pstmt.setBigDecimal(13, BIG_DECIMAL_1);
      pstmt.setTimestamp(14, timestamp);
      pstmt.addBatch();
    }
    pstmt.executeBatch();
    pstmt.clearBatch();
  }
  pstmt.close();
}
 

public void testCombinationsOfClearPreparedStatBatch()
throws SQLException {

    int updateCount[];

    println("Positive Prepared Stat: add 3 statements, " +
        "clear batch and execute batch");
    PreparedStatement pStmt =
        prepareStatement("insert into t1 values(?)");
    pStmt.setInt(1, 1);
    pStmt.addBatch();
    pStmt.setInt(1, 2);
    pStmt.addBatch();
    pStmt.setInt(1, 3);
    pStmt.addBatch();
    pStmt.clearBatch();
    /* there were 0 statements in the batch,
     * update count length should be 0 */
    assertBatchUpdateCounts(new int[] {}, pStmt.executeBatch());

    println("Positive Prepared Stat: " +
        "add 3 statements, clear batch, add 3 and execute batch");
    pStmt.setInt(1, 1);
    pStmt.addBatch();
    pStmt.setInt(1, 2);
    pStmt.addBatch();
    pStmt.setInt(1, 3);
    pStmt.addBatch();
    pStmt.clearBatch();
    pStmt.setInt(1, 1);
    pStmt.addBatch();
    pStmt.setInt(1, 2);
    pStmt.addBatch();
    pStmt.setInt(1, 3);
    pStmt.addBatch();

    assertBatchUpdateCounts(new int[] {1,1,1}, pStmt.executeBatch());

    assertTableRowCount("T1", 3);

    pStmt.close();

    commit();
}
 

private void populateTablesAndDelete(boolean largeData) throws SQLException,
    InterruptedException {

  PreparedStatement customers = TestUtil.jdbcConn
      .prepareStatement("insert into customer values(?,?,?)");
  PreparedStatement neworders = TestUtil.jdbcConn
      .prepareStatement("insert into new_order values(?,?,?)");

  for (int w_id = 1; w_id <= 2; w_id++) {
    for (int d_id = 1; d_id <= 10; d_id++) {
      for (int c_id = 1; c_id <= (largeData ? 3000 : 100); c_id++) {
        customers.setInt(1, w_id);
        customers.setInt(2, d_id);
        customers.setInt(3, c_id);
        customers.addBatch();

        if (c_id > (largeData ? 2100 : 10)) {
          neworders.setInt(1, w_id);
          neworders.setInt(2, d_id);
          neworders.setInt(3, c_id);
          neworders.addBatch();
        }
      }
      getLogWriter().info("committing for d_id=" + d_id + " w_id=" + w_id);
      customers.executeBatch();
      neworders.executeBatch();
      customers.clearBatch();
      neworders.clearBatch();
    }
  }

  Thread terminals[] = new Thread[20];
  for (int i = terminals.length; i > 0; i--)
    terminals[i - 1] = new Thread(new ticket40864Query(i),
        "40864Query thread " + i);

  getLogWriter().info("spwaned all the threads");

  for (int i = terminals.length; i > 0; i--)
    terminals[i - 1].start();

  getLogWriter().info("started all the threads");

  for (int i = terminals.length; i > 0; i--)
    terminals[i - 1].join();
}
 

/**
 * @param conn
 * @param idPId
 * @param addedRoles
 * @param deletedRoles
 * @param renamedOldRoles
 * @param renamedNewRoles
 * @throws SQLException
 */
private void updateIdPRoles(Connection conn, int idPId, List<String> addedRoles,
                            List<String> deletedRoles, List<String> renamedOldRoles, List<String> renamedNewRoles)
        throws SQLException {

    PreparedStatement prepStmt1 = null;
    PreparedStatement prepStmt2 = null;
    PreparedStatement prepStmt3 = null;
    String sqlStmt = null;

    try {

        for (String deletedRole : deletedRoles) {
            sqlStmt = IdPManagementConstants.SQLQueries.DELETE_IDP_ROLES_SQL;
            prepStmt1 = conn.prepareStatement(sqlStmt);
            prepStmt1.setInt(1, idPId);
            prepStmt1.setString(2, deletedRole);
            prepStmt1.addBatch();
        }

        prepStmt1.executeBatch();

        for (String addedRole : addedRoles) {
            sqlStmt = IdPManagementConstants.SQLQueries.ADD_IDP_ROLES_SQL;
            prepStmt2 = conn.prepareStatement(sqlStmt);
            prepStmt2.setInt(1, idPId);
            prepStmt2.setString(2, addedRole);
            prepStmt2.addBatch();
        }

        prepStmt2.executeBatch();
        prepStmt2.clearParameters();
        prepStmt2.clearBatch();

        for (int i = 0; i < renamedOldRoles.size(); i++) {
            sqlStmt = IdPManagementConstants.SQLQueries.UPDATE_IDP_ROLES_SQL;
            prepStmt3 = conn.prepareStatement(sqlStmt);
            prepStmt3.setString(1, renamedNewRoles.get(i));
            prepStmt3.setInt(2, idPId);
            prepStmt3.setString(3, renamedOldRoles.get(i));
            prepStmt3.addBatch();
        }

        prepStmt3.executeBatch();

    } finally {
        IdentityDatabaseUtil.closeStatement(prepStmt3);
        IdentityDatabaseUtil.closeStatement(prepStmt2);
        IdentityDatabaseUtil.closeStatement(prepStmt1);
    }

}
 

/**
 * 批量添加扩展属性
 * @param parentName
 * @param parentIds
 * @param attName
 * @param attriValue
 * @param monitor
 * @return
 * @throws SQLException
 *             ;
 */
public boolean addExAttributes(String parentName, List<Integer> parentIds, String attName, String attriValue,
		IProgressMonitor monitor) throws SQLException {
	if (parentIds.isEmpty()) {
		return false;
	}
	int total = parentIds.size() / 1000;
	monitor.beginTask("", total == 0 ? 1 : total);
	// sql : INSERT INTO MEXTRA (PARENTNAME , PARENTID ,NTYPE ,NNAME,CONTENT) VALUES(?,?,'A',?,?)
	String insertPropSql = dbConfig.getOperateDbSQL("add-attribute");
	PreparedStatement prepareStatement = conn.prepareStatement(insertPropSql);
	int count = 0;
	int[] executeBatch = new int[0];
	boolean flag = false;
	for (int id : parentIds) {
		prepareStatement.setString(1, parentName);
		prepareStatement.setInt(2, id);
		prepareStatement.setString(3, attName);
		prepareStatement.setString(4, attriValue);
		count++;
		prepareStatement.addBatch();
		if (count % 1000 == 0) {
			executeBatch = prepareStatement.executeBatch();
			prepareStatement.clearBatch();
			if (executeBatch.length > 0) {
				flag = true;
			}
			count = 0;
			monitor.worked(1);
		}
	}
	if (count > 0) {
		executeBatch = prepareStatement.executeBatch();
		prepareStatement.clearBatch();
		if (executeBatch.length > 0) {
			flag = true;
		}
		monitor.worked(1);
	}
	prepareStatement.close();
	monitor.done();
	return flag;
}
 

private void insertDefaultData() throws Exception {
  PreparedStatement ps = getPreparedStatement("INSERT INTO testClob "
      + "(a, b, c) VALUES (?, ?, ?)");

  String clobValue = "";
  ps.setString(1, clobValue);
  ps.setInt(2, clobValue.length());
  ps.setLong(3, 0);
  ps.addBatch();
  clobValue = "you can lead a horse to water but you can't form it "
      + "into beverage";
  ps.setString(1, clobValue);
  ps.setInt(2, clobValue.length());
  ps.setLong(3, 0);
  ps.addBatch();
  clobValue = "a stitch in time says ouch";
  ps.setString(1, clobValue);
  ps.setInt(2, clobValue.length());
  ps.setLong(3, 0);
  ps.addBatch();
  clobValue = "here is a string with a return \n character";
  ps.setString(1, clobValue);
  ps.setInt(2, clobValue.length());
  ps.setLong(3, 0);
  ps.addBatch();

  ps.executeBatch();
  ps.clearBatch();

  insertLoopingAlphabetStreamData(ps, CharAlphabet.modernLatinLowercase(), 0);
  insertLoopingAlphabetStreamData(ps, CharAlphabet.modernLatinLowercase(), 56);
  insertLoopingAlphabetStreamData(ps, CharAlphabet.modernLatinLowercase(),
      5000);
  insertLoopingAlphabetStreamData(ps, CharAlphabet.modernLatinLowercase(),
      10000);
  insertLoopingAlphabetStreamData(ps, CharAlphabet.modernLatinLowercase(),
      300000);

  ps.setNull(1, Types.CLOB);
  ps.setInt(2, 0);
  ps.setLong(3, 0);
  ps.executeUpdate();

  ps.close();

  ps = getPreparedStatement("INSERT INTO testBlob (a, b, crc32) "
      + "VALUES (?, ?, ?)");

  byte[] blobValue = "".getBytes("US-ASCII");
  ps.setBytes(1, blobValue);
  ps.setInt(2, blobValue.length);
  ps.setLong(3, getStreamCheckSum(new ByteArrayInputStream(blobValue)));
  ps.addBatch();
  blobValue = ("you can lead a horse to water but you can't form it "
      + "into beverage").getBytes("US-ASCII");
  ps.setBytes(1, blobValue);
  ps.setInt(2, blobValue.length);
  ps.setLong(3, getStreamCheckSum(new ByteArrayInputStream(blobValue)));
  ps.addBatch();
  blobValue = "a stitch in time says ouch".getBytes("US-ASCII");
  ps.setBytes(1, blobValue);
  ps.setInt(2, blobValue.length);
  ps.setLong(3, getStreamCheckSum(new ByteArrayInputStream(blobValue)));
  ps.addBatch();
  blobValue = "here is a string with a return \n character"
      .getBytes("US-ASCII");
  ps.setBytes(1, blobValue);
  ps.setInt(2, blobValue.length);
  ps.setLong(3, getStreamCheckSum(new ByteArrayInputStream(blobValue)));
  ps.addBatch();

  ps.executeBatch();
  ps.clearBatch();

  insertLoopingAlphabetStreamData(ps, 0);
  insertLoopingAlphabetStreamData(ps, 56);
  insertLoopingAlphabetStreamData(ps, 5000);
  insertLoopingAlphabetStreamData(ps, 10000);
  insertLoopingAlphabetStreamData(ps, 300000);

  ps.setNull(1, Types.BLOB);
  ps.setInt(2, 0);
  ps.setNull(3, Types.BIGINT);
  ps.executeUpdate();

  ps.close();
}
 

/**
 * TABLE XML_DOC_1
 *   XML_DOC_ID_NBR DECIMAL(19) NOT NULL,
 *   STRUCTURE_ID_NBR DECIMAL(22) NOT NULL,
 *   CREATE_MINT_CD CHAR(1) NOT NULL,
 *   MSG_PAYLOAD_QTY DECIMAL(22) NOT NULL,
 *   MSG_PAYLOAD1_IMG BLOB(2000) NOT NULL,
 *   MSG_PAYLOAD2_IMG BLOB(2000),
 *   MSG_PAYLOAD_SIZE_NBR DECIMAL(22),
 *   MSG_PURGE_DT DATE,
 *   DELETED_FLG CHAR(1) NOT NULL,
 *   LAST_UPDATE_SYSTEM_NM VARCHAR(30),
 *   LAST_UPDATE_TMSTP TIMESTAMP NOT NULL,
 *   MSG_MAJOR_VERSION_NBR DECIMAL(22),
 *   MSG_MINOR_VERSION_NBR DECIMAL(22),
 *   OPT_LOCK_TOKEN_NBR DECIMAL(22) DEFAULT 1,
 *   PRESET_DICTIONARY_ID_NBR DECIMAL(22) DEFAULT 0 NOT NULL
 */
private void useCase2LoadData() throws SQLException {
  long currentTime = System.currentTimeMillis();
  Date date = new Date(currentTime);
  Timestamp timestamp = new Timestamp(currentTime);
  SerialBlob blob = new SerialBlob("12345678452984560289456029847609487234785012934857109348156034650234560897628900985760289207856027895602785608560786085602857602985760206106110476191087345601456105610478568347562686289765927868972691785634975604562056104762978679451308956205620437861508561034756028475180756917856190348756012876510871789546913485620720476107856479238579385923847934".getBytes(Charset.forName("UTF-8")));

  String stmt = "INSERT INTO XML_DOC_1 ("
              + " LAST_UPDATE_SYSTEM_NM,"    //  1
              + " XML_DOC_ID_NBR,"           //  2
              + " MSG_PAYLOAD1_IMG,"         //  3
              + " MSG_PAYLOAD_QTY,"          //  4
              + " MSG_MINOR_VERSION_NBR,"    //  5
              + " PRESET_DICTIONARY_ID_NBR," //  6
              + " CREATE_MINT_CD,"           //  7
              + " MSG_PAYLOAD_SIZE_NBR,"     //  8
              + " DELETED_FLG,"              //  9
              + " STRUCTURE_ID_NBR,"         // 10
              + " MSG_PURGE_DT,"             // 11
              + " OPT_LOCK_TOKEN_NBR,"       // 12
              + " MSG_MAJOR_VERSION_NBR,"    // 13
              + " LAST_UPDATE_TMSTP"         // 14
              + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
  PreparedStatement pstmt = this.connection.prepareStatement(stmt);

  int numBatches = POCPrms.getNumBatches();
  int batchSize = POCPrms.getBatchSize();

  Log.getLogWriter().info("Generating " + numBatches + " batches of " + batchSize);
  for (int i = 0; i < numBatches; i++) {
    for (int j = 0; j < batchSize; j++) {
      pstmt.setString(1, USECASE2_APP);
      pstmt.setInt(2, i * batchSize + j);
      pstmt.setBlob(3, blob);
      pstmt.setBigDecimal(4, BIG_DECIMAL_1);
      pstmt.setBigDecimal(5, BIG_DECIMAL_1);
      pstmt.setBigDecimal(6, BIG_DECIMAL_1);
      pstmt.setString(7, N_FLAG);
      pstmt.setBigDecimal(8, BIG_DECIMAL_1);
      pstmt.setString(9, N_FLAG);
      pstmt.setBigDecimal(10, BIG_DECIMAL_9);
      pstmt.setDate(11, date);
      pstmt.setBigDecimal(12, BIG_DECIMAL_1);
      pstmt.setBigDecimal(13, BIG_DECIMAL_1);
      pstmt.setTimestamp(14, timestamp);
      pstmt.addBatch();
    }
    pstmt.executeBatch();
    pstmt.clearBatch();
  }
  pstmt.close();
}
 

/**
 * @todo 针对sqlserver timestamp不能保存情况,提供特殊的批量执行方式，跳过类型为timestamp的值
 * @param updateSql
 * @param rowDatas
 * @param batchSize
 * @param updateTypes
 * @param autoCommit
 * @param conn
 * @param dbType
 * @return
 * @throws Exception
 */
private static Long batchUpdateByJdbc(final String updateSql, final List<Object[]> rowDatas, final int batchSize,
		final Integer[] updateTypes, final Boolean autoCommit, final Connection conn, final Integer dbType)
		throws Exception {
	if (rowDatas == null) {
		logger.error("batchUpdateByJdbc:{} 传递的数据为空!", updateSql);
		return 0L;
	}
	PreparedStatement pst = null;
	long updateCount = 0;
	try {
		boolean hasSetAutoCommit = false;
		// 是否自动提交
		if (autoCommit != null && !autoCommit == conn.getAutoCommit()) {
			conn.setAutoCommit(autoCommit);
			hasSetAutoCommit = true;
		}
		pst = conn.prepareStatement(updateSql);
		int totalRows = rowDatas.size();
		boolean useBatch = (totalRows > 1) ? true : false;
		Object[] rowData;
		// 批处理计数器
		int meter = 0;
		int pstIndex = 0;
		for (int i = 0; i < totalRows; i++) {
			rowData = rowDatas.get(i);
			if (rowData != null) {
				// 使用对象properties方式传值
				pstIndex = 0;
				for (int j = 0, n = rowData.length; j < n; j++) {
					// 类型为timestamp 则跳过
					if (!updateTypes[j].equals(java.sql.Types.TIMESTAMP)) {
						SqlUtil.setParamValue(conn, dbType, pst, rowData[j], updateTypes[j], pstIndex + 1);
						pstIndex++;
					}
				}
				meter++;
				if (useBatch) {
					pst.addBatch();
					if ((meter % batchSize) == 0 || i + 1 == totalRows) {
						int[] updateRows = pst.executeBatch();
						for (int t : updateRows) {
							updateCount = updateCount + ((t > 0) ? t : 0);
						}
						pst.clearBatch();
					}
				} else {
					pst.execute();
					updateCount = updateCount + ((pst.getUpdateCount() > 0) ? pst.getUpdateCount() : 0);
				}
			}
		}
		if (hasSetAutoCommit) {
			conn.setAutoCommit(!autoCommit);
		}
	} catch (Exception e) {
		logger.error(e.getMessage(), e);
		e.printStackTrace();
		throw e;
	} finally {
		try {
			if (pst != null) {
				pst.close();
				pst = null;
			}
		} catch (SQLException se) {
			logger.error(se.getMessage(), se);
			se.printStackTrace();
		}
	}
	return updateCount;
}
 

/**
 * @todo 通过jdbc方式批量插入数据，一般提供给数据采集时或插入临时表使用
 * @param updateSql
 * @param rowDatas
 * @param fieldsType
 * @param fieldsDefaultValue
 * @param fieldsNullable
 * @param batchSize
 * @param autoCommit
 * @param conn
 * @param dbType
 * @return
 * @throws Exception
 */
private static Long batchUpdateByJdbc(final String updateSql, final List<Object[]> rowDatas,
		final Integer[] fieldsType, final String[] fieldsDefaultValue, final Boolean[] fieldsNullable,
		final int batchSize, final Boolean autoCommit, final Connection conn, final Integer dbType)
		throws Exception {
	if (rowDatas == null || rowDatas.isEmpty()) {
		logger.warn("batchUpdateByJdbc批量插入或修改数据库操作数据为空!");
		return 0L;
	}
	long updateCount = 0;
	PreparedStatement pst = null;
	// 判断是否通过default转换方式插入
	boolean supportDefaultValue = (fieldsDefaultValue != null && fieldsNullable != null) ? true : false;
	try {
		boolean hasSetAutoCommit = false;
		// 是否自动提交
		if (autoCommit != null && autoCommit.booleanValue() != conn.getAutoCommit()) {
			conn.setAutoCommit(autoCommit.booleanValue());
			hasSetAutoCommit = true;
		}
		pst = conn.prepareStatement(updateSql);
		int totalRows = rowDatas.size();
		// 只有一条记录不采用批量
		boolean useBatch = (totalRows > 1) ? true : false;
		Object[] rowData;
		// 批处理计数器
		int meter = 0;
		for (int i = 0; i < totalRows; i++) {
			rowData = rowDatas.get(i);
			if (rowData != null) {
				// 使用对象properties方式传值
				for (int j = 0, n = rowData.length; j < n; j++) {
					if (supportDefaultValue) {
						setParamValue(conn, dbType, pst, rowData[j], fieldsType[j], fieldsNullable[j],
								fieldsDefaultValue[j], j + 1);
					} else {
						SqlUtil.setParamValue(conn, dbType, pst, rowData[j],
								fieldsType == null ? -1 : fieldsType[j], j + 1);
					}
				}
				meter++;
				// 批量
				if (useBatch) {
					pst.addBatch();
					// 判断是否是最后一条记录或到达批次量,执行批处理
					if ((meter % batchSize) == 0 || i + 1 == totalRows) {
						int[] updateRows = pst.executeBatch();
						for (int t : updateRows) {
							updateCount = updateCount + ((t > 0) ? t : 0);
						}
						pst.clearBatch();
					}
				} else {
					pst.execute();
					updateCount = updateCount + ((pst.getUpdateCount() > 0) ? pst.getUpdateCount() : 0);
				}
			}
		}
		// 恢复conn原始autoCommit默认值
		if (hasSetAutoCommit) {
			conn.setAutoCommit(!autoCommit);
		}
	} catch (Exception e) {
		logger.error(e.getMessage(), e);
		throw e;
	} finally {
		try {
			if (pst != null) {
				pst.close();
				pst = null;
			}
		} catch (SQLException se) {
			logger.error(se.getMessage(), se);
		}
	}
	return updateCount;
}