Batch MySQL inserts, one a primary record, one a detail record with foreign key

Question

I have an application that logs a lot of data to a MySQL database. The in-production version already runs insert statements in batches to improve performance. We're changing the db schema a bit so that some of the extraneous data is sent to a different table that we can join on lookup.

However, I'm trying to properly design the queries to work with our batch system. I wanted to use the mysql LAST_QUERY_ID so I wouldn't have to worry about getting the generated keys and matching them up (seems like a very difficult task).

However, I can't seem to find a way to add different insert statements to a batch, so how can resolve this? I assume I need to build a second batch and add all detail queries to that, but that means that the LAST_QUERY_ID loses meaning.

s = conn.prepareStatement("INSERT INTO mytable (stuff) VALUES (?)");
while (!queue.isEmpty()){
    s.setLong(1, System.currentTimeMillis() / 1000L);
    // ... set other data
    s.addBatch();

    // Add insert query for extra data if needed
    if( a.getData() != null && !a.getData().isEmpty() ){
        s = conn.prepareStatement("INSERT INTO mytable_details (stuff_id,morestuff)
                                   VALUES (LAST_INSERT_ID(),?)");
        s.setString(1, a.getData());
        s.addBatch();
    }
}

Could you elaborate a bit more? What do you mean with "can't seem to find a way"? Does the code above throw an exception or something? — HQCasanova, Nov 03 '13 at 16:46
The code above won't work because the statement gets overwritten. If there's data for the second query, the first query never runs. I can't make it work without make a second statement variable/batch but then the queries would run out of order and last_insert_id is useless — helion3, Nov 03 '13 at 16:48
Thanks. I've added my answer below but I'm not sure if it'll help you. — HQCasanova, Nov 03 '13 at 17:32

score 0 · Answer 1 · answered Nov 03 '13 at 18:05

I've fixed it for now though I wish there was a better way. I built an arraylist of extra data values that I can associates with the generatedKeys returned from the batch inserts. After the first query batch executes, I build a second batch with the right ids/data.

score 0 · Accepted Answer · answered Nov 03 '13 at 18:08

This is not how batching works. Batching only works within one Statement, and for a PreparedStatement that means that you can only add batches of parameters for one and the same statement. Your code also neglects to execute the statements.

For what you want to do, you should use setAutoCommit(false), execute both statement and then commit() (or rollback if an error occurred).

Also I'd suggest you look into the JDBC standard method of retrieving generated keys, as that will make your code less MySQL specific. See also Retrieving AUTO_INCREMENT Column Values through JDBC.

Batch MySQL inserts, one a primary record, one a detail record with foreign key

2 Answers2