Clash Royale CLAN TAG#URR8PPP

How to do manual transaction management with JOOQ and Spring-boot 2.0?

Using Spring Boot 2.0.4 and JOOQ 3.11.3.

I have a server endpoint that needs fine-grained control over transaction management; it needs to issue multiple SQL statements before and after an external call and must not keep the DB transaction open while talking to the external site.

In the below code testTransactionV4 is the attempt I like best.

testTransactionV4

I've looked in the JOOQ manual but the transaction-management section is pretty light-on and seems to imply this is the way to do it.

It feels like I'm working harder than I should be here, which is usually a sign that I'm doing it wrong. Is there a better, "correct" way to do manual transaction management with Spring/JOOQ?

Also, any improvements to the implementation of the TransactionBean would be greatly appreciated (and upvoted).

But the point of this question is really just: "Is this the right way"?

TestEndpoint:

@Role.SystemApi
@SystemApiEndpoint
public class TestEndpoint
private static Log log = to(TestEndpoint.class);

@Autowired private DSLContext db;
@Autowired private TransactionBean txBean;
@Autowired private Tx tx;

private void doNonTransactionalThing()
log.info("long running thing that should not be inside a transaction");


/** Works; don't like the commitWithResult name but it'll do if there's
no better way. Implementation is ugly too.
*/
@JsonPostMethod("testTransactionV4")
public void testMultiTransactionWithTxBean()
log.info("start testMultiTransactionWithTxBean");

AccountRecord account = txBean.commitWithResult( db ->
db.fetchOne(ACCOUNT, ACCOUNT.ID.eq(1)) );

doNonTransactionalThing();

account.setName("test_tx+"+new Date());

txBean.commit(db -> account.store() );


/** Works; but it's ugly, especially having to work around lambda final
requirements on references. */
@JsonPostMethod("testTransactionV3")
public void testMultiTransactionWithJooqApi()
log.info("start testMultiTransactionWithJooqApi");

AtomicReference<AccountRecord> account = new AtomicReference<>();
db.transaction( config->
account.set(DSL.using(config).fetchOne(ACCOUNT, ACCOUNT.ID.eq(1))) );

doNonTransactionalThing();

account.get().setName("test_tx+"+new Date());

db.transaction(config->
account.get().store();
);


/** Does not work, there's only one commit that spans over the long operation */
@JsonPostMethod("testTransactionV1")
@Transactional
public void testIncorrectSingleTransactionWithMethodAnnotation()
log.info("start testIncorrectSingleTransactionWithMethodAnnotation");

AccountRecord account = db.fetchOne(ACCOUNT, ACCOUNT.ID.eq(1));

doNonTransactionalThing();

account.setName("test_tx+"+new Date());
account.store();


/** Works, but I don't like defining my tx boundaries this way, readability
is poor (relies on correct bean naming and even then is non-obvious) and is
fragile in the face of refactoring. When explicit TX boundaries are needed
I want them getting in my face straight away.
*/
@JsonPostMethod("testTransactionV2")
public void testMultiTransactionWithNestedComponent()
log.info("start testTransactionWithComponentDelegation");

AccountRecord account = tx.readAccount();

doNonTransactionalThing();

account.setName("test_tx+"+new Date());
tx.writeAccount(account);


@Component
static class Tx
@Autowired private DSLContext db;

@Transactional
public AccountRecord readAccount()
return db.fetchOne(ACCOUNT, ACCOUNT.ID.eq(1));


@Transactional
public void writeAccount(AccountRecord account)
account.store();






TransactionBean:

@Component
public class TransactionBean
@Autowired private DSLContext db;

/**
Don't like the name, but can't figure out how to make it be just "commit".
*/
public <T> T commitWithResult(Function<DSLContext, T> worker)
// Yuck, at the very least need an array or something as the holder.
AtomicReference<T> result = new AtomicReference<>();
db.transaction( config -> result.set(
worker.apply(DSL.using(config))
));
return result.get();


public void commit(Consumer<DSLContext> worker)
db.transaction( config ->
worker.accept(DSL.using(config))
);


public void commit(Runnable worker)
db.transaction( config ->
worker.run()
);




TransactionTemplate

1 Answer
1

Use the TransactionTemplate to wrap the transactional part. Spring Boot provides one out-of-the-box so it is ready for use. You can use the execute method to wrap a call in a transaction.

TransactionTemplate

execute

@Autowired
private TransactionTemplate transaction;

@JsonPostMethod("testTransactionV1")
public void testIncorrectSingleTransactionWithTransactionTemplate()
log.info("start testIncorrectSingleTransactionWithMethodAnnotation");

AccountRecord account = transaction.execute( status -> db.fetchOne(ACCOUNT, ACCOUNT.ID.eq(1)));

doNonTransactionalThing();

transaction.execute(status ->
account.setName("test_tx+"+new Date());
account.store();
return null;




Something like that should do the trick. Not sure if the lambdas would work (keep forgetting the syntax of the TransactionCallback

TransactionCallback

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.