CODEWITHSUNDEEP
javasqlbatch-fileinsertjooq
Patrik Knoerr
Patrik Knoerr

Reputation: 11

jooq batch insert issue (duplicating the first row)

I'm trying to use jOOQ for batch inserts into my postgres database. What I'm trying to do is:

BatchBindStep bbs = context.batch(context.insertInto(TABLENAME,FIELD1,FIELD2,....).values("?","?",...));
bbs = bbs.bind(v1a,v2a).bind(v1b,v2b)....;
bbs.execute();

as described at http://www.jooq.org/doc/3.1/manual-single-page/#batch-execution

To make it clear, I want to insert thousands of rows in one query, not by using a batch with thousands of queries :

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

The problem is: To get to the point where the BatchBindStep accepts a .bind() call, one need to have called context.batch with an argument, that has .values(...) as the last call. In the documentation is stated, that "?" has to be used. This is typed as String, and may work only for tables where alls columns are varchars, since jOOQ does static typing. This irritates me. I tried my luck with arbitrary default values (null,0...) just to go through the values(...) step, hoping that since these "values" are not really values that I want to batch insert, they get overwritten later by the binds.

As a matter of fact, they will. TWICE for the first row. Which completly baffles me.

To repeat, I CAN do batch inserts, but the first row gets inserted TWICE. I have the intuition that It has to do with the "values" call (at least there is a conceptual problem in the DSL with the typing).

Has anyone tried to use jOOQ for batch inserts, and how does one that without inserting the first row twice ?

P.S. This happens when I try to use .values("?", "?", "?", "?", "?", "?", "?", "?", "?","?","?","?","?","?","?","?","?","?","?","?","?","?") : "The method values(Integer, String, String, String, String, String, String, String, String, String, Double, Double, String, String, String, String, Timestamp, String, String, String, String, String) in the type InsertValuesStep22 is not applicable for the arguments (String, String, String, String, String, String, String, String, String, String, String, String, String, String, String, String, String, String, String, String, String, String)" So clearly, the typing is wrong, when I try to adapt the example from the documentation.

Upvotes: 1

Views: 3432

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221106

The example from the documentation was wrong. It has now been fixed:

http://www.jooq.org/doc/latest/manual/sql-execution/batch-execution

In principle, as you've noticed, it doesn't matter what dummy bind values you're passing to the insert statement, as those values will be replaced when binding the values specified by the various .bind() calls. So in principle, some correct solutions would be:

// Passing in null
create.insertInto(AUTHOR, ID, NAME).values((Integer) null, null);

// Passing in a dummy value (even with a wrong type)
create.insertInto(AUTHOR, ID, NAME).values(Arrays.asList("?", "?"))

jOOQ integration tests suggest that batch insertion works correctly. The issue you have been experiencing with double-inserts of the first record would be surprising. Either this is a subtle bug that is not visible from your current question, or you might have called .bind() one too many times?

Upvotes: 2

Related Questions