刚接到这个需求感觉很简单,看大致意思主要是,从Excel中读取数据并写入到数据库中,唯一特殊之处是有一列大字段(超过4000个字符,在数据库中需要用CLOB类型存储)需特殊处理。想了下把代码写好了。开始测试发现,导入一条数据需要8秒左右,按这速度导20万数据的话,不敢往下想了,于是想着代码调优,优化以后速度变成了一分钟导入一千多条。以此为背景,分享下修改过程中的心得。
过程中遇到很多问题,但都顺利解决,下面罗列下主要遇到的问题以及有效解决方式。
问题1:如何获取合并单元格的值?
解决方式:
public static void InsertData(String sjly, File excelFile, String tableName) {
Workbook rwb = null;
//创建输入流
InputStream stream = new FileInputStream(excelFile);
//获取Excel文件对象
rwb = Workbook.getWorkbook(stream);
//选择第一个工作表
Sheet sheet1 = rwb.getSheet(0);
String str = null;
for (int j = 1; j < sheet1.getRows(); j++) {
for (int k = 0; k < sheet1.getColumns(); k++) {
str = sheet1.getCell(k, j).getContents();
Range[] ranges = sheet1.getMergedCells();//合并单元格范围
for (Range r : ranges) {
if (j > r.getTopLeft().getRow()&& j <= r.getBottomRight().getRow() && k == r.getTopLeft().getColumn()) {
str = sheet1.getCell(r.getTopLeft().getColumn(), r.getTopLeft().getRow()).getContents();
}
}
}
}
问题2:获取当前时间比系统时间晚8个小时
解决方法:
在获取当前时间的代码前设置时区,代码如下所示:
TimeZone tz =TimeZone.getTimeZone("Asia/Shanghai");
TimeZone.setDefault(tz);
String Nowtime=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(Calendar.getInstance().getTime());
问题3:CLOB类型字段处理
解决方式:由于Excel中部分数据列超过4000字符,varchar2不够用,因此可使用clob数据类型对这些大数据量的数据列进行存储。而LOB数据不能像其它类型数据一样直接插入(INSERT)。插入前必须先插入一个空的LOB对象,CLOB类型的空对象为EMPTY_CLOB (),之后使用带“for update”的查询语句锁定更新行,继而将空对象修改为所要插入的LOB对象。对于clob数据的修改,可在修改该表的其他字段信息时同时将clob字段修改为EMPTY_CLOB (),然后才对clob字段单独修改。
问题4:向CLOB对象中写入数据
解决方式:
String update_sql = "select GSXX from " + tableName + " where ID='"+id+"' for update";
conn.setAutoCommit(false);
rs = conn.createStatement().executeQuery(update_sql);
if (rs.next()) {
/* 取出此CLOB对象 */
oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob("GSXX");
/* 向CLOB对象中写入数据 */
BufferedWriter out = new BufferedWriter(clob .getCharacterOutputStream());
try {
out.write(impvo.getGSXX().toString());
out.flush();
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
rs.close();
conn.commit();
conn.setAutoCommit(true);
这样写单条的时候问题不大,当有大数据时会报:ORA-00604: 递归 SQL 级别 1 出现错误,ORA-01000: 超出打开游标的最大数的错误,
解决方式:将 rs = conn.createStatement().executeQuery(update_sql);
改为:stmt = conn.createStatement();
rs = stmt.executeQuery(update_sql);
并在关闭rs对象时同时关闭stmt 对象即可解决。
解决完以上问题,最大的问题是效率,调试代码发现比较慢的地方,第一个就是创建数据库链接:Connection conn = DBUtil.getOracleConnection();耗时大概3秒,检查代码发现,这一段代码竟然是在一个For循环里,,于是把这一段放在For循环外面,然后当参数传递。这个修改以后稍微快了两三秒,但还是很慢;于是把单条处理做成批量处理。速度是1分钟大概一千多条。大功告成。
代码下载链接:数据导入实现参考源码