Referencing outer criteria query aliases from within an SQLProjection

java hibernate hibernate-criteria
EkcenierK picture EkcenierK · Oct 19, 2011 · Viewed 15.4k times · Source

I am aware that you can use {alias} to refer to the root entity within an SQLProjection:

Projections.sqlProjection("MIN({alias}.field) as value", new String[]{"value"}, new Type[]{new LongType()}))

What I am trying to do is to reference an alias for a non-root entity:

Projections.sqlProjection("MIN(i.powerRestarts) as value", new String[]{"value"}, new Type[]{new LongType()}))

where i is an alias from the outer criteria query. The code above throws an SQL exception saying that i.powerRestarts cannot be found.

Is it possible to refer to a non-root alias from an SQLProjection?

Answer

EkcenierK picture EkcenierK · Oct 20, 2011

Having done some googling, it appears that this this is not possible - Hibernate only allows inclusion of the root entity alias using {alias} in the SQL string of the SQLProjection. I did however find this issue regarding the limitation on the Hibernate JIRA pages.

Someone has kindly submitted a patch that allows the use of non-root aliases in the SQLProjection string, through a new RestrictionsExt class. Using my example from the question:

Projections.sqlProjection("MIN(i.powerRestarts) as value", new String[]{"value"}, new Type[]{new LongType()}))

The alias i can now be referenced as:

RestrictionsExt.sqlProjection("MIN({i}.powerRestarts) as value", "value", new LongType())

I had to modify the static RestrictionsExt.sqlProjection method to allow specification of the type for the column alias ("value") (here defined as LongType), as the patch didn't allow this and defaulted to StringType.

The SQLAliasedProjection class in the patch also requires access to the following private methods in org.hibernate.loader.criteria.CriteriaQueryTranslator: getOuterQueryTranslator and getAliasedCriteria. To get this to work without modifying the Hibernate source, I used reflection:

cri = ((org.hibernate.loader.criteria.CriteriaQueryTranslator) criteriaQuery).getAliasedCriteria(alias);

was changed to:

Method m = ((org.hibernate.loader.criteria.CriteriaQueryTranslator) criteriaQuery).getClass().getDeclaredMethod("getAliasedCriteria", String.class);
m.setAccessible(true);
cri = (Criteria) m.invoke(((org.hibernate.loader.criteria.CriteriaQueryTranslator) criteriaQuery), alias);

Hopefully this will help anyone else facing the same problem.

Related questions

JPA and Hibernate - Criteria vs. JPQL or HQL

What are the pros and cons of using Criteria or HQL? The Criteria API is a nice object-oriented way to express queries in Hibernate, but sometimes Criteria Queries are more difficult to understand/build than HQL. When do you use …

Read More
org.hibernate.QueryException: could not resolve property: filename

I am using Hibernate Criteria to get values from column filename in my table contaque_recording_log. But when I'm getting the result, it throws an exception org.hibernate.QueryException: could not resolve property: filename of: com.contaque.hibernateTableMappings.contaque_…

Read More
Hibernate Criteria Query to get specific columns

I am using Criteria Query in my code. It always fires select * from ... Instead I want to neglect one column(field) from my query as that field have large number of data stored in bytes. And that causing performance issue. …

Read More