Hibernate Criteria API Equivalent to Oracle's Decode

Question

What would the equivalent of Oracle's DECODE() function be in the Hibernate Criteria API?

An SQL example of what I need to do:

SELECT DECODE(FIRST_NAME, NULL, LAST_NAME, FIRST_NAME) as NAME ORDER BY NAME;

Which returns LAST_NAME to NAME in the event that FIRST_NAME is NULL.

I would prefer to use the Criteria API but could use HQL if there's no other way.

Answer 1

Check out org.hibernate.criterion.Projections.sqlProjection(...).

Similar to this answer.

Answer 2

For the example you give, you could use COALESCE().

How to simulate NVL in HQL

Answer 3

You can use sqlRestriction to call the native decode function.

session.createCriteria(Table.class).add(Restrictions.sqlRestriction("decode({alias}.firstName,null, {alias}.lastName, {alias}.firstName)"))

With HQL, the Oracle dialect already has coalesce and nvl functions, or if you really need decode, you could subclass the dialect and add it as a custom function. I don't know if Hibernate supports a variable length number of arguments like decode does, but worst-case, you could create decode1, decode2, etc to support different numbers of arguments.

Or, if you aren't using the column in a where or group by, you could just bring both attributes back and do the check in Java.

Answer 4

Ended up adding a formula for it:

<property name="name" formula="coalesce(first_name, last_name)"/>

I'm concerned about cross-database problems and possibly efficiency problems with this approach so I'm willing to change the accepted answer.

Answer 5

You can Use Hibernate @Type attribute,Based on your requirement you can customize the annotation and apply on top of the fied. like :

public class PhoneNumberType implements UserType {
@Override
public int[] sqlTypes() {
   return new int[]{Types.INTEGER, Types.INTEGER, Types.INTEGER};
}

 @Override
public Class returnedClass() {
return PhoneNumber.class;
}

 // other methods
 }   

First, the null SafeGet method:

 @Override
 public Object nullSafeGet(ResultSet rs, String[] names, 
 SharedSessionContractImplementor session, Object owner) throws HibernateException,                
 SQLException {
 int countryCode = rs.getInt(names[0]);
 if (rs.wasNull())
  return null;
  int cityCode = rs.getInt(names[1]);
 int number = rs.getInt(names[2]);
 PhoneNumber employeeNumber = new PhoneNumber(countryCode, cityCode, number);
  return employeeNumber;
  }

Next, the null SafeSet method:

 @Override
  public void nullSafeSet(PreparedStatement st, Object value, 
  int index, SharedSessionContractImplementor session) 
    throws HibernateException, SQLException {
     if (Objects.isNull(value)) {
     st.setNull(index, Types.INTEGER);
     } else {
     PhoneNumber employeeNumber = (PhoneNumber) value;
     st.setInt(index,employeeNumber.getCountryCode());
     st.setInt(index+1,employeeNumber.getCityCode());
     st.setInt(index+2,employeeNumber.getNumber());
   }
  }

Finally, we can declare our custom PhoneNumberType in our OfficeEmployee entity class:

 @Entity
 @Table(name = "OfficeEmployee")
  public class OfficeEmployee {

   @Columns(columns = { @Column(name = "country_code"), 
   @Column(name = "city_code"), @Column(name = "number") })
   @Type(type = "com.baeldung.hibernate.customtypes.PhoneNumberType")
    private PhoneNumber employeeNumber;

 // other fields and methods
   }

This might solve your problem, This will work for all database. if you want more info refer :: https://www.baeldung.com/hibernate-custom-types

Answer 6

If you can use HQL the you can replace DECODE with CASE.

You can update your query from,

SELECT DECODE(FIRST_NAME, NULL, LAST_NAME, FIRST_NAME) as NAME ORDER BY NAME;

to,

SELECT CASE WHEN FIRST_NAME = NULL then LAST_NAME ELSE FIRST_NAME END as NAME ORDER BY NAME;