title: Hibernate mapping between PostgreSQL enum and Java enum
body: <h2>Background</h2>
  
  <ul>
  <li>Spring 3.x, JPA 2.0, Hibernate 4.x, Postgresql 9.x.</li>
  <li>Working on a Hibernate mapped class with an enum property that I want to map to a Postgresql enum.</li>
  </ul>
  
  <h2>Problem</h2>
  
  <p>Querying with a where clause on the enum column throws an exception.</p>
  
  <pre><code>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></pre>
  
  <h2>Code (heavily simplified)</h2>
  
  <p>SQL:</p>
  
  <pre><code>create type movedirection as enum (
      'FORWARD', 'LEFT'
  );
  
  CREATE TABLE move
  (
      id serial NOT NULL PRIMARY KEY,
      directiontomove movedirection NOT NULL
  );
  </code></pre>
  
  <p>Hibernate mapped class:</p>
  
  <pre><code>@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
  }
  </code></pre>
  
  <p>Java that calls the query:</p>
  
  <pre><code>public List&lt;Move&gt; getMoves(Direction directionToMove) {
      return (List&lt;Direction&gt;) sessionFactory.getCurrentSession()
              .getNamedQuery("getAllMoves")
              .setParameter("directionToMove", directionToMove)
              .list();
  }
  </code></pre>
  
  <p>Hibernate xml query:</p>
  
  <pre><code>&lt;query name="getAllMoves"&gt;
      &lt;![CDATA[
          select move from Move move
          where directiontomove = :directionToMove
      ]]&gt;
  &lt;/query&gt;
  </code></pre>
  
  <h2>Troubleshooting</h2>
  
  <ul>
  <li>Querying by <code>id</code> instead of the enum works as expected.</li>
  <li><p>Java without database interaction works fine:</p>
  
  <pre><code>public List&lt;Move&gt; getMoves(Direction directionToMove) {
      List&lt;Move&gt; moves = new ArrayList&lt;&gt;();
      Move move1 = new Move();
      move1.setDirection(directionToMove);
      moves.add(move1);
      return moves;
  }
  </code></pre></li>
  <li><code>createQuery</code> instead of having the query in XML, similar to the <code>findByRating</code> example in <a href="http://tomee.apache.org/examples-trunk/jpa-enumerated/README.html" rel="noreferrer">Apache's JPA and Enums via @Enumerated documentation</a> gave the same exception.</li>
  <li>Querying in psql with <code>select * from move where direction = 'LEFT';</code> works as expected.</li>
  <li><strong>Hardcoding <code>where direction = 'FORWARD'</code> in the query in the XML works.</strong></li>
  <li><p><code>.setParameter("direction", direction.name())</code> does not, same with <code>.setString()</code> and <code>.setText()</code>, exception changes to:</p>
  
  <pre><code>Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: movedirection = character varying
  </code></pre></li>
  </ul>
  
  <h2>Attempts at resolution</h2>
  
  <ul>
  <li><p>Custom <code>UserType</code> as suggested by this accepted answer <a href="https://stackoverflow.com/a/1594020/1090474">https://stackoverflow.com/a/1594020/1090474</a> along with:</p>
  
  <pre><code>@Column(name = "direction", nullable = false)
  @Enumerated(EnumType.STRING) // tried with and without this line
  @Type(type = "full.path.to.HibernateMoveDirectionUserType")
  private Direction directionToMove;
  </code></pre></li>
  <li><p>Mapping with Hibernate's <code>EnumType</code> as suggested by a higher rated but not accepted answer <a href="https://stackoverflow.com/a/1604286/1090474">https://stackoverflow.com/a/1604286/1090474</a> from the same question as above, along with:</p>
  
  <pre><code>@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")
      })
  </code></pre>
  
  <p>With and without the two second parameters, after seeing <a href="https://stackoverflow.com/a/13241410/1090474">https://stackoverflow.com/a/13241410/1090474</a></p></li>
  <li>Tried annotating the getter and setter like in this answer <a href="https://stackoverflow.com/a/20252215/1090474">https://stackoverflow.com/a/20252215/1090474</a>.</li>
  <li>Haven't tried <code>EnumType.ORDINAL</code> because I want to stick with <code>EnumType.STRING</code>, which is less brittle and more flexible.</li>
  </ul>
  
  <h2>Other notes</h2>
  
  <p>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.</p>