在Hibernate HQL中使用派生表的子查询 [英] Subquery using derived table in Hibernate HQL

问题描述

我有一个Hibernate HQL问题. 我想将子查询编写为派生表(出于性能原因). 是否可以在HQL中做到这一点? 示例:

I have a Hibernate HQL question. I'd like to write a subquery as a derived table (for performance reasons). Is it possible to do that in HQL? Example:

FROM Customer WHERE country.id in 
(SELECT id FROM (SELECT id FROM Country where type='GREEN') derivedTable)

(顺便说一句，这只是一个示例查询，因此不建议重写它，只是我感兴趣的派生表概念)

(btw, this is just a sample query so don't give advice on rewriting it, is just the derived table concept I'm interested in)

推荐答案

不幸的是，派生表目前在HQL中不起作用. 例如，以下作品:

Unfortunately no, derived tables don't currently work in HQL. For example, the following works:

List<int> result =
  nHSession.CreateQuery( @"select distinct Id from User u")
  .List<int>().ToList();

...以下引发此异常: 引发了类型为"Antlr.Runtime.NoViableAltException"的异常. 1号线附近 第24列[从中选择不同的ID(从S2.BP.Model.User u中选择u)]

...the following throws the this exception: Exception of type 'Antlr.Runtime.NoViableAltException' was thrown. near line 1, column 24 [select distinct Id from (select u from S2.BP.Model.User u)]

List<int> result = nHSession.CreateQuery(
    @"select distinct Id from (select u from User u)")
    .List<int>().ToList();

后退是创建一个包含原始sql的命名查询，或者创建一个存储过程并通过命名查询调用它，如下所示:

The fall back would be to create a named query containing raw sql or to create a stored procedure and invoke it via named query, like so:

List<int> result = nHSession.GetNamedQuery("spUserIds")
    .SetInt32("id", 3)
    .List<int>().ToList();

这篇关于在Hibernate HQL中使用派生表的子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！