java 使用带有 autocommit=true 的 jdbc 时回滚批处理执行

Rollback batch execution when using jdbc with autocommit=true

提问by Teja

Im using JDBC, with autocommit=true. In one of the operation, I'm doing a batch inserts, using prepared statements.

我使用 JDBC，autocommit=true。在其中一个操作中，我使用准备好的语句进行批量插入。

public void executeBatchInsert(String query, List<Object[]> entityList)  {
        try {
            pstmt = conn.prepareStatement(query);
            for(int i=0; i<entityList.size(); i++) {
                int j=1;
                for(Object o: entityList.get(i)) {
                    pstmt.setObject(j++, formatColumnValue(o));
                }

                pstmt.addBatch();
                if((i+1)%1000 == 0) {
                    pstmt.executeBatch();
                }
            }
            pstmt.executeBatch();
        } catch (SQLException e) {
        }
    }

If I get an exception while executing it, when I close this connection, will all the locks be released and the rollback happens?

如果我在执行时遇到异常，当我关闭此连接时，是否会释放所有锁并进行回滚？

-- B. Teja.

——B.泰哈。

回答by dazito

The direct answer to your question is: no. If an exception occurs you have to manually call the rollbackmethod. And before doing so you must setAutoCommitto false. By default auto commit is set to true. With auto commit set to trueyou can't do a rollback, an exceptionwill occur telling you that.

你的问题的直接答案是：不。如果发生异常，您必须手动调用该rollback方法。而这样做之前，所以你必须setAutoCommit给false。默认情况下，自动提交设置为true. 将自动提交设置为true您不能执行 a rollback，exception将会发生告诉您这一点。

Later on do not forget to set autoCommitback to trueor you may have non expectable results with other methods you may have.

稍后不要忘记重新设置autoCommit，true否则您可能会使用其他方法获得非预期的结果。

Here's an example on how to implement this feature. This is just sketch and you should probably pay more attention on how to handle the connection, prepared statment, exceptionand so on.

这是有关如何实现此功能的示例。这只是草图，你可能要如何处理更多的关注connection，prepared statment，exception等等。

public void insertAndRollback(Connection connection) {
    try {
        final ArrayList parameters = new ArrayList();

        // Add your parameters to the arraylist
        parameters.add("John");
        parameters.add("Lee");
        parameters.add("Mary");
        parameters.add("Peter");
        parameters.add("Lewis");
        parameters.add("Patrick");

        final String parameterizedQuery = "insert into person (name) values (?)";

        final int batchSize = 5; // Set your batch size here
        int count = 0;
        int aux = 0;

        // Get the total number of '?' in the query
        int totalQueryParameters = Utils.countCharOccurrences(parameterizedQuery, '?');
        final int auxTotalQueryParameters = totalQueryParameters;

        final PreparedStatement preparedStatement = connection.prepareStatement(parameterizedQuery);

        // Auto Commit must be set to false
        connection.setAutoCommit(false);

        for(int i = 0; i < parameters.size(); i++)
        {
            Object obj = parameters.get(i);

            aux++;
            preparedStatement.setObject(aux, obj);

            if(totalQueryParameters == i + 1) { // Because the ArrayList starts from zero.
                // First query "parsed" - > Add to batch
                preparedStatement.addBatch();
                // One query has been added to the batch. Re-adapt the cycle.
                totalQueryParameters = totalQueryParameters + auxTotalQueryParameters;
                aux = 0;
            }

            if(++count % batchSize == 0) {
                preparedStatement.executeBatch();
            }
        }

        preparedStatement.executeBatch(); // insert remaining queries
        preparedStatement.close();
        connection.setAutoCommit(true); // Make it back to default.
    } catch (SQLException ex) {
        // Do the rollback
        doRollback(connection);

        try {
            // Make it back to default.
            connection.setAutoCommit(true);
        } catch (SQLException ex1) {
            ex1.printStackTrace();
        }

        // Dont forget to close the preparedStatement and the connection
        // if you don't need the connection open any more.

        ex.printStackTrace();
    }
}


private void doRollback(Connection c) {
    try {
        c.rollback();
    } catch (SQLException ex) {
        ex.printStackTrace();
    }
}

回答by Evgeniy Dorofeev

As a matter of fact PreparedStatement.executeBatch does not clarify the issue, maybe somewhere else, but I am sure it is not an atomic operation because SQL has no batch operation so executeBatch executes each statement separately at DB level. I tested it on MySQL:

事实上 PreparedStatement.executeBatch 没有澄清问题，也许在其他地方，但我确定它不是原子操作，因为 SQL 没有批处理操作，因此 executeBatch 在 DB 级别单独执行每个语句。我在 MySQL 上测试过：

t1 is an empty table that has n1 INT(11) Not Null column, autocommit = true

t1 是一个空表，有 n1 个 INT(11) Not Null 列，autocommit = true

    ResultSet rs1 = conn.createStatement().executeQuery("select count(*) from t1");
    rs1.next();
    System.out.println(rs1.getInt(1));

    String query = "insert into t1 (n1) values(?)";
    PreparedStatement ps = conn.prepareStatement(query);
    ps.setObject(1, 1);
    ps.addBatch();
    ps.setObject(1, null);
    ps.addBatch();
    try {
        ps.executeBatch();
    } catch (Exception e) {
        System.out.println(e);
    }

    ResultSet rs2 = conn.createStatement().executeQuery("select count(*) from t1");
    rs2.next();
    System.out.println(rs2.getInt(1));

it prints

它打印

0
java.sql.BatchUpdateException: Column 'n1' cannot be null
1

that is, there were 2 inserts in the batch; first succeded, the second failed, still t1 got 1 row

也就是说，批次中有 2 个插入；第一次成功，第二次失败，仍然 t1 有 1 行

回答by TheWhiteRabbit

tricky one , autocommit=trueStrongly not recommended, when executing batch.

棘手的一个， autocommit=true强烈不推荐，在执行批处理时。

Having said that, i recommend use getUpdateCount()and build logic around to execute remaining.

话虽如此，我建议使用getUpdateCount()和构建逻辑来执行剩余部分。

finally commit

最后 commit