Hibernate Criteria Query用于IN子句和子查询的多列 [英] Hibernate Criteria Query for multiple columns with IN clause and a subselect

问题描述

我有个问题，与这个问题非常相似

a>。

我从table1中选择所有匹配field3和field4的唯一组合的唯一组合。

这是我精简的SQL：

  select * 
 from table1 as t1 
 where（t1 .field1，t1.field2）in（从table2中选择不同的field3，field4 
作为t2 
，其中t2.id = 12345）; 
  

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

有

  Criteria criteria = getSession（）。createCriteria（Table1.class）; 
 
 DetachedCriteria子查询= 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））; 
  

我希望我的where子句如下所示：

  criteria.add（Subqueries.in（field1，field2，subquery））; 
  

但这不是Hibernate允许的。

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

这是一样的

 从table1中选择* 
作为t1 
其中t1.field1 in（select field3 
 from table2 as t2 
 where t2.id = 12345 
 by field3，field4）
 and t1.field2 in（select field4 $ b $ from table2 as b $ b b $ b其中t2.id = 12345 
按字段3，字段4分组）; 
  

是否有可能使用Hibernate Criteria来执行where子句？

如果使用Hibernate Criteria是不可能的，是否可以使用HQL来执行where子句？

编辑：

@ Larry.Z通过使用HQL解答我的问题。

我能解决我的问题，条件，但我不得不修改查询：

  select * 
 from table1 as t1 
 where （select 1 
 table2 as t2 
 where t2.id = 12345 
 and t2.field3 = t1.field1 
 and t2.field4 = t1.field2）; 
  

转换为Hibernate标准：

<$ p $标准条件= getSession（）。createCriteria（Table1.class，t1）;

DetachedCriteria子查询= 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））; //选择ID而不是1

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

解决方案

尝试编写像这样的HQL查询

  String hql =from Table1 t1 where（t1.field1，t1.field2）in（
）从Table2 t2中选择不同的t2.field3，t2.field4 
 
 where t2.id = 12345）; 
 sessionFactory.getCurrentSession（）。createQuery（hql）.list（）
  

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.

解决方案

Try to write HQL query like this

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()

这篇关于Hibernate Criteria Query用于IN子句和子查询的多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！