I have 2 classes Person and Address. Person has a collection of addresses. I'd like to search using criteria for a person that has:
1) Personal address in the city of Moscow
2) Business addresss in the London
SQL is simple:
Code:
SELECT Person
FROM Person As Pers
WHERE Pers.Id in
(SELECT PersonId
FROM Address as Adr
WHERE Adr.City = 'Moscow' AND Adr.Type = 1)
AND Pers.Id in
(SELECT PersonId
FROM Address as Adr
WHERE Adr.City = 'London' AND Adr.Type = 2)
But my goal is to search the same using criteria (because I've already made my application search using criterias).
So I've used 2 appropaches (both doesn't work):
Approach 1:
Code:
Criteria c = session.createCriteria(Person.class);
c.createAlias("AddressSet", "adr1");
c.add(Expression.and(Expression.eq("adr1.City", "Moscow"), Expression.eq("adr1.Type", new Integer(1)));
c.createAlias("AddressSet", "adr2");
c.add(Expression.and(Expression.eq("adr2.City", "London"), Expression.eq("adr2.Type", new Integer(2)));
Approach 2:
Code:
Criteria c = session.createCriteria(Person.class);
c.createCriteria("AddressSet", "adr1").add(Expression.and(Expression.eq("City", "Moscow"), Expression.eq("Type", new Integer(1)));
c.createCriteria("AddressSet", "adr2").add(Expression.and(Expression.eq("City", "London"), Expression.eq("Type", new Integer(2)));
The result SQL of these produce a error because the Hibernate produces a single natural join instead of two sub-queries.
The question is: "Can i make this kind of search using criteria?"
P.S. This is not exactly my code. I've made a simple illustration of my problem.
Login
FAQ
Search