PreparedStatement in Batch Mode

如果想实现在一次事务中执行提交多条数据，可以使用PreparedStatement 对象，每次调用addBatch()方法就可以生成SQL statement的一个副本，但是不会执行，直到excute()方法被调用，才会执行。以下是一个简单的示例：

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

下面是一段程序实例用 PrepareStatement 对象以batch形式一次插入4条数据

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