Mix JOOQ query with JDBC transaction

Question

I have a use case where I would like to mix a jdbc transaction with jooq context.

The JDBC code looks like that:

  public void inTransaction(InTransaction lambda) {
    DataSource ds = dataSource.get();
    try (Connection connection = ds.getConnection()) {
      try {
        logger.info("set autocommit to false");
        connection.setAutoCommit(false);
        try (Statement statement = connection.createStatement()) {
          lambda.execute(statement);
          logger.info("commiting transaction");
          connection.commit();
        }
      } catch (RuntimeException e) {
        logger.info("rolling back transaction");
        connection.rollback();
        throw e;
      } finally {
        logger.info("set autocommit to true");
        connection.setAutoCommit(true);
      }
    } catch (SQLException e) {
      throw new TilerException(e);
    }
  }

  @FunctionalInterface
  public interface InTransaction {
    void execute(Statement statement) throws SQLException;
  }

And I would like the lambda parameter to be able to work with both jdbc and jooq.

For jdbc using a statement is pretty straight-forward. For example something like this tutorail:

 inTransaction(stmt -> {
   String SQL = "INSERT INTO Employees  " +
                "VALUES (106, 20, 'Rita', 'Tez')";
   stmt.executeUpdate(SQL);
   String SQL = "INSERTED IN Employees  " +
                "VALUES (107, 22, 'Sita', 'Singh')";
   stmt.executeUpdate(SQL);
 });

In order to execute jooq queries on the same transaction I have to obtain a context. I found an api to get a DSLContext from datasource/connection.
What is not clear to me is if/how to create a jooq DSLContext from a statement?

Answer 1

A solution to the problem you described

You can do all of this with jOOQ's transaction API:

// Create this ad-hoc, or inject it, or whatever
DSLContext ctx = DSL.using(dataSource, dialect);

And then:

public void inJDBCTransaction(InJDBCTransaction lambda) {
    ctx.transaction(config -> {
        config.dsl().connection(connection -> {
            try (Statement statement = connection.createStatement()) {
                lambda.execute(statement);
            }
        });
    });
}

public void inJOOQTransaction(InJOOQTransaction lambda) {
    ctx.transaction(config -> lambda.execute(config.dsl()));
}

@FunctionalInterface
public interface InJDBCTransaction {
    void execute(Statement statement) throws SQLException;
}

@FunctionalInterface
public interface InJOOQTransaction {
    void execute(DSLContext ctx);
}

Your final code:

inJDBCTransaction(stmt -> {
    String SQL = "INSERT INTO Employees  " +
                 "VALUES (106, 20, 'Rita', 'Tez')";
    stmt.executeUpdate(SQL);
    String SQL = "INSERTED IN Employees  " +
                 "VALUES (107, 22, 'Sita', 'Singh')";
    stmt.executeUpdate(SQL);
});

inJOOQTransaction(ctx -> {
    ctx.insertInto(EMPLOYEES).values(106, 20, "Rita", "Tez").execute();
    ctx.insertInto(EMPLOYEES).values(107, 22, "Sita", "Singh").execute();
});

I'm not too convinced about the need for this abstraction over jOOQ and JDBC. jOOQ never hides JDBC from you. You can always access the JDBC API as shown above when using the DSLContext.connection() method. So, as shown above:

• The jOOQ transaction API does exactly what you're planning to do. Wrap a lambda in a transactional context, commit if it succeeds, rollback if it fails (your version's rollback doesn't work because it catches the wrong exception).
• If the "JDBC escape hatch" is needed, jOOQ can offer that

Side note

In many RDBMS, you don't want to run queries on a static JDBC Statement. You'll want to use PreparedStatement instead because:

• You'll profit from execution plan caching (and less contention on the cache)
• You'll avoid syntax errors (in case your real query is dynamic)
• You'll avoid SQL injection trouble