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.

Answer 1

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.