The API Documenataion of Hibernate's Criteria API's sqlRestriction method, defines as follows:
/**
* Apply a constraint expressed in SQL, with the given JDBC
* parameter. Any occurrences of {alias} will be replaced
* by the table alias. */
Make note of the {alias} placeholder in the above comments.
The catch is the only {alias} this method understands is the alias name of the root table on which the Criteria call is constructed. If more than one table is joined in the Criteria query either using the createCriteria or the createAlias method, the alias of these 'joined' tables are not understood by the Criteria API.
In order to get over this problem in our project, we had to look at the SQL generated by the Criteria object, figure out the alias name generated by hibernate and then code that in the SQL that is passed in as an argument to the sqlRestriction method.
For example in the below snippet,
Criteria c = session.createCriteria(BaseTable.class, "base");
c.createAlias(SecondTable.class, "second", CriteriaSpecification.LEFT_JOIN);
sqlRestriction method can interpret only "base" as the alias.
Subscribe to:
Post Comments (Atom)
4 comments:
I didnt try, but the {alias} may be associated to the actual criteria, if so, we could try to create another criteria to the object property and use it to see if it works.
Yep, something like this should be possible https://forums.hibernate.org/viewtopic.php?p=2359059&sid=96d086733f32c50766bba777b4ed99ad
However, my problem is, that the Criteria is build automagically from the user input by some library and the generated alias is not the same all the time :-(
Great post. Yeah we hit this same issue. I'd think aliases might be something they'd want to be able to handle....guess not.
This post is referenced a lot through google so I'll post my solution here.
I changed createAlias to createCriteria and added sqlRestriction directly onto that criteria. Then {alias} references correct table.
So your example becomes:
Criteria c = session.createCriteria(BaseTable.class, "base");
Criteria secondCriteria = c.createCriteria("secondTable","second", CriteriaSpecification.LEFT_JOIN);
secondCriteria.add(Restrictions.sqlRestriction("{alias}.id = someCoolCondition");
This worked.
Post a Comment