easyexcel结合jdbc手动控制事务加批处理实现百万数据导入

@Resource
private EmpService empService;
 
@GetMapping("/importData")
public void importData() {
    String fileName = "C:\\Users\\asus\\Desktop\\员工信息.xlsx";
    //记录开始读取Excel时间,也是导入程序开始时间
    long startReadTime = System.currentTimeMillis();
    System.out.println("------开始读取Excel的Sheet时间(包括导入数据过程):" + startReadTime + "ms------");
    //读取所有Sheet的数据.每次读完一个Sheet就会调用这个方法
    EasyExcel.read(fileName, new EasyExceGeneralDatalListener(empService)).doReadAll();
    long endReadTime = System.currentTimeMillis();
    System.out.println("------结束读取Excel的Sheet时间(包括导入数据过程):" + endReadTime + "ms------");
    System.out.println("------读取Excel的Sheet时间(包括导入数据)共计耗时:" + (endReadTime-startReadTime) + "ms------");
}

Excel导入事件监听

// 事件监听
public class EasyExceGeneralDatalListener extends AnalysisEventListener<Map<Integer, String>> {
    /**
     * 处理业务逻辑的Service,也可以是Mapper
     */
    private EmpService empService;
 
    /**
     * 用于存储读取的数据
     */
    private List<Map<Integer, String>> dataList = new ArrayList<Map<Integer, String>>();
    public EasyExceGeneralDatalListener() {
    }
 
    public EasyExceGeneralDatalListener(EmpService empService) {
        this.empService = empService;
    }
 
    @Override
    public void invoke(Map<Integer, String> data, AnalysisContext context) {
        //数据add进入集合
        dataList.add(data);
        //size是否为100000条:这里其实就是分批.当数据等于10w的时候执行一次插入
        if (dataList.size() >= ExcelConstants.GENERAL_ONCE_SAVE_TO_DB_ROWS) {
            //存入数据库:数据小于1w条使用Mybatis的批量插入即可;
            saveData();
            //清理集合便于GC回收
            dataList.clear();
        }
    }
 
    /**
     * 保存数据到DB
     *
     * @param
     * @MethodName: saveData
     * @return: void
     */
    private void saveData() {
        empService.importData(dataList);
        dataList.clear();
    }
 
    /**
     * Excel中所有数据解析完毕会调用此方法
     *
     * @param: context
     * @MethodName: doAfterAllAnalysed
     * @return: void
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData();
        dataList.clear();
    }
}

核心业务代码

public interface EmpService {
    void export() throws IOException;
 
    void importData(List<Map<Integer, String>> dataList);
}
    /*
     * 测试用Excel导入超过10w条数据,经过测试发现,使用Mybatis的批量插入速度非常慢,所以这里可以使用 数据分批+JDBC分批插入+事务来继续插入速度会非常快
    */
    @Override
    public void importData(List<Map<Integer, String>> dataList) {
        //结果集中数据为0时,结束方法.进行下一次调用
        if (dataList.size() == 0) {
            return;
        }
        //JDBC分批插入+事务操作完成对20w数据的插入
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            long startTime = System.currentTimeMillis();
            System.out.println(dataList.size() + "条,开始导入到数据库时间:" + startTime + "ms");
            conn = JDBCDruidUtils.getConnection();
            //控制事务:默认不提交
            conn.setAutoCommit(false);
            String sql = "insert into emp (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) values";
            sql += "(?,?,?,?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            //循环结果集:这里循环不支持lambda表达式
            for (int i = 0; i < dataList.size(); i++) {
                Map<Integer, String> item = dataList.get(i);
                ps.setString(1, item.get(0));
                ps.setString(2, item.get(1));
                ps.setString(3, item.get(2));
                ps.setString(4, item.get(3));
                ps.setString(5, item.get(4));
                ps.setString(6, item.get(5));
                ps.setString(7, item.get(6));
                ps.setString(8, item.get(7));
                //将一组参数添加到此 PreparedStatement 对象的批处理命令中。
                ps.addBatch();
            }
            //执行批处理
            ps.executeBatch();
            //手动提交事务
            conn.commit();
            long endTime = System.currentTimeMillis();
            System.out.println(dataList.size() + "条,结束导入到数据库时间:" + endTime + "ms");
            System.out.println(dataList.size() + "条,导入用时:" + (endTime - startTime) + "ms");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //关连接
            JDBCDruidUtils.close(conn, ps);
        }
    }
 
}

jdbc工具类

//JDBC工具类
public class JDBCDruidUtils {
    private static DataSource dataSource;
 
    /*
   创建数据Properties集合对象加载加载配置文件
    */
    static {
        Properties pro = new Properties();
        //加载数据库连接池对象
        try {
            //获取数据库连接池对象
            pro.load(JDBCDruidUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
            dataSource = DruidDataSourceFactory.createDataSource(pro);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
 
    /*
    获取连接
     */
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
 
 
    /**
     * 关闭conn,和 statement独对象资源
     *
     * @param connection
     * @param statement
     * @MethodName: close
     * @return: void
     */
    public static void close(Connection connection, Statement statement) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
 
    /**
     * 关闭 conn , statement 和resultset三个对象资源
     *
     * @param connection
     * @param statement
     * @param resultSet
     * @MethodName: close
     * @return: void
     */
    public static void close(Connection connection, Statement statement, ResultSet resultSet) {
        close(connection, statement);
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
 
    /*
    获取连接池对象
     */
    public static DataSource getDataSource() {
        return dataSource;
    }
 
}

druid.properties配置文件

这里我将文件创建在类路径下，需要注意的是连接mysql数据库时需要指定rewriteBatchedStatements=true批处理才会生效，否则还是逐条插入效率较低，allowMultiQueries=true表示可以使sql语句中有多个insert或者update语句（语句之间携带分号），这里可以忽略。

# druid.properties配置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/llp?autoReconnect=true&useUnicode=true&useSSL=false&serverTimezone=GMT%2B8&allowMultiQueries=true&rewriteBatchedStatements=true
username=root
password=root
initialSize=10
maxActive=50
maxWait=60000

测试结果

------开始读取Excel的Sheet时间(包括导入数据过程):1674181403555ms------
200000条,开始导入到数据库时间:1674181409740ms
2023-01-20 10:23:29.943  INFO 18580 --- [nio-8888-exec-1] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} inited
200000条,结束导入到数据库时间:1674181413252ms
200000条,导入用时:3512ms
200000条,开始导入到数据库时间:1674181418422ms
200000条,结束导入到数据库时间:1674181420999ms
200000条,导入用时:2577ms
.....
200000条,开始导入到数据库时间:1674181607405ms
200000条,结束导入到数据库时间:1674181610154ms
200000条,导入用时:2749ms
------结束读取Excel的Sheet时间(包括导入数据过程):1674181610155ms------
------读取Excel的Sheet时间(包括导入数据)共计耗时:206600ms------