java.sql.PreparedStatement class is more powerful and efficient than java.sql.Statement, This example will show you how to execute PreparedStatement in batch.
1. Please Note the Below Points For Batch Database Operation.
- Before you can execute PreparedStatement SQL in batch, you need to set auto-commit to false use
dbConnection.setAutoCommit(false). - After setting all the values for prepared statements use
preparedStatement.addBatch()to add it to the batch. - Do not forget to commit the batch to the database server in the code manually (
dbConnection.commit();). - Enable auto-commit at last for later database operations (
dbConnection.setAutoCommit(true);). - Close PreparedStatement and Connection object.
2. PreparedStatement Batch Insert Example.
- This example will use PostgreSQL to demo how to insert, update and delete with PreparedStatement in batch. You can read the article Use JDBC To Connect PostgreSQL Database to learn how to use JDBC to connect PostgreSQL.
- This example will read PostgreSQL JDBC connection data saved in a properties file. You can read Article Load JDBC Configuration From Properties File Example for detailed introduction.
// PreparedStatement for batch insert example. public void preparedStatementBatchInsert() { try { Connection dbConn = this.getDBConnectionFromPropertiesFile(); // Set auto commint to false dbConn.setAutoCommit(false); // Create insert sql. StringBuffer insertBuf = new StringBuffer(); insertBuf.append("insert into \"UserAccount\".\"UserInfo\"(\"UserName\", \"Password\") values(?,?)"); String insertSql = insertBuf.toString(); // Create PreparedStatement object. PreparedStatement pStmt = dbConn.prepareStatement(insertSql); // Add batch insert data. pStmt.setString(1, "[email protected]"); pStmt.setString(2, "abcdefg"); pStmt.addBatch(); pStmt.setString(1, "[email protected]"); pStmt.setString(2, "abcdefg"); pStmt.addBatch(); pStmt.setString(1, "[email protected]"); pStmt.setString(2, "1234567"); pStmt.addBatch(); pStmt.setString(1, "[email protected]"); pStmt.setString(2, "1234567"); pStmt.addBatch(); pStmt.setString(1, "[email protected]"); pStmt.setString(2, "[email protected]#$%^&"); pStmt.addBatch(); pStmt.setString(1, "[email protected]"); pStmt.setString(2, "&^%$#@!"); pStmt.addBatch(); // The returned int array store insert sql affected record counts. int successCount[] = pStmt.executeBatch(); int successCountLen = successCount.length; for(int i =0; i < successCountLen; i++) { int success = successCount[i]; if(success>0) { System.out.println(" Insert sql operation success compelete. The affected row count is " + success); } } dbConn.commit(); dbConn.setAutoCommit(true); pStmt.close(); dbConn.close(); }catch(Exception ex) { ex.printStackTrace(); } } - Batch Insert Console Output.
Insert sql operaton success compelete. The affected row count is 1 Insert sql operaton success compelete. The affected row count is 1 Insert sql operaton success compelete. The affected row count is 1 Insert sql operaton success compelete. The affected row count is 1 Insert sql operaton success compelete. The affected row count is 1 Insert sql operaton success compelete. The affected row count is 1
- Database query after execute PreparedStatement batch insert.
delete from "UserAccount"."UserInfo" select * from "UserAccount"."UserInfo" UserName Password [email protected] abcdefg [email protected] abcdefg [email protected] 1234567
3. PreparedStatement Batch Update Example.
- Example source code.
// PreparedStatement for batch update example. public void preparedStatementBatchUpdate() { try { Connection dbConn = this.getDBConnectionFromPropertiesFile(); // Set auto commint to false dbConn.setAutoCommit(false); // Create update sql. StringBuffer updateBuf = new StringBuffer(); updateBuf.append("update \"UserAccount\".\"UserInfo\" set \"Password\" = ? where \"Password\" = ? "); String updateSql = updateBuf.toString(); // Create PreparedStatement object. PreparedStatement pStmt = dbConn.prepareStatement(updateSql); // Add batch update data. pStmt.setString(1, "abc#123"); pStmt.setString(2, "1234567"); pStmt.addBatch(); pStmt.setString(1, "[email protected]#"); pStmt.setString(2, "abcdefg"); pStmt.addBatch(); pStmt.setString(1, "163***"); pStmt.setString(2, "[email protected]#$%^&"); pStmt.addBatch(); // The returned int array store update sql affected record counts. int successCount[] = pStmt.executeBatch(); int successCountLen = successCount.length; for(int i =0; i < successCountLen; i++) { int success = successCount[i]; if(success>0) { System.out.println("Update sql operation success compelete. Updated row count is " + success); } } dbConn.commit(); dbConn.setAutoCommit(true); pStmt.close(); dbConn.close(); }catch(Exception ex) { ex.printStackTrace(); } } - Batch Update Console Output.
Update sql operation success complete. Updated row count is 2 Update sql operation success complete. Updated row count is 2 Update sql operation success complete. Updated row count is 1
- Database query after executing the batch update.
delete from "UserAccount"."UserInfo" select * from "UserAccount"."UserInfo" UserName Password [email protected] ^&*^*&^* [email protected] abc#123 [email protected] abc#123 [email protected] [email protected] [email protected] [email protected] [email protected] [email protected]
4. PreparedStatement Batch Delete Example.
- Example source code.
// PreparedStatement for batch delete exmple. public void preparedStatementBatchDelete() { try { Connection dbConn = this.getDBConnectionFromPropertiesFile(); // Set auto commint to false dbConn.setAutoCommit(false); // Create update sql. StringBuffer updateBuf = new StringBuffer(); updateBuf.append("delete from \"UserAccount\".\"UserInfo\" where \"Password\" = ? "); String updateSql = updateBuf.toString(); // Create PreparedStatement object. PreparedStatement pStmt = dbConn.prepareStatement(updateSql); // Add batch delete data. pStmt.setString(1, "abc#123"); pStmt.addBatch(); pStmt.setString(1, "163***"); pStmt.addBatch(); // The returned int array store delete sql affected record counts. int successCount[] = pStmt.executeBatch(); int successCountLen = successCount.length; for(int i =0; i < successCountLen; i++) { int success = successCount[i]; if(success>0) { System.out.println("Update sql operation success compelete. Updated row count is " + success); } } dbConn.commit(); dbConn.setAutoCommit(true); pStmt.close(); dbConn.close(); }catch(Exception ex) { ex.printStackTrace(); } } - Batch Delete Console Output.
Delete sql operation success complete. Deleted row count is 2 Delete sql operation success complete. Deleted row count is 1
- Database query after executing batch delete.
delete from "UserAccount"."UserInfo" select * from "UserAccount"."UserInfo" UserName Password [email protected] ^&%&^%*&^*^ [email protected] [email protected] [email protected] [email protected]
