How to start transaction and rollback with JOOQ?

Question

Yes! I have read the docs about

jOOQ will never commit or rollback on the Connection (Except for CSV-imports, if explicitly configured in the Import API)

jOOQ will never start any transactions.

...

but when I need some transaction management, what is the best practice to do this?

Have I said that I'm a big fan of a way of JOOQ!

Lukas Eder · Answer 1 · 2018-02-22T08:00:13.553

12

This question was asked at a time when jOOQ did not yet implement a transaction API. As of jOOQ 3.4 onwards, such an API is available and documented here:

https://www.jooq.org/doc/latest/manual/sql-execution/transaction-management

Transaction API and its default binding to JDBC

By default, jOOQ binds its (nested) transaction support to the JDBC API directly through a simple, functional API:

DSL.using(configuration)
   .transaction(c -> {
        c.dsl().insertInto(...).execute();
        c.dsl().update(...).execute();
   });

... the lambda expression (or more specifically, the TransactionalRunnable) creates a new transaction at its beginning and commits it upon normal completion, or rolls it back upon exception.

Such transactions can be nested

DSL.using(configuration)
   .transaction(c1 -> {
        c1.dsl().insertInto(...).execute();
        c1.dsl().transaction(c2 -> {
            c2.dsl().insertInto(...).execute();
        });
        c1.dsl().update(...).execute();
   });

... in case of which a Savepoint will be created at the beginning of the nested transaction and the nested transaction discards the savepoint upon normal completion, or rolls back to it upon exception.

Overriding the default JDBC binding

In many applications, you will already have a pre-existing transaction management system, e.g. JTA or Spring TX or something else. In this case, you can either:

Not use the jOOQ transaction API at all
Implement your own TransactionProvider which implements the semantics of the begin(), commit(), and rollback() operations, e.g. by binding them to Spring.

edited Feb 22 '18 at 08:00

answered Mar 02 '13 at 09:29

Lukas Eder

211,314
129
689
1,509

Nowadays, JOOQ does have code for transactions. https://www.jooq.org/doc/3.10/manual/sql-execution/transaction-management/ So I guess this answer should be updated. StackOverflow discourages questions about "best practice", but I also would like to know. Inside a transaction you should keep using the same "DSLContext" object, so I guess you must pass that to any method calls. – Enwired Feb 20 '18 at 22:09
@Enwired: Thanks for your ping. I have updated the outdated answer. Don't worry about "best practice" questions. If a question is about understanding the correct behaviour, it should be fine. Feel free to ask a new question if you like. I'll be happy to answer. – Lukas Eder Feb 22 '18 at 08:02
I notice in the outer block you use `DSL.using(configuration)` and inside you use `c1.dsl()`, where `c1` is a configuration. Would it have made any difference if you used `DSL.using(c1)` ? – Enwired Feb 23 '18 at 00:19
What if the connection we are passing to Jooq has autocommit set to true. Will then also the Jooq transaction work, or do we need to set auto-commit to false manually, before passing the connection to Jooq ? – Pratik Singhal Apr 11 '18 at 13:55
1

@PratikSinghal: jOOQ will set autocommit to false for the scope of a transaction, and restore it back to true afterwards. – Lukas Eder Apr 11 '18 at 19:35

score 5 · Answer 2 · answered Mar 02 '13 at 09:36

Transaction control is independent of a DB access layer like what JOOQ provides.

Starting and finishing transactions is probably best handled in the Service Layer of your application. See the diagram at that page showing the Service Layer's relationship to lower layers it calls.

See also patterns like Unit of Work or Transaction Script.

score 0 · Answer 3 · edited May 23 '17 at 12:15

0

I could find an easy way to do this from this link: http://blog.liftoffllc.in/2014/06/jooq-and-transactions.html.

This answer might give you more detailed explanation: https://stackoverflow.com/a/24380508/542108

edited May 23 '17 at 12:15

Community

1
1

answered Jun 24 '14 at 07:24

CodePredator

415
2
6
14

How to start transaction and rollback with JOOQ?

3 Answers3

Transaction API and its default binding to JDBC

Overriding the default JDBC binding

Linked