Hi all,
We have a query conceptually similar to the following:
Code:
String hsql = "SELECT c.name, k FROM Cat c INNER JOIN c.kittens k WHERE c.colour in (:colours) and k.whiskerLength > :length";
// populate 'colours' with a collection, and 'length' with an int
which returns a List of 2-element array tuples, one containing the String (the name) and one containing an individual Kitten entity. If a Cat has more than one Kitten, we get multiple rows with the same 'name' (this is what we want).
The problem is, we want to change this into a Criteria query, but I am unable to work out how to produce the same result.
We can do this:
Code:
Criteria crit = session.createCriteria(Cat.class)
.createAlias("kittens", "k")
.add(Restrictions.in("colour", colours))
.add(Restrictions.gt("k.whiskerLength", length));
.setProjection(Projections.projectionList()
.add(Projections.property("name"))
// What goes here?
// Not this:
// .add(Projections.property("kittens"))
// nor this:
// .add(Projections.property("k"))
);
I'm not aware of any way to get a Projection which returns the complete 'Kitten' entity, in this case. If we use 'kittens', it comes out as null (not 100% sure why, I'll admit), but that's clearly not what we want anyway ('kittens' implies ALL kittens of each cat; we just want the particular kitten that came out of the join).
We can use the ALIAS_TO_ENTITY_MAP transformer instead of projecting, I guess, but that would involve selecting out all of the fields for the Cat class (not just 'name'): in this case, that's quite expensive and we'd really like to avoid it.
Is there something in the Projection framework that will deal with this situation?
(If it helps, the reason for the change is that in some circumstances, the 'Restrictions.in("colour", colours)' creates a massive query -- think thousands of colours -- which SQL Server rejects for having too many parameters. We're replacing, in this case, Restrictions.in()'s InExpression with a new Expression, which uses a temporary-table-like solution to work around this limitation. That works fine, it's just the projection of the result that's causing me grief. If anyone can think of a good way to do something similar using the HQL approach, then that might work too)
Thanks in advance for your help.
Login
FAQ
Search