使用子查询连接两列的Hibernate条件查询 [英] Hibernate criteria query with subquery joining two columns

问题描述

我有一张表格Quote，映射在hibernate中，它有一个整数id和一个日期的组合键，还有几个额外的列。我希望编写一个使用DetachedCriteria的条件查询来获取每个具有最大日期的id的行。

在sql中，我可能会写一个查询，比如

  SELECT * FROM引用q1 
 INNER JOIN（SELECT id，max（date）as maxdate FROM Quote 
 GROUP BY id，date）q2 
 ON q1.id = q2.id AND q1.date = q2.maxdate 
  

在hibernate中，我认为可以像这样为group by子查询创建一个DetachedCriteria（其中Quote是映射表的类，而Qid是该键的复合id类，属性id和日期，由Quote类的qid属性访问）：

  DetachedCriteria maxDateQry = DetachedCriteria.forClass（Quote.class ）; 
 maxDateQry.setProjection（
 Projections.projectionList（）
 .add（Projections.max（qid.date，maxdate））
 .add（Projections.groupProperty（ qid.id）））; 
  

但是，我不确定如何在标准查询中使用它，上面的sql的外部部分。我正在寻找一些类似于

的内容：

  Criteria criteria = session.createCriteria（Quote.class）; 
 criteria.add（
 Restrictions.and（
 Property.forName（qid.id）。eq（maxDateQry ???），
 Property.forName（qid。 。日期）当量（maxDateQry ???）））; 
列表<引用> quoteList = criteria.list（）; 
  

其中两个Property.forName的上述内容将外部表与子查询的相应列相关联。如果内部联接只提供一个值，我只需给DetachedCriteria一个Projection，然后直接将DetachedCriteria传递给Property.forName（...）。eq（..）。我不确定如何在Projection中使用带有两个值（id和maxdate）的DetachedCriteria。

解决方案

同样的问题，并找不到多列子查询匹配的确切解决方案。我所做的是重写查询，以便它只需要匹配ONE列中的子查询。因此，而不是

  SELECT * FROM引用q1 
 INNER JOIN（SELECT id，max（date）as maxdate FROM Quote 
 GROUP BY id，date）q2 
 ON q1.id = q2.id AND q1.date = q2.maxdate 
  

尝试

  SELECT * FROM引用q1其中g1.date = 
（ SELECT max（inner.date）FROM引用内部where inner.id = g1.id 
 GROUP BY inner.id）
  

关键区别在于所有条形图的JOIN标准的MAX条件现在都在内部SELECT中。

标准/分离标准如下所示：

  DetachedCriteria innerCriteria = DetachedCriteria.forClass（Quote.class，inner）
 .add（Restrictions.eqProperty（inner.id，q1.id））
 .setProjection（Projections.projectionList（） 。新增（Projections.max（ inner.date）））; 
 
 DetachedCriteria outerCriteria = DetachedCriteria.forClass（ClmClaim.class，q1）; 
 outerCriteria.add（Subqueries.propertyEq（q1.date，innerCriteria））; 
  

产生的SQL如下所示：

  select 
 this _。< Blah> as blah2_49_0_，
 this _。< Blah> as blah2_50_0_，
 this _。< Blah> as blah2_51_0_，
 
 from 
引用this_ 
其中
 this_.date =（
选择
 max（inner_.date）为y0_ 
 from 
引用inner_ 
其中
 inner_.claim_number = this_.claim_number 
）; 
  

您会注意到SQL中没有'group by'，因为它不是必需的。如果在子选择中有多个匹配条件，我会希望有人在那里。

无论如何，希望它有帮助。这是我在圣诞节前最后一件事！

I have a table, "Quote", mapped in hibernate that has a composite key of an integer id and a date, and several additional columns. I'd like to write a criteria query that uses a DetachedCriteria to get the row for each id with the greatest date.

In sql, I might write a query like

SELECT * FROM Quote q1
  INNER JOIN (SELECT id, max(date) as maxdate FROM Quote
               GROUP BY id, date) q2
  ON q1.id = q2.id AND q1.date = q2.maxdate

In hibernate, I think can create a DetachedCriteria for the "group by" subquery like this (where Quote is the class mapping the table, and "Qid" is a composite id class for the key, with properties id and date, accessed by a "qid" property of the Quote class):

DetachedCriteria maxDateQry = DetachedCriteria.forClass(Quote.class);
maxDateQry.setProjection(
    Projections.projectionList()
        .add(Projections.max("qid.date", "maxdate"))
        .add(Projections.groupProperty("qid.id")));

However, I'm not sure how to use this in a criteria query that would be equivalent to the outer part of the sql, above. I'm looking for something along the lines of

Criteria criteria = session.createCriteria(Quote.class);
criteria.add(
    Restrictions.and(
        Property.forName("qid.id").eq(maxDateQry???),
        Property.forName("qid.date").eq(maxDateQry???)));
List<Quote> quoteList = criteria.list();

Where the two Property.forName's above relate the outer table to the corresponding columns of the subquery. If the inner join provided only one value, I would simply give the DetachedCriteria a single Projection and pass the DetachedCriteria straight into Property.forName(...).eq(..). I'm not sure how to use the DetachedCriteria with two values (id and maxdate) in the Projection.

解决方案

Had exactly the same problem and couldn't find an exact solution for a multi-column subquery match. What I did was rewrite the query so that it only needs to match the subselect on ONE column. So instead of

SELECT * FROM Quote q1
  INNER JOIN (SELECT id, max(date) as maxdate FROM Quote
               GROUP BY id, date) q2
  ON q1.id = q2.id AND q1.date = q2.maxdate

Try

SELECT * FROM Quote q1 where g1.date = 
       (SELECT max(inner.date) FROM Quote inner where inner.id = g1.id
                   GROUP BY inner.id)

The key difference is that all bar the MAX condition of the JOIN criteria is now inside the inner SELECT.

The criteria/detached criteria for this looks like:

DetachedCriteria innerCriteria = DetachedCriteria.forClass(Quote.class, "inner")
    .add(Restrictions.eqProperty("inner.id","q1.id"))
    .setProjection(Projections.projectionList().add(Projections.max("inner.date")));

DetachedCriteria outerCriteria= DetachedCriteria.forClass(ClmClaim.class, "q1");
outerCriteria.add(Subqueries.propertyEq("q1.date", innerCriteria ));

The SQL produced looks like:

select
        this_.<Blah> as blah2_49_0_,
        this_.<Blah> as blah2_50_0_,
        this_.<Blah> as blah2_51_0_,

    from
        Quote this_ 
    where
         this_.date = (
            select
                max(inner_.date) as y0_ 
            from
                Quote inner_ 
            where
                inner_.claim_number=this_.claim_number
        );

You'll note that there is no 'group by' in the SQL because it's not needed. I would expect one to be there if there were more than one match condition in the subselect.

Anyway, hope it helps. It's the last thing I'm going to do before Christmas!

这篇关于使用子查询连接两列的Hibernate条件查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！