最近有个需求是需要在java 后端执行导入,数据量比较大, 需要对数据进行很多操作,最后要执行插入数据操作, 一开始先组织好插入数据的sql语句放在数组中,使用的是 jdbcTemplate.batchUpdate()方法,后来发现当数据比较多 ,成千上万条时,他的插入效率比较慢,所以又改用了jdbc 的批量插入.插入速度有了很大的提速

方法1

直接使用了jdbctemplate内部封装的batchUpdate方法,他的底层也是用了 jdbc 的批量执行,但是执行效率要比 正常使用jdbc 批量执行要慢很多,插入2600条数据的20个字段左右的表用时40s 左右,

// sqls 为组织好的插入数据的sql数组,
//{"insert into user(id,name,pwd) values('1','a','123456')","insert into user(id,name,pwd) values('2','b','123456')",...   }
// j
public void insertData(String[] sqls,JdbcTemplate jdbctemplate){
	jdbctemplate.batchUpdate(sqls);
}

方法2

使用了 jdbc 的批量执行sql, 主要使用了addBatch(),executeBatch(),clearBatch()方法,
这样批量执行 大大的提高了执行效率,2600条数据 20个字段的表插入只用了4-5s,
下面方法控制每5000条sql执行一次,剩下不足500条最后再执行一次.

public void insertData2(String[] sqls,JdbcTemplate jdbctemplate){
	Connection conn = jdbcTemplate.getDataSource().getConnenction();
	conn.setAutoCommit(false);//设置连接对象的自动提交为false
	Statement statement = conn.createStatement();
	for(int i=0;i<sqls.length;i++){
		statement.addBatch(sqls[i]);
		if(i % 5000 ==0){
			statement.executeBatch(); // 批量执行
			conn.commit();  // 手动提交
			statement.clearBatch();  // 清楚之前批量执行的
		}
	}
	statement.executeBatch();
	conn.commit();
	statement.clearBatch();
}

注意:

  1. 数据库区别:

本项目用的是 oracle数据库, 而mysql服务器默认是关闭批处理的，我们需要通过一个参数，让mysql开启批处理的支持。 ?rewriteBatchedStatements=true 写在配置文件的url后面

作者：Nemo

  1. 可能出现的sql错误(本项目未遇到)
  • Oracle数据库中打开的游标最大数为一定值，默认情况下是300，当代码到第二步时，循环中一个PreparedStatement占用了一个数据库游标，执行的循环超过这个数时就会产生游标数目溢出错误。
    解决办法:每次执行完PreparedStatement，都将PreparedStatement.close()下，释放掉这个资源就好了
    Exception in thread “main” java.sql.SQLException: ORA-00604: 递归 SQL 级别 1 出现错误
    ORA-01000: 超出打开游标的最大数
    ORA-00604: 递归 SQL 级别 1 出现错误
    ORA-01000: 超出打开游标的最大数
    ORA-01000: 超出打开游标的最大数
  • java.sql.SQLException：ORA-00054；资源正忙，但指定以NOWAIT方式获取资源，或者超时失败
    原因：数据库中存在未提交的记录
  • java.sql.SQLException 索引中丢失in或out参数
    原因：SQL语句有问题
    解决：jdbc进行字段拼接插入操作时，为string类型的加上单引号
    另外，用prepareStatement.setString(1, 字段值)，可以防止字段值有单引号而破坏sql问题
  • java.sql.BathUpdateException：ORA-01461；仅能绑定要插入LONG列的LONG值
    原因：由于要插入的字段长度超出了数据库中表定义的字段长度
    --------------------------补充-----------------------------------
    varchar2是Oracle提供的特定数据类型
    varchar2(10)一般情况最多存5个汉字，10个字符。具体要看数据库使用的字符集：GBK（汉字2字节；英文1个；Oracle安装默认GBK编码格式）；UTF-8（汉字3字节，英文1个）
    一般页面做输入字符串长度校验时，以数据库设计字段最大长度/3作为最大长度
    nvarchar(10)可以存10个汉字，10个字符
    当长度大于4000的时候就应该用CLOB，因为oracle的varchar2最多4000个字符
  • ORA-24816：在实际的LONG或LOB列之后提供了扩展的非LONG绑定数据
    原因：这个问题很奇怪，就算没有LONG类型的数据，全部都是VARCHAR2和CLOB在操作数据库更新的时候一直报这个错误
    解决：歪打正着，调整了一下各种更新字段位置，将放在前面的CLOB类型放在后面，就不报错了