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. :(

1. Sort the t1 table(see below) results as per ORDER BY Sort1 DESC, Sort2 DESC, Sort3 DESC2. Select a single row per Age group3. 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

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.

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.

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.

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.