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);
}
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;
}
}
@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();
statement.clearBatch();
statement.addBatch("insert into employee (name) values ('lowly')");
statement.addBatch("insert into employee (name) values ('pig will')");
statement.executeBatch();
} finally {
statement.close();
}
}
/** 检查是否支持批量更新 */
public static boolean checkBatchUpdate(Connection con) {
try {
return con.getMetaData().supportsBatchUpdates();
} catch (SQLException e) {
Statement statemnet = null;
try {
statemnet = con.createStatement();
statemnet.clearBatch();
return true;
} catch (SQLException ee) {
logger.warn("Current driver not support batch update,caused by: " + e);
return false;
} finally {
CloseUtil.close(statemnet);
statemnet = null;
}
}
}
/** @see java.sql.Statement#clearBatch() */
public void clearBatch() throws SQLException {
clusterCall("clearBatch", null, null, true);
st.clearBatch();
}
@Test(expected = SQLFeatureNotSupportedException.class)
public void assertClearBatch() throws SQLException {
actual.clearBatch();
}
@Override
public void clearBatch() throws SQLException {
stat.clearBatch();
}
/**
* Update the calculated values for vehicle sales in the Neptune Auto application database.
*
* @param sales list of vehicle sales
*/
public static void updateVehicleSalesCalculations(List<VehicleSale> sales) {
// Declare the JDBC objects.
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
String table = "VehicleSales";
int lastDealerID = 0;
try {
String sqlTemplate =
"update %s set "
+ "promotionId = %s, "
+ "totalSalesAmount = %s, "
+ "totalProfit = %s, "
+ "IsCalculated = 1 "
+ "WHERE employeeID = %s AND month = %s AND modelId = \'%s\' AND dealershipId = %s";
conn = ConnectionFactory.getConnection();
stmt = conn.createStatement();
int i = 0; // used for batching
for (VehicleSale sale : sales) {
String SQL =
String.format(
sqlTemplate,
table,
sale.getPromotionId(),
sale.getTotalSalesAmount(),
sale.getTotalProfit(),
sale.getEmployeeId(),
sale.getMonth(),
sale.getModelId(),
sale.getDealershipId());
stmt.addBatch(SQL);
lastDealerID = sale.dealershipId;
// process in batches of 500 records; this volume seemed about right after a few different
// tests
if (i % 2000 == 0 && i != 0) {
System.out.println(
"Beginning commit on dealership "
+ sale.dealershipId
+ i
+ " @ "
+ LocalDateTime.now().toString());
stmt.executeBatch();
stmt.clearBatch();
System.out.println("Completed @ " + LocalDateTime.now().toString());
}
i++;
}
System.out.println(
String.format(
"Beginning commit %s for dealership %s @ %s", i, lastDealerID, LocalDateTime.now()));
// execute final batch
stmt.executeBatch();
System.out.println("Completed @ " + LocalDateTime.now().toString());
}
// Handle errors.
catch (Exception e) {
e.printStackTrace();
} finally {
if (null != rs)
try {
rs.close();
} catch (Exception e) {
}
if (null != stmt)
try {
stmt.close();
} catch (Exception e) {
}
if (null != conn)
try {
conn.close();
} catch (Exception e) {
}
}
}
@Override
public void clearBatch() throws SQLException {
inner.clearBatch();
}
/** Load raw Salary Band data into the SalaryBand database table. */
public static void loadRaw() {
// Declare the JDBC objects.
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
String table = "SalaryBand";
try {
String sqlTemplate =
"insert into %s (bandID, salMin, salMax, paidTimeOff, bonusPercentage) "
+ "VALUES (\'%s\', %s, %s, %s, %s)";
List<SalaryBand> items = ParseSalaryBand.parse();
conn = ConnectionFactory.getConnection();
stmt = conn.createStatement();
// handle case where data exists
rs = stmt.executeQuery(String.format("select count(*) from %s", table));
rs.next();
if (rs.getInt(1) > 0) {
stmt.execute(String.format("TRUNCATE table %s", table));
}
int i = 0;
while (i < items.size()) {
String SQL =
String.format(
sqlTemplate,
table,
items.get(i).getBand(),
items.get(i).getMinimum(),
items.get(i).getMaximum(),
items.get(i).getPaidTimeOffDays(),
items.get(i).getBonusPercentage());
stmt.addBatch(SQL);
i++;
}
stmt.executeBatch();
stmt.clearBatch();
}
// Handle errors.
catch (Exception e) {
e.printStackTrace();
} finally {
if (null != rs)
try {
rs.close();
} catch (Exception e) {
}
if (null != stmt)
try {
stmt.close();
} catch (Exception e) {
}
if (null != conn)
try {
conn.close();
} catch (Exception e) {
}
}
}
@Override
public void clearBatch() throws SQLException {
rawStatement.clearBatch();
}
