Please provide example for an If statement in hibernate criteria

Question

How do you write criteria on if condition using hibernate criteria.

My query that needs to be transformed is

SELECT date, product, IF(type = 'msrp', amount, 0) price, 
     IF(type != 'msrp', amount, 0) tax FROM productdetail group by date, product;

Please provide the right usage of if statement in hibernate criteria.

This is not supported by the criteria API. You'll have to implement your own projection (if possible, I haven't checked the API) if you need that. — JB Nizet, Apr 08 '13 at 19:41

score 5 · Answer 1 · edited May 23 '17 at 12:29

As @JBnizet said,This is not supported by the criteria API. unless you are using NHibernate

if your using NHIbernate

  Projections.Conditional(
         Restrictions.EqProperty(..............

But here are my possible solutions with Hibernate.

1)You can use the same query By using NativeSqlQuery in such type of situations

2)Or by using HQL.There is an expression in HQL called case when.This might be helpful Using a CASE statement in HQL select

score 0 · Answer 2 · answered Apr 15 '13 at 19:12

0

The standard workaround for union queries is a View table, otherwise it is 2 criteria and logic in java to manipulate the results.

answered Apr 15 '13 at 19:12

DAJ

2 Answers2