休眠条件查询在键字段上使用 Max() 投影并按外主键分组 [英] Hibernate criteria query using Max() projection on key field and group by foreign primary key

问题描述

在 Hibernate(版本 3.2.5)中，我很难将此查询(直接在数据库上运行)表示为条件查询:

I'm having difficulty representing this query (which works on the database directly) as a criteria query in Hibernate (version 3.2.5):

SELECT s.* 
  FROM ftp_status s 
 WHERE (s.datetime,s.connectionid) IN (SELECT MAX(f.datetime),
                                              f.connectionid 
                                         FROM ftp_status f
                                        GROUP BY f.connectionid);

到目前为止，这是我想出的不起作用，并抛出 could not resolve property: datetime of: common.entity.FtpStatus 错误消息:

so far this is what I've come up with that doesn't work, and throws a could not resolve property: datetime of: common.entity.FtpStatus error message:

Criteria crit = s.createCriteria(FtpStatus.class);
crit = crit.createAlias("connections", "c");
crit = crit.createAlias("id", "f");
ProjectionList proj = Projections.projectionList();
proj = proj.add(Projections.max("f.datetime"));
proj = proj.add(Projections.groupProperty("c.connectionid"));
crit = crit.setProjection(proj);
List<FtpStatus> dtlList = crit.list();

以下是 Netbeans 6.8 直接从数据库生成的相关参考配置:

Here's the relevant reference configuration that Netbeans 6.8 generated directly from the database:

FtpStatus.hbm.xml -

FtpStatus.hbm.xml -

<hibernate-mapping>
   <class name="common.entity.FtpStatus" table="ftp_status" catalog="common">
        <composite-id name="id" class="common.entity.FtpStatusId">
            <key-property name="siteid" type="int">
                <column name="siteid" />
            </key-property>
            <key-property name="connectionid" type="int">
                <column name="connectionid" />
            </key-property>
            <key-property name="datetime" type="timestamp">
                <column name="datetime" length="19" />
            </key-property>
        </composite-id>
        <many-to-one name="connections" class="common.entity.Connections" update="false" insert="false" fetch="select">
            <column name="connectionid" not-null="true" />
        </many-to-one>
        <many-to-one name="sites" class="common.entity.Sites" update="false" insert="false" fetch="select">
            <column name="siteid" not-null="true" />
        </many-to-one>
        <property name="upInd" type="boolean">
            <column name="up_ind" not-null="true" />
        </property>
        <property name="lastMessage" type="string">
            <column name="last_message" length="65535" not-null="true" />
        </property>
    </class>
</hibernate-mapping>

Connections.hbm.xml -

Connections.hbm.xml -

<hibernate-mapping>
    <class name="common.entity.Connections" table="connections" catalog="common">
        <id name="connectionid" type="java.lang.Integer">
            <column name="connectionid" />
            <generator class="identity" />
        </id>
        <property name="ip" type="string">
            <column name="ip" length="15" not-null="true" />
        </property>
        <property name="port" type="int">
            <column name="port" not-null="true" />
        </property>
        <property name="user" type="string">
            <column name="user" length="8" not-null="true" />
        </property>
        <property name="password" type="string">
            <column name="password" length="50" not-null="true" />
        </property>
        <set name="ftpStatuses" inverse="true">
            <key>
                <column name="connectionid" not-null="true" />
            </key>
            <one-to-many class="common.entity.FtpStatus" />
        </set>
    </class>
</hibernate-mapping>

我知道我错过了一些东西，但是我在 hibernate 上的谷歌搜索还没有发现它.或者，直接使用 s.createSQLQuery() 或 s.createQuery() 的 SQL 查询也是可以接受的，但我写那个的成功率更低.......

I know I'm missing something, but my googling on hibernate hasn't revealed it yet. Alternatively a SQL query directly using s.createSQLQuery() or s.createQuery() is also acceptable, but I've had even less success writing that one.....

推荐答案

您提供的 Criteria 似乎只生成内部查询部分.您可以结合内部查询，例如通过使用 DetachedCriteria:

The Criteria you supplied seems to generate only the inner query part. You can combine the inner query e.g. by using DetachedCriteria:

DetachedCriteria maxDateQuery = DetachedCriteria.forClass(FtpStatus.class);
ProjectionList proj = Projections.projectionList();
proj.add(Projections.max("datetime"));
proj.add(Projections.groupProperty("connectionid"));
maxDateQuery.setProjection(proj);

Criteria crit = s.createCriteria(FtpStatus.class);
crit.add(Subqueries.propertiesEq(new String[] {"datetime", "connectionid"}, maxDateQuery));

List<FtpStatus> dtlList = crit.list();

请注意，对多列子查询的支持直到 Hibernate 4.0.0.CR5 (HHH-6766).

Note that support for multicolumn subqueries is not implemented until in Hibernate 4.0.0.CR5 (HHH-6766).

就原生 SQL 查询而言，Hibernate 的 createSQLString 应该立即与您指定的查询字符串一起工作，或者与添加的查询中涉及的实体一起工作，如果希望在其中有明确的列名结果查询.

As what comes to native SQL queries, Hibernate's createSQLString should work straight away with the query string you specified, or with the entities involved in the query added, if one want's to have explicit column names in the resulting query.

String queryString = "SELECT {status.*}"
                   + "  FROM ftp_status status"
                   + "  WHERE (datetime, connectionid) IN ("
                   + "    SELECT MAX(datetime), connectionid"
                   + "      FROM ftp_status"
                   + "      GROUP BY connectionid"
                   + "  )";

SQLQuery query = s.createSQLQuery(queryString).addEntity("status", FtpStatus.class);

List<FtpStatus> dtlList = query.list();

这篇关于休眠条件查询在键字段上使用 Max() 投影并按外主键分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！