5

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)

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Vladimir
  • 61
  • 1
  • 4

2 Answers2

2

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

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

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();
Tahbaza
  • 9,486
  • 2
  • 26
  • 39
1

You can find some information about derived properties and performance considerations on my blog in http://blog.eyallupu.com/2009/07/hibernate-derived-properties.html

Hope it will help,
Eyal Lupu

Community
  • 1
  • 1
Eyal Lupu
  • 822
  • 9
  • 9