jooq vs hibernate use case(transaction management)

[prasath....@gmail.com]

Hi,

I am using jooq with hibernate in my project for data persistence.

jooq - for read operations.
hibernate - for saving, deleting, updating data.

Jooq and hibernate have their own independent datasources configured pointing to same underlying database.

1. Is it possible to make the jooq datasource read only-  to restrict save, update and delete using jooq?
2. What is the difference in the transaction management feature supported by hibernate and jooq? Is there any transaction management feature supported by hibernate which is not available in jooq?

Thanks in advance for clarifying.

Regards,
Prasath

[Lukas Eder]

Hi Prasath,

Thank you very much for sharing your very interesting use-case with us. I'm aware of other users who integrated jOOQ with Hibernate in a similar way. Let's hope they too will share their experience.

Some comments from my side, inline:

2016-04-11 9:29 GMT+02:00 <prasath....@gmail.com>:

Hi,

I am using jooq with hibernate in my project for data persistence.

jooq - for read operations.
hibernate - for saving, deleting, updating data.

Jooq and hibernate have their own independent datasources configured pointing to same underlying database.

1. Is it possible to make the jooq datasource read only-  to restrict save, update and delete using jooq?

Depending on your database / JDBC driver, there are some measures you can take to prevent write operations on that level. For instance, if you're using Oracle, you should certainly create a separate database user and only grant SELECT privilege to that user.

In addition to the above, within jOOQ, you can implement an ExecuteListener, and intercept already the ExecuteListener.start() event. From the argument ExecuteContext.type(), you can see whether the jOOQ object being executed is any of:

- A routine

- A batch statement

- A DDL operation

- A Read operation

- A Write operation

- Something else

In your case, you'd only allow read operations. Otherwise, you could throw an exception.

All statement types executed by jOOQ are guaranteed to run by the ExecuteListener lifecycle. More info here:

http://www.jooq.org/doc/latest/manual/sql-execution/execute-listeners

 

2. What is the difference in the transaction management feature supported by hibernate and jooq? Is there any transaction management feature supported by hibernate which is not available in jooq?

Yes, many. jOOQ does provide a transaction API, but not really an implementation. The idea of the jOOQ transaction API is to make using transactions more explicit, by providing a functional API where you can pass "transactional lambdas" to jOOQ. The transaction semantics is most often implemented by JDBC, JTA, Spring, etc. Users are expected to bring their own jOOQ TransactionProviders.

In that way, jOOQ can work inside of Hibernate's transactions, or inside of JDBC's transactions, or inside of your JTA UserTransactions, etc.

Hope this helps,

Lukas

[Daniele Antonini]

Hi Prasath,

I work on a large project using both hibernate and jooq.

Hibernate is used when:
* we need to save/update/delete an entity
* fetch entities objects

We use QueryDSL to create custom query to retrieve complex objects graph.

jooq is used when:
* to overcome QueryDSL limitations
* to overcome ORM constraints: reports or complex queries do not fit well in entity based world

A quick strategy I use to choose between hibernate and jooq is:

If I need to present data to the user like a query result, with zero o few business logic on top of it AND I don't need to have an entity to work with I choose for jooq.

If I need an entity I use pure hibernate or QueryDSL.

I usually don't use jooq to fetch data and fill hibernate entity pojo, or when I do that entity is very simple.

We experienced to use jooq also for non trivial entity fetch and mapping the result into entity graph, but we realize that we need too much effort to do that and than we discovered QueryDSL :p

Regarding datasource: I share same datasource between hibernate and jooq. Seems quite reasonable to me use database permission to separate functionalities, not technology framework database access but if you need to do that you can follow Lukas tips.

Regards
Daniele

[Lukas Eder]

Thanks a lot, Daniele, for chiming in!

2016-04-13 9:07 GMT+02:00 Daniele Antonini <antonini...@gmail.com>:

We use QueryDSL to create custom query to retrieve complex objects graph.

Perhaps we should offer a DSL also to form JPQL queries, hmm? :)

 

A quick strategy I use to choose between  hibernate and jooq is:

If I need to present data to the user like a query result, with zero o few business logic on top of it AND I don't need to have an entity to work with I choose for jooq.

Depending on the database (i.e. not MySQL), of course, it might even be suitable to move most "business logic" into that query... I'm curious, what kind of report / complex query is complex enough to use SQL / jOOQ, but doesn't have any business logic on top of it?

We experienced to use jooq also for non trivial entity fetch and mapping the result into entity graph, but we realize that we need too much effort to do that

I agree, that's a good idea only in rare cases...

[Daniele Antonini]

Hi Lukas,

You are right, I've abused of "business logic" term :p

Surely you can move any business logic into the query.
For services that I built from scratch, I use only jooq implementing complex query (with a lot of business logic on top of it)

Let me explain a bit more what I mean.
We introduced jooq on an already running system, so we had to maintain and evolve a 100% hibernate software (with a lot performance issues).

Many of the business logic was already built in Java layer: object graph navigation, state machine management, also a pure object filtering!

The problematic scenario was that Java layer use a lot the "transparent" aspect of an orm, and in particular the lazy fetching feature. When we try to replace the hibernate dao (unmaintenable queries using hibernate criteria) with ad hoc optimize jooq query, we faced out that another piece of code used that method to retrieve the same BUT then it navigate a different part of objects graph.

In conclusion,
If you have a lot of Java logic traversing your entities and your code rely heavily on orn lazy fetching, using jooq to replace the native orm behavior is a game over.

If you fetch from db _all_ data you need to perform any business logic you can safely replace orm queries with jooq, but you have to deal with mapping.

In both scenarios jooq support for jpql would be great, but I see technical problems (maybe it requires a new discussion)

If you need a pure relational feature, like report, you have to use jooq.

Regards
Daniele

[Lukas Eder]

Thank you very much for your additional feedback, Daniele

2016-04-14 10:29 GMT+02:00 Daniele Antonini <antonini...@gmail.com>:

You are right, I've abused of "business logic" term :p

Surely you can move any business logic into the query.
For services that I built from scratch, I use only jooq implementing complex query (with a lot of business logic on top of it)

I couldn't resist :) I've recently written a series on the topic for a partner blog:

- http://www.vertabelo.com/blog/notes-from-the-lab/why-sql-is-neither-legacy-nor-low-level-but-simply-awesome

- http://www.vertabelo.com/blog/notes-from-the-lab/business-logic-in-the-database-yes-or-no-it-depends

In conclusion,
If you have a lot of Java logic traversing your entities and your code rely heavily on orn lazy fetching, using jooq to replace the native orm behavior is a game over.

Yes, I absolutely agree with that. There's a big paradigm shift between the JPA/ORM and jOOQ/SQL approaches. I also like Mike Hadlow's diagram for this purpose:

http://mikehadlow.blogspot.ch/2012/06/when-should-i-use-orm.html

 

If you fetch from db _all_ data you need to perform any business logic you can safely replace orm queries with jooq, but you have to deal with mapping.

Are you using Java 8 yet? I'm personally convinced that functional programming will greatly influence the mapping discussion. The existing annotation-based configurative mapping approach is obsolete in my opinion. People would really prefer more explicit, functional mapping algorithms.

I'd love to know what other people think, though!

 

In both scenarios jooq support for jpql would be great, but I see technical problems (maybe it requires a new discussion)

Well, it would help remove the QueryDSL dependency. But the original problem remains. When to "query" the entity graph, and when to query the database.