JPA 2 (EclipseLink) Trying to use UUID as primary key EntityManager.find() always throws exception (Database is PostgreSQL)

Question

I'm trying to use a UUID for a primary key using JPA 2 (EclipseLink). I'm using PostgreSQL as the database. I have my entity declared as follows: I have an Employee table with its PK set as a UUID. I have a JPA Entity mapping to the employee table, which looks like this:

@Entity
public class Employee {
    @Id
    private String id;
    ...
    ...
}

When I call EntityManager.find(Employee.class, id)

I get an exception from postgres:

Internal Exception: org.postgresql.util.PSQLException: ERROR: operator does not exist: uuid = character varying

Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

I have also tried changing the id in the Employee class to java.util.UUID, but then I get the following (very similar error):

Internal Exception: org.postgresql.util.PSQLException: ERROR: operator does not exist: uuid = bytea Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

I'm really not sure how to go about fixing this... anyone have any ideas?

Thanks!

Answer 1

I'm trying to use a UUID for a primary key using JPA 2 (EclipseLink)

Unfortunately, standard JPA doesn't include UUID as a strategy for generated identifiers.

I have also tried changing the id in the Employee class to java.util.UUID (...)

This is not a valid type for an Id (which has been treated as Serializable in your case). The JPA 2.0 specification states:

2.4 Primary Keys and Entity Identity

...

A simple primary key or a field or property of a composite primary key should be one of the following types: any Java primitive type; any primitive wrapper type; java.lang.String; java.util.Date; java.sql.Date; java.math.BigDecimal; java.math.BigInteger. If the primary key is a composite primary key derived from the primary key of another entity, the primary key may contain an attribute whose type is that of the primary key of the referenced entity as described in Section 2.4.1. Entities whose primary keys use types other than these will not be portable. If generated primary keys are used, only integral types will be portable. If java.util.Date is used as a primary key field or property, the temporal type should be specified as DATE.

In other words, if there is a solution, it will be provider specific.

I'm really not sure how to go about fixing this... anyone have any ideas?

You need to configure EL for custom sequencing and to provide a custom sequence generator. See EclipseLink/Examples/JPA/CustomSequencing for a full example (using a UUID generator).

Answer 2

The error seems to indicate that your are binding the id as a String, but the database does not allow this. If you access that type directly from JDBC what type is returned? You may need to use this type in your class and map it using a Converter.

See, http://wiki.eclipse.org/Using_EclipseLink_JPA_Extensions_%28ELUG%29#Using_EclipseLink_JPA_Converters

EclipseLink also allows setting the JDBC type on the DatabaseField, which may help with binding the type.