Hibernate标准查询使用Max（）投影关键字段和group by foreign主键 [英] Hibernate criteria query using Max() projection on key field and group by foreign primary key
问题描述
在Hibernate（版本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）;
到目前为止，这是我想出来的，不起作用， code>无法解析属性：datetime：common.entity.FtpStatus 错误信息：
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）;
列表< FtpStatus> dtlList = crit.list（）;
以下是Netbeans 6.8直接从数据库生成的相关参考配置： b
FtpStatus.hbm.xml -
< hibernate-mapping>
< class name =common.entity.FtpStatustable =ftp_statuscatalog =common>
< composite-id name =idclass =common.entity.FtpStatusId>
< key-property name =siteidtype =int>
< column name =siteid/>
< / key-property>
< key-property name =connectionidtype =int>
< column name =connectionid/>
< / key-property>
< key-property name =datetimetype =timestamp>
< column name =datetimelength =19/>
< / key-property>
< / composite-id>
< /多对一>
< /多对一>
< property name =upIndtype =boolean>
< column name =up_indnot-null =true/>
< / property>
< property name =lastMessagetype =string>
< column name =last_messagelength =65535not-null =true/>
< / property>
< / class>
< / hibernate-mapping>
Connections.hbm.xml -
<休眠映射>
< column name =connectionid/>
< generator class =identity/>
< / id>
< property name =iptype =string>
< column name =iplength =15not-null =true/>
< / property>
< property name =porttype =int>
< column name =portnot-null =true/>
< / property>
< property name =usertype =string>
< column name =userlength =8not-null =true/>
< / property>
< property name =passwordtype =string>
< column name =passwordlength =50not-null =true/>
< / property>
< set name =ftpStatusesinverse =true>
< key>
< column name =connectionidnot-null =true/>
< / key>
<一对多等级=common.entity.FtpStatus/>
< / set>
< / class>
< / hibernate-mapping>
我知道我错过了一些东西，但是我在搜索引擎上使用hibernate还没有透露。另外，直接使用 s.createSQLQuery（）或 s.createQuery（）的SQL查询也是可以接受的，我已经写了一个更少的成功.....
Criteria DetachedCriteria ：
DetachedCriteria maxDateQuery = DetachedCriteria.forClass（FtpStatus。类）;
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））;
列表< FtpStatus> dtlList = crit.list（）;
请注意，对多列子查询的支持在。
由于Hibernate 4.0.0.CR5（HHH-6766）对于原生SQL查询，Hibernate的 createSQLString 应该直接用你指定的查询字符串，或者添加查询中涉及的实体，如果想要有明确的列名字。
$ $ $ $ $ $ $ $ $ $ $ $ $ $ $'
+WHERE（datetime，connectionid）IN（
+SELECT MAX（datetime），connectionid
+FROM ftp_status
+GROUP BY connectionid
+）;
SQLQuery query = s.createSQLQuery（queryString）.addEntity（status，FtpStatus.class）;
列表< FtpStatus> dtlList = query.list（）;
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);
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();
Here's the relevant reference configuration that Netbeans 6.8 generated directly from the database:
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 -
<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>
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.....
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();
Note that support for multicolumn subqueries is not implemented until in Hibernate 4.0.0.CR5 (HHH-6766).
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();
这篇关于Hibernate标准查询使用Max（）投影关键字段和group by foreign主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
