PreparedStatement中的批处理

在PreparedStatement对象中使用批处理更新机制，是指先对SQL语句执行查询优化，然后在批处理队列中置入多组输入参数，而后提交数据源一次处理。下面是一个例子：

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

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

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

import org.junit.Test;

public class PrepareStatementTest {
	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);

			// 改变auto-commit模式
			con.setAutoCommit(false);
			sql = "INSERT INTO TEMP VALUES(?,?)";
			// 先做查询优化
			PreparedStatement pstmt = con.prepareStatement(sql);
			// 设置参数值
			pstmt.setInt(1, 200);
			pstmt.setString(2, "Tom Kaufmann");
			pstmt.addBatch();
			// 设置参数值
			pstmt.setInt(1, 300);
			pstmt.setString(2, "Mike Barnes");
			pstmt.addBatch();
			// 执行，取得更新记录
			int[] updateCounts = pstmt.executeBatch();
			assertNotNull(updateCounts);
			con.commit();
			// 查询数据
			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();
			pstmt.close();
			stmt.close();
			con.close();
		} catch (Exception e) {
			fail(e.getMessage());
		}

	}

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

例子说明：

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

旧协议：每个元素值表示每批所影响的行数。

新协议：第1个元素表示：共影响的行数，其他元素值为0。