Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
Transaction management
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
jOOQ can work with any pre-existing transaction model (e.g. JDBC, Spring, Jakarta EE), or alternatively, offers its own convenience transaction API. In particular:
- You can use third-party transaction management libraries like Spring TX
- You can use a JTA-compliant Java EE transaction manager from your container.
- You can call JDBC's
Connection.commit(),Connection.rollback()and other methods on your JDBC driver, or use the equivalent methods on your R2DBC driver. - You can issue vendor-specific
COMMIT,ROLLBACKand other statements directly in your database. - You use jOOQ's transaction API.
When using Spring Boot, its jOOQ starter already pre-configures the correct Spring transaction aware data source, so Spring transactions will work out of the box with jOOQ.
While jOOQ does not aim to replace any of the above, it offers a simple API (and a corresponding SPI) to provide you with jOOQ-style programmatic fluency to express your transactions. Below are some Java examples showing how to implement (nested) transactions with jOOQ. For these examples, we're using Java 8 syntax. Java 8 is not a requirement, though.
create.transaction(configuration -> {
AuthorRecord author =
DSL.using(configuration)
.insertInto(AUTHOR, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
.values("George", "Orwell")
.returning()
.fetchOne();
DSL.using(configuration)
.insertInto(BOOK, BOOK.AUTHOR_ID, BOOK.TITLE)
.values(author.getId(), "1984")
.values(author.getId(), "Animal Farm")
.execute();
// Implicit commit executed here
});
Note how the lambda expression receives a new, derived configuration that should be used within the local scope:
create.transaction(configuration -> {
// Wrap configuration in a new DSLContext:
DSL.using(configuration).insertInto(...);
DSL.using(configuration).insertInto(...);
// Or, reuse the new DSLContext within the transaction scope:
DSLContext ctx = DSL.using(configuration);
ctx.insertInto(...);
ctx.insertInto(...);
// ... but avoid using the scope from outside the transaction:
create.insertInto(...);
create.insertInto(...);
});
While some org.jooq.TransactionProvider implementations (e.g. ones based on ThreadLocals, e.g. Spring or JTA) may allow you to reuse the globally scoped DSLContext reference, the jOOQ transaction API design allows for TransactionProvider implementations that require your transactional code to use the new, locally scoped Configuration, instead.
Transactional code is wrapped in jOOQ's org.jooq.TransactionalRunnable or org.jooq.TransactionalCallable types:
public interface TransactionalRunnable {
void run(Configuration configuration) throws Exception;
}
public interface TransactionalCallable<T> {
T run(Configuration configuration) throws Exception;
}
Such transactional code can be passed to transaction(TransactionRunnable) or transactionResult(TransactionCallable) methods. An example using transactionResult():
int updateCount = create.transactionResult(configuration -> {
int result = 0;
DSLContext ctx = DSL.using(configuration);
result += ctx.insertInto(AUTHOR, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME).values("John", "Doe").execute();
result += ctx.insertInto(AUTHOR, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME).values("Jane", "Doe").execute();
return result;
});
Rollbacks
Any uncaught checked or unchecked exception thrown from your transactional code will rollback the transaction to the beginning of the block. This behaviour will allow for nesting transactions, if your configured org.jooq.TransactionProvider supports nesting of transactions. An example can be seen here:
create.transaction(outer -> {
final AuthorRecord author =
DSL.using(outer)
.insertInto(AUTHOR, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
.values("George", "Orwell")
.returning()
.fetchOne();
// Implicit savepoint created here
try {
DSL.using(outer)
.transaction(nested -> {
DSL.using(nested)
.insertInto(BOOK, BOOK.AUTHOR_ID, BOOK.TITLE)
.values(author.getId(), "1984")
.values(author.getId(), "Animal Farm")
.execute();
// Rolls back the nested transaction
if (oops)
throw new RuntimeException("Oops");
// Implicit savepoint is discarded, but no commit is issued yet.
});
}
catch (RuntimeException e) {
// We can decide whether an exception is "fatal enough" to roll back also the outer transaction
if (isFatal(e))
// Rolls back the outer transaction
throw e;
}
// Implicit commit executed here
});
TransactionProvider implementations
By default, jOOQ ships with the org.jooq.impl.DefaultTransactionProvider, which implements nested transactions using JDBC java.sql.Savepoint. Spring Boot will configure an alternative TransactionProvider that should work for most use-cases. You can, however, still implement your own org.jooq.TransactionProvider and supply that to your Configuration to override jOOQ's default behaviour. A simple example implementation using Spring's DataSourceTransactionManager can be seen here:
import static org.springframework.transaction.TransactionDefinition.PROPAGATION_NESTED;
import org.jooq.Transaction;
import org.jooq.TransactionContext;
import org.jooq.TransactionProvider;
import org.jooq.tools.JooqLogger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;
public class SpringTransactionProvider implements TransactionProvider {
private static final JooqLogger log = JooqLogger.getLogger(SpringTransactionProvider.class);
@Autowired
DataSourceTransactionManager txMgr;
@Override
public void begin(TransactionContext ctx) {
log.info("Begin transaction");
// This TransactionProvider behaves like jOOQ's DefaultTransactionProvider,
// which supports nested transactions using Savepoints
TransactionStatus tx = txMgr.getTransaction(new DefaultTransactionDefinition(PROPAGATION_NESTED));
ctx.transaction(new SpringTransaction(tx));
}
@Override
public void commit(TransactionContext ctx) {
log.info("commit transaction");
txMgr.commit(((SpringTransaction) ctx.transaction()).tx);
}
@Override
public void rollback(TransactionContext ctx) {
log.info("rollback transaction");
txMgr.rollback(((SpringTransaction) ctx.transaction()).tx);
}
}
class SpringTransaction implements Transaction {
final TransactionStatus tx;
SpringTransaction(TransactionStatus tx) {
this.tx = tx;
}
}
Feedback
Do you have any feedback about this page? We'd love to hear it!