jOOQ - General way to insert multiple data and get generated IDs

[akash verma]

 Hey Lukas,

I just wanted to know is there any ways to get the generated IDs when we batch insert, does JOOQ provide any methods for the same.

I try to use valuesOfRecords() but it is not working.

List<Record3<Integer, String, String>> records = ...
create.insertInto(AUTHOR,
AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
.valuesOfRecords(records)
.execute();

[Lukas Eder]

jOOQ calls this a bulk insert, not a batch insert. Just append a RETURNING clause and you're all set (if your underlying RDBMS supports this, that is):

https://www.jooq.org/doc/latest/manual/sql-building/sql-statements/insert-statement/insert-returning/

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/c01ec364-0d1f-4afb-93df-f33a378a8995n%40googlegroups.com.

[akash verma]

Thank you for reply.

[akash verma]

I am facing the error when  I try to use batchInsert() method in jooq it does'nt throw error like BatchUpdateException, now I am not able to get the count of the inserted rows in the BatchInsert().

for e.g I have 1000 records to insert with the help of batchInsert() I am able to insert the 990 records but now i am not able to get the count of inserted records as rest 10 records throws the error in this case batchInsert() must return the BatchUpdateException like JDBC so we can get the count of errors records.

[Lukas Eder]

Akash,

Thanks for your message. I'm guessing you have some constraint violation exceptions or something like that? jOOQ's batch API just delegates to JDBC. If JDBC doesn't throw any exceptions, then neither can jOOQ.

I might be able to help you a bit further, but you'll have to provide more information to see what exactly you're doing.

Best Regards,

Lukas

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/920c77fe-05ce-4cf0-8891-c4c5702ef25bn%40googlegroups.com.

[akash verma]

i just want the  numbers of rows that inserted successfully as in JDBC we something like this:-  to get the count of updated records.

try { 

  // Batch is ready, execute it to insert the data 

 batchResults = pstmt.executeBatch();

} catch (BatchUpdateException e) {

System.out.println("Error message: " + e.getMessage()); 

batchResults = e.getUpdateCounts();

}

here with the help of e.getUpdateCounts  iam able to get the counts of inserted rows.

but what happen in JOOQ is We get DataAccessException which contains the cause BatchUpdateException. so here What happen i am not able to use e.getUpdateCounts(); which is a feature of BatchUpdateException;

All i want is If we get some error in batch Insertion/Deletion , still we can get the number of inserted/deleted records as in JDBC.

[Lukas Eder]

Hi Akash,

Why do you get an exception, and what could jOOQ possibly do about it?

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/a15967ad-a9d3-42d2-a7b0-347cc51267d2n%40googlegroups.com.

[akash verma]

2022-12-16 17:12:01.085 DEBUG 16528 --- [           main] org.jooq.tools.LoggerListener            : Executing batch query    : insert into `items`.`author` (`first_name`, `last_name`) values (?, ?)
2022-12-16 17:12:01.126 DEBUG 16528 --- [           main] org.jooq.tools.LoggerListener            : Exception                

org.jooq.exception.DataAccessException: SQL [insert into `items`.`author` (`first_name`, `last_name`) values (?, ?)]; Data truncation: Data too long for column 'first_name' at row 1

this is the exception I am getting when I try to batch insert but I don't know which record is causing this error as I have 10,000 records so in this case JOOQ have to send the number of Inserted records so that i can check.

[Lukas Eder]

Hi Akash,

We currently don't re-implement this sort of validation in jOOQ. Usually, server side validation is good enough. I'm not sure if jOOQ can always correlate arbitrary server side errors to individual batched records in all RDBMS. It might be possible, but hasn't been too popular a feature request in the past.

You know those 10000 records, and can run through them, checking them against the field length to quickly see which ones are not working. The meta data is available to you for automated checks via DataType::length, if you're using the code generator.

I hope this helps,
Lukas

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/eaddaf2d-4c84-44ec-8862-b57040e60291n%40googlegroups.com.

[akash verma]

Hi Lukas,

Thanks for the suggestion,  as we doing the same, just try to loop every object we pass in the batch and validate it.

But I think this will be the great feature if we are able to add it. 

[Lukas Eder]

Thanks Akash,

We'll consider it:

https://github.com/jOOQ/jOOQ/issues/14420

As it so happens, another customer has just now requested the same thing for NOT NULL constraints, which cause errors that aren't always so easy to spot in data after the fact.

I've described the potential feature in the above issue. In short, there are 2 parts to it:

- Adding additional troubleshooting logs. This seems easy as there isn't any requirement for the logs to be complete/precise. This is what you seem to be looking for.

- Formally reimplementing the server side constraints on the client side (I vaguely remember this having been a topic a few times in the past). This is much more difficult to get right.

If we'll go forward with this, then for now, it will only be the first bullet.

Best Regards,

Lukas

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/d6d7c573-82b5-49a0-b1a9-70dd62222cf1n%40googlegroups.com.

[Lukas Eder]

Note that generic workarounds are possible using VisitListener or QOM API traversal (starting from jOOQ 3.18 for INSERT and UPDATE statements):

https://github.com/jOOQ/jOOQ/issues/14420#issuecomment-1357246961

Not sure if those will be practical for you (probably a bit too laborious), but I wanted to mention it here, just in case.

[Lukas Eder]

Issue #14420 has been implemented for jOOQ 3.18.0, see:

https://www.jooq.org/doc/dev/manual/sql-execution/logging-sql-exceptions/

[akash verma]

Thankyou for the updates.

[akash verma]

Hey lukas,

Why we skip the DAOs generation when script does'nt have primary key field?

[Lukas Eder]

Because most operations aren't available if you don't have a PK

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/65f9cc6e-90dc-483b-8a0a-3b759b37bb9fn%40googlegroups.com.

[akash verma]

Okay.

[akash verma]

Hey lukas,

Like when we try to run  DDL script and have some alter keywords in script not able to parse it but some alter statements are running, I searched it but did'nt find every keywords which are not working with alter keywords, Do u have any reference?

[Lukas Eder]

You mean this?

https://www.jooq.org/doc/latest/manual/sql-building/sql-parser/sql-parser-grammar/

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/0ea64750-b629-4578-988c-9cbf0b9af623n%40googlegroups.com.

[akash verma]

Yes Thankyou,

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/0ea64750-b629-4578-988c-9cbf0b9af623n%40googlegroups.com.