Executing jOOQ statements inside Hibernate transaction

Question

Say I use both Hibernate (JPA) and jOOQ for my data persistence.

I want to open transaction via EntityManager to do some work.

Then I use jOOQ for selecting and updating few tables in the same transaction that I opened with EntityManager, and I commit or roll back all the operations above.

I don't use Spring or any other container transaction manager.

try {
    entityManager.getTransaction().begin();
    //do JPA stuff here fetch from FB update entity
    DLSConext - want to use JOOQ DSL for crud operation
    em.getTransaction().commit()
} catch (Exception e) {
    em.getTransaction().rollback();  //All Hibernate and jooq operation should rool back
}

Edit So How do I do it? Say for example I using jOOQ for some updates and I then I want Hibernate to find an entity, but I also want Hibernate to see the updates done by jOOQ few lines before fetching an entity.

You might want to check https://stackoverflow.com/questions/3493495/getting-database-connection-in-pure-jpa-setup . I would still advise you not to go down this road. Mixing JPA/ORM and raw SQL./JDBC is bad for your caches (the session itself, the L2 cache, query cache) and leads to pretty hard to debug issues unless you're sure you're not manipulating / flushing data "behind the back of JPA/Hibernate", which is hard to prove as you do it, and harder still to enforce in future evolutions. — GPI, Sep 01 '20 at 11:47

Lukas Eder · Accepted Answer · 2020-09-08T08:44:18.897

2

This isn't really specific to jOOQ but applies to all mixing of JPA with native SQL. You have to make sure that all of Hibernate's caches are flushed before you run any jOOQ (or JDBC, etc.) queries.

If you execute your queries using jOOQ directly, you could do this inside of jOOQ's ConnectionProvider SPI, which is invoked by jOOQ every time just before jOOQ wants to execute a query. Or, alternatively, use Hibernate's Session.doWork() API like this:

public static Work jOOQWork(Session session, Consumer<DSLContext> consumer) {
    session.flush();
    session.doWork(connection -> consumer.accept(DSL.using(connection)));
}

And then:

jOOQWork(session, ctx -> 
    ctx.insertInto(TABLE)
       .columns(TABLE.COL)
       .values(1)
       .execute()
);

Note that Session.flush() is required here, although it probably shouldn't be, see https://hibernate.atlassian.net/browse/HHH-14209

If you execute your queries via EntityManager::createNativeQuery, EntityManager::flush should be called automatically for you.

edited Sep 08 '20 at 08:44

answered Sep 07 '20 at 08:21

Lukas Eder

211,314
129
689
1,509

Thank you. I need to set jooq's connectionProvider each time I open Hibernate session? also does it mean jooq execution is in the same transaction I started with Hibernate? – user1409534 Sep 07 '20 at 14:22
@user1409534: Well, you could obtain a JDBC connection from Hibernate, see https://stackoverflow.com/q/3526556/521799. Then just wrap that with `DSL.using(connection)` in jOOQ, and make sure that your logic obtaining the JDBC connection takes care of flushing... – Lukas Eder Sep 07 '20 at 16:22
1

@user1409534: I've updated my answer with an example – Lukas Eder Sep 07 '20 at 16:31

Executing jOOQ statements inside Hibernate transaction

1 Answers1