How to clear a batch in JOOQ?

user_8161041 0
Viewed 245

I am trying to reuse a prepared statement when executing multi-inserts. Something like

InsertValuesStepN<Record> batch = create.insertInto(table, fields);
for(int i=0; i<100000; i++) {
    batch.values();
    if(i % 1000 == 0) {
        batch.execute();
        // need to call clearBatch here so we don't insert records twice
    }
}

but I don't see any way to have InsertValuesStepN clear it's records after calling execute. Is this possible?

jooq sql java
1 Answers

Create a new statement for each batch:

You could create a new statement on each batch, instead of reusing the previous one.

InsertValuesStepN<Record> batch = null;
for(int i=0; i<100000; i++) {
    if (batch == null)
        batch = create.insertInto(table, fields);

    batch.values();
    if(i % 1000 == 0) {
        batch.execute();
        batch = null;
    }
}

Using the bind() API

This is usually not recommended because of performance issue #6616, but since your bottleneck (as per your comment) is with creating a new prepared statement, you might try to use the Query.bind() API which you could use on your 2nd, 3rd, etc. batch to replace existing bind values with new ones in an existing query. Call Query.bind() like this:

// Create the initial statement with dummy values for your batch
Query batch = create.insertInto(table, fields).values(...).keepStatement(true);
for(int i=0; i<100000; i += 1000) {

    // Call this once for each bind value
    batch.bind(...);
    batch.execute();

    // Handle the last insertions, where you have less than 1000 rows per insert
    // ...
}

Proxy JDBC

You could implement a proxy JDBC PreparedStatement that doesn't actually close the delegate statement when jOOQ calls PreparedStatement.close(), but keeps it open and offers the same statement again to jOOQ when jOOQ tries to prepare it again.

There's a pending feature request to offer such a PreparedStatement cache out of the box: https://github.com/jOOQ/jOOQ/issues/7327, or maybe, your JDBC driver already has one (e.g. Oracle does).

Using the import API

But perhaps, you're actually using for jOOQ's import API, which allows for specifying the batch, bulk, and commit sizes, e.g.

create
   .loadInto(table)
   .bulkAfter(1000)
   .loadArrays(...) // There are other possible data sources
   .fields(fields)
   .execute();
user_5217990
Related