JDBC PreparedStatement Example – Batch Insert, Update, Delete

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.

1. Before you can execute PreparedStatement SQL in batch, you need to set auto-commit to false use dbConnection.setAutoCommit(false).
2. After setting all the values for prepared statements use preparedStatement.addBatch() to add it to the batch.
3. Do not forget to commit the batch to the database server in the code manually (dbConnection.commit();).
4. Enable auto-commit at last for later database operations (dbConnection.setAutoCommit(true);).
5. Close PreparedStatement and Connection object.

2. PreparedStatement Batch Insert Example.

1. 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.
2. 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, "jerry@qq.com");
   		pStmt.setString(2, "abcdefg");
   		pStmt.addBatch();
   			
   		pStmt.setString(1, "hello@gmail.com");
   		pStmt.setString(2, "abcdefg");
   		pStmt.addBatch();
   			
   		pStmt.setString(1, "hi@163.com");
   		pStmt.setString(2, "1234567");
   		pStmt.addBatch();
   		
   		pStmt.setString(1, "richard@qq.com");
   		pStmt.setString(2, "1234567");
   		pStmt.addBatch();
   			
   		pStmt.setString(1, "steve@hotmail.com");
   		pStmt.setString(2, "!@#$%^&");
   		pStmt.addBatch();
   			
   		pStmt.setString(1, "henry@gmail.com");
   		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();
   	}
   }
   

3. 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

4. Database query after execute PreparedStatement batch insert.

   delete from "UserAccount"."UserInfo"
   
   select * from "UserAccount"."UserInfo"
   
   UserName            Password
   jerry@qq.com        abcdefg
   hello@gmail.com     abcdefg
   hi@163.com          1234567

3. PreparedStatement Batch Update Example.

1. 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, "gmail!@#");
   		pStmt.setString(2, "abcdefg");
   		pStmt.addBatch();
   			
   		pStmt.setString(1, "163***");
   		pStmt.setString(2, "!@#$%^&");
   		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();
   	}
   }

2. 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

3. Database query after executing the batch update.

   delete from "UserAccount"."UserInfo"
   select * from "UserAccount"."UserInfo"
   
   UserName             Password
   henry@gmail.com      ^&*^*&^*
   hi@163.com           abc#123
   richard@qq.com       abc#123
   jerry@qq.com         gmail@123
   hello@gmail.com      gmail@123
   steve@hotmil.com     gmail@123

4. PreparedStatement Batch Delete Example.

1. 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();
   	}
   }

2. Batch Delete Console Output.

   Delete sql operation success complete. Deleted row count is 2
   Delete sql operation success complete. Deleted row count is 1

3. Database query after executing batch delete.

   delete from "UserAccount"."UserInfo"
   
   select * from "UserAccount"."UserInfo"
   
   UserName            Password
   henry@gmail.com     ^&%&^%*&^*^
   jerry@qq.com        gmail@12
   hello@gmail.com     gmail@12