CODEWITHSUNDEEP
javapostgresqljpaeclipselinkjpa-2.0
Avner Levy
Avner Levy

Reputation: 6741

Using UUID with EclipseLink and PostgreSQL

I want to use the PostgreSQL uuid type for objects' primary keys. For that I've created a converter (implementing the Converter interface). Bellow is the relevant code: 

    @Override
    public void initialize(DatabaseMapping mapping, Session session) {
        final DatabaseField field;
        if (mapping instanceof DirectCollectionMapping) {
            field = ((DirectCollectionMapping) mapping).getDirectField();
        } else {
            field = mapping.getField();
        }

        field.setSqlType(Types.OTHER);
        field.setTypeName("uuid");
        field.setColumnDefinition("UUID");
    }

Then I've annotated the relevant entity X with the bellow annotations: 

@Converter(name="uuidConverter",converterCalss=UUIDConverter.class)
@Convert("uuidConverter")
@Id
public UUID getId()
{
    return id;
}

The problem is that I have another class (Y) which has the following definition:

@ManyToOne(targetEntity = X.class)
@JoinColumn(name = "x_id")
public X getX();

Although EclipseLink created the tables as expected it sends a string to the database when trying to insert objects of type Y. Postgres returns the following error message:

column "id" is of type uuid but expression is of type character varying at character

Any solutions / work around will be appreciated.

Upvotes: 4

Views: 6460

Answers (6)

Paul LeBeau
Paul LeBeau

Reputation: 101810

It seems there is a bug/incompatibility between EclipseLink and PostgresQL. If you just use UUID for primary keys you should be okay. But if you have a nullable UUID column, and you try to store null in it, you will get the reported error:

column "whatever" is of type uuid but expression is of type character varying

See: https://bugs.eclipse.org/bugs/show_bug.cgi?id=538138 (log in and vote for it if you have the time!)

That bug report proved very useful to me. Specifically the link to the forum thread at:

https://www.eclipse.org/forums/index.php?t=msg&th=1073632&goto=1719530&#msg_1719530

I tried all sorts of solutions from here on SO, and elsewhere on the web. The only one that seemed to work for me was the one posted by David Wheeler there. Specifically, creating a cast from character varying to uuid in the database.

Note that you have to be user postgres to create the cast:

$ sudo su - postgres
$ psql <your database name>

# drop cast if exists (character varying as uuid);
# create or replace function uuid(_text character varying) returns uuid language sql as 'select uuid_in(_text::cstring)';
# create cast (character varying as uuid) with function uuid(character varying) as assignment;

For completeness here is the rest of what I use (in case it helps)

All my entities (that have a UUID primary key) extend a base class called EntityBase:

package com.example.entity;

import java.io.Serializable;
import java.util.UUID;

import javax.persistence.Id;
import javax.persistence.MappedSuperclass;

import org.eclipse.persistence.annotations.Convert;
import org.eclipse.persistence.annotations.Converter;

import com.example.converter.UUIDTypeConverter;

@MappedSuperclass
@Converter(name="uuidConverter", converterClass=UUIDTypeConverter.class)
public class EntityBase implements Serializable, Cloneable
{
   private static final long serialVersionUID = 1L;

   @Id
   @Convert("uuidConverter")
   private UUID  id;


   public EntityBase() {
      this.id = UUID.randomUUID();
   }

   @Override
   public int hashCode() {
      return id.hashCode();
   }

   @Override
   public boolean equals(Object obj) {
      if (this == obj)
         return true;
      if (obj == null)
         return false;
      if (!(obj instanceof EntityBase)) {
         return false;
      }
      EntityBase other = (EntityBase) obj;
      return getId().equals(other.getId());
   }

   public UUID getId()
   {
      return this.id;
   }

   public void setId(UUID id)
   {
      this.id = id;
   }
}

The UUID converter class looks like this:

package com.example.converter;

import java.sql.Types;
import java.util.UUID;

import org.eclipse.persistence.internal.helper.DatabaseField;
import org.eclipse.persistence.mappings.DatabaseMapping;
import org.eclipse.persistence.mappings.converters.Converter;
import org.eclipse.persistence.sessions.Session;


public class UUIDTypeConverter implements Converter
{
   @Override
   public UUID convertObjectValueToDataValue(Object objectValue, Session session)
   {
      return (UUID) objectValue;
   }


   @Override
   public UUID convertDataValueToObjectValue(Object dataValue, Session session)
   {
      return (UUID) dataValue;
   }


   @Override
   public boolean isMutable()
   {
      return true;
   }


   @Override
   public void initialize(DatabaseMapping mapping, Session session)
   {
      DatabaseField field = mapping.getField();
      field.setSqlType(Types.OTHER);
      field.setTypeName("java.util.UUID");
      field.setColumnDefinition("UUID");
   }
}

If you have entities that have UUID columns that are not primary keys, you can annotate them as follows:

import org.eclipse.persistence.annotations.Convert
import org.eclipse.persistence.annotations.Converter;

@Entity
@Converter(name="uuidConverter", converterClass=UUIDTypeConverter.class)
public class BillingEvent extends EntityBase
{

   @Convert("uuidConverter")
   private UUID  entityId;

}

Note that if that entity has other columns that use the standard javax.persistence.convert annotation, you'll need to differentiate the two Convert annotations to avoid a compile error.

For example:

import javax.persistence.Convert;
import org.eclipse.persistence.annotations.Converter;

@Entity
@Converter(name="uuidConverter", converterClass=UUIDTypeConverter.class)
public class BillingEvent extends EntityBase
{

   @org.eclipse.persistence.annotations.Convert("uuidConverter")
   private UUID  entityId;

   @Convert(converter = JSR310InstantTypeConverter.class)
   private Instant     createdOn;
}

I hope this saves others some time. Good luck!

Upvotes: 1

Rajesh Guptan
Rajesh Guptan

Reputation: 277

You don't need a converted. Use this column definition in the entity. You need to register the uuid extension first. This works with Postgres 10 and Wildfly 10.1

@Column(name = "UUID", nullable=false, insertable = false, columnDefinition="uuid DEFAULT uuid_generate_v4()")
private String uuid;

Upvotes: 0

Антон Афанасьев
Антон Афанасьев

Reputation: 1

Universal UUIDConverter for EclipseLink (not only PostgreSQL)

Code:

import java.nio.ByteBuffer;
import java.util.UUID;

import org.eclipse.persistence.internal.helper.DatabaseField;
import org.eclipse.persistence.mappings.DatabaseMapping;
import org.eclipse.persistence.mappings.DirectCollectionMapping;
import org.eclipse.persistence.mappings.converters.Converter;
import org.eclipse.persistence.sessions.Session;

public class UUIDConverter implements Converter {

    private Boolean isUUIDasByteArray = true;

    @Override
    public Object convertObjectValueToDataValue(Object objectValue,
                                                Session session) {
        if (isUUIDasByteArray) {
            UUID uuid = (UUID)objectValue;
            if (uuid == null) return null;
            byte[] buffer = new byte[16];
            ByteBuffer bb = ByteBuffer.wrap(buffer);
            bb.putLong(uuid.getMostSignificantBits());
            bb.putLong(uuid.getLeastSignificantBits());
            return buffer;
        }
        return objectValue;
    }

    @Override
    public UUID convertDataValueToObjectValue(Object dataValue,
                                              Session session) {
        if (isUUIDasByteArray) {
            byte[] bytes = (byte[])dataValue;
            if (bytes == null) return null;
            ByteBuffer bb = ByteBuffer.wrap(bytes);
            long high = bb.getLong();
            long low = bb.getLong();
            return new UUID(high, low);
        }
        return (UUID) dataValue;
    }

    @Override
    public boolean isMutable() {
        return true;
    }

    @Override
    public void initialize(DatabaseMapping mapping, Session session) {
        final DatabaseField field;
        if (mapping instanceof DirectCollectionMapping) {
            // handle @ElementCollection...
            field = ((DirectCollectionMapping) mapping).getDirectField();
        } else {
            field = mapping.getField();
        }

        if (session != null && session.getLogin()!= null && session.getLogin().getPlatform() != null) {
            String platform = session.getLogin().getPlatform().getClass().getSimpleName();

            if (platform.equals("PostgreSQLPlatform")) {
                field.setSqlType(java.sql.Types.OTHER);
                field.setTypeName("java.util.UUID");
                field.setColumnDefinition("UUID");
                isUUIDasByteArray = false;
            } else if (platform.equals("H2Platform")) {
                field.setColumnDefinition("UUID");
            } else if (platform.equals("OraclePlatform")) {
                field.setColumnDefinition("RAW(16)");
            } else if (platform.equals("MySQLPlatform")) {
                field.setColumnDefinition("BINARY(16)");
            } else if (platform.equals("SQLServerPlatform")) {
                field.setColumnDefinition("UNIQUEIDENTIFIER");
            }
        }

    }
}

Upvotes: 0

Davide Ungari
Davide Ungari

Reputation: 1960

I had some issues with EclipseLink JPA 2.1 + Postgresql + UUID as primary key but I find out different solution. I adopted AttributeConverter but I faced a problem with EclipseLink implementation that I resolved with this code:

@javax.persistence.Converter(autoApply = true)
public class PostgresUuidConverter implements AttributeConverter<UUID, Object> {        

    @Override
    public Object convertToDatabaseColumn(UUID uuid) {
        PostgresUuid object = new PostgresUuid();
        object.setType("uuid");
        try {
            if (uuid == null) {
                object.setValue(null);
            } else {
                object.setValue(uuid.toString());
            }
        } catch (SQLException e) {
            throw new IllegalArgumentException("Error when creating Postgres uuid", e);
        }
        return object;
    }

    @Override
    public UUID convertToEntityAttribute(Object dbData) {
        if (dbData instanceof String) {
            return UUID.fromString(dbData.toString());
        } else {    
            return (UUID) dbData;
        }
    }

}


public class PostgresUuid extends PGobject implements Comparable<Object> {

    private static final long serialVersionUID = 1L;

    @Override
    public int compareTo(Object arg0) {
        return 0;
    }

}

As I exaplined in detail in this post http://blog-ungarida.rhcloud.com/persisting-uuid-in-postgresql-using-jpa-eclipselink/

Upvotes: 2

TR4NQUILLITY
TR4NQUILLITY

Reputation: 44

I had the same issue with EclipseLink JPA + Postgresql + UUID as primary key.

To solve it, I've merged codes from Github and below link: https://forums.oracle.com/forums/thread.jspa?messageID=4584157

The below code for UUIDConverter worked for me, though the code surely isn't the best.

public void initialize(DatabaseMapping ARGMapping, Session ARGSession)
{
    final DatabaseField Field;
    if (ARGMapping instanceof DirectCollectionMapping)
    {
        Field = ((DirectCollectionMapping) ARGMapping).getDirectField();
    }
    else
    {
        Field = ARGMapping.getField();
    }
    Field.setSqlType(Types.OTHER);
    Field.setTypeName("uuid");
    Field.setColumnDefinition("UUID");

    for (DatabaseMapping m : ARGMapping.getDescriptor().getMappings())
    {
        assert OneToOneMapping.class.isAssignableFrom(ManyToOneMapping.class);
        if (m instanceof OneToOneMapping)
        {
            for (DatabaseField field : ((OneToOneMapping) m).getForeignKeyFields())
            {
                field.setSqlType(Types.OTHER);
                field.setColumnDefinition("UUID");
                field.setTypeName("uuid");
            }
        }
    }
}

Upvotes: 2

James
James

Reputation: 18379

Try checking what the fieldClassification of the mapping is in the initialize method. It might be getting String.class somehow, try setting it to Object.class.

or, field.setType(Object.class)

Upvotes: 1

Related Questions