需求
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;