CODEWITHSUNDEEP
oracle-databasehibernatejpahqlcriteria
Chris Williams
Chris Williams

Reputation: 12481

Is there a way to rank results using Hibernate's Criteria API?

I've got a query that's going to become very complicated and we would like to switch it to the Criteria API in order to keep it manageable. We basically select some data that matches filters but order the result set based on how many filters were matched. We do this by adding a field to the select clause that keeps track of how many filters match.

An example would be helpful:

http://sqlfiddle.com/#!4/90e02/2

Is there any way to do this using the Hibernate Criteria API. Our goal is to make sure this doesn't become code that creates a large HQL String that we have to maintain.

I don't necessarily need an example as an answer (though that would be great!) but a yes/no and something to point me to how it can be done would work.

Upvotes: 4

Views: 2871

Answers (3)

ben75
ben75

Reputation: 28726

Yes it can be done. Here is the API to build an SQL CASE expression.

And here is some pseudo-code to do it:

CriteriaBuilder cb = ...
javax.persistence.criteria.Expression rankExpression = 
        cb.sum(
              cb.selectCase().when(first_name = 'Carter',1).otherwise(0),
              cb.sum(cb.selectCase().when(last_name = 'Mason',1).otherwise(0),
                     cb.sum(...)
                     )
              );

Upvotes: 2

Chris Williams
Chris Williams

Reputation: 12481

I've found another way to make this happen:

http://blog.tremend.ro/2008/06/10/how-to-order-by-a-custom-sql-formulaexpression-when-using-hibernate-criteria-api/

Upvotes: 0

Alex
Alex

Reputation: 11579

You can use @Formula annotation and put there (CASE WHEN ... END) expression for rank field.

Upvotes: 0

Related Questions