JDBC batch operations with jOOQ


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

  1. Execute several queries without bind values
  2. 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();

5 thoughts on “JDBC batch operations with jOOQ

    • 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…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s