CallableStatement中的批处理

CallableStatement对象使用批处理更新的方法和PreparedStatement对象相同。需要说明的是，遵照JDBC3.0规范，可以执行批处理更新的存储过程不能带有OUT或者INOUT参数，否则在调用CallableStatement.executeBatch方法时，驱动会抛出SQLException异常。下面是一个例子：

package com.sun.cts.tests.jdbc.help_doc.pi_chu_li;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.fail;

import java.io.File;
import java.io.FileInputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

import org.junit.Test;

public class CallableStatementTest {
	private Connection con;
	public String DBUSER = "";
	public String DBPASSWD = "";
	public String DBURL="";
	public String DBDRIVER="";

	public void init() {
		Properties prop = new Properties();
		try{

			FileInputStream in = new FileInputStream("."+File.separator+"testFile"+File.separator+"cts_env.properties");
			prop.load(in);
			in.close();
		}
		catch(Exception e){
			fail(e.getMessage());
			e.printStackTrace();
		}
		//从配置文件中读取
		// DBUSER = "sysdba";
		DBUSER = prop.getProperty("DBUSER");
		// DBPASSWD = "szoscar55";
		DBPASSWD = prop.getProperty("DBPASSWD");
		// DBURL = "jdbc:oscar://localhost:2003/osrdb";
		DBURL = prop.getProperty("DBURL");
		// DBDRIVER = "com.oscar.Driver";
		DBDRIVER = prop.getProperty("DBDRIVER");
		try{
			Class.forName(DBDRIVER);
			con = DriverManager.getConnection(DBURL, DBUSER, DBPASSWD);
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	@Test
	public void execute() {
		init();
		try {
			Statement stmt = con.createStatement();
			String sql;
			// 创建表
			sql = "CREATE TABLE TEMP"
					+ "(id INT not NULL,name varchar(255),PRIMARY KEY ( id ))";
			stmt.executeUpdate(sql);
			// 创建存储过程
			sql = "CREATE OR REPLACE PROCEDURE TestProc"
				+ "(param1 IN INT) "
				+ " AS  BEGIN "
				+ "INSERT INTO TEMP VALUES(param1,'shentong');"
				+ " END TestProc; ";
			stmt.executeUpdate(sql);
			
			//改变auto-commit模式
			con.setAutoCommit(false);
			CallableStatement cstmt= con.prepareCall("{call TestProc(?)}");
			//设置参数值
			cstmt.setInt(1,2);
			cstmt.addBatch();
			//设置参数值
			cstmt.setInt(1,3);
			cstmt.addBatch();
			//设置参数值
			cstmt.setInt(1,4);
			cstmt.addBatch();
			//执行，取得更新记录
			int[] updateCounts = cstmt.executeBatch();
			assertNotNull(updateCounts);
			// 查询数据
			sql = "SELECT * FROM TEMP";
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				System.out.println(rs.getString(2));
			}
			// 删除表
			sql="DROP TABLE TEMP ";
			stmt.executeUpdate(sql);
				
			rs.close();
			stmt.close();
			con.close();
		} catch (Exception e) {
			fail(e.getMessage());
		}

	}

	public static void main(String[] args) {
		new CallableStatementTest().execute();
	}
}

例子说明：

updateCounts中元素个数为批数。各个元素的值在新旧协议中不一致。

旧协议：每个元素值为-2或0，0表示该批执行失败，-2表示该批执行成功。

新协议：每个元素值为1或0，0表示该批执行失败，1表示该批执行成功。