Hibernate Criteria Query for multiple columns with IN clause and a subselect
I have a question very similar to this question.
I am selecting all data from table1 for all matching unique combinations of field3 and field4 from table2.
Here is my stripped down SQL:
select *
from table1 as t1
where (t1.field1, t1.field2) in (select distinct field3, field4
from table2 as t2
where t2.id=12345);
I need to translate my SQL to Hibernate Criteria. I have my entity objects mapping correctly to the tables and transform the response into the correct result entity, but I cannot get my where clause translated correctly.
What I Have
Criteria criteria = getSession().createCriteria(Table1.class);
DetachedCriteria subquery = DetachedCriteria.forClass(Table2.class);
ProjectionList projectionList = Projections.projectionList();
projectionList.add(Projections.property("field3"), "field3");
projectionList.add(Projections.property("field4"), "field4");
subquery.setProjection(Projections.distinct(projectionList));
subquery.add(Restrictions.eq("id", 12345));
I want my where clause to be something like:
criteria.add(Subqueries.in("field1, field2", subquery));
But that is not allowed by Hibernate.
I have tried rolling out the where clause to have two subqueries and checking both field1 and field2 against the results, but it seems like the subqueries will always have to return multiple columns. I did this using group by but Hibernate will automatically add the columns in the group by to the projection list and I cannot find a way to remove them.
Here is the same query using group by:
select *
from table1 as t1
where t1.field1 in (select field3
from table2 as t2
where t2.id=12345
group by field3, field4)
and t1.field2 in (select field4
from table2 as t2
where t2.id=12345
group by field3, field4);
Is it possible to do my where clause using Hibernate Criteria?
If it is not possible using Hibernate Criteria, is it possible to do my where clause using HQL?
EDIT:
@Larry.Z answers my question by using HQL.
I was able to solve my problem with Hibernate Criteria, but I had to modify the query to:
select *
from table1 as t1
where exists (select 1
table2 as t2
where t2.id=12345
and t2.field3=t1.field1
and t2.field4=t1.field2);
Translated to Hibernate Criteria:
Criteria criteria = getSession().createCriteria(Table1.class, "t1");
DetachedCriteria subquery = DetachedCriteria.forClass(Table2.class, "t2");
subquery.add(Restrictions.eq("t2.id", 12345));
subquery.add(Restrictions.eqProperty("t2.field3", "t1.field1"));
subquery.add(Restrictions.eqProperty("t2.field4", "t1.field2"));
subquery.setProjection(Projections.property("t2.id")); // select the ID rather than 1
I'm still curious if it is possible to write Hibernate Criteria using my original SQL.
尝试编写像这样的HQL查询
String hql = "from Table1 t1 where (t1.field1, t1.field2) in (
select distinct t2.field3, t2.field4
from Table2 t2
where t2.id=12345)";
sessionFactory.getCurrentSession().createQuery(hql).list()
Subqueries.propertiesIn是你需要的：
criteria.add(Subqueries.propertiesIn(
new String[] { "field1", "field2" },
detachedCriteria));