How Can I Efficiently Insert Multiple Rows into MySQL Using Java's PreparedStatement?

Insert Multiple Rows into MySQL with PreparedStatement Using PreparedStatement

In Java, efficiently inserting multiple rows into a MySQL table can be achieved using PreparedStatement's batch processing capabilities. Previously, executing individual SQL statements for each row was common practice:

for (String element : array) {
    myStatement.setString(1, element[0]);
    myStatement.setString(2, element[1]);

    myStatement.executeUpdate();
}

However, to optimize performance, MySQL offers a syntax that allows inserting multiple rows in one go:

INSERT INTO table (col1, col2) VALUES ('val1', 'val2'), ('val1', 'val2')[, ...]

Utilizing this approach with PreparedStatement requires batch processing:

1. Create a PreparedStatement: Prepare the SQL statement and set its parameters for each row. Use addBatch() to append each statement to the batch.
2. Execute the Batch: Once all rows are added, call executeBatch() to execute the entire batch. This method returns an array of update counts, indicating the number of rows affected by each statement.

Here's an example illustrating the process:

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.
            }
        }
    }
}

Executing the batch every 1000 rows helps avoid potential limitations imposed by JDBC drivers or databases on batch size.

Refer to the JDBC tutorial for further details on using PreparedStatement and Statement objects for batch updates.

The above is the detailed content of How Can I Efficiently Insert Multiple Rows into MySQL Using Java's PreparedStatement?. For more information, please follow other related articles on the PHP Chinese website!