[Java] 高效快速导入EXCEL数据
需求
1、高效率的以excel表格的方式导入多条数据。
2、以身份证号为唯一标识，如果身份证号已存在，则该条数据不导入。
分析
刚开始的时候是传统的做法，解析excel数据，获取单个对象，判断身份证是否已存在表中，若没有则插入数据库。否则pass
结果数据才几百条的时候，导数据都花了10分钟才导入完毕。实在太慢了，看来在程序中做数据的校验很脑残。
优化
在插入数据库之前作判断太花时间，没必要。所以最后将数据的处理交给了数据库来做。
建一个临时表CUST_OBJECT_TEMP，先把所有的数据都用 jdbc batch insert 插入或者分数据段插入，这个过程不做任何的数据校验。
再搞个存储过程来处理两个表的数据就行了。
详细代码
① 加载上传的xls文件
private File file; //上传的文件 private String fileFileName; //文件名称 public File getImage() { return image; } public void setImage(File image) { this.image = image; } public String getImageFileName() { return imageFileName; } public void setImageFileName(String imageFileName) { this.imageFileName = imageFileName; } public String importExcel() throws Exception { HttpServletRequest request = ServletActionContext.getRequest(); String realpath = ServletActionContext.getServletContext().getRealPath("/"); if (file != null) { File savefile = new File(new File(realpath), fileFileName); if (!savefile.getParentFile().exists()){ savefile.getParentFile().mkdirs(); } FileUtils.copyFile(file, savefile); String newName = savefile.getName(); //执行操作 dao.addAll(realpath+newName); } return SUCCESS; }
② 解析excel并调用存储过程
public void addAll(String url){ //生成一个备用码 String guid=RandomUtils.myGetNo(9); //将excel数据转为集合并以100条每次的节奏插入临时表中 Workbook rwb = null; try { //InputStream is = new FileInputStream("F:/龙里县贫困户.xls"); InputStream is = new FileInputStream(url); rwb = Workbook.getWorkbook(is); } catch (Exception ex) { ex.printStackTrace(); } //获取第一张Sheet表 Sheet rs = (Sheet) rwb.getSheet(0); //总行数 int rsRows = rs.getRows(); //总列数 int rsColumns = rs.getColumns(); Cell c1,c2 = null; String strTmp1,strTmp2=""; int no=Integer.parseInt(findMaxId())+10; System.out.println("总行数"+rsRows+"总列数"+rsColumns+" "+no); try { openConn(); //设置为不自动提交，等凑足100条的时候再批量提交一次 conn.setAutoCommit(false); stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY); } catch (SQLException e1) { e1.printStackTrace(); } for(int i=2;i<rsRows;i++){ c1 = ((jxl.Sheet) rs).getCell(0, i); strTmp1 = c1.getContents(); c2 = ((jxl.Sheet) rs).getCell(1, i); strTmp2 = c2.getContents(); no++; if(!strTmp1.equals("") && !strTmp3.equals("")){ StringBuilder strSql = new StringBuilder(); strSql.append(" INSERT INTO CUST_OBJECT_TEMP ");//CUST_OBJECT_TEMP strSql.append(" (CUST_ID,CREDENTIALS,COUNZHENG,GUID) "); strSql.append(" VALUES "); strSql.append(" ('"+no+"','"+strTmp1+"','"+strTmp2+"','"+guid+"') "); try { stmt.execute(strSql.toString()); //100条数据提交一次 if(i%100==0||i==(rsRows-1)){ conn.commit(); } } catch (SQLException e) { e.printStackTrace(); } } } try { closeAll(); }catch (Exception e) { e.printStackTrace(); } //至此所有的excel数据都已经插入到了临时表CUST_OBEJCT_YEMP中了 //注意：临时表除了有正是表的所有字段，还额外新增了两个字段 1、IF_INSERT 建表的时候默认为0 表示该数据还没有插入正式表 ，当插入正是表之后，会变为1. 2、GUID 标识了数据的批次，String guid=RandomUtils.myGetNo(9); 每一次excel导入的数据都同属于一个批次。 //调用存储过程 excelAlladd(guid); }
③ 调用存储过程定义
public String excelAlladd(String guid){
String result = "1"; openConn(); try{ CallableStatement c=(CallableStatement) conn.prepareCall("{call PRO_CUST_TABLE(?,?)}"); c.setString(1,guid);//传入批次号guid c.registerOutParameter(2, Types.INTEGER);//设置返回值类型为INT c.execute(); result = c.getString(2); //获取返回值 if("0".equals(result)){ System.out.println("存储过程调用成功"); }else{ System.out.println("存储过程调用失败"); } }catch (Exception e) { System.out.println("调用存储过程"+e); } closeAll(); return result; }
④ 存储过程定义
CREATE OR REPLACE PROCEDURE PRO_CUST_TABLE(V_GUID VARCHAR2,V_RETURN OUT integer) IS V_CNT_1 NUMBER;//定义变量V_CNT_1 V_RETCODE VARCHAR2(20);//定义结果代码 V_RETINFO VARCHAR2(20);//定义结果提示 //开始执行存储过程 BEGIN //取得满足A.IF_INSERT = 0 AND A.GUID = V_GUID（未插入正式表且是同一个批次号）的数据条数 作为 V_CNT_1变量的值 SELECT COUNT(*) INTO V_CNT_1 FROM CUST_OBJECT_TEMP A WHERE A.IF_INSERT = 0 AND A.GUID = V_GUID; V_RETURN := 1;//定义返回值为1 //如果有符合插入条件的数据则执行以下操作 IF V_CNT_1 > 0 THEN //1） 将满足条件的数据插入到正式表中 INSERT INTO CUST_OBJECT( cust_id, CREDENTIALS, COUNZHENG ) SELECT CUST_ID, CREDENTIALS, COUNZHENG FROM CUST_OBJECT_TEMP A1 WHERE IF_INSERT = 0 AND A1.GUID = V_GUID AND NOT EXISTS ( SELECT 1 FROM CUST_OBJECT A2 WHERE TRIM(A1.CREDENTIALS) = TRIM(A2.CREDENTIALS) ); COMMIT; //2） 将满足条件的数据的插入状态更新为已插入IF_INSERT = 1 UPDATE CUST_OBJECT_TEMP A SET IF_INSERT = 1 WHERE IF_INSERT = 0 AND A.GUID = V_GUID; COMMIT; //3） 将临时表已插入状态的数据插入历史记录表 HIS_CUST_OBJECT 作为追溯的证据 INSERT INTO HIS_CUST_OBJECT( CUST_ID, CREDENTIALS, COUNZHENG, IF_INSERT ) SELECT CUST_ID, CREDENTIALS, COUNZHENG, IF_INSERT FROM CUST_OBJECT_TEMP A WHERE IF_INSERT = 1 AND A.GUID = V_GUID; COMMIT; //4） 删除临时表的数据 DELETE FROM CUST_OBJECT_TEMP A WHERE IF_INSERT = 1 AND A.GUID = V_GUID; COMMIT; //如果这些都通过了，设置返回值为0 V_RETURN := 0; END IF; //捕捉异常 EXCEPTION WHEN OTHERS THEN ROLLBACK; V_RETCODE := 'FAIL'; V_RETINFO := SQLERRM; end;
