Postgres ENUM, Java ENUM and JPA

We have a postgres DB with postgres enums on one side and Java enums on the other. The question is – how to combine them together over JPA?

The problem is in JDBC driver which returns Postgres enums as an instance of type PGObject. The type property of this has the name of postgres enum, and the value property its value (the ordinal is not stored however, so technically it’s not an enum anymore and possibly completely useless because of this).

So if your Postgres type definition looks like this:

CREATE TYPE direction AS ENUM ('from', 'to');

1	CREATE TYPE direction AS ENUM ('from', 'to');

Then the resultset will contain a PGObject with type “direction” and value “from” for a column having this enum type and a row with the value “from”.

Next thing to do is writing some interceptor code that sits between the spot where JPA reads from the raw resultset and sets the value on your entity. E.g. suppose you had the following in Java:

• Enum:

• And entity:

Java

@Entity @Table(name = "log") @TypeDef(name = "directionConverter", typeClass = DirectionConverter.class) public class Log implements Serializable{ ... private Direction direction; ... @Column(name = "direction") @Type(type="directionConverter") public Direction getDirection() { return direction; } public void setDirection(Direction direction) { this.direction = direction; } ... }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

@Entity @Table(name = "log") @TypeDef(name = "directionConverter", typeClass = DirectionConverter.class) public class Log implements Serializable{ ... private Direction direction;   ...   @Column(name = "direction") @Type(type="directionConverter") public Direction getDirection() { return direction; }   public void setDirection(Direction direction) { this.direction = direction; }   ...   }

Type and TypeDef annotations allows you to map type names to Hibernate UserType. Now we should supply an instance of UserType that does the actual conversion:

Java

public class DirectionConverter implements UserType { private static final int[] SQL_TYPES = new int[]{Types.OTHER}; public int[] sqlTypes() { return SQL_TYPES; } public Object nullSafeGet(ResultSet resultSet, String[] names, Object owner) throws HibernateException, SQLException { Object pgObject = resultSet.getObject(names[0]); // names[0] is the column containing the enum try { Method valueMethod = pgObject.getClass().getMethod("getValue"); String value = (String) valueMethod.invoke(pgObject); return Direction.valueOf(value); } catch (Exception e) { e.printStackTrace(); } return null; } public void nullSafeSet(PreparedStatement preparedStatement, Object value, int i) throws HibernateException, SQLException { preparedStatement.setObject(i, value); } // Rest of methods omitted }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

public class DirectionConverter implements UserType { private static final int[] SQL_TYPES = new int[]{Types.OTHER};   public int[] sqlTypes() { return SQL_TYPES; }   public Object nullSafeGet(ResultSet resultSet, String[] names, Object owner) throws HibernateException, SQLException { Object pgObject = resultSet.getObject(names[0]); // names[0] is the column containing the enum   try { Method valueMethod = pgObject.getClass().getMethod("getValue"); String value = (String) valueMethod.invoke(pgObject); return Direction.valueOf(value); } catch (Exception e) { e.printStackTrace(); }   return null; }   public void nullSafeSet(PreparedStatement preparedStatement, Object value, int i) throws HibernateException, SQLException { preparedStatement.setObject(i, value); }   // Rest of methods omitted   }

The nullSafeGet method is used to retrieve the mapped class from the result set and nullSafeSet method is used to store the mapped class in the database.