CODEWITHSUNDEEP
sqlhibernateenumshqltransformation
TJ-
TJ-

Reputation: 14373

Hibernate SQL transformation fails for Enum field type

I am using a SQL query and then transforming the result using Hibernates's Transformers.aliasToBean(). One of the columns in my query is an enum. The transformation somehow fails for the enum. What should I do? Which datatype should I use? I want more than 1 character to transform the result into my enum type.

This is how the simplified version of my query/code looks like (b is an enum in the table profiles):

session.createSQLQuery("select a, b from profiles").setResultTransformer(Transformers.aliasToBean(Profile.class))
                    .list();

Exception : expected type: Foo.ProfileStateEnum, actual value: java.lang.Character

Upvotes: 8

Views: 9524

Answers (3)

xmcx
xmcx

Reputation: 346

I found two ways to achieve it.

  1. Use org.hibernate.type.CustomType with org.hibernate.type.EnumType(put either EnumType.NAMED or EnumType.TYPE, see EnumType#interpretParameters). Like below:

    Properties parameters = new Properties();
parameters.put(EnumType.ENUM, MyEnum.class.getName());
// boolean or string type of true/false; declare database type
parameters.put(EnumType.NAMED, true);
// string only; declare database type
parameters.put(EnumType.TYPE, String.valueOf(Types.VARCHAR));
EnumType<MyEnum> enumType = new EnumType<>();
enumType.setTypeConfiguration(new TypeConfiguration());
enumType.setParameterValues(parameters);
CustomType customEnumType = new CustomType(enumType);

  2. Another simple way. Use org.hibernate.type.StandardBasicTypeTemplate with org.hibernate.type.descriptor.sql.*TypeDescriptor. Like below:

    StandardBasicTypeTemplate<MyEnum> enumType =
        new StandardBasicTypeTemplate<>(VarcharTypeDescriptor.INSTANCE,
                new EnumJavaTypeDescriptor<>(MyEnum.class));

Upvotes: 5

AJA
AJA

Reputation: 596

Let's see why you are getting this exception.

From the question it is obvious that you have used @Enumerated(EnumType.STRING) annotation for the field 'b' in you model class. So the field is an enum for your model class and a varchar for your database. Native SQL is not concerned about you model class and returns what ever is there in the database table as it is. So in your case, the SQLQuery you are using will return a String for 'b' instead of a ProfileStateEnum type. But your setter method for 'b' in the Profile class takes a ProfileStateEnum type argument.

Thus you get the exception "expected type: Foo.ProfileStateEnum, actual value: java.lang.Character"

You can use Aliasing to solve the problem.

What I suggest is, alias your column with any name you want and create a setter method for that alias in your model/dto.

For example, lets alias your column as 'enumStr'.

Then your query will look like this : "select a, b as enumStr from profiles"

Now, create a setter method for that alias in the your Profile class.

(Assuming that the enum ProfileStateEnum can have any of the two values STATE1 and STATE2)

public void setEnumStr(String str){
    /*Convert the string to enum and set the field 'b'*/
    if(str.equals(ProfileStateEnum.STATE1.toString())){
        b = ProfileStateEnum.STATE1;
    } else {
        b = ProfileStateEnum.STATE2;
    }
}

Now on transforming, the setter for the alias setEnumStr(String) will be invoked instead of setter for the field setB(ProfileStateEnum) and the string will be converted and saved to the type you want without any exceptions.

I am a beginner in Hibernate and the solution worked for me. I am using PostgreSQL. But I believe it works for other databases too.

Upvotes: 0

gunalmel
gunalmel

Reputation: 411

Assuming that the java enum type that corresponds to column b is Foo.ProfileStateEnum, the following code snippet should work for you. (I tested with Hibernate 4.1.6) 

import java.util.Properties;
import org.hibernate.type.Type;
import org.hibernate.type.IntegerType;
import org.hibernate.internal.TypeLocatorImpl.TypeLocatorImpl;
import org.hibernate.type.TypeResolver.TypeResolver;
import org.hibernate.type.EnumType;

Properties params = new Properties();
params.put("enumClass", "Foo.ProfileStateEnum");
params.put("type", "12"); /*type 12 instructs to use the String representation of enum value*/
/*If you are using Hibernate 5.x then try:
params.put("useNamed", true);*/
Type myEnumType = new TypeLocatorImpl(new TypeResolver()).custom(EnumType.class, params);

List<Profile> profileList= getSession().createSQLQuery("select a as ID, b from profiles")
            .addScalar("ID", IntegerType.INSTANCE)
            .addScalar("b", myEnumType )
            .setResultTransformer(Transformers.aliasToBean(Profile.class))
            .list();

Upvotes: 14

Related Questions