Oracle：高效批量插入数据

Tieven

声明：这不是教程，只是个小结。不适用于生产环境，仅属于实验性质的设定。

环境：JDK8，Oracle 11g

机器：Lenovo Y50，8G内存，512G硬盘，i5处理器

结论：18个字段，插入100万条数据，耗时20~30s。

Java部分：

1，使用的jar包是ojdbc6.jar

2，关闭自动提交

3，使用Connection.prepareStatement()预编译SQL语句，对于一个批次的数据而言，只需要编译一次即可。

4，对于每一条数据，在将参数设置到PreparedStatement对象之后，使用PreparedStatement.addBatch()方法添加到批次中。

5，每添加1000条数据，执行1次PreparedStatement.executeBatch()方法。

6，全部执行完毕后，手动一次性提交。

代码示例：

public void testA1_SetInsertParameters() {
	System.out.println();
	System.out.println(">>>Test1SchemaTest->testA1_SetInsertParameters()");
	Connection conn = null;
	PreparedStatement ps = null;
	int count = 1000000;
	try {
		// 获取连接
		conn = JdbcPool.getConnection();
		// 关闭自动提交
		conn.setAutoCommit(false);
		// 清空测试表
		String deleteSql = "truncate table test1";
		ps = conn.prepareStatement(deleteSql);
		ps.execute();
		conn.commit();
		ps.close();
		long startTime = System.currentTimeMillis();
		// 获取插入语句SQL
		 insert into test1 (my_binary_double, my_binary_float, my_blob, my_clob, my_char, 
		 my_date, my_interval_day_to_second_, my_interval_year_to_month, my_long, my_nclob, 
		 my_number, my_nvarchar2, my_raw, my_timestamp_with_local, my_timestamp_with_time_zone, 
		 my_timestamp, my_integer, my_varchar2) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 
		 ?, ?, ?, ?, ?, ?)
		String sql = new Test1Schema().insertSql();
		// 预编译插入语句
		ps = conn.prepareStatement(sql);
		for (int i = 0; i < count; i++) {
			Test1Schema test1Schema = new Test1Schema();
			test1Schema.setMyNumber(Double.valueOf(i));
			test1Schema.setMyNvarchar2("A");
			test1Schema.setMyChar("ZZZ");
			test1Schema.setMyBinaryDouble(1.1d);
			test1Schema.setMyBinaryFloat(2.2d);
			test1Schema.setMyDate(Utility.getCurrentDate());
			test1Schema.setMyInteger(10);
			test1Schema.setMyLong("VVVV");
			test1Schema.setMyTimestamp(Utility.getCurrentTimeStamp());
			test1Schema.setMyTimestampWithLocal(Utility.getCurrentTimeStamp());
			test1Schema.setMyTimestampWithTimeZone(Utility.getCurrentTimeStamp());
			test1Schema.setMyVarchar2("UU");
			// 将参数传递给PreparedStatement
			test1Schema.insertParameters(ps);
			if ((i + 1) % 1000 == 0) {
				// 执行批次
				ps.executeBatch();
				// 清空批次
				ps.clearBatch();
		// 执行最后的不足1000条数据的批次
		ps.executeBatch();
		// 提交
		conn.commit();
		long endTime = System.currentTimeMillis();
		System.out.println("测试插入" + count + "条用时：" + 
			String.format("%.2f", (double) (endTime - startTime) / 1000) + "秒");
	} catch (SQLException e1) {
		e1.printStackTrace();
		fail();
	} finally {
		try {
			if (ps != null) {
				ps.close();
			if (conn != null) {
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			fail();
}

代码示例：

/** 为经过预编译的ps设置插入语句的参数 */
public boolean insertParameters(PreparedStatement ps) {
	if (!checkNotNullKey() || ps == null) {
		return false;
	try {
		if (this.myBinaryDouble == null) {
			ps.setNull(1, 8);
		} else {
			ps.setDouble(1, this.myBinaryDouble);
		// 略......
		if (this.myVarchar2 == null) {
			ps.setNull(18, 12);
		} else {
			ps.setString(18, this.myVarchar2);
		ps.addBatch();