Quote:
from Company c where c.addresses.city = 'New York' and c.addresses.city = 'Boston'
This HQL won't work for 2 reasons. Firstly, you can't navigate through a collection (addresses) using the dot notation - you have to create an aliased join.
Secondly, each returned row would contain a single city from one of the company's addresses. Its impossible for that city to be BOTH new york and boston - your query would return no results.
I think what you want is something like this:
Code:
from Company c where 2 = (select count(distinct city) from Company c1 join c1.addresses a1 where c1.id=c.id and city in ('New York', 'Boston'))
"distinct" just incase a company has, say, 2 offices in New York.
Translating this into a Criteria _should_ be simple:
Code:
String[] cities = new String[] { "New York", "Boston" };
DetachedCriteria subquery = DetachedCriteria.forClass(Company.class, "c1")
.createCriteria("addresses")
.add(Restrictions.in("city", cities))
.add(Property.forName("c1.id").eqProperty("c.id"))
.setProjection(Projections.countDistinct("city"));
Criteria criteria = session.createCriteria(Company.class, "c")
.add(Subqueries.eq(cities.length, subquery));
However, there's currently a bug in hibernate...
http://opensource.atlassian.com/project ... se/HHH-952...which means subqueries containing joins don't work. The good news is its fixed in head so should be in the 3.3 release. There's a patch in the JIRA issue if you need to fix this bug before 3.3.
To get around this you can use an SQL restriction in the criteria. Its not pretty though. I've assumed your main tables are called Company and Address and have ID columns called id. The many-to-many mapping table I've called Company_Address with columns company_id and address_id.
Code:
String[] cities = new String[] { "New York", "Boston" };
Criteria criteria = session.createCriteria(Company.class, "c")
.add(createCitiesRestriction(cities));
...
private Criterion createCitiesRestriction(String[] cities) {
List<Object> paramsList = new ArrayList<Object>();
List<Type> typesList = new ArrayList<Type>();
StringBuffer inClause = new StringBuffer();
// Add the count param
paramsList.add(new Integer(cities.length));
typesList.add(Hibernate.INTEGER);
// Create cities params
for (int i=0; i<cities.length; i++) {
paramsList.add(cities[i]);
typesList.add(Hibernate.STRING);
if (i != 0) {
inClause.append(", ");
}
inClause.append("? ");
}
StringBuffer query = new StringBuffer();
query.append("? = (select count(distinct city) from Company c1 ");
query.append("inner join Company_Address ca on c1.id=ca.company_id ");
query.append("inner join Address a on ca.address_id=a.id ");
query.append("where c1.id = {alias}.id ");
query.append("and city in (");
query.append(inClause);
query.append("))");
return Restrictions.sqlRestriction(query.toString(),
paramsList.toArray(),
typesList.toArray(new Type[0]));
}
Login
FAQ
Search
