Hibernate SQL conversion failed for Enum field type

Question

I use a SQL query and then convert the result using Hibernates Transformers.aliasToBean() . One of the columns in my query is an enumeration. The conversion is somehow not suitable for enumeration. What should I do? What type of data should be used? I want more than 1 character to convert the result to my enum type.

Here's what a simplified version of my query / code looks like ( b - listing in 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

Answer 1

Assuming the java enum type matching 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(); 

Answer 2

See why you get this exception.

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

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

You can use the Aliasing function to solve the problem.

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

For example, allows an alias of your column as "enumStr".

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

Now create the setter method for this alias in your Profile class.

(Assuming the ProfileStateEnum can have either 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, when converting, the installer for the alias setEnumStr(String) will be called instead of setter for the setB(ProfileStateEnum) field, and the string will be converted and saved to the type you need without any exceptions.

I'm new to Hibernate and the solution worked for me. I am using PostgreSQL. But I believe that it works for other databases as well.