关联表多数据的批量insert （批量导入，测试19W条数据用时46秒）_insertbatch 大批量

作者：代码秘术家 | 2024-02-05 22:05:10

踩

insertbatch 大批量

前些天发现了一个巨牛的人工智能学习网站，通俗易懂，风趣幽默，忍不住分享一下给大家。点击跳转到教程。

一、业务需求：作多个批量导入，根据业务不同，每条数据导入前作各种验证，

导入后提示验证失败的所有数据和原因。

二、思路： 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)); // custname
					customList.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, ?,?,?,?,? ,?,?,?,?,? ,?,?,?,?,? ,?,?,?) ";
				
				// 拼装userList
				List<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_id
					user.add(usrlist.get(i).get(0));//msisdn
					user.add(String.valueOf(usrlist.get(i).get(3))); //creditamount
					if("电话号码".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_stat
					user.add("08"); //user_prvc_code
					user.add("0"); //user_trade_type-账户行业类型：默认为家庭
					user.add(0); //is_black
					user.add(0); //is_active
					user.add("01"); //LANGUAGE
					user.add("01"); //USER_LEVEL
					user.add(0); //IS_TEST_USER
					user.add("'3|0|0|0|0|0|0|0|0'");//consumeLimit
					user.add("'0|0|0'");//LIMIT_TIPS
					user.add("0"); //DAYFEE
					user.add("0"); //MONTHFEE
					user.add("1"); //IS_REALNAME
					userList.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()+")";

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/代码秘术家/article/detail/61845