Jooq: Return generated IDs from batch executions

[lukaseder]

There might be a way to fetch generated IDs also from batch executions in various databases.

---

See also

• #1537: INSERT .. RETURNING in batch statements not supported by PostgreSQL
• http://stackoverflow.com/q/25371405/521799

[aliaksei-lithium]

Hi! Can you suggest any workarounds to get ids after the batch insert?

[lukaseder]

No, I'm afraid right now, there are no known workarounds that work on all databases / JDBC drivers - short of running the batch explicitly with JDBC directly...

[aliaksei-lithium]

Ok, I made a workaround for myself for PostgreSQL DB with the prefetch of values from the sequence. Maybe someone will find this useful.
https://gist.github.com/aliaksei-lithium/cb3c21f74b85a69f98ea7e04a6657fc6

[tomb50]

Hi @lukaseder,

Are you aware of a nice approach if restricted to MySQL (and only worried about batch insert, not update)?

Thanks
Tom

[lukaseder]

As I mentioned before, you can always resort to using JDBC directly. Just extract the query from jOOQ using Query.getSQL()

[studouglas-scx]

Workaround for Postgres if anyone's interested:

public static <R extends Record> List<R> bulkInsert(final Table<R> table, final List<R> records, final DSLContext dslContext) {
        if (records.isEmpty()) {
            return Collections.emptyList();
        }

        InsertSetStep<R> insertStep = dslContext.insertInto(table);
        for (int i = 0; i < records.size() - 1; i++) {
            insertStep = insertStep.set(records.get(i)).newRecord();
        }
        final R lastRecord = records.get(records.size() - 1);

        return insertStep.set(lastRecord).returning(table.fields()).fetch();
}

[JamesMcMahon]

As I mentioned before, you can always resort to using JDBC directly. Just extract the query from jOOQ using Query.getSQL()

Is there a documented example of this?

This is one of the first times I've run into an issue in JOOQ that would require me to drop to the SQL level, so I am admittedly not well versed in doing that translation. The doc example is useful, but the Batch object doesn't have a getSQL method on it.

[JamesMcMahon]

Also, it is worth mentioning that this issue also affects batch updates as well.

In my use case, I need to know what records were updated during a batch operation.

[lukaseder]

but the Batch object doesn't have a getSQL method on it.

Not the Batch object. The Query object. Query.getSQL(). You can then use that to create a PreparedStatement:

try (PreparedStatement s = connection.prepareStatement(query.getSQL())) {
  for (...) {
    s.setInt(1, ...);
    s.setString(2, ...);
    s.addBatch();
  }

  int[] results = s.executeBatch();
  // Do vendor specific fetching of generated IDs.
}

Use your results array to check which statement index had which update count.

[SimpleIto]

so, is there some official recommend way (except origin JDBC API Statement#getGeneratedKeys) to achieve the goal now? like"DSLContext#batchInsert" or "DSLContext#batchStore"

noticed the issue was opened in 2014... a little frustrating

[lukaseder]

noticed the issue was opened in 2014... a little frustrating

Thank you so much for your patience.

May I recommend, as a temporary remedy, to take great joy in the tons of value jOOQ is offering elsewhere and let this one slip just once.

[SimpleIto]

May I recommend, as a temporary remedy, to take great joy in the tons of value jOOQ is offering elsewhere and let this one slip just once.

Sorry for my impulse, i like JOOQ, example "Typesafe" and "Codegen" they are great features.

I decide wrap method to achieve it. But i found there is no convenience JOOQ provided which can help me do it.
Now i can only wite:

        List<OrgRecord> ors = List.of(new OrgRecord(null, "aa","aa"),
                new OrgRecord(null, "bb", "bb"));

        Connection con = dataSource.getConnection();
        var st = con.prepareStatement("INSERT INTO org VALUES(?,?,?)", Statement.RETURN_GENERATED_KEYS);
        for (OrgRecord r : rs) {
            st.setNull(1, JDBCType.INTEGER.getVendorTypeNumber());
            st.setString(2, r.getName());
            st.setString(3, r.getAddress());
            st.addBatch();
        }
        st.executeBatch();

        var r = st.getGeneratedKeys();
        for (int i = 0; r.next(); i++)
            orgs.get(i).setId(r.getInt(1));

You see, it's specific sql and value setting. It's bad.
Maybe I can use Record#getTable().getName() and Record#getTable().fields() to improve it, But it's still a lot of work to do.

Dose JOOQ provide convenience to help this ? like following code, or something else

  create.batchInsert(orgs).getSql()
  // OR
  create.batchInsert(orgs).getPreparedStatement(Statement.RETURN_GENERATED_KEYS)

[lukaseder]

Thanks for your message. The problem (and popularity of the request) is understood. But we don't have an answer or workaround yet.

You see, it's specific sql and value setting. It's bad.

You can always extract SQL from jOOQ using Query.getSQL()

create.batchInsert(orgs).getSql()

You could extract that kind of SQL using an ExecuteListener

create.batchInsert(orgs).getPreparedStatement(Statement.RETURN_GENERATED_KEYS)

I don't think we should introduce public API just for this kind of workaround...

[SimpleIto]

You can always extract SQL from jOOQ using Query.getSQL()

I have no idea how to extract Query object from Batch or something else for batch insert.

Sorry, i haven't found a doc to explain it.
Can you write a sample? Thanks

[lukaseder]

Since you're going to use batchStore() or something similar, which generates the SQL internally, you will have to hook into the lifecycle management and use an ExecuteListener to get a hold of the SQL query, and abort its execution. That's just a hack. I'd like to avoid documenting a hack here, because people will take it at face value.

Or, you just use the ordinary jOOQ DSL API: dsl.insertInto(T).set(record).getSQL().

[SimpleIto]

I'd like to avoid documenting a hack here, because people will take it at face value.

Thanks for your replying, i'll try it.
I understand what you worried, and also don't want to throw a Exception to Break The Lifecycle.

I don't think it's a so specific requirement, just like JDBC provide a API Statement#getGeneratedKeys()
Maybe you could consider provide a parameter for batch and insert/update API, just like Mybatis Framework doing

  <insert id="insertList" useGeneratedKeys="true">
    ...
  </insert>

  <update id="update" useGeneratedKeys="true">
    ...
  </update>

When I use ExecuteListener, I saw the implementation in BatchMultiple#execute and BatchSingle#execute

// BatchMultiple#execute
            if (ctx.statement() == null)
                ctx.statement(new SettingsEnabledPreparedStatement(connection));
// BatchSingle#execute
            if (ctx.statement() == null)
                ctx.statement(connection.prepareStatement(ctx.sql()));

As you can see, it's not configurable.

And because of ctx.statement(s) is at different lifecycle stage between BatchMultiple(before render) and BatchSingle(after render), for avoiding connection().prepareStatement(ctx.sql(),...) throw the NullPointException, I have to write the following code:

    class MyListener extends DefaultExecuteListener {
        @Override
        public void renderEnd(ExecuteContext ctx) {
            if (ctx.batchQueries().length > 1)
                ctx.statement(null);
        }

        @Override
        public void prepareStart(ExecuteContext ctx) {
            try {
                if (ctx.statement() == null)
                    ctx.statement(ctx.connection().prepareStatement(ctx.sql(), Statement.RETURN_GENERATED_KEYS));
            } catch (SQLException throwables) {}
        }

        @Override
        public void executeEnd(ExecuteContext ctx) {
            try {
                var cr = RowSetProvider.newFactory().createCachedRowSet();
                cr.populate(ctx.statement().getGeneratedKeys());
                throw new MyException(cr);
            } catch (SQLException throwables) {}
        }
    }

As you can see, it's hardly make it configurable.
All batch operation will use the Statement.RETURN_GENERATED_KEYS, and there is no way pass the parameter to MyListener through create.batchInsert or something else JOOQ API.

Even I use DSLContext#batch(Query...) like the following code, the question is Batch cover the JDBC API and not provide parameter to configure Statement when creation.

var batch = create.batch(
   create.insertInto...,
   create.insertInto...
);
batch.execute();

Maybe you could consider provide a parameter for batch and insert/update API, just like Mybatis Framework doing

I'm still a novice, these are my simple opinion. I wish you could consider improve JOOQ API or other opinion to make things easy.
look forward to your reply.

[lukaseder]

Maybe you could consider provide a parameter for batch and insert/update API, just like Mybatis Framework doing

Thanks for your suggestion. Looking at the mess that is fetching generated keys for non-batch queries, I sincerely don't think it will be as easy as that. MyBatis may have aimed for the quick win. I doubt this works well for a lot of dialects.

People will want this to work for:

• UpdatableRecord calls to store(), insert(), update(), merge() (they're all different)
• In the presence of optimistic locking
• For the query based batch API like you've shown (including single query multi bind value and multi query no bind value mode)
• For bulk insertions (one insert with multiple values)
• For insert on duplicate key update, or insert on conflict, or even merge
• For the data import API
• In the presence of RETURNING clauses
• For update statements
• For DAOs

This list isn't complete...

Maybe, for you, only one of those bullets is required, and even that only partially. Maybe, the JDBC generated keys feature would suffice for you. I'm pretty sure it won't for most people, so if we add a quick win, we're going to open the door for dozens of bug reports about this feature not working "as expected" in special case X.

This will be addressed eventually, but it's not as easy as you're trying to make it sound.

When I use ExecuteListener, I saw the implementation in BatchMultiple#execute and BatchSingle#execute

I'm not going to comment too much on these attempts, because that would be going back to documenting the hack. Rather than exploring all these workarounds, I'd rather (eventually) tackle the feature itself.

I'm still a novice, these are my simple opinion. I wish you could consider improve JOOQ API or other opinion to make things easy.

This area will be improved eventually. Until then, I'm sorry, you can't do what you want to do with jOOQ. You may be able to use a workaround.