关联表多数据的批量insert (批量导入,测试10W条数据用时46秒)
来源:互联网 发布:淘宝新店铺如何引流 编辑:程序博客网 时间:2024/05/01 09:39
maven/Java/web/bootstrap/dataTable/app开发QQ群:566862629。希望更多人一起帮助我学习。
一、业务需求 :作多个批量导入 ,根据业务不同,每条数据导入前作各种验证,
导入后提示验证失败的所有数据和原因。
二、思路: 1. 批量导入用 jdbc 直连数据库 addBatch方法实现 ,不走 Mybatis ,
2. 数据验证用 SQL 语句实现,不走 Mybatis ,
3. 创建临时表记录合格数据并导入正式数据库表
4. 创建临时表记录验证失败的数据,并最终返回
5. 由于业务需求批量导入时是要导入到 2 张主外键关联表 ,
所以一次性获取多个序列值以实现 2 表主外键的一致性。
6. 用 sessionID 区分表名和序列名,实现并发导入时数据无污染 。
三、代码:
/** * 跳转用户导入页面 * @param request * @param session * @return String */ @RequestMapping("userInfoImport") public String userInfoImport(HttpServletRequest request,HttpSession session){ return "op/usermgr/userInfoImport"; } @RequestMapping("userInfoExcelImport") public String userInfoExcelImport(HttpServletRequest request,HttpSession session,@RequestParam("excelPath")MultipartFile excelPath){ Long start = System.currentTimeMillis(); String backUrl = "../usermgr/initQuery.do"; String[] excelTop = new String[]{"msisdn","custname","phoneType","creditamount","payType"}; // 表头 String tableName = session.getId().substring(0,8); // 定义-修改数据临时表 String updateListTemp ="CREATE TABLE updateListTemp"+tableName+"(" +"id integer NOT NULL," +"msisdn varchar2(12) NOT NULL," +"custname varchar2(50) NOT NULL," +"phoneType varchar2(50) NOT NULL," +"creditamount varchar2(10) NOT NULL,"+"payType varchar2(50) NOT NULL)" +" tablespace ISMP pctfree 60 initrans 1 maxtrans 255 storage (initial 64K) "; String updateTemp_seq ="CREATE SEQUENCE updateTemp_seq"+tableName+"" +" INCREMENT BY 1" // 每次加1 +" START WITH 1 " // 从1开始计数 +" NOMAXvalue " // 不设置最大值 +" NOCYCLE " // 一直累加,不循环 +" CACHE 10 "; // 缓存10个 // 定义-新增数据临时表 String insertListTemp ="CREATE TABLE insertListTemp"+tableName+"(" +"id integer NOT NULL," +"msisdn varchar2(12) NOT NULL," +"custname varchar2(50) NOT NULL," +"phoneType varchar2(50) NOT NULL," +"creditamount varchar2(10) NOT NULL," +"payType varchar2(50) NOT NULL)" +" tablespace ISMP pctfree 60 initrans 1 maxtrans 255 storage (initial 64K) "; String insertTemp_seq ="CREATE SEQUENCE insertTemp_seq"+tableName+"" +" INCREMENT BY 1" // 每次加1 +" START WITH 1 " // 从1开始计数 +" NOMAXvalue " // 不设置最大值 +" NOCYCLE " // 一直累加,不循环 +" CACHE 10 "; try { if(excelPath == null){ OprResult.SetOprResult(request, "导入用户异常","导入用户失败,请选择要导入的内容 !", backUrl,OprResult.FAILURE); return "/op/oprResult"; } // 解析得到的用户表集合 List<List<Object>> usrlist = ImportExcelUtil.getExcelList(excelPath.getInputStream(),excelTop);if(usrlist == null){OprResult.SetOprResult(request, "导入用户异常", "导入用户信息,数据表格不能有空值 !", backUrl,OprResult.FAILURE);return "/op/oprResult";}// 验证电话、宽带账号List<String> failMsisdnList = new ArrayList<String>();List<String> msiddn = new ArrayList<String>();for(int y =0; y<usrlist.size(); y++){if(String.valueOf(usrlist.get(y).get(2).toString()).equals("电话号码")){ // 验证电话号码if(!ValidateUtils.isMobile(usrlist.get(y).get(0).toString())){failMsisdnList.add(usrlist.get(y).get(0).toString());usrlist.remove(usrlist.get(y));y--;}}else{if(ValidateDhmp.validateUserMsisdn(usrlist.get(y).get(0).toString())){ // 验证宽带账号failMsisdnList.add(usrlist.get(y).get(0).toString());usrlist.remove(usrlist.get(y));y--;}}}int failsize = failMsisdnList.size();// 去重for(int y =0; y<usrlist.size(); y++){if(y<1){msiddn.add(usrlist.get(y).get(0).toString());}else{if(msiddn.contains(usrlist.get(y).get(0).toString())){usrlist.remove(usrlist.get(y));y--;}else{msiddn.add(usrlist.get(y).get(0).toString());}}}// 创建临时表和序列 -正确数据表、问题数据表 BatchInsert.goSql(updateListTemp); BatchInsert.goSql(updateTemp_seq); BatchInsert.goSql(insertListTemp); BatchInsert.goSql(insertTemp_seq); // 导入sql: String insertSql ="insert into insertListTemp"+tableName+" values(updateTemp_seq"+tableName+".nextval,?,?,?,?,?)"; BatchInsert.exeBatch(insertSql , usrlist); // 批量导入到新增数据表 // 插入问题数据(用户已存在)-sql insertSql ="insert into updateListTemp"+tableName+" select updateTemp_seq"+tableName+".nextval," +"temp.msisdn,temp.custname,temp.phoneType,temp.creditamount,temp.payType" +" from insertListTemp"+tableName+" temp where temp.msisdn in (select msisdn from usr_end_user )"; BatchInsert.goSql(insertSql); // 删除问题数据(用户已存在)-sql String deleteErrorSql ="delete from insertListTemp"+tableName+" where msisdn in (select msisdn from usr_end_user )"; BatchInsert.goSql(deleteErrorSql); // 只修改数据 String[] col = new String[] {"id","msisdn","custname","phoneType","creditamount","payType"}; List<List<Object>> failList = BatchInsert.selectToList("select * from updateListTemp"+tableName,col); failsize += failList.size(); StringBuffer failbuffer = new StringBuffer(); failbuffer.append("<br><br>一、请注意保留以下信息,此提示只显示一次 !"); failbuffer.append("<br><br>二、失败数据,"); if(failList != null && failList.size() > 0){ failbuffer.append("<br><br> 账号:"); for(List<Object> fail :failList){ failbuffer.append(fail.get(1)+", "); } failbuffer.append("<br><br>原因:用户已经存在,请直接编辑。"); } for(List<Object> fail : failList){ fail.remove(0); } usrlist.removeAll(failList); // 求差 if(failMsisdnList != null && failMsisdnList.size() > 0){ failbuffer.append("<br><br> 账号:"); for(String msisdn :failMsisdnList){ // 电话、宽带账号格式不对 failbuffer.append(msisdn+", "); } failbuffer.append("<br><br>原因:电话号码或宽带账号格式不对。"); } String squence ="select USR_CUSTOMER_SEQ.nextval cust_id from (select 1 from all_objects where rownum <= "+usrlist.size()+")"; List<String> squenceList = BatchInsert.selectSql(squence); // 拼装customList List<List<Object>> customList = new ArrayList<List<Object>>(); Long maxCode = (long) 0; String maxCustCode = "";for(int i=0; i< usrlist.size(); i++){List<Object> custom = new ArrayList<Object>();custom.add(squenceList.get(i)); // usr_customer表的序列值custom.add(usrlist.get(i).get(1)); // custnamecustomList.add(custom);if(i<2){maxCustCode = usermgrService.findMaxCustCode();if(maxCustCode!= null && !"".equals(maxCustCode)){ maxCode = Long.parseLong(maxCustCode)+1; custom.add(String.valueOf(maxCode)); }else{ custom.add(String.valueOf("10000000")); // setCustCode }}else{maxCode += 1;custom.add(String.valueOf(maxCode)); // setCustCode}}// 客户表insert Sql:String customerSql ="insert into usr_customer(cust_id,cust_name,cust_code)values(?,?,?)";BatchInsert.exeBatch(customerSql , customList); // 插入客户表// 用户表insert Sql:String userSql ="insert into usr_end_user(user_id,cust_id,msisdn,creditamount,phone_type,user_payment_type," +"user_stat,user_prvc_code,user_trade_type,is_black,is_active,language,user_level,is_test_user," +"consume_limit,limit_tips,dayfee,monthfee,is_realname)" +" values(usr_end_user_seq.nextVal, ?,?,?,?,? ,?,?,?,?,? ,?,?,?,?,? ,?,?,?) ";// 拼装userListList<List<Object>> userList = new ArrayList<List<Object>>(); for(int i=0;i<usrlist.size();i++){List<Object> user = new ArrayList<Object>();user.add(squenceList.get(i)); //cust_iduser.add(usrlist.get(i).get(0));//msisdnuser.add(String.valueOf(usrlist.get(i).get(3))); //creditamountif("电话号码".equals(String.valueOf(usrlist.get(i).get(2)))){user.add("1"); //phone_type}else if("宽带号码".equals(String.valueOf(usrlist.get(i).get(2)))){user.add("3"); //phone_type}if("后付费用户".equals(String.valueOf(usrlist.get(i).get(4)))){user.add("1"); //user_payment_type}else if("预付费用户".equals(String.valueOf(usrlist.get(i).get(4)))){user.add("2"); //user_payment_type}user.add("1"); //user_statuser.add("08"); //user_prvc_codeuser.add("0"); //user_trade_type-账户行业类型:默认为家庭user.add(0); //is_blackuser.add(0); //is_activeuser.add("01"); //LANGUAGEuser.add("01"); //USER_LEVELuser.add(0); //IS_TEST_USERuser.add("'3|0|0|0|0|0|0|0|0'");//consumeLimituser.add("'0|0|0'");//LIMIT_TIPSuser.add("0"); //DAYFEEuser.add("0"); //MONTHFEEuser.add("1"); //IS_REALNAMEuserList.add(user);}BatchInsert.exeBatch(userSql ,userList); // 正式插入用户表 /*try{//日志 log(session, UsermgrServiceImpl.class,"exeBatch", "批量新增用户");log(session, UsermgrServiceImpl.class,"exeBatch", "批量新增客户");}catch (Exception e){ e.printStackTrace();}*/ // 删除临时表 String dropSql ="drop table updateListTemp"+tableName; String dropSql2 ="drop sequence updateTemp_seq"+tableName; String dropSql3 ="drop table insertListTemp"+tableName; String dropSql4 ="drop sequence insertTemp_seq"+tableName; BatchInsert.goSql(dropSql); BatchInsert.goSql(dropSql2); BatchInsert.goSql(dropSql3); BatchInsert.goSql(dropSql4); Long end = System.currentTimeMillis();System.out.println();System.out.println("导入用户信息表"+userList.size()+"条数据,总用时==============================:"+ (end-start)/1000+"秒 。");System.out.println();if(failsize > 0){OprResult.SetOprResult(request, "提示信息", "部分用户信息导入失败,如下:"+failbuffer, backUrl, OprResult.FAILURE);}else{OprResult.SetOprResult(request, "提示信息", "用户信息导入成功", backUrl, OprResult.SUCCESS);}} catch (EncryptedDocumentException e) {e.printStackTrace();OprResult.SetOprResult(request, "导入用户异常","导入用户信息失败,请刷新页面后重试 !", backUrl,OprResult.FAILURE);} catch (InvalidFormatException e) {e.printStackTrace();OprResult.SetOprResult(request, "导入用户异常","导入用户信息失败,请刷新页面后重试 !", backUrl,OprResult.FAILURE);} catch (IOException e) {e.printStackTrace();OprResult.SetOprResult(request, "导入用户异常","导入用户信息失败,请刷新页面后重试 !", backUrl,OprResult.FAILURE);} catch (Exception e) {e.printStackTrace();OprResult.SetOprResult(request, "导入用户异常","导入用户信息失败,请刷新页面后重试 !", backUrl,OprResult.FAILURE);} return "/op/oprResult"; }
/** * 判断是不是合法手机号码 * @param mobile * @return */public static boolean isMobile(String mobile) {Pattern pattern = Pattern.compile("^((13[0-9])|(15[0-9])|(18[0-9]))\\d{8}$");return pattern.matcher(mobile).matches();}
批量导入、表格解析工具类:
package com.mc.common.util;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.List;import com.zznode.ismp.mc.common.MspException;/** * 批量导入工具类 * @author JiangYu */public class BatchInsert {// private static String url="jdbc:oracle:thin:@127.0.0.1:9521:orcl"; private static String url = MspConfiguration.getInstance().getParaValue("DBUrl"); // oracle数据库用户名 private static String user = MspConfiguration.getInstance().getParaValue("DBUser"); // oracle数据库密码 private static String password = MspConfiguration.getInstance().getParaValue("DBPassword"); public static Connection conn; public static PreparedStatement ps; public static ResultSet rs; public static Statement st ; public static Connection getConnection(){ //连接数据库的方法 try { Class.forName("oracle.jdbc.driver.OracleDriver"); //初始化驱动包 conn = DriverManager.getConnection(url, user, password); } catch (Exception e) { e.printStackTrace(); } return conn; } public static void main(String[] args) { getConnection(); if(conn==null){ System.out.println("与oracle数据库连接失败!"); }else{ System.out.println("与oracle数据库连接成功!"); } } /** * 批量运行sql * @param con * @param sql * @param list */ public static void exeBatch(String sql,List<List<Object>> list) throws Exception{ try { StringBuffer sqlbuffer = new StringBuffer(); sqlbuffer.append(sql); Connection con = getConnection(); con.setAutoCommit(false);// 关闭事务自动提交 final int batchSize = 1000; // 每满1000条数据运行一次 int count = 0; Long startTime = System.currentTimeMillis(); PreparedStatement pst = (PreparedStatement) con.prepareStatement(String.valueOf(sql)); if(list != null && list.size() > 0){ for (int i = 0; i < list.size(); i++) { for(int x =0;x<list.get(i).size();x++){ pst.setObject(x+1,list.get(i).get(x)); } pst.addBatch();// 把一个SQL命令加入命令列表 if(++count % batchSize == 0 ){ pst.executeBatch(); count = 0; } } } pst.executeBatch(); con.commit(); pst.close(); con.close(); Long endTime = System.currentTimeMillis(); System.out.println("单纯inserrt用时:" + (endTime - startTime)); } catch (Exception e) { e.printStackTrace(); throw new MspException("网络不畅,请刷新页面后重试 !"); } } // 解析数据表 public static List<List<Object>> selectToList(String sql,String[] col){ Connection conn = null;//定义为空值 Statement stmt = null; ResultSet rs = null; conn = getConnection(); List<Object> list = null; List<List<Object>> resultList = new ArrayList<List<Object>>(); try { stmt = conn.createStatement();//创建一个Statement语句对象 rs = stmt.executeQuery(sql);//执行sql语句 while(rs.next()){ list = new ArrayList<Object>(); for(int i=0; i< col.length; i++){ list.add(rs.getObject(col[i])); } resultList.add(list); } } catch (SQLException e) { e.printStackTrace(); }finally{ try {conn.close();stmt.cancel(); rs.close(); }catch (SQLException e) {e.printStackTrace(); } } return resultList; } // 执行增、删、改sql public static void goSql(String sql){ Connection conn = null;//定义为空值 Statement stmt = null; conn = getConnection(); try { stmt = conn.createStatement(); } catch (SQLException e) { e.printStackTrace(); } //4、执行语句 try { stmt.executeUpdate(sql); } catch (SQLException e) { e.printStackTrace(); } //5、关闭操作 try { stmt.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } // 单纯查询 public static List<String> selectSql(String sql){ Connection conn = null;//定义为空值 Statement stmt = null; ResultSet rs = null; conn = getConnection(); List<String> list = new ArrayList<String>(); try { stmt = conn.createStatement();//创建一个Statement语句对象 rs = stmt.executeQuery(sql);//执行sql语句 while(rs.next()){ list.add(rs.getString("cust_id")); } } catch (SQLException e) { e.printStackTrace(); }finally{ try {conn.close();stmt.cancel(); rs.close(); }catch (SQLException e) {e.printStackTrace(); } } return list; } }
说明:此句是为了一次性获取 多个序列值,方便 2 表关联:
String squence ="select USR_CUSTOMER_SEQ.nextval cust_id from (select 1 from all_objects where rownum <= "+usrlist.size()+")";
阅读全文
1 0
- 关联表多数据的批量insert (批量导入,测试10W条数据用时46秒)
- 一次性获取多个oracle序列的值,实现关联表多数据的批量insert
- Bulk insert 批量导入数据的限制
- Oracle批量导入100万条数据不到1秒
- bulk insert批量数据导入
- 百万条数据批量导入
- BULK INSERT 实现批量数据导入
- SQL Server Bulk Insert批量数据导入
- SQL Server Bulk Insert批量数据导入
- SQL Server Bulk Insert 批量数据导入
- SqlServer批量导入C#100万条数据仅4秒附源码
- 批量更新关联表的数据
- sqlserver批量更新关联表的数据
- Springboot+Mybatis批量导入多条数据
- MySQL像数据库批量插入100w条数据
- 【mybatis】批量insert数据
- Oracle 批量数据的导入
- SQL批量数据导入,性能测试
- 关于阶乘你了解多少?
- 分栏报表-物品清单报表实现
- docker操作
- truffle环境搭建
- codeforces Vessels(并查集)
- 关联表多数据的批量insert (批量导入,测试10W条数据用时46秒)
- Codeforces 337D 树形dp + 小技巧
- 快速幂1006
- Ubuntu 16.04 IP分享服务器搭建
- 本人精心收集的近80个国内最好的嵌入式技术相关网站和论坛和博客
- Problem 10 Summation of primes
- 全局异常处理
- 海康摄像头
- android webview加载html图片自适应手机屏幕大小&点击查看大图