How to use jOOQ within a JTA Container Managed Transaction under JBoss/Hibernate?

[johne...@gmail.com]

I'm looking to replace our Criteria API queries with jOOQ.

We're running a J2EE environment, under JBoss 8 with Hibernate as our JPA provider, and JBossTS as our full XA JTA provider.

At the moment, our Criteria API queries are made from within Container Managed Transactions.

Is it possible to simply replace these Criteria API queries with jOOQ queries using the existing Container Managed Transaction?

I see there is an example for integrating jOOQ with Spring's transaction mechanism, but I haven't a clue on the relationship between Spring and JPA/JTA.

Any pointers would be much appreciated.

Thanks

[Lukas Eder]

Hello,

Thank you very much for your enquiry. Indeed, we're working on producing examples where jOOQ is used in Java EE contexts, rather than Spring contexts.

In essence, however, it does not matter, as jOOQ simply wraps a JDBC Connection or a DataSource as input. This has also been mentioned once by Adam Bien on his airhacks:

https://twitter.com/JavaOOQ/status/468285734504628224

If you pass a container managed DataSource (with an implicit container managed transaction associated with it), jOOQ will not notice and transparently execute SQL for you.

Beware, however, that the Criteria API will be able to access JPA's cached entities, whereas writing jOOQ queries corresponds to writing native SQL via JPA.

Hope this helps,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[John Edo]

Thanks Lukas

I found that the following technique works quite nicely under Hibernate 4.3.5:

@Stateless

public class JooqTest {

    @PersistenceContext

    private EntityManager em;

    public String get() {

        em.unwrap(Session.class).doWork(new Work() {

            @Override

            public void execute(final Connection conn) throws SQLException {

                final DSLContext create = DSL.using(conn, SQLDialect.POSTGRES);

                …

You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/xkK48Mrh5so/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.

[Witold Szczerba]

Hi,
I think it could be much easier to ask for @Resource DataSource, and use just that for jOOQ...
You can create a producer which provides DSLContext, so in any bean you can just @Inject it instead of EntityManager, or you can have both injected if needed (EM using @PersistenceContext of course).

[Lukas Eder]

I agree with Witold that operating with a DataSource might be a bit better from a design perspective, as you'd be avoiding Hibernate-specific API when not really needed (Session, Work). Also, Hibernate's Work expects SQLExceptions to be thrown. I'm not sure if jOOQ's DataAccessExceptions would conform to that contract. However, you may be able to factor out some utility logic to extract the SQLException cause from DataAccessExceptions...

But again, if there's a DataSource-related solution, I'd probably prefer that.

Cheers,

Lukas

[John Edo]

Thanks all. I’ll give that approach a try.

One concern: is the connection returned by the datasource part of the active JTA transaction? e.g. suppose the jOOQ SQL runs fine, but subsequently something else causes the JTA transaction to rollback…will the jOOQ SQL be rolled back too?

[Lukas Eder]

2014-10-10 17:52 GMT+02:00 John Edo <johne...@gmail.com>:

Thanks all. I’ll give that approach a try.

One concern: is the connection returned by the datasource part of the active JTA transaction? e.g. suppose the jOOQ SQL runs fine, but subsequently something else causes the JTA transaction to rollback…will the jOOQ SQL be rolled back too?

Yes - by default, jOOQ is completely unaware of your transaction implementation and just uses org.jooq.ConnectionProviders to acquire JDBC Connections from which it then creates PreparedStatements without any magic. After statement execution, the Connection is released again to the ConnectionProvider

If you provide jOOQ with a DataSource, you are implicitly using a DataSourceConnectionProvider, and the following logic is used to acquire / release connections:

public class DataSourceConnectionProvider implements ConnectionProvider {

    private final DataSource dataSource;

    public DataSourceConnectionProvider(DataSource dataSource) {

        this.dataSource = dataSource;

    }

    public DataSource dataSource() {

        return dataSource;

    }

    @Override

    public Connection acquire() {

        try {

            return dataSource.getConnection();

        }

        catch (SQLException e) {

            throw new DataAccessException("Error getting connection from data source " + dataSource, e);

        }

    }

    @Override

    public void release(Connection connection) {

        try {

            connection.close();

        }

        catch (SQLException e) {

            throw new DataAccessException("Error closing connection " + connection, e);

        }

    }

}

More details can be seen here:

http://www.jooq.org/doc/latest/manual/sql-building/dsl-context/connection-vs-datasource/

Hope this helps,

Lukas

[John Edo]

Ok, so tying all of these ideas together, I came up with the following code that seems to interoperate properly with JTA transactions. Could you please have a look and let me know if you see any problems with it? Otherwise, we’re off to the races, and I appreciate all of your help.

@Qualifier

@Retention(RUNTIME)

@Target({ FIELD, TYPE, METHOD })

public @interface PlatformDSLContext {

}

public final class PlatformDSLContextProducer {

    @Resource(lookup = "java:jboss/datasources/PlatformDS")

    private DataSource ds;

    @Produces @PlatformDSLContext

    public DSLContext create() {

        return DSL.using(ds, SQLDialect.POSTGRES); // supply a Configuration here for further customization if reqd

    }

    private PlatformDSLContextProducer() {

    }

}

@Stateless

public class JooqTest {

    @Inject @PlatformDSLContext

    private DSLContext jooq;

    public void test() {

        jooq.select()…

    }

}

--

[Witold Szczerba]

Looks good to me, but there is no need to create and use custom
annotation like @PlatformDSLContext. @Inject alone should be enough as
far as I remember, I am not doing anything in Java, SQL and CDI for
many months so far...

Regards,
Witold Szczerba

> You received this message because you are subscribed to the Google Groups
> "jOOQ User Group" group.
> To unsubscribe from this group and stop receiving emails from it, send an