CODEWITHSUNDEEP
postgresqljpaeclipselink
Beasterfield
Beasterfield

Reputation: 7113

Mapping Java enum on Postgres enum with EclipseLink

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?

Upvotes: 4

Views: 4786

Answers (4)

bichoFlyer
bichoFlyer

Reputation: 198

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.

Upvotes: 1

zellus
zellus

Reputation: 9592

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;

Upvotes: 5

James
James

Reputation: 18379

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.

Upvotes: 0

Chris
Chris

Reputation: 21145

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

Upvotes: 0

Related Questions