animesh kumar

Running water never grows stale. Keep flowing!

Postgres Enum with Hibernate

with 8 comments

[tweetmeme source=”anismiles” only_single=false http://www.URL.com%5D

In a running project, we had to map Postgres Enum onto Java Enum using Hibernate. It turned out to be more tricky than we had assumed initially. So, I thought a little tutorial here might save your time if you are stuck in the similar situation.

I am taking an example of Person-Gender mapping where Gender is of enum type.

  1. First, create proper Enums and Tables in Postgres database. Here is the script.
    create type genderType as enum (
    	'MALE', 'FEMALE'
    );
    
    create table person (
    	personid serial NOT NULL,
    	gender genderType not null
    );
    
  2. Then, you need to define you Enum class.
    package com.impetus.ilabs.entity;
    
    public enum GenderType {
        MALE, FEMALE;
    }
    
  3. Now, define your Entity class like this,
    package com.impetus.ilabs.entity;
    
    import java.io.Serializable;
    
    public class Person implements Serializable {
    
    	private Integer personId;
    	
    	private GenderType gender;
    
    	public Integer getPersonId() {
    		return personId;
    	}
    
    	public void setPersonId(Integer personId) {
    		this.personId = personId;
    	}
    
    	public GenderType getGender() {
    		return gender;
    	}
    
    	public void setGender(GenderType gender) {
    		this.gender = gender;
    	}
    
    	@Override
    	public String toString() {
    		StringBuilder builder = new StringBuilder();
    		builder.append("Person [gender=");
    		builder.append(gender);
    		builder.append(", personId=");
    		builder.append(personId);
    		builder.append("]");
    		return builder.toString();
    	}
    }
    
  4. This is the magical part. You need to implement a custom UserType that can understand Enum objects. Here is an implementation of PGEnumUserType.
    package com.impetus.ilabs;
    
    import java.io.Serializable;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Types;
    import java.util.Properties;
    
    import org.hibernate.HibernateException;
    import org.hibernate.usertype.EnhancedUserType;
    import org.hibernate.usertype.ParameterizedType;
    import org.postgresql.util.PGobject;
    
    // This implementation works only with Postgres
    public class PGEnumUserType implements EnhancedUserType, ParameterizedType {
    	// Enum  class under observation
    	private Class<Enum> enumClass;
    
    	public void setParameterValues(Properties parameters) {
    		String enumClassName = parameters.getProperty("enumClassName");
    		try {
    			enumClass = (Class<Enum>) Class.forName(enumClassName);
    		} catch (ClassNotFoundException cnfe) {
    			throw new HibernateException("Enum class not found", cnfe);
    		}
    	}
    
    	public Object assemble(Serializable cached, Object owner)
    			throws HibernateException {
    		return cached;
    	}
    
    	public Object deepCopy(Object value) throws HibernateException {
    		return value;
    	}
    
    	public Serializable disassemble(Object value) throws HibernateException {
    		return (Enum) value;
    	}
    
    	public boolean equals(Object x, Object y) throws HibernateException {
    		return x == y;
    	}
    
    	public int hashCode(Object x) throws HibernateException {
    		return x.hashCode();
    	}
    
    	public boolean isMutable() {
    		return false;
    	}
    
    	public Object nullSafeGet(ResultSet rs, String[] names, Object owner)
    			throws HibernateException, SQLException {
    		Object object = rs.getObject(names[0]);
    		if (rs.wasNull()) {
    			return null;
    		}
    
    		// Notice how Object is mapped to PGobject. This makes this implementation Postgres specific
    		if (object instanceof PGobject) {
    			PGobject pg = (PGobject) object;
    			return Enum.valueOf(enumClass, pg.getValue());
    		}
    		return null;
    	}
    
    	public void nullSafeSet(PreparedStatement st, Object value, int index)
    			throws HibernateException, SQLException {
    		if (value == null) {
    			st.setNull(index, Types.VARCHAR); 
    			// UPDATE: To support NULL insertion, change to: st.setNull(index, 1111);
    		} else {
    			// Notice 1111 which java.sql.Type for Postgres Enum
    			st.setObject(index, ((Enum)value), 1111);
    		}
    	}
    
    	public Object replace(Object original, Object target, Object owner)
    			throws HibernateException {
    		return original;
    	}
    
    	public Class returnedClass() {
    		return enumClass;
    	}
    
    	public int[] sqlTypes() {
    		return new int[] { Types.VARCHAR }; 
    		// UPDATE: To support NULL insertion, change to: return new int[] { 1111 };
    	}
    
    	public Object fromXMLString(String xmlValue) {
    		return Enum.valueOf(enumClass, xmlValue);
    	}
    
    	public String objectToSQLString(Object value) {
    		return '\'' + ((Enum) value).name() + '\'';
    	}
    
    	public String toXMLString(Object value) {
    		return ((Enum) value).name();
    	}
    }
    
  5. Okay, so the tricky part is over. Now, you need to write Hibernate Mapping xml for Person class.
    <?xml version="1.0"?>
    <!DOCTYPE hibernate-mapping PUBLIC
            "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
            "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
    
    <hibernate-mapping>
    	<class name="com.impetus.ilabs.entity.Person" table="person">
    		<id name="personId" type="java.lang.Integer"
    			column="personid">
    			<generator class="native"></generator>
    		</id>
    
    		<property name="gender">
    			<type name="com.impetus.ilabs.PGEnumUserType">
    				<param name="enumClassName">com.impetus.ilabs.entity.GenderType</param>
    			</type>
    		</property>
    	</class>
    </hibernate-mapping>
    
  6. Done! I am including a sample usage class, but you don’t worry about this part, you are good with whatever DAO implementation you might have.
    package com.impetus.ilabs;
    
    import org.hibernate.Session;
    import org.hibernate.SessionFactory;
    import org.hibernate.cfg.Configuration;
    
    import com.impetus.ilabs.entity.GenderType;
    import com.impetus.ilabs.entity.Person;
    
    public class App {
    	
    	private static final SessionFactory sessionFactory = buildSessionFactory();
    
    	private static SessionFactory buildSessionFactory() {
    		try {
    			return new Configuration().configure().buildSessionFactory();
    		} catch (Throwable ex) {
    			throw new ExceptionInInitializerError(ex);
    		}
    	}
    	
    	private static void save (Person person) {
    		Session session = sessionFactory.openSession();
    		session.beginTransaction();
    			
    		// create object
    		session.save(person);
    		session.getTransaction().commit();
    	}
    
    	private static void load (int id) {
    		Session session = sessionFactory.openSession();
    		session.beginTransaction();
    			
    		System.out.println (session.load(Person.class, id));
    		session.getTransaction().commit();
    	}
    
    	public static void main(String[] args) {
    		// create object
    		Person person = new Person();
    		person.setGender(GenderType.MALE);
    		save(person);
    		
    		// load object
    		load(person.getPersonId());
    	}
    }
    

Enjoy! By the way, the whole thing that is described above is also available as maven project to download.

Advertisements

Written by Animesh

August 4, 2010 at 3:48 pm

Posted in Technology

Tagged with , , , ,

8 Responses

Subscribe to comments with RSS.

  1. Hi,

    very good and usefull article.
    Here are two problems I have faced to while implementing:
    1. You must put the enum in a new java file. It’s throwing Exceptions when the enum is defined inline in a existing class.
    2. There is a bug in the code of the PGEnumUserType class. When trying to persist a entity where the enum is NULL, the JDBC driver threw an exception like ‘argument is of type VARCHAR, expected type genderType’.
    Solution: in line 72 in the PGEnumUserType class, change st.setNull(index, Types.VARCHAR); to st.setNull(index, 1111);.
    I’ve also changed line 89 to return new int[] { 1111 };

    Animesh thank you for sharing!
    Sepp

    Sepp

    October 6, 2010 at 2:00 pm

    • Thanks Sepp for pointing things out. In our use-case, we never wanted to store NULL.

      I have updated the above code based upon your suggestion.

      -Animesh

      Animesh

      October 6, 2010 at 6:19 pm

    • Just an other problem I run into:
      I found no way to build a HQL query, which uses the enum as query parameter.
      The problem is, that I don’t know how I have to declare the usertype in the definition of the HQL query in the .hbm file. So Hibernate does not know how to handle the enum usertype.

      My workaround is to use a SQL query instead:

      where category is the usertype.
      That is how I execute the query. Category is of type EnumAmazonProductCategory.

      List result = this.getSession().getNamedQuery(“Amazon.SQL.FindByCategory”)
      .setParameter(“ctry”, ctry)
      .setParameter(“category”, category.toString())
      .setMaxResults(limit)
      .setFirstResult(offset)
      .setResultTransformer(Transformers.aliasToBean(Amazon.class))
      .list();

      Sepp

      October 8, 2010 at 2:26 pm

  2. Hi

    Aug

    July 15, 2011 at 1:02 am

  3. Hi,
    I am using JPA 2 with Hibernate 4 as a provider and can read Postgresql enum types mapped to my Java enum types, but unable to insert records using JPA persist() call. The exception is:

    Caused by: org.postgresql.util.PSQLException: ERROR: column “gender_type” is of type gender_enum but expression is of type character varying
    Hint: You will need to rewrite or cast the expression.

    Any ideas on how to use JPA 2/Hibernate 4.1+ to “insert” enumerated types to Postgresql 9.1 that has a column type as enum?

    Thx
    -Rob

    Rob Mitchell

    December 6, 2012 at 1:30 am

  4. how can i do mapping with Annotations instead of xml? I am using spring beans and annotations for entity mapping

    Ankita

    October 29, 2013 at 6:15 pm

  5. […] extra UserType-Klasse definieren wollte, suchte ich weiter. Die Antwort fand ich dann auf diesem Blog. Dort wird erklärt wie ein parametrisierten UserType erstellt wird. Durch die Mischung beider […]

  6. Thanks a lot for this, it saved us a lot of time.

    @Ankita: we solved it with annotations like this (of course subsitute your own class names:

    @Type(type = “org.drugis.addis.trialverse.repository.PGEnumUserType”,
    parameters = {@Parameter(name = “enumClassName”, value = “org.drugis.addis.trialverse.model.MeasurementType”)})

    Daan Reid

    March 26, 2014 at 5:04 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: