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:


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


The result returned from the above sql query is as follows:


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.


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 :)

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.

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!

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.

Thanks vlad, I'll take a look into those.