在MySQL JDBC中使用批处理SQL命令

Question

我正在尝试找出让MySQL使用批量更新的最好方法。

我创建了，比如说，10,000条记录。每条记录都需要像这样插入到数据库中：

for 1 to 10000
 1) insert into table1
 2) get auto increment id from table1 (call it table1ID)
 3) use table1ID to insert into table2
next

我看不到一种简单的方法来批量处理这种情况。也许批处理table1插入10,000次，然后获得所有表ID，然后对table2进行另一批10,000次插入。但是我需要确保我所有的插入都匹配。对于每次插入到table1中，我需要正确的插入到table2中。

Answer 1

没有办法批量插入行并返回相应的ID(从自动增量列)。您必须在循环中逐个插入，将所有ID存储在一个列表中，然后批量插入/更新/任何表2。

在您的示例中，这是10,000 +1个DB操作，与20,000个操作相比，这是一个相当大的改进。

Answer 2

下面是我如何在一个非常大的批处理SQL文件中执行此操作的示例：

• I首先插入一个值，没有父
• 我记得最后插入的id
• 我用它来插入子类别，并使用它作为最后插入的id。

这非常接近您想要做的事情：

insert into category (idparent,description) values (NULL, 'Mycat');
set @lt=LAST_INSERT_ID();
insert into category (idparent,description) values
    (@lt, 'MySubCat'),
    (@lt, 'MyOtherSubCat');

希望这能有所帮助。

Answer 3

您可以使用以下方法：在使用因为getGeneratedKeys() in语句之前进行测试，因为它依赖于使用的驱动程序。下面的代码是在Maria DB 10.0.12和Maria JDBC driver 1.2上测试的，Maria DB 10是在MySQL 5.6的基础上构建的，因此它也可以与MySQL一起工作。

请记住，增加批处理大小只能在一定程度上提高性能...对于我的设置，将批处理大小增加到500以上实际上会降低性能。

public Connection getConnection(boolean autoCommit) throws SQLException {
    Connection conn = dataSource.getConnection();
    conn.setAutoCommit(autoCommit);
    return conn;
}

private void testBatchInsert(int count, int maxBatchSize) {
    String querySql = "insert into batch_test(keyword) values(?)";
    try {
        Connection connection = getConnection(false);
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = true;
        int[] executeResult = null;
        try {
            pstmt = connection.prepareStatement(querySql, Statement.RETURN_GENERATED_KEYS);
            for (int i = 0; i < count; i++) {
                pstmt.setString(1, UUID.randomUUID().toString());
                pstmt.addBatch();
                if ((i + 1) % maxBatchSize == 0 || (i + 1) == count) {
                    executeResult = pstmt.executeBatch();
                }
            }
            ResultSet ids = pstmt.getGeneratedKeys();
            for (int i = 0; i < executeResult.length; i++) {
                ids.next();
                if (executeResult[i] == 1) {
                    System.out.println("Execute Result: " + i + ", Update Count: " + executeResult[i] + ", id: "
                            + ids.getLong(1));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            success = false;
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (connection != null) {
                if (success) {
                    connection.commit();
                } else {
                    connection.rollback();
                }
                connection.close();
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}