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, Jul 17 '14 at 06: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, Jul 17 '14 at 06:28
@Lukas ..I know that it can be used through plain SQL but I have to use Hibernate. — abhijeet, Jul 17 '14 at 06:38

score 4 · Accepted Answer · edited May 23 '17 at 11:57

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 '17 at 11:57

Community

1
1

answered Jul 17 '14 at 07:09

Vlad Mihalcea

142,745
71
566
911

finally I chose native query.. Thanks – abhijeet Jul 17 '14 at 11:35

Nested Query in Hibernate using Criterion

1 Answers1

Linked