Java 具有 IN 子句和子选择的多列的休眠条件查询

Hibernate Criteria Query for multiple columns with IN clause and a subselect

提问by CoffeePasta

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.

我正在为 table2 中 field3 和 field4 的所有匹配的唯一组合选择 table1 中的所有数据。

Here is my stripped down SQL:

这是我精简的 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.

我需要将我的 SQL 转换为 Hibernate Criteria。我的实体对象正确映射到表并将响应转换为正确的结果实体，但我无法正确翻译我的 where 子句。

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:

我希望我的 where 子句类似于：

criteria.add(Subqueries.in("field1, field2", subquery));

But that is not allowed by Hibernate.

但这是 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.

我已经尝试推出 where 子句来拥有两个子查询，并根据结果检查 field1 和 field2，但似乎子查询总是必须返回多个列。我使用 group by 做到了这一点，但 Hibernate 会自动将 group by 中的列添加到投影列表中，我找不到删除它们的方法。

Here is the same query using group by:

这是使用 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?

是否可以使用 Hibernate Criteria 来执行我的 where 子句？

If it is not possible using Hibernate Criteria, is it possible to do my where clause using HQL?

如果无法使用 Hibernate Criteria，是否可以使用 HQL 执行我的 where 子句？

EDIT:

编辑：

@Larry.Z answers my question by using HQL.

@Larry.Z 使用 HQL 回答我的问题。

I was able to solve my problem with Hibernate Criteria, but I had to modify the query to:

我能够使用 Hibernate Criteria 解决我的问题，但我不得不将查询修改为：

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.

我仍然很好奇是否可以使用我的原始 SQL 编写 Hibernate Criteria。

采纳答案by Larry.Z

Try to write HQL query like this

尝试像这样编写 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()

回答by iTake

Subqueries.propertiesInis what you need:

Subqueries.propertiesIn是您所需要的：

criteria.add(Subqueries.propertiesIn(
                new String[] { "field1", "field2" },
                detachedCriteria));