How to execute multiple update queries using jooq execute or batch execute without code generation?

Question

I am using JOOQ ( 3.10. 5 ) to update records in ORACLE table without jooq auto code generation in below ways

Approach 1- Using DSL execute by using plain SQL String

dslContext.execute("update author set first_name = 'updateTest-111111' where id = 1 ");
logger.info("1st update Done ");

dslContext.execute("update author set first_name = 'updateTest-2222222' where id = 2 ");
logger.info("2nd update Done ");

Approach 2 - Using DSL batch by passing Query list

List<Query> updateQueries = new ArrayList<>();

updateQueries.add(dslContext.parser().parseQuery("update author set first_name = 'updateTest-111' where id = 1 "));

updateQueries.add(dslContext.parser().parseQuery("update author set first_name = 'updateTest-222' where id = 2 "));

dslContext.batch(updateQueries).execute();

But in both cases, it is just updating 1st record and then stop execution , keeps on running.

Below is the output for Approach -1

2022-05-13 02:43:50.848  INFO 25524 --- [nio-9010-exec-1] org.jooq.Constants                       : 
                                      
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@  @@        @@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@        @@@@@@@@@@
@@@@@@@@@@@@@@@@  @@  @@    @@@@@@@@@@
@@@@@@@@@@  @@@@  @@  @@    @@@@@@@@@@
@@@@@@@@@@        @@        @@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@        @@        @@@@@@@@@@
@@@@@@@@@@    @@  @@  @@@@  @@@@@@@@@@
@@@@@@@@@@    @@  @@  @@@@  @@@@@@@@@@
@@@@@@@@@@        @@  @  @  @@@@@@@@@@
@@@@@@@@@@        @@        @@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@  @@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@  Thank you for using jOOQ 3.10.5
                                      
2022-05-13 02:43:50.922  WARN 25524 --- [nio-9010-exec-1] o.a.tomcat.jdbc.pool.ConnectionPool      : minIdle is larger than maxActive, setting minIdle to: 5
2022-05-13 02:43:50.923  WARN 25524 --- [nio-9010-exec-1] o.a.tomcat.jdbc.pool.ConnectionPool      : maxIdle is larger than maxActive, setting maxIdle to: 5
2022-05-13 02:43:52.670  INFO 25524 --- [nio-9010-exec-1] c.d.e.dao.ECRebootServiceDaoImpl         : 1st update Done 

As you can see it stopped after 1st update Done.

How i should be executing multiple update queries using JOOQ ? or is there any better way to do this in JOOQ without code generation?

Answer 1

The problem you encountered

The most likely reason for the problem you've encountered is that you have locked the row in another transaction (e.g. in a SQL editor?) and now your program is blocked right after printing the debug message, in the update.

Regarding your queries

With both of your approaches, you're not using bind variables, which will be a problem for Oracle's cursor cache, producing cursor cache contention. At the least, you should execute something like this:

ctx.execute("update author set first_name = ? where id = ?", "updateTest-111111", 1);
ctx.execute("update author set first_name = ? where id = ?", "updateTest-2222222", 2);

In the batch case, you have used the jOOQ parser to get a Query representation of your string, but in your case, that seems to be overkill. You can wrap any plain SQL string in a Query using DSLContext.query(String).

In that case, again, it would be better to use bind variables, e.g. like this:

ctx.batch(query("update author set first_name = ? where id = ?"))
   .bind("updateTest-111", 1)
   .bind("updateTest-222", 2)
   .execute();

Or even just:

ctx.batch("update author set first_name = ? where id = ?")
   .bind("updateTest-111", 1)
   .bind("updateTest-222", 2)
   .execute();

Regarding code generation

I suggest you reconsider using code generation. While there isn't much benefit in your particular case (at least the parts you've shared in this question), it is very useful in general.