JDBC批量插入遇到的坑

 首先看代码，这段代码是测试插入多条数据耗时。

 测试环境：jdk1.8，mysql-5.7.27-winx64，mysql-connector-java-5.1.10.jar

public class Test
{
    /**
     * rewriteBatchedStatements=true    开启批量更新
     */
    private static final String URL = "jdbc:mysql://localhost:3306/test?characterEncoding=utf-8";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "root";
    private static Connection connection;
    private static PreparedStatement pstm;

    static
    {
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (ClassNotFoundException e)
        {
            e.printStackTrace();
        } catch (SQLException e)
        {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws SQLException
    {
        deleteAll();
        update();
        deleteAll();
        addBatch();
        connection.close();
    }
    
    public static void update() throws SQLException
    {
        long startTime = System.currentTimeMillis();
        String sql = "insert into `user` (`id`,`name` ) values (?, ?);";
        pstm = connection.prepareStatement(sql);
        for (int i = 1; i <= 10000; i++)
        {
            pstm.setInt(1, i);
            pstm.setString(2, "test" + i);
            pstm.executeUpdate();
        }
        pstm.close();
        long endTime = System.currentTimeMillis();
        System.out.println("update方法耗时: " + (endTime - startTime));
    }


    public static void addBatch() throws SQLException
    {
        long startTime = System.currentTimeMillis();
        String sql = "insert into `user` (`id`,`name` ) values (?, ?);";
        pstm = connection.prepareStatement(sql);
        for (int i = 1; i <= 10000; i++)
        {
            pstm.setInt(1, i);
            pstm.setString(2, "test" + i);
            pstm.addBatch();
        }
        pstm.executeBatch();
        pstm.close();
        long endTime = System.currentTimeMillis();
        System.out.println("addBatch方法耗时: " + (endTime - startTime));
    }

    /**
     * 删除user表中的所有数据
     */
    public static void deleteAll() throws SQLException
    {
        String sql = "delete from `user` where `id` > 0;";
        pstm = connection.prepareStatement(sql);
        pstm.executeUpdate();
        pstm.close();
    }
}

代码执行结果： 

update方法耗时: 19291
addBatch方法耗时: 18971

可以看到区别不大。原因是：

MySQL JDBC驱动在默认情况下会无视executeBatch()语句，把我们期望批量执行的一组sql语句拆散，一条一条地发给MySQL数据库，批量插入实际上是单条插入，直接造成较低的性能。只有把rewriteBatchedStatements参数置为true, 驱动才会帮你批量执行SQL

修改URL为:

private static final String URL = "jdbc:mysql://localhost:3306/test?characterEncoding=utf-8&rewriteBatchedStatements=true";

然后执行会出现如下结果:

update方法耗时: 20696
Exception in thread "main" java.sql.BatchUpdateException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(2, 'test2'),(3, 'test3'),(4, 'test4'),(5, 'test5'),(6, 'test6'),(7, 'test7'),(8' at line 1

查看错误信息提示SQL语法有问题，但是没有加rewriteBatchedStatements=true参数时代码可以正常运行。

debug调试源码发现加了rewriteBatchedStatements=true会执行 this.executeBatchedInserts(batchTimeout);

然后判断Mysql版本是否高于4.1.0并且数据大于3条执行 this.executePreparedBatchAsMultiStatement(batchTimeout)；

否则执行 this.executeBatchSerially(batchTimeout);

接下来查看executeBatchedInserts方法:

protected int[] executeBatchedInserts(int batchTimeout) throws SQLException {
        String valuesClause = this.getValuesClause();
        Connection locallyScopedConn = this.connection;
        if (valuesClause == null) {
            return this.executeBatchSerially(batchTimeout);
        } else {
            int numBatchedArgs = this.batchedArgs.size();
            if (this.retrieveGeneratedKeys) {
                this.batchedGeneratedKeys = new ArrayList(numBatchedArgs);
            }

            int numValuesPerBatch = this.computeBatchSize(numBatchedArgs);
            if (numBatchedArgs < numValuesPerBatch) {
                numValuesPerBatch = numBatchedArgs;
            }

            java.sql.PreparedStatement batchedStatement = null;
            int batchedParamIndex = 1;
            int updateCountRunningTotal = 0;
            int numberToExecuteAsMultiValue = false;
            int batchCounter = 0;
            CancelTask timeoutTask = null;
            SQLException sqlEx = null;
            int[] updateCounts = new int[numBatchedArgs];

            for(int i = 0; i < this.batchedArgs.size(); ++i) {
                updateCounts[i] = 1;
            }

            Object ex;
            try {
                try {
                    batchedStatement = this.prepareBatchedInsertSQL((ConnectionImpl)locallyScopedConn, numValuesPerBatch);
                    if (this.connection.getEnableQueryTimeouts() && batchTimeout != 0 && this.connection.versionMeetsMinimum(5, 0, 0)) {
                        timeoutTask = new CancelTask(this, (StatementImpl)batchedStatement);
                        ConnectionImpl.getCancelTimer().schedule(timeoutTask, (long)batchTimeout);
                    }

                    int numberToExecuteAsMultiValue;
                    if (numBatchedArgs < numValuesPerBatch) {
                        numberToExecuteAsMultiValue = numBatchedArgs;
                    } else {
                        numberToExecuteAsMultiValue = numBatchedArgs / numValuesPerBatch;
                    }

                    ex = numberToExecuteAsMultiValue * numValuesPerBatch;

                    for(int i = 0; i < ex; ++i) {
                        if (i != 0 && i % numValuesPerBatch == 0) {
                            try {
                                updateCountRunningTotal += batchedStatement.executeUpdate();
                            } catch (SQLException var41) {
                                sqlEx = this.handleExceptionForBatch(batchCounter - 1, numValuesPerBatch, updateCounts, var41);
                            }

                            this.getBatchedGeneratedKeys(batchedStatement);
                            batchedStatement.clearParameters();
                            batchedParamIndex = 1;
                        }

                        batchedParamIndex = this.setOneBatchedParameterSet(batchedStatement, batchedParamIndex, this.batchedArgs.get(batchCounter++));
                    }

                    try {
                        updateCountRunningTotal += batchedStatement.executeUpdate();
                    } catch (SQLException var40) {
                        sqlEx = this.handleExceptionForBatch(batchCounter - 1, numValuesPerBatch, updateCounts, var40);
                    }

                    this.getBatchedGeneratedKeys(batchedStatement);
                    numValuesPerBatch = numBatchedArgs - batchCounter;
                } finally {
                    if (batchedStatement != null) {
                        batchedStatement.close();
                    }

                }

                try {
                    if (numValuesPerBatch > 0) {
                        batchedStatement = this.prepareBatchedInsertSQL((ConnectionImpl)locallyScopedConn, numValuesPerBatch);
                        if (timeoutTask != null) {
                            timeoutTask.toCancel = (StatementImpl)batchedStatement;
                        }

                        batchedParamIndex = 1;

                        while(true) {
                            if (batchCounter >= numBatchedArgs) {
                                try {
                                    int var10000 = updateCountRunningTotal + batchedStatement.executeUpdate();
                                } catch (SQLException var39) {
                                    ex = var39;
                                    sqlEx = this.handleExceptionForBatch(batchCounter - 1, numValuesPerBatch, updateCounts, var39);
                                }

                                this.getBatchedGeneratedKeys(batchedStatement);
                                break;
                            }

                            batchedParamIndex = this.setOneBatchedParameterSet(batchedStatement, batchedParamIndex, this.batchedArgs.get(batchCounter++));
                        }
                    }

                    if (sqlEx != null) {
                        throw new BatchUpdateException(sqlEx.getMessage(), sqlEx.getSQLState(), sqlEx.getErrorCode(), updateCounts);
                    }

                    ex = updateCounts;
                } finally {
                    if (batchedStatement != null) {
                        batchedStatement.close();
                    }

                }
            } finally {
                if (timeoutTask != null) {
                    timeoutTask.cancel();
                }

                this.resetCancelledState();
            }

            return (int[])ex;
        }
    }

这句代码生成了一个sql语句模板：

batchedStatement = this.prepareBatchedInsertSQL((ConnectionImpl)locallyScopedConn, numValuesPerBatch);

insert into `user` (`id`,`name` ) values (** NOT SPECIFIED **, ** NOT SPECIFIED **);,(** NOT SPECIFIED **, ** NOT SPECIFIED **)...

然后这段代码是给模板逐个赋值：

 batchedParamIndex = this.setOneBatchedParameterSet(batchedStatement, batchedParamIndex, this.batchedArgs.get(batchCounter++));

 现在问题已经找到了，在生成模板的时候多了个分号。是前面写的sql语句的，所以将SQL语句最后的分号去掉然后执行就没问题了。

 分号去掉后的运行结果：

update方法耗时: 20934
addBatch方法耗时: 106

文章到此结束，如有错误敬请指正。