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 thebegin()
,commit()
, androllback()
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