一、业务需求 ：作多个批量导入 ，根据业务不同，每条数据导入前作各种验证，
导入后提示验证失败的所有数据和原因。
二、思路： 1. 批量导入用 jdbc 直连数据库 addBatch方法实现 ，不走 Mybatis ，
2. 数据验证用 SQL 语句实现，不走 Mybatis ，
3. 创建临时表记录合格数据并导入正式数据库表
4. 创建临时表记录验证失败的数据，并最终返回
5. 由于业务需求批量导入时是要导入到 2 张主外键关联表 ，
所以一次性获取多个序列值以实现 2 表主外键的一致性。
6. 用 sessionID 区分表名和序列名，实现并发导入时数据无污染 。
三、代码：
代码语言：javascript复制 /**
* 跳转用户导入页面
* @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();
}批量导入、表格解析工具类：
代码语言：javascript复制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() ")";
