So this was requested again, by another jOOQ user – support for an ancient JDBC feature – java.sql.Statement.addBatch(); With JDBC, you can easily execute several statements at once using the addBatch() method. Essentially, there are two modes in JDBC
- Execute several queries without bind values
- Execute one query several times with bind values
With code, this looks like the following snippet:
// 1. several queries // ------------------ Statement stmt = connection.createStatement(); stmt.addBatch("INSERT INTO author VALUES (1, 'Erich Gamma')"); stmt.addBatch("INSERT INTO author VALUES (2, 'Richard Helm')"); stmt.addBatch("INSERT INTO author VALUES (3, 'Ralph Johnson')"); stmt.addBatch("INSERT INTO author VALUES (4, 'John Vlissides')"); int[] result = stmt.executeBatch(); // 2. a single query // ----------------- PreparedStatement stmt = connection.prepareStatement( "INSERT INTO autho VALUES (?, ?)"); stmt.setInt(1, 1); stmt.setString(2, "Erich Gamma"); stmt.addBatch(); stmt.setInt(1, 2); stmt.setString(2, "Richard Helm"); stmt.addBatch(); stmt.setInt(1, 3); stmt.setString(2, "Ralph Johnson"); stmt.addBatch(); stmt.setInt(1, 4); stmt.setString(2, "John Vlissides"); stmt.addBatch(); int[] result = stmt.executeBatch();
This will also be supported by jOOQ
The upcoming version 1.6.9 of jOOQ will support executing queries in batch mode as follows:
// 1. several queries // ------------------ create.batch( create.insertInto(AUTHOR, ID, NAME).values(1, "Erich Gamma"), create.insertInto(AUTHOR, ID, NAME).values(2, "Richard Helm"), create.insertInto(AUTHOR, ID, NAME).values(3, "Ralph Johnson"), create.insertInto(AUTHOR, ID, NAME).values(4, "John Vlissides")) .execute(); // 2. a single query // ----------------- create.batch(create.insertInto(AUTHOR, ID, NAME).values("?", "?")) .bind(1, "Erich Gamma") .bind(2, "Richard Helm") .bind(3, "Ralph Johnson") .bind(4, "John Vlissides") .execute();
Thanks :)
Haha, with that avatar, it sounded like “excellent” :)
Event if ancient (since JDK 1.2), batching does seem produce better performance with many JDBC drivers/databases. See http://java-persistence-performance.blogspot.com/2013/05/batch-writing-and-dynamic-vs.html
Very interesting post, thanks for sharing. Generally, it can be said that batching is never worse, but mostly better. I’m not sure if this is a JDBC driver problem, or if the “bad” databases just don’t support batch in their protocols…