How to start transaction and rollback with JOOQ? How to start transaction and rollback with JOOQ? sql sql

How to start transaction and rollback with JOOQ?


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.


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.


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