Merge multiple PreparedStatements together

Go To


I'm trying to set up a while loop that inserts multiple rows into a MySQL table using the jdbc drivers in Java. The idea is that I end up with a statement along the lines of:

INSERT INTO table (column1, column2) VALUES (column1, column2), (column1, column2);

I want to set up this statement using a java.sql.PreparedStatement, but I'd like to prepare small bits of the statement, one row at a time - mainly because the number of entries will be dynamic, and this seems like the best way to create one big query.

This requires the small parts to be 'merged' together every time another chunk is generated. How do I merge these together? Or would you suggest to forget about this idea, and simply execute thousands of INSERT statements at once?

Thank you, Patrick

2012-04-04 19:52
by Patrickdev
You want to use batch updates. See here for an example - Jim Garrison 2012-04-04 19:55


It sort of depends on how often you plan to run this loop to execute thousands of statements, but that is one of the exact purposes of prepared statements and stored procedures - since the query does not have to be recompiled on each execution, you get potentially massive performance gains when querying in a loop over a simple SQL statement execution, which must be compiled and executed on every loop iteration.

Those gains may still not match the performance of a prepared statement built up into a long multi-insert in a loop as you're asking, but will be simpler to code. I would recommend staying with an execution loop unless the performance becomes problematic.

2012-04-04 19:55
by Michael Berkowski
Fair enough, I was worried that it might affect the performance speed-wise, but this makes things clear. Thanks for your answer - Patrickdev 2012-04-04 19:56


Better to prepare one PreparedStatement and reuse it as much as you want:

INSERT INTO (table) (column1, column2) VALUES (column1, column2)

2012-04-04 19:54
by ahanin
Thank you for your reply - Patrickdev 2012-04-04 20:11