Welcome

JDBC Tutorial


JDBC JDBC Bulk Updates

        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.

Batch Update methods in Statement or PreparedStatement Interfaces

      		addBatch()
      		executeBatch();
      		clearBatch()
      

addBatch method

       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 method

       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.

Exception Class in Batch Update: BatchUpdateException

  there are 2 reaons why BatchUpdateException is thrown.

  • One of the SQL statement produces Resultset Object
  • One of the DML Statement Failed to Execute successfully.

Batch Update Using Statement Object

      
      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