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.
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>
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 the findByRating example in Apache's JPA and Enums via @Enumerated documentation gave the same exception.select * from move where direction = 'LEFT'; works as expected.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
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
EnumType.ORDINAL because I want to stick with EnumType.STRING, which is less brittle and more flexible.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.
If you're using Hibernate 5 or 6, Vlad's answer will be more helpful. But if you're stuck on Hibernate 4, read on:
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>
@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);
}
}
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);
}
}
Annotate the property and you're done.
@Column(name = "directiontomove", nullable = false)
@Type(type = "full.path.to.HibernateDirectionUserType")
private Direction directionToMove;
EnhancedUserType and the three methods it wants implemented
public 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.