JDBC Tutorial
JDBC Batch Updates,sending bunch of SQL statements as a batch to the Database. Otherwise sending Multiple SQL statements to the Database implies multiple calls to the Database. JDBC Batch Updates avoids this round trips, submits Batch of SQL statements as whole to the Database or as a Unit. Each SQL statement gets updatecount. Based on Updatecount, the programmer can identify SQL statement's success or Failure.
addBatch() executeBatch(); clearBatch()
addBatch methods adds SQL statements to statement Object or PreparedStatement Object. Only DML Operations are permitted in batch Updates.That is statements that sends update count. It can be INSERT,UPDATE or DELETE operations.
DDL commands that return 0 also permitted,such as CREATE TABLE, DROP TABLE,ALTER TABLE.
Note: No SELECT statements are permitted in Batch Updates.
executeBatch methods Executes/submits all SQL statements previously added to statement Object or PreparedStatement. Returns int array ,each array element has status of DML operations submitted. 1 means success, Statement.EXECUTE_FAILED means failure. In failure BatchUpdateException will be thrown, it has failure details.
there are 2 reaons why BatchUpdateException is thrown.
conn.setAutoCommit(false); Statement stmt = conn.createStatement(); try { stmt.addBatch("INSERT INTO IIDs VALUES(4,'RAM') "); stmt.addBatch("INSERT INTO IIDs VALUES(5,'SITA') "); stmt.addBatch("INSERT INTO IIDs VALUES(6,'LAKSHMAN') "); int[]status = stmt.executeBatch(); for(int i=0; i < status.length; i++) { System.out.println(status[i]); } conn.commit(); conn.setAutoCommit(true); } catch(BatchUpdateException ex) { System.out.println("BATCH"+ ex.getMessage() ); int[]status = ex.getUpdateCounts(); for(int i=0; i < status.length; i++) { System.out.println(status[i]); } } catch(SQLException ex) { System.out.println("sql"+ex.getErrorCode()+" "+ex.getMessage()); }
ADS