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

Inserting Multiple Rows into MySQL using PreparedStatement

When attempting to efficiently insert numerous rows into a MySQL table, one may encounter limitations with the traditional PreparedStatement approach because it cannot The number of rows to be inserted is determined in advance in PreparedStatement.

In order to optimize the insertion process, MySQL provides batch insertion syntax, as follows:

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

Use PreparedStatement for batch insertion

Use PreparedStatement The steps for batch insertion are as follows:

1. Use addBatch() Method adds each row of data to the batch.
2. Use executeBatch() method to execute batch processing.

Sample code:

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

It should be noted that when performing batch insertion, it is recommended to perform it every certain number of rows (for example, 1000) , because some JDBC drivers or databases may have limitations on batch length.

Related references:

• JDBC tutorial - Using PreparedStatement
• JDBC tutorial - Using Statement Objects for Batch Updates

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