Nested Query in Hibernate using Criterion

Question

I have a following query where I have to select rows from temporary table created by subquery.

select x, y, x 
from (select x, y, z from some_table where x between x1 and x2) 
where y like 'y1' 
order by z by desc

I have to use Criteria for fetching result from database

I have gone through several examples and documentation for handling subqueries using criteria and detached criteria. I have used Detached query but it is not serving the purpose or I am missing something.

I have used following code

    DetachedCriteria subCriteria =  
                      DetachedCriteria.forClass(SomeClass.class)
                     .add(Restrictions.between("x","x1","x2"))  
                     .setProjection(Projections.projectionList()
                     .add(Projections.property("x"))
                     .add(Projections.property("y"))
                     .add(Projections.property("z"));

   List<Object[]> results = session
                .createCriteria(Program.class)
                .add(Subqueries.exists(subCriteria))
                .add(Restrictions.like("y", "y1"))
                .addOrder(Order.desc("z")).list();

Unless you post what you have tried, its hard to know what's not working for you, and by that I mean your Criteria, and DetachedCriteria statements — JamesENL
– JamesENL, Commented Jul 17, 2014 at 6:24
Does this have to be done with Hibernate? Looks like a perfect use-case for plain SQL to me. Also, why the derived table? In your example, there is not real need for nesting that query... — Lukas Eder
– Lukas Eder, Commented Jul 17, 2014 at 6:28
@Lukas ..I know that it can be used through plain SQL but I have to use Hibernate. — abhijeet
– abhijeet, Commented Jul 17, 2014 at 6:38

Community · Accepted Answer · 2017-05-23 11:57:51Z

4

Neither HQL or JPQL support "Derived Table Expressions". You can use sub-selects or in-selects but that's much it.

You need to use a native query this time and that's actually the right thing to do. HQL/JPQL are mostly useful when you want to fetch Entities not Projections.

edited May 23, 2017 at 11:57

CommunityBot

11 silver badge

answered Jul 17, 2014 at 7:09

Vlad Mihalcea

156k85 gold badges599 silver badges984 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

abhijeet Over a year ago

finally I chose native query.. Thanks

Collectives™ on Stack Overflow

Nested Query in Hibernate using Criterion

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related