前些天發現了一個巨牛的人工智能學習網站,通俗易懂,風趣幽默,忍不住分享一下給大家。點擊跳轉到教程。
一、業務需求 :作多個批量導入 ,根據業務不同,每條數據導入前作各種驗證,
? ? ? ? ? ? ? ? ? ? ? ? ? 導入后提示驗證失敗的所有數據和原因。
?
二、思路: 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); // 批量導入到新增數據表// 插入問題數據(用戶已存在)-sqlinsertSql ="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); // 刪除問題數據(用戶已存在)-sqlString 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);// 拼裝customListList<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;}// 執行增、刪、改sqlpublic 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()+")";
?
?