后端开发：【批量插入海量数据之Java插入MySql】解决方案

一、解析问题。

Java向MySql数据库插入万级记录时，采用的方案不同时执行速度会有所不同，数据量越大则优劣越明显。所以选取最优方案尤其重要，本文目前提供如下两种解决方案（不借用第三方框架或工具）。

二、解决问题。

1、方案一：循环逐条插入。

关键代码：

//DataModel 为自定义的数据模型类，dataList 即传入的即将要插入的数据集合；
public int insertData(List<DataModel> dataList) throws ClassNotFoundException, SQLException{

//开始计时；
Long begin = new Date().getTime();

//创建要执行的sql语句；
String sql = "insert into tb_ncdc values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

/* 创建并获取JDBC连接类"Connection"的实例对象。（DBUtil类内为数据库访问的配置信息，需要自定义）*/
Connection connection = new DBUtil().getDbCon();

//PrepareStatement类存放每条记录对应的字段值；
PreparedStatement preparedStatement= connection.prepareStatement(sql);

    for (int i = 0; i < dataList.size(); i ++) {

        preparedStatement.clearParameters();

        preparedStatement.setString(1, dataList.get(i).getSTN());

        preparedStatement.setString(2, dataList.get(i).getWBAN());

        preparedStatement.setString(3, dataList.get(i).getYEARMODA());

        preparedStatement.setString(4, dataList.get(i).getTEMP());

        preparedStatement.setString(5, dataList.get(i).getDEWP());

        preparedStatement.setString(6, dataList.get(i).getSLP());

        preparedStatement.setString(7, dataList.get(i).getSTP());

        preparedStatement.setString(8, dataList.get(i).getVISIB());

        preparedStatement.setString(9, dataList.get(i).getWDSP());

        preparedStatement.setString(10, dataList.get(i).getMXSPD());

        preparedStatement.setString(11, dataList.get(i).getGUST());

        preparedStatement.setString(12, dataList.get(i).getMAX());

        preparedStatement.setString(13, dataList.get(i).getMIN());

        preparedStatement.setString(14, dataList.get(i).getPRCP());

        preparedStatement.setString(15, dataList.get(i).getSNDP());

        preparedStatement.setString(16, dataList.get(i).getFRSHTT());

        preparedStatement.execute();
     }

    /*如果autocommit=false时（默认为true，即自动提交事务）记得将本次事务提交，否则数据库里没有数据的；*/
    //connection.commit();

    //所有数据库操作结束后记得关闭连接，减少内存的占用；
    preparedStatement.close();
    connection.close();

    // 结束时间
    Long end = new Date().getTime();

    //总耗时
    System.out.println("插入"+dataList.size()+"条数据的总时间为 : " + (end - begin)  + " ms");

    return 1;

}复制代码

2、方案二：分批事务插入。

//DataModel 为自定义的数据模型类，dataList 即传入的即将要插入的数据集合；
public int insertData(List<DataModel> dataList) throws ClassNotFoundException, SQLException {

        //设定每批、每次事务插入多少条数据；
        int itemNum = 1000;

        //开始时间；
        Long begin = new Date().getTime();

        // 创建sql前缀
        String prefix = "INSERT INTO tb_ncdc VALUES ";

        /* 创建并获取JDBC连接类"Connection"的实例对象。（DBUtil类内为数据库访问的配置信息，需要自定义） */
        Connection connection = new DBUtil().getDbCon();

        // PrepareStatement类存放每条记录对应的字段值；
        PreparedStatement preparedStatement= connection.prepareStatement("");

        // 创建sql后缀
        StringBuffer suffix = new StringBuffer();

        // 设置事务为非自动提交
        connection.setAutoCommit(false);

        //根据总的数据量计算需要分多少次事务插入；
        int numTrans = dataList.size() / itemNum + 1;

        //设定首次事务中的数据在集合中的索引为0；
        int numData = 0;

        //  外层循环，j代表提交事务次序；
        for (int j = 1; j <= numTrans; j++) {

        // 从索引numData开始查找总数为itemNum个数据，即为本批要插入的数据量；
        for (int i = numData; i < numData + itemNum; i++) {

            //判定如果是最后一批，可能会不足itemNum数量，则够数结束，防止数组越界；
            if (i == dataList.size()) {
                break;
            }

            // 构建sql后缀
            suffix.append("('" + dataList.get(i).getSTN() + "','" + dataList.get(i).getWBAN() + "','"
            + dataList.get(i).getYEARMODA() + "','" + dataList.get(i).getTEMP() + "','"
            + dataList.get(i).getDEWP() + "','" + dataList.get(i).getSLP() + "','"
            + dataList.get(i).getSTP() + "','" + dataList.get(i).getVISIB() + "','"
            + dataList.get(i).getWDSP() + "','" + dataList.get(i).getMXSPD() + "','"
            + dataList.get(i).getGUST() + "','" + dataList.get(i).getMAX() + "','"
            + dataList.get(i).getMIN() + "','" + dataList.get(i).getPRCP() + "','"
            + dataList.get(i).getSNDP() + "','" + dataList.get(i).getFRSHTT() + "'),");
        }

        // 构建完整sql
        String sql = prefix + suffix.substring(0, suffix.length() - 1);

        // 添加sql批；
        preparedStatement.addBatch(sql);

        // 执行sql批；
        preparedStatement.executeBatch();

        // 提交本次事务
        connection.commit();

        // 清空上一次的sql后缀；
        suffix = new StringBuffer();

        numData += itemNum;

        }

        // 所有数据库操作结束后记得关闭连接，减少内存的占用；
        preparedStatement.close();
        connection.close();

        // 结束时间
        Long end = new Date().getTime();

        // 耗时
        System.out.println("插入" + dataList.size() + "条数据的总时间为 : "+ (end - begin) + " ms");

        return 1;

}复制代码

三 、总结问题。

1.两种方案的主要区别在于，sql语句的不同、batch批和事务的使用。

单条插入sql语句：insert into Table (col1,col2...) values (val11,val12...);

多条批插入sql语句：insert intoTable (col1,col2...) values (val11,val12...),(val11,val12...),...;

2.本次测试的实例中，插入69万条数据左右，方案二要比方案一的速度快上10倍左右。具体测试得到的具体毫秒数可能不同。影响因素个人认为有如下几条：

（1）数据模型，每条数据记录的字段越多，就需要调整itemNum(每批插入的数据量，可以采用二分法找到最合适的数值)，或者调整MySql数据库对每次执行sql语句的字节长度限制（网上自行搜索）。itemNum值找到最合适的，速度才可能在其他条件同等的条件下是最快的；

（2）主机配置。包括处理器性能、硬盘性能 ，mysql数据库可能也会影响到速度；

本节Demo源码附带测试数据包及数据库脚本GitHub下载地址：

github.com/Breaker-93/…

若有任何疑问，请留言。