-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 5 posts ] 
Author Message
 Post subject: Nested subquery using Hibernate Criteria
PostPosted: Wed May 17, 2017 10:42 pm 
Newbie

Joined: Wed May 17, 2017 10:32 pm
Posts: 3
Hi everyone!
I have a raw sql query which I need to write in hibernate criteria. The entire codebase I'm working on is all in hibernate criteria, so I'm trying to explore all possibilities with hibernate criteria before moving on to something else eg. using hql or raw query.

If it's not possible to convert the below raw sql query into hibernate, please say so too! It will be much help since my knowledge in hibernate criteria is quite limited. :(

The raw sql query I have is:
Code:
SELECT
        *
    FROM
    (
        SELECT
            *
        FROM
            Table t1
        ORDER BY Sort1 DESC, Sort2 DESC, Sort3 DESC
    )
GROUP BY Age
ORDER BY Sort1 DESC, Sort2 DESC, Sort3 DESC


What it does is,

    1. Sort the t1 table(see below) results as per ORDER BY Sort1 DESC, Sort2 DESC, Sort3 DESC
    2. Select a single row per Age group
    3. Sort the grouped results as per ORDER BY Sort1 DESC, Sort2 DESC, Sort3 DESC

Note) The reason why I need Step 1) before Step 2) is because GROUP BY, be fault, doesn't guarantee ordering and I need to have strict ordering applied to pick the single row per Age group

I have a table as below: Table t1
Code:
| ID |  Lastname  |  Firstname |  Age |  Created_at | Sort1 | Sort2 | Sort3 
---------------------------------------------------------------------------
| 1  |    Jolie   |   Angey    |  40  |    2011     |   3   |   2   |   1
| 2  |    Pitt    |   Brad     |  41  |    2012     |   2   |   1   |   2
| 3  |    Smith   |   Casey    |  40  |    2010     |   3   |   1   |   2
| 4  |    Smith   |   Carey    |  40  |    2009     |   3   |   2   |   3
---------------------------------------------------------------------------


The result returned from the above sql query is as follows:
Code:
| ID |  Lastname  |  Firstname |  Age |  Created_at | Sort1 | Sort2 | Sort3
----------------------------------------------------------------------------
| 4  |    Smith   |   Carey    |  40  |    2009     |   3   |   2   |   3   
| 2  |    Pitt    |   Brad     |  41  |    2012     |   2   |   1   |   2     


Based on what I've found so far, it seems like DetachedCriteria is the way forward, and saw some solutions that use results from DetachedCriteria feeding to WHERE clause, but not directly after FROM clause.

So I made the subquery using DetachedCriteria, but not sure where to go about completing Criteria.
Code:
DetachedCriteria subQuery = DetachedCriteria.forClass(Some.class, "a")
        .addOrder(Order.desc("a.Sort1"))
        .addOrder(Order.desc("a.Sort2"))
        .addOrder(Order.desc("a.Sort3"));

// stuck from below... :(
Criteria criteria = hibernateSession.createCriteria(??)
      .add(Projections.groupProperty("Age"))


If you need any clarification with the explanation above, feel free to let me know!
I would much appreciate your help. Thank you in advance :)


Top
 Profile  
 
 Post subject: Re: Nested subquery using hibernate criteria
PostPosted: Thu May 18, 2017 1:32 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
You can't use Hibernate Criteria because it does not support derived tables. Using Hibernate Criteria as the default query mechanism is a code smell. Criteria is only needed when you build entity queries dynamically.

For entity queries that don't change their predicates, you can use JPQL which is much more expressive. However, you don't always have to select entities. It's much more efficient to use native queries, like in this case. Otherwise, why do you think JPA and Hibernate support native queries after all?

Also, the Hibernate Criteria has been deprecated for a long time and will probably be removed in a future version. You need to switch to JPA Criteria in the long run.


Top
 Profile  
 
 Post subject: Re: Nested subquery using hibernate criteria
PostPosted: Thu May 18, 2017 1:50 am 
Newbie

Joined: Wed May 17, 2017 10:32 pm
Posts: 3
Thanks for your response, vlad!

Didn't know that Hibernate Criteria has been deprecated for a while,,, good to know from my side.
Unfortunately, the legacy code here uses it very extensively.

I just want to add that the above query should be dynamic, but for the sake of simplicity, I have posted a simple fixed case scenario. In the code, what to GROUP BY changes per request(via API), so it could be some other column, as well as attributes that come after ORDER BY.

Regardless, like you said, if implementing the above raw sql in Hibernate Criteria is not possible, I will have to resort to using HQL for the timebeing.

And I will look into JPA Criteria, thanks for the heads up!


Top
 Profile  
 
 Post subject: Re: Nested subquery using Hibernate Criteria
PostPosted: Thu May 18, 2017 2:36 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
If you need to vary the predicates of such query dynamically, then the worst thing to do is to rely on String concatenation for building a JQPL/HQL query. As I explained in this article, JPQL or HQL is also vulnerable to SQL Injection, so don't do that!

What you need to do is to use a dynamic SQL query builder, like jOOQ or QueryDSL.


Top
 Profile  
 
 Post subject: Re: Nested subquery using Hibernate Criteria
PostPosted: Thu May 18, 2017 4:07 am 
Newbie

Joined: Wed May 17, 2017 10:32 pm
Posts: 3
Thanks vlad, I'll take a look into those.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 5 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.