下面列出了java.sql.PreparedStatement#clearBatch ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
@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;
}
}
/**
* 写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 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;
}
@Override
protected void onEnd(PreparedStatement statement) throws SQLException {
int[] updateCount = statement.executeBatch();
statement.clearBatch();
}
/**
* @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;
}
/**
* @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;
}
/**
* 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();
}
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();
}
/**
* 批量添加扩展属性
* @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;
}
/**
* @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);
}
}
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();
}
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();
}
/**
* 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 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;
}