Attempt to integrate Hibernate with jOOQ

[Antonio Gallo]

Hello Everybody,

I am new to jOOQ community, I discovered lately this database framework thanks to my collegue and i really love it.

Me and my collegue wanted to use this framework in already made web application (initially written by another software company) to replace the Hibernate Criteria API.

My collegue already used it for new functionalities but to replace Criteria API we encountered a problem due to the software architecture.

Infact, the DAO Layer of this web application, think in terms of Hibernate Entity (for the input (sometimes) and the output (always)), and it's impossible replace the the Criteria API without modify all the stack in order to remove the reference to Hibernate Entity.

Also if we populate the root Entity doesn't work, because the upper layer asks for associations (eagerly or lazely fetched by Hibernate) and if the Entity is not attached to a Hibernate Session, a LazyInitializationException will be thrown.

So I just thought about a way to resolve this problem and to use jOOQ to replace Criteria API without modifying the DAO interfaces and the upper layer (actually we don't have such time, we prefer to focus only on the query optimization).

These are my initial consideration:

- jOOQ can used to only generate SQL Code (.getSQL() method)

- Hibernate can execute SQL Code through the SQLQuery API

So at the start i tried this approach:

DSLContext dsl = ...;

String sql = dsl.select().from(...).join(...).on(...).getSQL();

SQLQuery query = session.createSQLQuery(sql);

query = query.addEntity(Entity.class);

query.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

List<Entity> result = query.list(); 

or if I expect only one result from the query:

Entity result = query.uniqueResult();

This approach seems to work fine. SQLQuery return the entities with an attached session of hibernate, so if I ask for the associations, hibernate can load it through the proxy making ad-hoc query.

But it's unaccettable for our web application to load only the root entities, because the upper layer ask often for the associations, and replace old implementation based on Criteria with these approach brings to inefficient procedure.

So I searched the SQLQuery API and I found these reference: https://docs.jboss.org/hibernate/orm/4.1/devguide/en-US/html/ch13.html#d5e3571

It's possible to tell to load also the association if we declare explicitly with the method .addJoin(String tableAlias, String path);

So for example if it's envolved a new entity in the query and we want to map it we can write:

query = query.addEntity(Entity.class, "e")

.addJoin(Entity2.class, "e.entity2");

In the chapter "Returning multiple entities", is described that to avoid problem with column name duplicates, we should write in the select query {tableAlias1.*}, {tableAlias2.*} and so on.

So after all the first phase of experimentation, I decided to encapsulate all these feature and behaviour in what i called "HibernateDSLContext" that is able to do the following things:

1) discovery the tables involved in the query (excluding subquery, only the "primary" tables)

2) discovery all the join conditions (excluding subquery, only on the "primary" tables)

3) methods:

SelectSelectStep<Record> select() to return the SelectSelectStep without declaring the fields (it's useless declaring fields, please read the following)

<T> List<T> list(Select<?> select) 

<T> T uniqueResult

 list and uniqueResult take in input the select statement generated with jOOQ and build the SQLQuery code using 1 and 2.

with the tables discovery, I take the first table and use it for the root entity. And then I use them to generate select statement with this form: select {tableAlias1.*}, {tableAlias2.*} from

with the join conditions, i am able to found (using the Hibernate Session "Metadata" API) the Entity relations between tables

I tested with some cases (simple and complex) and seems to work.

This is the version of the first prototipal code using HibernateDSLContext:

Session session = ...;

HibernateDSLContext hdsl = HibernateDSLContext.using(session);

Select<?> select = hdsl.select().from(...).join(...).on(...);

List<Entity> hdsl.list(select);

For the point 1 and 2 at first i used GSP (General SQL Parser), at the end i tried to use VisitListener of jOOQ (in the next post i will write some "issues" that i encountered and my "workaround").

I want to know what do you think about this HibernateDSLContext for a better integration with Hibernate.

The project is just in development and the code is not already available on github. I would to improve it (maybe better integration with DSLContext and SQLQuery), for now it's just a prototype.

Thank you all for your time.

I am sorry for my bad english :P

Antonio

[Lukas Eder]

Hello Antonio,

This sounds like an extremely interesting project.

As a framework, jOOQ will never really engage in attempting to solve the object graph persistence problem, simply because Hibernate has solved that very well already. We've discussed this strategy recently on a blog post:

http://blog.jooq.org/2015/03/24/jooq-vs-hibernate-when-to-choose-which/

Nonetheless, this doesn't mean that users shouldn't use both APIs in the same project. This can make perfect sense - although, the project still needs to adhere to the architecture assumptions made by Hibernate - which you do.

I like your idea of using Hibernate's session.createSQLQuery(sql); API. We were experimenting with that in the past, although because of time constraints, we didn't get very far - at least not to a point where we could publish something. But we believe that this will be the way to go for use-cases like yours. And this:

Session session = ...;
HibernateDSLContext hdsl = HibernateDSLContext.using(session);
Select<?> select = hdsl.select().from(...).join(...).on(...);
List<Entity> hdsl.list(select);

... is exactly where we wanted to go eventually, with our ideas.

For the point 1 and 2 at first i used GSP (General SQL Parser)

We didn't get too much traction with this GSP (i.e. almost none at all) - we're thinking of no longer shipping it with future versions of jOOQ.

 

at the end i tried to use VisitListener of jOOQ (in the next post i will write some "issues" that i encountered and my "workaround").

Yes, please! We're very happy to learn about those issues. Any indications that help us improve this SPI are very welcome.

 

I want to know what do you think about this HibernateDSLContext for a better integration with Hibernate.

That's the future!

 

The project is just in development and the code is not already available on github. I would to improve it (maybe better integration with DSLContext and SQLQuery), for now it's just a prototype.

I'd love to see the prototype, and I'm sure, others will be very interested as well.

Cheers,

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.

[mihalc...@gmail.com]

Hi Antonio,

This is a very interesting approach, but I a little bit reserved when combining dynamic criteria with entity fetching.
jOOQ type-safe querying is ideal for SQL projections, you normally display in a table-view or a data-grid, while
the entity-graph model fits better for the DML part.

I usually like to separate Read operations (jOOQ, SQL) from Write ones (Hibernate, JPA).

Is there any particular reason why you'd favor entity graphs instead of business-case projections?

Vlad Mihalcea

[Antonio Gallo]

Hi Lukas,

thank you, if it's really work (i have to write more tests on it and test with different version of Hibernate) I think that could be really interesting for jOOQ because it's truth that you always say that jOOQ don't replace JPA\Hibernate framework but can complement it, but it's also true that you can't use together but you have to use separetely because there isn't any kind of integration with those framework, and if you only use the fetchInto feature of jOOQ to fill the Entities, you don't work with the proxy so the entire ORM framework is useless from the point of view of lazy associations and stuff like this

Regarding the Vlad observation, this project can be useful if you have an architecture completely based on Hibernate framework when the DAO Layer is coupled with Hibernate Entity (that is our case) and you want to take control of the DAO Layer replacing the Criteria API with query written in jOOQ.

For us it could be really simple replace Criteria API because we can use a SQL wrapper to get the SQL Query, use the tool SQL 2 jOOQ to generate code base for jOOQ query, modify it and replace Criteria API with a call to the HibernateDSLContext (maybe here could be also interest to write an article if the prototype evolve in a good way :)  )

You said that you prefer separate reading using jOOQ and writing using Hibernate\JPA, but what if you have a business method that read some data before write or update something based on what is read? If you can work with the entities also for the reading part (written in jOOQ), you can reuse the same objects to write or update those data. So in this scenario it can be really useful thinks in terms of Entities.

It's also true that for other kind of reading, for example in the case of a complex report\view of data, thinks in terms of Entities is not so good instead is a really pain in the xxx, and in that case is obviously better using select with projections and specific bean to represent the data maybe 1-1 with the select fields.

So i don't prefer entity graph instead of business-case projections, it's only that the best choise depends on what are you doing and for sure you can\have to use both approaches depending on the case.

Finally i think that this project has a lot of application field.

I can also think to develop a JPADslContext that is limited only to fetch the entity without any associations (unfortunately there isn't an API for that like in Hibernate).

I will publish soon the project (actually called jooq4hibernate) in a public BitBucket repository and i'll share the link with you.

I will also post my doubts and problems encountered using VisitListener of jOOQ (when the code will be available is better).

Lucas you can eventually tell me how to handle certain things that actually i handle using reflection in the VisitListener (to extract some data, i had to study little pieces of jOOQ code to access it, also because the poor documentation of VisitListener).

Stay tuned!

Antonio

[Lukas Eder]

Hi Antonio,

2015-04-18 13:41 GMT+02:00 Antonio Gallo <dokki...@gmail.com>:

Hi Lukas,

thank you, if it's really work (i have to write more tests on it and test with different version of Hibernate) I think that could be really interesting for jOOQ because it's truth that you always say that jOOQ don't replace JPA\Hibernate framework but can complement it, but it's also true that you can't use together but you have to use separetely because there isn't any kind of integration with those framework, and if you only use the fetchInto feature of jOOQ to fill the Entities, you don't work with the proxy so the entire ORM framework is useless from the point of view of lazy associations and stuff like this

Yes, that's true. jOOQ's current support for JPA annotations is very limited. In fact, we only chose JPA annotations because those are probably already on your class path (instead of implementing yet another set of annotations).

Regarding the Vlad observation, this project can be useful if you have an architecture completely based on Hibernate framework when the DAO Layer is coupled with Hibernate Entity (that is our case) and you want to take control of the DAO Layer replacing the Criteria API with query written in jOOQ.

For us it could be really simple replace Criteria API because we can use a SQL wrapper to get the SQL Query, use the tool SQL 2 jOOQ to generate code base for jOOQ query, modify it and replace Criteria API with a call to the HibernateDSLContext (maybe here could be also interest to write an article if the prototype evolve in a good way :)  )

We have to be extremely careful here, and I will need to place a disclaimer - just in case.

jOOQ will never replace the Criteria API much like SQL will never replace JPQL. I will use SQL as a synonym for jOOQ and JPQL as a synonym for Criteria API from now on.

With JPQL, you're querying the object graph, i.e. the entities. JPQL has been kept extremely limited in functionality for good reasons. When querying the object graph, for instance, a UNION simply doesn't make any sense. Neither does a derived table, because the derived table can only ever materialise full entities, not arbitrary projections.

This is also what Vlad meant (I suspect), when he referred to SQL as being ideal for "table-view" or "data-grid" projections.

So, let's repeat: 

- With SQL, you will NOT query the object graph.

- Thus, SQL is NOT an equivalent replacement for JPQL.

- Thus, jOOQ is NOT an equivalent replacement for the Criteria API.

I'm sure we're on the same lines of thought, here, but I have to say this, just to be sure I can link back to this discussion later on :-) (e.g. when someone else finds this discussion via Google)

Now, since you *know* the above, you *can* use jOOQ to generate native SQL and let the EntityManager (or Hibernate Session) materialise entities from such results, either directly via EntityManager.createNativeQuery(String, Class), or indirectly via EntityManager.createNativeQuery(String, String) i.e. via @SqlResultSetMappings (if you must). I'm curious if you will make better query decisions than Hibernate, directly. Would be interesting to learn more about that.

You said that you prefer separate reading using jOOQ and writing using Hibernate\JPA, but what if you have a business method that read some data before write or update something based on what is read? If you can work with the entities also for the reading part (written in jOOQ), you can reuse the same objects to write or update those data. So in this scenario it can be really useful thinks in terms of Entities.

Yes, I agree that this might be useful in some situations.

It's also true that for other kind of reading, for example in the case of a complex report\view of data, thinks in terms of Entities is not so good instead is a really pain in the xxx, and in that case is obviously better using select with projections and specific bean to represent the data maybe 1-1 with the select fields.

So i don't prefer entity graph instead of business-case projections, it's only that the best choise depends on what are you doing and for sure you can\have to use both approaches depending on the case.

Finally i think that this project has a lot of application field.

I can also think to develop a JPADslContext that is limited only to fetch the entity without any associations (unfortunately there isn't an API for that like in Hibernate).

Are you sure? Maybe with JPA 2.1 @NamedEntityGraph (behold of the www.annotatiomania.com that is involved with using those)

I will publish soon the project (actually called jooq4hibernate) in a public BitBucket repository and i'll share the link with you.

Wonderful! Looking forward. 

I will also post my doubts and problems encountered using VisitListener of jOOQ (when the code will be available is better).

Lucas you can eventually tell me how to handle certain things that actually i handle using reflection in the VisitListener (to extract some data, i had to study little pieces of jOOQ code to access it, also because the poor documentation of VisitListener).

Sure, I can. As soon as you tell me what exactly those problems were :-)

Cheers,

Lukas 

[Antonio Gallo]

Starting from this: jOOQ will never replace the Criteria API much like SQL will never replace JPQL.

Obiouvsly the project is not intended to replace the Criteria API\JPQL, but for some scenario\functionality i think that for sure with this library (in theory) you could think about use Criteria API or JOOQ with the HibernateDSLContext, because basically both can work in terms of JPA Entity.

- With SQL, you will NOT query the object graph.

Yes, with SQL you don't query the object graph, infact one of the purpose of the library is try to find (if exists) the relationship between join conditions and Entity Objects, in order to specify automatically the "addJoin" for the hibernate SQLQuery API. The effect of doing this is obtain the root entity with the entity object associated eagerly fetched.

 

- Thus, SQL is NOT an equivalent replacement for JPQL.

Totally agree.

- Thus, jOOQ is NOT an equivalent replacement for the Criteria API.

Not equivalent for sure, but the purpose of this "jooq4hibernate" is trying to write a little layer that enable you to write select that returns Entity with Associations attached. So as i said, in some scenario maybe you can think about use Criteria API or HibernateDSLContext.

 Are you sure? Maybe with JPA 2.1 @NamedEntityGraph

Maybe, i didn't study those yet, but you should write by hands the association logic. Instead the purpose of this library is to find "automagically" those associations from join conditions :P

The reason of posting here is for sure try to understand with you if my work\thought have sense :D

So for me it's a good thing receive those kind of feedback!

Antonio

 

[antoni...@fhoster.com]

As I said, I share with you the prototypal code of jooq4hibernate :)

just execute:

git clone https://bitbucket.org/fhostercom/jooq4hibernate.git

and import existing project from maven.

I wrote some JUnit tests, but I have to improve them.

Thanks Vlad for this post: http://vladmihalcea.com/2013/12/06/jooq-facts-from-jpa-annotations-to-jooq-table-mappings/ that I used to generate Jooq model from JPA Entity of the test classes.

@Lukas: Is there a way from the maven plugin jooq-codegen-maven to manipulate the prefix of the table? Because actually I have same classname with different packages for JPA Entity and Jooq table and I want to avoid it with a prefix.

@Lukas: I ask you the favour to see the class DefaultSQLQueryAnalyzer. It extends DefaultVisitListener and it's the class that extract informations related to tables and join conditions. At the start of files I wrote some "issues"\question. If you could review and send me a feedback would be great!

Thank you,

Antonio

 

[Lukas Eder]

Hi Antonio,

We're on the same line of thought, about SQL vs. ORM. I had no doubts about it, but I had to make a clear statement for future readers :-) (e.g. https://groups.google.com/forum/#!topic/jooq-user/dVw0unCHJGo)

Thanks for sharing the link to your jooq4hibernate prototype. I'll be looking into it sometime these days.

Regarding your question:

2015-04-20 13:07 GMT+02:00 <antoni...@fhoster.com>:

@Lukas: Is there a way from the maven plugin jooq-codegen-maven to manipulate the prefix of the table? Because actually I have same classname with different packages for JPA Entity and Jooq table and I want to avoid it with a prefix.

If by "prefix" you mean a prefix for the class names only, then yes. This is called the generator strategies:

http://www.jooq.org/doc/latest/manual/code-generation/codegen-generatorstrategy/

http://www.jooq.org/doc/latest/manual/code-generation/codegen-matcherstrategy/

I'll get back to you with more feedback once I've seen jooq4hibernate.

Cheers

Lukas

[antoni...@fhoster.com]

Hi Antonio,

We're on the same line of thought, about SQL vs. ORM. I had no doubts about it, but I had to make a clear statement for future readers :-) (e.g. https://groups.google.com/forum/#!topic/jooq-user/dVw0unCHJGo)

 

Yep I think the same :)

Thanks for sharing the link to your jooq4hibernate prototype. I'll be looking into it sometime these days.

Yes please, when you have some time, I absolutely have to improve drastically those code.

 

Regarding your question:

2015-04-20 13:07 GMT+02:00 <antoni...@fhoster.com>:

@Lukas: Is there a way from the maven plugin jooq-codegen-maven to manipulate the prefix of the table? Because actually I have same classname with different packages for JPA Entity and Jooq table and I want to avoid it with a prefix.

If by "prefix" you mean a prefix for the class names only, then yes. This is called the generator strategies:

http://www.jooq.org/doc/latest/manual/code-generation/codegen-generatorstrategy/

http://www.jooq.org/doc/latest/manual/code-generation/codegen-matcherstrategy/

Thank you, i didn't search before for those feature :P

 

I'll get back to you with more feedback once I've seen jooq4hibernate.

Thank you very much.

I just find out that in the project QueryDSL, there is a little documentation that describe the same approach.

QueryDSL for sure have JPA native support using JPQL and metamodel based on entity (infact in our architecture I discuss with my collegues to use this instead of jOOQ, to rewrite some statements written with Criteria) but it also have SQL support.

Here http://www.querydsl.com/static/querydsl/2.1.0/reference/html/ch02s02.html is described how to use SQL metadata to handle query with Hibernate Native Query (in the chapter 2.2.14).

I don't know if the returned entities have the association fetched if you join tables or it only fetch the root entity by default (in those case you can always, I think, fetch the association writing by hands the addJoin, that is a little boring). In my case, I try to automatically write addJoin through the analysis of join conditions (i have to review, test and improve those part too).

Fun fact is that I called the Query type returned by HibernateDSLContext, HibernateSQLQuery which is exact the same name used in QueryDSL XD

Antonio

[Lukas Eder]

Hi there,

I've had a look at your prototype now with the debugger. I'll comment at the end of this E-Mail, responding to your latest E-Mail first

2015-04-20 20:04 GMT+02:00 <antoni...@fhoster.com>:

 

I'll get back to you with more feedback once I've seen jooq4hibernate.

Thank you very much.

I just find out that in the project QueryDSL, there is a little documentation that describe the same approach.

QueryDSL for sure have JPA native support using JPQL and metamodel based on entity (infact in our architecture I discuss with my collegues to use this instead of jOOQ, to rewrite some statements written with Criteria) but it also have SQL support.

Here http://www.querydsl.com/static/querydsl/2.1.0/reference/html/ch02s02.html is described how to use SQL metadata to handle query with Hibernate Native Query (in the chapter 2.2.14).

Hmm, there's no magic in QueryDSL like the magic you've implemented in jooq4hibernate. QueryDSL has different sets of query APIs for different backends. The documentation page you've linked is used to produce JPQL, not SQL...

QueryDSL is a higher-abstraction query API than jOOQ. Timo aims for supporting any sort of backend, including MongoDB, Java Collections, etc. similar to .NET's LINQ. jOOQ is only about SQL, and thus abstracts less from JDBC and from the SQL language. This obviously makes QueryDSL better suited for JPQL, and jOOQ better suited for SQL.

Now, about jooq4hibernate. I'm actually impressed that you've managed to extract that much information from the jOOQ AST and transform it into a Hibernate SQLQuery with additional meta information. I can imagine that it might add value to your projects where you know exactly what you're doing, and why you're doing it.

Here are some comments to the questions in DefaultSQLQueryAnalyzer:

 * Open "issues"\question:

 * 1) Do i use the correct Clause to extract the information that i need?

 * Select\join table, excluding any subquery only on the main query, and their join conditions.

 * Is it correct check for Clause.SELECT_FROM in the context.clauses() for this purpose?

Probably, although, I'm not 100% sure if I understand the question

 * 2) In clause TABLE_REFERENCE in some cases the queryPart is Table in others TableList

 * A simple select with alias specified return Table the same select without alias seems to return TableList with one element inside.

 * To extract the table from tableList actually I use the reflection on the superClass to retrieve the wrapperList, assuming that contains Objects implementing Table interface.

This is an interesting observation. There's definitely some Javadoc missing on VisitContext.queryPart(). The problem here is that you're implementing "clauseStart()", which emits the start of a Clause *before* all the clauses QueryParts are emitted. The order of calls is this (pseudo code):

- clauseStart(TABLE)

- clauseStart(TABLE_REFERENCE)

- visitStart(... the actual QueryPart ...)

This can be seen in the code of AbstractContext.visit(QueryPart):

            // Issue start clause events

            // -----------------------------------------------------------------

            Clause[] clauses = visitListeners.length > 0 ? clause(part) : null;

            if (clauses != null)

                for (int i = 0; i < clauses.length; i++)

                    start(clauses[i]); // This is when clauseStart(TABLE_REFERENCE) is called

            // Perform the actual visiting, or recurse into the replacement

            // -----------------------------------------------------------------

            QueryPart original = part;

            QueryPart replacement = start(part); // This is when your ctx.queryPart() reference becomes available

            if (original == replacement)

                visit0(original);

            else

                visit0(replacement);

            end(replacement);

            // Issue end clause events

            // -----------------------------------------------------------------

            if (clauses != null)

                for (int i = clauses.length - 1; i >= 0; i--)

                    end(clauses[i]);

You should probably move some of the logic from clauseStart() to visitStart(). clauseStart() and clauseEnd() events are useful to track the beginning and the end of a whole Clause, i.e. including keywords and everything.

We'll fix this in the next minor (or major) release. Probably, VisitContext.queryPart() should return null within clauseStart() and clauseEnd(). I'm tracking this as:

https://github.com/jOOQ/jOOQ/issues/4249

 * 3) For the case of TableList and case of Field I retrieve information needed with reflection. Can i do in some other way?

This should be resolved once you switch from clauseStart() to visitStart(). The QueryPart is simply "stale".

 *  4) In TABLE_REFERENCE if the alias is not specified, it seems to return "join" as table.getName(). See isValidAlias (I actually exclude "join" as valid name alias, this is dangerous because if for some reason, some table has declared with 'join' alias, this is ignored!).

Same thing. You won't get the "join" in TABLE_REFERENCE, once you switch to visitStart()

 *  5) To trigger the VisitListener, I use DSL.using(configuration).renderContext().visit(select); Is it ok? I want to avoid select.getSQL() just to trigger the VisitListener.

I also noticed this. This is correct, and much better than using attach() and getSQL(), as it will not modify the state of your select reference. Note that the following two are the same:

DSL.using(configuration).renderContext().visit(select);

DSL.using(configuration).render(select);

The latter actually returns the SQL string that you need.

 *  6) Just to report, it's not an issue here. For some clauses, if i try to print the queryPart, a stackoverflowexception will be trown.

Yes, this can happen because you re-enter your VisitListener indefinitely. This probably happens when you attach your Configuration/VisitListener to the query part.

I think with the above input, your existing prototype will become a lot simpler. For us, it's extremely valuable to see you using the VisitListener like that. We obviously got 1-2 things wrong, and we obviously have to better document this part of jOOQ. Looking forward to your further experiments!

Lukas