Getting error while executing criteria query on enum column in mysql table hibernate

Question

This is the code :

Session session = sessionFactory.getCurrentSession();
Criteria criteria = session.createCriteria(Contact.class)
    .add(Restrictions.like("gender", "male", MatchMode.ANYWHERE)));
List<Object[]> contactList = criteria.list();

Where gender is enum type column in Contact.java

@Entity
@Table(name = "contact_master")
public class Contact {

      public enum Gender {
           MALE,FEMALE
      }

      @Column(name = "gender")
      @Enumerated(EnumType.STRING)
      private Gender gender;

      public Gender getGender() {
         return gender;
      }

      public void setGender(Gender gender) {
          this.gender = gender;
      }

}

When i run above code i am getting exception like :

java.lang.ClassCastException: java.lang.String cannot be cast to java.lang.Enum

Loferski

Answer 1

You can define another non-insertable and non-updatable field of String type mapped to the same column:

@Entity
@Table(name = "contact_master")
public class Contact {

      public enum Gender {
           MALE,FEMALE
      }

      @Column(name = "gender")
      @Enumerated(EnumType.STRING)
      private Gender gender;

      @Column(name = "gender", insertable = false, updatable = false)
      private String genderAsText;

      public Gender getGender() {
         return gender;
      }

      public void setGender(Gender gender) {
          this.gender = gender;
      }
}

You don't need getters and setters for it and you can use it only in queries in which you need to treat gender as plain text:

Criteria criteria = session.createCriteria(Contact.class)
    .add(Restrictions.ilike("genderAsText", "male", MatchMode.ANYWHERE)));

Note that I also use Restrictions.ilike method as you probably want the comparison to be case insensitive.

Spheroidal

I think it's a likeable hack, but why won't hibernate handle it normally?? – Peccary

@Peccary It would be a bit logically difficult to make a generic behavior in this regard when it comes to enums, because enums can be also stored as numbers by using EnumType.ORDINAL, so I assume that the authors just picked a consistent behavior for all cases. – Spheroidal

If i'm using liquibase, do I need to include this in changelog since it's mapping the new field to the same column? – Illampu

Answer 2

define your enum outside in a saperate file so that you can access it from anywhere and then use it like this:

Criteria criteria = session.createCriteria(Contact.class)
   .add(Restrictions.like("gender", Gender.MALE, MatchMode.ANYWHERE)));

---

EDIT 1

As you specified, if the values are dynamic, then you can define a function like this in your enum.

public enum Gender {
    MALE,FEMALE;

    public static Gender getGender(String strGender) {
        if(strGender.equalsIgnoreCase("m") | strGender.equalsIgnoreCase("ma") | strGender.equalsIgnoreCase("male")) 
            return Gender.MALE;

        if(strGender.equalsIgnoreCase("f") | strGender.equalsIgnoreCase("fema") | strGender.equalsIgnoreCase("female")) 
            return Gender.FEMALE;

        return null;
    }
}

and then you can use them like:

Criteria criteria = session.createCriteria(Contact.class)
   .add(Restrictions.like("gender", Gender.getGender("fema"), MatchMode.ANYWHERE)));

Sped

There is dynamic value for search parameter. like ma, mal, fe, fema – Loferski

@Loferski can you please accept an answer from this page which worked for you? Thanks! – Sped

Answer 3

Try this instead (assuming the intention is to only return male contacts):

Criteria criteria = session.createCriteria(Contact.class)
    .add(Restrictions.eq("gender", Contact.Gender.MALE));

Dialogize

Answer 4

If you think about what you are trying to do, a partial match on an enum column, it doesn't really make sense to hibernate.

You've told hibernate that gender is an enum, and only has two allowable values - MALE or FEMALE. So these are the only possible values hibernate expects/allows. It looks like you can only query on the enum using the Restrictions class on an exact match of an Enum type.

But you can do this using a query as below without changing your entity model. So if you pass in "f" or "fem" it will give you only female, but most other combos will return all as all letters of male also in female - you can obviously change the way it generates the like parameter so you could match on m%, ma%, mal% to find MALE, or f%, fe% etc to find FEMALE.

@GetMapping(value = "/list/{queryParam}")
public ResponseEntity<List<Contact>> list(@PathVariable String queryParam ) {
    Session session = entityManager.unwrap(Session.class);
    SQLQuery query = session.createSQLQuery("select id, gender from contact_master where gender like :param");
    query.setParameter("param", "%"+queryParam.toUpperCase()+"%");
    final List<Contact> list = query.list();

    return ResponseEntity.ok(list);
}

Hope that helps

Lawmaker

@Peccary not sure what problem you having as I had it working with the supplied code with an attached database and firing rest queries via postman. – Lawmaker