jdbc 批处理操作

jdbc的批处理操作是指把一组SQL语句（增删改操作）一次性提交给数据库去执行，提高效率。分为Statement版和PreparedStatement版。

1. 例子

数据库软件：postgreSQL
数据库名称：test
数据库图表：intense
数据库表：miracle
id integer
name character varying(20)
timestamp timestamp without time zone

a. Statement

Connection conn = null;
Statement st = null;

try {
	Class.forName("org.postgresql.Driver");
	conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/test", "postgres", "postgres");

	st = conn.createStatement();
	
	String sql = "INSERT INTO intense.miracle(id, name, timestamp) VALUES(1,'particles',current_timestamp)";
	st.addBatch(sql);
	
	sql = "UPDATE intense.miracle SET name='parallel' WHERE id=1";
	st.addBatch(sql);

	int[] results = st.executeBatch();
	for (int result : results)
		System.out.println(result);
} catch (Exception ex) {
  // Exception processing logic is omitted
}

Statement的批处理每次可以处理增、删、改中的一种或几种。

b. PreparedStatement

Connection conn = null;
PreparedStatement ps = null;

try {
	...
	String sql = "INSERT INTO intense.miracle(id, name, timestamp) VALUES(?,?,?)";
	ps = conn.prepareStatement(sql);
	
	for (int i = 0; i < 4; i++) {
		ps.setInt(1, i);
		ps.setString(2, "income" + i);
		ps.setTimestamp(3, new java.sql.Timestamp(System.currentTimeMillis()));
		ps.addBatch();
	}

	int[] results = ps.executeBatch();
	for (int result : results)
		System.out.println(result);
} catch (Exception ex) {
  // Exception processing logic is omitted
}

PreparedStatement的批处理每次只能处理增删改中的一种。

2. 批处理的最大数限制