Mapping Java enum on Postgres enum with EclipseLink

Question

I am making first attempts with JPA (EclipseLink implementation) and feel quite stuck:

In PostgreSQL I have the following db schema

CREATE TYPE mood AS ENUM ( 'sad', 'happy', 'enthusiastic' );

CREATE TABLE person (
  pk      BIGINT   PRIMARY KEY,
  name    VARCHAR  NOT NULL,
  mood    mood     NOT NULL
);

CREATE SEQUENCE person_pk_seq INCREMENT BY 100 MINVALUE 100;

Which works pretty fine, as this insert shows INSERT INTO PERSON (PK, mood, name) VALUES (3, 'happy', 'Joe') (Committing the pk as String makes no difference.)

On the JPA side I wrote the following class:

package testdb;
import java.io.Serializable;
import javax.persistence.*;
import org.eclipse.persistence.annotations.*;

@Entity
public class Person implements Serializable {
  private static final long serialVersionUID = 1L;

  public enum Mood {
    sad, happy, enthusiastic;
  }

  @Id
  @SequenceGenerator(
    name="PERSON_PK_GENERATOR",
    sequenceName="PERSON_PK_SEQ",
    allocationSize = 100
  )
  @GeneratedValue(
    strategy=GenerationType.SEQUENCE,
    generator="PERSON_PK_GENERATOR"
  )
  public Long pk;

  @Enumerated( EnumType.STRING )
  @Column( name = "mood" )
  @ObjectTypeConverter( name = "moodConverter", objectType = Mood.class,
    dataType = String.class, conversionValues = {
      @ConversionValue( objectValue = "sad", dataValue = "sad" ),
      @ConversionValue( objectValue = "happy", dataValue = "happy" ),
      @ConversionValue( objectValue = "enthusiastic", dataValue = "enthusiastic" )
  })
  @Convert( "moodConverter" )
  public Mood mood;

  @Column( name = "name" )
  public String name;

  public static void main(String[] args) {
    EntityManagerFactory factory = Persistence.createEntityManagerFactory("TestDb.jpa.tests" );
    EntityManager em = factory.createEntityManager();

    em.getTransaction().begin();
    Person p = new Person();
    em.persist( p );
    System.out.println(p.pk);
    p.name = "Joe";
    p.mood = Mood.enthusiastic;
    em.getTransaction().commit();

    Query q = em.createQuery( "select p from Person p" );
    Person x = (Person)q.getResultList().get(0);
    System.out.println( x.pk + " :: " +x.mood );

    em.close();
  }
}

However, this example is not working and I have no clue what the problem is:

[EL Warning]: 2012-06-05 15:28:20.646--UnitOfWork(845463623)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.BatchUpdateException: Batch-Eintrag 0 INSERT INTO PERSON (PK, mood, name) VALUES ('801', 'enthusiastic', 'Joe') wurde abgebrochen.  Rufen Sie 'getNextException' auf, um die Ursache zu erfahren.
Error Code: 0
Call: INSERT INTO PERSON (PK, mood, name) VALUES (?, ?, ?)
    bind => [3 parameters bound]

When I alter the column type of table person to varchar and remove the annotations @Convert and @ObjectTypeConverter everything is working as expected, as well.

Any ideas?

zellus · Accepted Answer · 2012-06-06T17:07:55.027

4

Why do you use a @ObjectTypeConverter, you can map Enumerations out of the box with eclipse link as shown here. @Enumerated is part of JSR-220 whereas @ObjectTypeConverter is a proprietary extension from EclipseLink JPA Extensions.

@Enumerated(EnumType.STRING)
@Column(name = "mood")
private Mood mood;

edited Jun 06 '12 at 17:07

answered Jun 06 '12 at 16:56

zellus

9,617
5
39
56

This is the implementation, I am using currently. But this requires that the `mood` column is of a varchar/text/String type. – Beasterfield Jun 06 '12 at 17:10
There is nothing wrong about representing an enum as String in postgresql. – zellus Jun 06 '12 at 17:15
I was thinking/hoping that using enum-types makes Selecting/Joining on them faster as on char-types. – Beasterfield Jun 06 '12 at 17:19
In the first place I might take advantage of what the object relational mapper offers. Then move on to native queries or use database optimization. – zellus Jun 06 '12 at 17:22

score 1 · Answer 2 · answered Sep 10 '22 at 14:23

I'm 10 years late but...

Adding stringtype=unspecified to the connection string will do the trick.

I use connection strings like:

jdbc:postgresql://localhost:5432/mydatabase?stringtype=unspecified

And then, all fields annotated with @Enumerated(STRING) are automatically converted to the pg enum type.

score 0 · Answer 3 · answered Jun 06 '12 at 13:52

0

Try removing the @Enumerated( EnumType.STRING ) as it might be overriding the converter settings.

answered Jun 06 '12 at 13:52

Chris

20,138
2
29
43

Unfortunately, this doesn't make a difference. – Beasterfield Jun 06 '12 at 17:10

score 0 · Answer 4 · answered Jun 06 '12 at 14:06

0

What is the mood type? This is not a standard JDBC type, so this is the reason for your error.

How does Postgres require this type to be bound through JDBC? It seems odd it does not auto convert varchar values. I did a little looking, and it seems to return this type as PGObject, so you will need to own custom Converter that converts between your Java enum, and a Postgres enum. You will also need to set the jdbcType on the DatabaseField in your converters init method to OTHER.

Please log a bug on EclipseLink to have support added to the Postgres platform for this type.

I think disabling parameter binding would also work.

answered Jun 06 '12 at 14:06

James

17,965
11
91
146

It's a user (me) defined enumeration. Basically the same as a Java `enum`, but on the database side. So my Question actually is, how to map this user defined database type to a user defined Java enum. Basically the same as in this question http://stackoverflow.com/questions/851758/java-enums-jpa-and-postgres-enums-how-do-i-make-them-work-together but using Eclipse/Link instead of Hibernate. I found already the `Converter`-Interface but do not really understand how to implement it. – Beasterfield Jun 06 '12 at 17:16
You really don't need a converter. The examples are a bit outdated. – zellus Jun 06 '12 at 17:19
To use a Converter you need to implement your own converter class. Start with just printing the value that the database returns, then you will need to convert from and to that value. – James Jun 07 '12 at 14:32

Mapping Java enum on Postgres enum with EclipseLink

4 Answers4

Linked