This section describes how to use PreparedStatement objects in batch mode.
If you want to execute a PreparedStatement object multiple times in a single transaction, you can use the
batch feature of the PreparedStatement object. Each time the addBatch() method is called, a copy of the embedded SQL
statement will be created, but not executed until the execution method call.
Here is sample sequence of method calls to execute a PreparedStatement object in batch mode:
ps.setXXX(...); // Set parameters for the first copy
...
ps.addBatch(); // Create the first copy of the SQL statement
ps.setXXX(...); // Set parameters for the second copy
...
ps.addBatch(); // Create the second copy of the SQL statement
ps.setXXX(...); // Set parameters for the third copy
...
ps.addBatch(); // Create the third copy of the SQL statement
ps.executeBatch(); // Execute all copies together as a batch
Here is a sample program that creates a PrepareStatement object and executes it in batch mode
to run an INSERT statement 4 times:
/**
* OraclePreparedStatementBatch.java
* Copyright (c) 2007 by Dr. Herong Yang. All rights reserved.
*/
import java.sql.*;
public class OraclePreparedStatementBatch {
public static void main(String [] args) {
Connection con = null;
try {
oracle.jdbc.pool.OracleDataSource ds
= new oracle.jdbc.pool.OracleDataSource();
ds.setDriverType("thin");
ds.setServerName("localhost");
ds.setPortNumber(1521);
ds.setDatabaseName("XE");
ds.setUser("Herong");
ds.setPassword("TopSecret");
con = ds.getConnection();
// PreparedStatement
PreparedStatement ps = con.prepareStatement(
"INSERT INTO Profile (ID, FirstName, LastName)"
+ " VALUES (?, ?, ?)");
// Provide values to parameters for copy 1
ps.setInt(1,101);
ps.setString(2,"John");
ps.setString(3,"First");
// Create copy 1
ps.addBatch();
// Provide values to parameters for copy 2
ps.setInt(1,102);
ps.setString(2,"Bill");
ps.setString(3,"Second");
// Create copy 2
ps.addBatch();
// Provide values to parameters for copy 3
ps.setInt(1,103);
ps.setString(2,"Mark");
ps.setString(3,"Third");
// Create copy 3
ps.addBatch();
// Provide values to parameters for copy 4
ps.setInt(1,104);
ps.setString(2,"Jack");
ps.setString(3,"Last");
// Create copy 4
ps.addBatch();
// Execute all 4 copies
int[] counts = ps.executeBatch();
int count = 0;
for (int i=0; i<counts.length; i++) {
count += counts[i];
}
System.out.println("Total effected rows: "+count);
// Close the PreparedStatement object
ps.close();
con.close();
} catch (Exception e) {
System.err.println("Exception: "+e.getMessage());
e.printStackTrace();
}
}
}
I looked at the Profile table in the database with SQL*Plus and saw 4 new rows inserted correctly.
But the Oracle JDBC driver returned the counts of affected row incorrectly.
This seems to be a bug in the JDBC driver.
