最近项目中有用到JDBC技术,存在大量数据要进行插入,通过研究采用批量插入速度快的不是一点点。下面简单比较了一下普通插入与批量插入5W条数据的时间效率。
常规插入:耗时12952ms
public static void normalInsert() throws SQLException {
long start = System.currentTimeMillis();
Connection conn = getConnection();
PreparedStatement ps = null;
for (int i=0;i < 50000;i++){
String sql = "insert into xxx values (?,'1','1')";
ps = conn.prepareStatement(sql);
ps.setString(1, i+"");
ps.execute();
ps.close();
}
long end = System.currentTimeMillis();
System.out.println(end - start);
}
批量插入:耗时930ms
public static void batchInsert() throws SQLException {
long start = System.currentTimeMillis();
Connection conn = getConnection();
conn.setAutoCommit(false);
PreparedStatement ps = null;
String sql = "insert into xxx values (?,'1','1')";
ps = conn.prepareStatement(sql); // 批量插入时ps对象必须放到for循环外面
for (int i=0;i < 50000;i++){
ps.setString(1, i+"");
ps.addBatch();
// 每1000条记录插入一次
if (i % 1000 == 0){
ps.executeBatch();
conn.commit();
ps.clearBatch();
}
}
// 剩余数量不足1000
ps.executeBatch();
conn.commit();
ps.clearBatch();
long end = System.currentTimeMillis();
System.out.println(end - start);
}
显而易见,这还是表结构简单,插入数据就相差十倍多。
oracle插入150W耗时3分钟;删除52秒。
truncate清空表,速度超快;delete删除比较慢,可以加条件;删除150w数据truncate只要2秒,而delete可能要差不多1分钟
truncate table xxx; delete from xxx;
常见SQL错误:
1)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: 超出打开游标的最大数
2)java.sql.SQLException:ORA-00054;资源正忙,但指定以NOWAIT方式获取资源,或者超时失败
原因:数据库中存在未提交的记录
3)java.sql.SQLException 索引中丢失in或out参数
原因:SQL语句有问题
解决:jdbc进行字段拼接插入操作时,为string类型的加上单引号
另外,用prepareStatement.setString(1, 字段值),可以防止字段值有单引号而破坏sql问题
4)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个字符
5)ORA-24816:在实际的LONG或LOB列之后提供了扩展的非LONG绑定数据
原因:这个问题很奇怪,就算没有LONG类型的数据,全部都是VARCHAR2和CLOB在操作数据库更新的时候一直报这个错误
解决:歪打正着,调整了一下各种更新字段位置,将放在前面的CLOB类型放在后面,就不报错了