Background
- Spring 3.x, JPA 2.0, Hibernate 4.x, Postgresql 9.x.
- Working on a Hibernate mapped class with an enum property that I want to map to a Postgresql enum.
Problem
Querying with a where clause on the enum column throws an exception.
org.hibernate.exception.SQLGrammarException: could not extract ResultSet ... Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: movedirection = bytea Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Code (heavily simplified)
SQL:
create type movedirection as enum ( 'FORWARD', 'LEFT' ); CREATE TABLE move ( id serial NOT NULL PRIMARY KEY, directiontomove movedirection NOT NULL );
Hibernate mapped class:
@Entity @Table(name = "move") public class Move { public enum Direction { FORWARD, LEFT; } @Id @Column(name = "id") @GeneratedValue(generator = "sequenceGenerator", strategy=GenerationType.SEQUENCE) @SequenceGenerator(name = "sequenceGenerator", sequenceName = "move_id_seq") private long id; @Column(name = "directiontomove", nullable = false) @Enumerated(EnumType.STRING) private Direction directionToMove; ... // getters and setters }
Java that calls the query:
public List<Move> getMoves(Direction directionToMove) { return (List<Direction>) sessionFactory.getCurrentSession() .getNamedQuery("getAllMoves") .setParameter("directionToMove", directionToMove) .list(); }
Hibernate xml query:
<query name="getAllMoves"> <![CDATA[ select move from Move move where directiontomove = :directionToMove ]]> </query>
Troubleshooting
- Querying by
id
instead of the enum works as expected. Java without database interaction works fine:
public List<Move> getMoves(Direction directionToMove) { List<Move> moves = new ArrayList<>(); Move move1 = new Move(); move1.setDirection(directionToMove); moves.add(move1); return moves; }
createQuery
instead of having the query in XML, similar to thefindByRating
example in Apache’s JPA and Enums via @Enumerated documentation gave the same exception.- Querying in psql with
select * from move where direction = 'LEFT';
works as expected. - Hardcoding
where direction = 'FORWARD'
in the query in the XML works. .setParameter("direction", direction.name())
does not, same with.setString()
and.setText()
, exception changes to:Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: movedirection = character varying
Attempts at resolution
Custom
UserType
as suggested by this accepted answer https://stackoverflow.com/a/1594020/1090474 along with:@Column(name = "direction", nullable = false) @Enumerated(EnumType.STRING) // tried with and without this line @Type(type = "full.path.to.HibernateMoveDirectionUserType") private Direction directionToMove;
Mapping with Hibernate’s
EnumType
as suggested by a higher rated but not accepted answer https://stackoverflow.com/a/1604286/1090474 from the same question as above, along with:@Type(type = "org.hibernate.type.EnumType", parameters = { @Parameter(name = "enumClass", value = "full.path.to.Move$Direction"), @Parameter(name = "type", value = "12"), @Parameter(name = "useNamed", value = "true") })
With and without the two second parameters, after seeing https://stackoverflow.com/a/13241410/1090474
- Tried annotating the getter and setter like in this answer https://stackoverflow.com/a/20252215/1090474.
- Haven’t tried
EnumType.ORDINAL
because I want to stick withEnumType.STRING
, which is less brittle and more flexible.
Other notes
A JPA 2.1 Type Converter shouldn’t be necessary, but isn’t an option regardless, since I’m on JPA 2.0 for now.
Advertisement
Answer
HQL
Aliasing correctly and using the qualified property name was the first part of the solution.
<query name="getAllMoves"> <![CDATA[ from Move as move where move.directionToMove = :direction ]]> </query>
Hibernate mapping
@Enumerated(EnumType.STRING)
still didn’t work, so a custom UserType
was necessary. The key was to correctly override nullSafeSet
like in this answer https://stackoverflow.com/a/7614642/1090474 and similar implementations from the web.
@Override public void nullSafeSet(PreparedStatement st, Object value, int index, SessionImplementor session) throws HibernateException, SQLException { if (value == null) { st.setNull(index, Types.VARCHAR); } else { st.setObject(index, ((Enum) value).name(), Types.OTHER); } }
Detour
implements ParameterizedType
wasn’t cooperating:
org.hibernate.MappingException: type is not parameterized: full.path.to.PGEnumUserType
so I wasn’t able to annotate the enum property like this:
@Type(type = "full.path.to.PGEnumUserType", parameters = { @Parameter(name = "enumClass", value = "full.path.to.Move$Direction") } )
Instead, I declared the class like so:
public class PGEnumUserType<E extends Enum<E>> implements UserType
with a constructor:
public PGEnumUserType(Class<E> enumClass) { this.enumClass = enumClass; }
which, unfortunately, means any other enum property similarly mapped will need a class like this:
public class HibernateDirectionUserType extends PGEnumUserType<Direction> { public HibernateDirectionUserType() { super(Direction.class); } }
Annotation
Annotate the property and you’re done.
@Column(name = "directiontomove", nullable = false) @Type(type = "full.path.to.HibernateDirectionUserType") private Direction directionToMove;
Other notes
EnhancedUserType
and the three methods it wants implementedpublic String objectToSQLString(Object value) public String toXMLString(Object value) public String objectToSQLString(Object value)
didn’t make any difference I could see, so I stuck with
implements UserType
.- Depending on how you’re using the class, it might not be strictly necessary to make it postgres-specific by overriding
nullSafeGet
in the way the two linked solutions did. - If you’re willing to give up the postgres enum, you can make the column
text
and the original code will work without extra work.