HSQL via criteria

HSQL w postaci :

select
count(d.id) as all ,
SUM( CASE WHEN (papaType=1) THEN 1 ELSE 0 END ) as sms,
SUM( CASE WHEN (papaType=2 and papaMerchantNumber='19999') THEN 1 ELSE 0 END ) as dotpay,
SUM( CASE WHEN (papaType=2 and papaMerchantNumber='RXX5XXXG7R6XX') THEN 1 ELSE 0 END ) as paypal,
papaRequestDate as date from TPayment as d
where (d.papaStatus=100 or d.papaStatus=2 or d.papaStatus=41 or d.papaStatus=20 )
and papaType!=4
group by d.papaRequestDate
order by d.papaRequestDate

Można zapisać jako

Criteria crit= getSession().createCriteria(TPayment.class);
crit.setProjection(Projections.projectionList()
.add(Projections.count("id"))
.add(Projections.sqlProjection("SUM( CASE WHEN (papa_type=1) THEN 1 ELSE 0 END ) as sms",new String[] {"sms"},new Type[] {Hibernate.INTEGER}))
.add(Projections.sqlProjection("SUM( CASE WHEN (papa_type=2 and papa_merchant_number='19999') THEN 1 ELSE 0 END ) as dotpay",new String[] {"dotpay"},new Type[] {Hibernate.INTEGER}))
.add(Projections.sqlProjection("SUM( CASE WHEN (papa_type=2 and papa_merchant_number='RXX5XXXG7R6XX') THEN 1 ELSE 0 END ) as paypal",new String[] {"paypal"},new Type[] {Hibernate.INTEGER}))
.add(Projections.groupProperty("papaRequestDate"))
);
crit.add(Restrictions.ne("papaType", 4));

Disjunction or = Restrictions.disjunction();
or.add(Restrictions.eq("papaStatus", 100));
or.add(Restrictions.eq("papaStatus", 41));
or.add(Restrictions.eq("papaStatus", 20));
or.add(Restrictions.eq("papaStatus", 2));

crit.add(or);

crit.addOrder(Order.asc("papaRequestDate"));

return crit.list();

Polecam tą drugą wersje bo operacje na ciągu znaków opisujących twoje zapytanie może się źle potem odbijać. Pozdrawiam