java.sql.PreparedStatement class is more powerful and efficient than java.sql.Statement, This example will show you how to execute PreparedStatement in batch.
Please Note 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 commit the batch to 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.
This example will use PostgreSql to demo how to insert, update and delete with PreparedStatement in batch. You can read article Use JDBC To Connect PostgreSQL Database to learn how to use JDBC to connect PostgreSQL.
This example will read PostfreSQL JDBC connection data saved in a properties file. You can read Article Load JDBC Configuration From Properties File Example for detail introduction.
PreparedStatement Batch Insert Example.
// 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.
Database query after execute PreparedStatement batch insert.
PreparedStatement Batch Update Example.
// 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.
Database query after execute batch update.
PreparedStatement Batch Delete Example.
// 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.
Database query after execute batch delete.
