CODEWITHSUNDEEP
hibernate
Ramesh Kotha
Ramesh Kotha

Reputation: 8322

Building a case statement in Hibernate

I have two fields emailid1Status, emailid2Status. If emailid1status is true then i have to compare the given email with emailId1 column and same with emailid2Status. How to achieve this in Hibernate.

Below i have tried but no luck.

   from User where user.emailId = email OR user.emailid1 = select case when user.emailid1Status is 0 then null   else email OR user.emailid2 =  select case when user.emailid2Status is 0 then null else 
   user.emailid2 = email
   end

UPDATE Getting correct result with below SQL Query. can any body tell me how to convert this to HQL or can it be possible with Criteria API

select * FROM user_profile a WHERE a.emailId = '[email protected]' or a.emailId1 = (CASE WHEN a.emailId1_status THEN '[email protected]' ELSE '' END) or a.emailId2 = (CASE WHEN a.emailId2_status THEN '[email protected]' ELSE '' END)

Upvotes: 0

Views: 293

Answers (2)

Learner
Learner

Reputation: 21393

If you want to use HQL then the query will be:

session.createQuery("FROM User user "
          + "WHERE user.emailId = '[email protected]' "
          + "or user.emailId1 = (CASE user.emailId1Status WHEN TRUE THEN '[email protected]' ELSE '' END) "
          + "or user.emailId2 = (CASE user.emailId2Status WHEN TRUE THEN '[email protected]' ELSE '' END)");

Upvotes: 0

Paris Tao
Paris Tao

Reputation: 365

You don't mention the case of email1Status1 & emailStatus2 are both true. By your above sql, assume NVL2 or UNION is more appropriate.

Upvotes: 1

Related Questions