Is there a way to programmatically rollback transactions in JooQ?

Question

In our application, we use an Either monad to convey failures. As per the Jooq transaction management docs, both @Transactional and DslContext.transaction require an Exception to be thrown to trigger the transaction rollback. We'd prefer to commit or rollback the transaction based on the state of the Either.

Psuedo-code of what we need looks like this:

public class DomainService {

    private DSLContext dslContext;

    public Either<SomeError, String> businessOperation() {
        return transactional(configuration ->
                firstDatabaseChange(configuration)
                .flatMap(i -> secondDatabaseChange(configuration)));

    }

    private Either<SomeError, String> firstDatabaseChange(
             DSLContext dslContext) {
        //Mutate the Db in some way
    }

    private Either<SomeError, String> secondDatabaseChange(
             DSLContext dslContext) {
        //Mutate the Db in some way
    }


    /* How do we implement this method */
    private Either<SomeError, String> transactional(Function<DSLContext, 
             Either<SomeError, String>> function) {
        return function.apply(dslContext)
                .onSuccess(i -> /*Commit the transaction */)
                .onFailure(i -> /*Rollback the transaction*/);
    }
}

I have the following working implementation of the transactional method that feels like a hack. (Interestingly, the @Transactional annotation is required for rollbacks to work even though we don't throw any exceptions). Is there a better way to do this?

@Transactional
public <T> Either<SomeError, T> transactional(Function<DSLContext, 
         Either<SomeError, T>> function) {
    Connection connection = dslContext.parsingConnection();

    try {
        connection.setAutoCommit(false);
        Either<SomeError, T> either = function.apply(dslContext);
        if (either.isSuccess()) {
            connection.commit();
        } else {
            connection.rollback();
        }
        return result;
    } catch (SQLException e) {
        log.error("SqlException encountered", e);
        return SomeError.failure(e);
    } catch (Exception e) {
        try {
            connection.rollback();
        } catch (SQLException e1) {
            log.error("Exception encountered while rolling back", e1);
        }
        return SomeError.failure(e);
    }
}

Answer 1

As mentioned by Lukas Seder, a procedural API is on Jooq's roadmap: github.com/jOOQ/jOOQ/issues/5376

Until this is released, you can achieve the required behaviour as below:

public <T> Either<SomeError, T> transactional(Function<DSLContext,
        Either<SomeError, T>> function) {
    return dslContext.connectionResult(connection -> {
        try {
            connection.setAutoCommit(false);
            Either<SomeError, T> either = function.apply(dslContext);
            if (either.isSuccess()) {
                connection.commit();
            } else {
                connection.rollback();
            }
            return result;
        } catch (SQLException e) {
            log.error("SqlException encountered", e);
            return SomeError.failure(e);
        } catch (Exception e) {
            try {
                connection.rollback();
            } catch (SQLException e1) {
                log.error("Exception encountered while rolling back", e1);
            }
            return SomeError.failure(e);
        }
    });
}