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();

Loading CSV data with jOOQ

After the recent efforts made in jOOX, developments of jOOQ have been continued. The main new feature of the upcoming release 1.6.5 is the support for loading of CSV data. The jOOQ Factory will now provide access to a dedicated fluent API for loading CSV files into generated tables, specifying a field mapping and various other parameters related to the batch-processing of bulk loads. Some sample code of what the API might look like:

// The typical jOOQ factory
Factory create = new Factory(connection, SQLDialect.ORACLE);

// Configure and execute a Loader object
Loader<TAuthor> loader =
create.loadInto(AUTHOR)
      .onDuplicateKeyError()
      .onErrorAbort()
      .commitAll()
      .loadCSV("1;'Kafka'\n" +
               "2;Frisch")
      .fields(Author.ID, Author.LAST_NAME)
      .quote('\'')
      .separator(';')
      .ignoreRows(0)
      .execute();

// The resulting Loader object then holds various
// information about the loading process:

// The number of processed rows
int processed = loader.processed();

// The number of stored rows (INSERT or UPDATE)
int stored = loader.stored();

// The number of ignored rows (due to errors, or duplicate rule)
int ignored = loader.ignored();

// The errors that may have occurred during loading
List<LoaderError> errors = loader.errors();
LoaderError error = errors.get(0);

// The exception that caused the error
SQLException exception = error.exception();

// The row that caused the error
int rowIndex = error.rowIndex();
String[] row = error.row();

// The query that caused the error
Query query = error.query();

Along with the previously implemented export API, it is easy to export results from org.jooq.Result into CSV, let users modify them in Excel or any other office software, and upload the CSV again. Other ideas for future versions of jOOQ will also include loading data from XML and JSON data sources, “merging” data (i.e. including DELETE operations), etc.

Feedback is very welcome.