How to Optimize Multiple Row Inserts in MySQL using Java's PreparedStatement?

Inserting Multiple Rows Simultaneously using PreparedStatement in Java

Question:

How can one optimize the insertion of multiple rows into a MySQL table using Java, taking advantage of the supported MySQL syntax for inserting multiple values in one statement?

Answer:

Utilizing PreparedStatement#addBatch() and PreparedStatement#executeBatch() allows for the creation of a batch of inserts and its subsequent execution.

Example:

Consider the following example to initiate batch insertion:

public void save(List<Entity> entities) throws SQLException {
    try (
        Connection connection = database.getConnection();
        PreparedStatement statement = connection.prepareStatement(SQL_INSERT);
    ) {
        int i = 0;

        for (Entity entity : entities) {
            statement.setString(1, entity.getSomeProperty());
            // ...

            statement.addBatch();
            i++;

            if (i % 1000 == 0 || i == entities.size()) {
                statement.executeBatch(); // Execute every 1000 items.
            }
        }
    }
}

Implementation Details:

• Insertions are performed in batches of 1000 to avoid potential limitations in JDBC drivers or databases on batch length.
• The JDBC tutorial provides additional resources on using PreparedStatements and batch updates.

The above is the detailed content of How to Optimize Multiple Row Inserts in MySQL using Java's PreparedStatement?. For more information, please follow other related articles on the PHP Chinese website!