Using mysql "per case" in sleeping criteria

Question

I am using Hibernate 4.3.1 final, Mysql 5.5, and I want to use the "in order" logic for some merged objects.

A pure sql representation of what I want to achieve would look something like this:

select adv.id, adv.published_date 
from advert as adv 
  join account as act on act.id = adv.act_id
  join account_status as aas on aas.id = act.current_aas_id
order by case aas.status
when 'pending' THEN 1
when 'approved' THEN 1
else 2
end, adv.published_date;

It orders advertising for pending and approved accounts before they are deactivated.

I managed to complete the entire selection request using the sleep criteria, but I'm not sure how to specify the order by case statement using api.

I found this post:

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

but I need to reference the merged entity classes in order, and I'm not sure how to do this.

, .

Answer 1

, .

Order String toSqlString (Criteria, CriteriaQuery):

@Override
public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) {
    final String[] columns = criteriaQuery.getColumnsUsingProjection( criteria, getPropertyName() );
    final StringBuilder fragment = new StringBuilder();
    fragment.append(" case ").append(columns[0]);
    fragment.append(" when 'pending' then 1 ");
    fragment.append(" when 'approved' then 1 ");
    fragment.append(" else 2 end");
    return fragment.toString();
}

( ) -

criteriaQuery.getColumnsUsingProjection(criteria, getPropertyName());

, case.

- , , , , propertyName .

Answer 2

fooobar.com/questions/1543180/... , , Order

criteria.addOrder(new CustomOrder());

criteria.addOrder(CustomOrder.desc(fieldName));

desc/asc - , Order CustomOrder