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
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.
Better to prepare one PreparedStatement and reuse it as much as you want:
INSERT INTO (table) (column1, column2) VALUES (column1, column2)