Will Hibernate always generate one SQL for one HQL?

Question

It is often better to execute two simpler SQL queries than one functionally identical complex SQL query.

Does Hibernate ever tries to use this optimalization for HQL (or Criterias or whatever) or does it always generate just one SQL?

You may ask, why don't I just write two HQL queries, just like in SQL. Consider this for example:

from User

Trivial HQL query, but it may generate very complicated SQL, because of all eager loaded associations.

Hibernate could theoretically execute just the basic query (SELECT * FROM user), gather One-To-One and One-To-Many references (ids) and then do second query to gather rows for these ids etc. This could be faster, especially when cardinality of reference ids in User is low (it would then produce smaller network traffic).

The question is, whether Hibernate (or possibly some other JPA implementation?) can use this kind of optimalization?

Thanks.

Answer 1

Short answer depends.

For any provider JPA you can really configure the way you want the provider to generate queries.

It does not matter what kind of API you are using - whether it be writing HQL/JPQL or using Criteria queries or using criteria builder - they all generate queries based on how you have configured JPA/Hibernate.

Specifically for you question since you are using Hibernate as your provider you can set fetching strategies. FetchType and FetchMode.

Fetch types can be {Lazy, Eager} But you will be interested in FetchMode which could be one of the following

FetchMode.SELECT, FetchMode.JOIN, FetchMode.SUBSELECT

Depending on the combination of FetchMode and FetchType you use, you can strongly define how Hibernate generates the queries. For example, FetchMode.JOIN will use a JOIN to fetch results and relationships in a single query while FetchMode.SELECT will use a different query for child relationships.

You can read more about this in the Hibernate documentation. http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/performance.html#performance-fetching

Answer 2

I don't think your questions have an answer, as Hibernate itself have different versions, as other JPA implementations, and the behavior can change between versions. On the other side, it really depends on your context (e.g if a OneToOne relationship is optional or no/column is nullable or no). Also it could depend on the way how you write the query. Often you will see that improvement (especially in the case of the OneToMany relationship), but as I said, there is no such a guarantee for that (as in the whole IT world).