Using batch SQL commands with MySQL JDBC

Question

I am trying to figure out the best way to get MySQL to use batch updates.

I create, let's say, 10,000 records. Each record needs to be inserted into the DB like so:

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

I don't see an easy way to batch that scenario. Maybe batch table1 insert 10,000 times, then get all table IDs, then another batch of 10,000 inserts to table2. But then I need to make sure all my inserts match. For each insert into table1 I need the correct insert into table2.

michael667 · Accepted Answer · 2011-10-12 20:09:45Z

1

There is no way to batch insert rows and get back the corresponding IDs (from an autoincrement column). You have to insert one-by-one in a loop, store all the IDs in a list, and then batch-insert/update/whatever table 2.

In your example, that is 10,000 + 1 DB operations, which is quite an improvement compared to 20,000 operations.

answered Oct 12, 2011 at 20:09

michael667

3,26026 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Olivier Pons Over a year ago

"There is no way to batch insert rows and get back the corresponding IDs" => This is totally wrong. See my own answer.

michael667 Over a year ago

I see how your solution works for parent/child relations, but what do you do if you want to insert 100 'parents' with 100 'children' each?

Olivier Pons Over a year ago

insert one parent, get its id, insert 100 children. Do this 100 times.

michael667 Over a year ago

How can I use this with SimpleJdbcInsert.executeBatch(), for example?

Olivier Pons Over a year ago

I'm sorry I've given a MySQL batch you can inject like "mysql -u user -ppass databasename < batch_sql_file.sql, I can't help you further, it's beyond my knowledge, sorry.

Olivier Pons · Accepted Answer · 2011-12-09 19:12:52Z

1

Here's a sample of how I do it in a very big batch SQL file:

I insert a value first, with no parent
I remember the last insert id
I use it to insert subcategories, and precise idparent as the last inserted id.

This is very close to what you want to do:

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

Hope this helps.

answered Dec 9, 2011 at 19:12

Olivier Pons

15.9k27 gold badges123 silver badges224 bronze badges

Comments

gladiator · Accepted Answer · 2015-07-30 07:36:22Z

You can use the following approach... Do test before using because getGeneratedKeys() in Statement because it depends on driver used. The below code is tested on Maria DB 10.0.12 and Maria JDBC driver 1.2 Maria DB 10 is build on top of MySQL 5.6, so it should work with MySQL as well.

Remember that increasing batch size improves performance only to a certain extent... for my setup increasing batch size above 500 was actually degrading the performance.

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();
    }
}

Collectives™ on Stack Overflow

Using batch SQL commands with MySQL JDBC

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related