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

Question

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.

Answer 1

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(...)
                     )
              );