I have a database running on postgres
14 RDBMS. This (relatively) late version has been selected specifically because it features UUID
DEFAULT
generation function.
The application that uses the DB is JAX RS
java
application running within Payara
application container using JPA
EclipseLink
as its DB layer (ORM). I have configured the application to work with UUIDs as PKs almost without a problem so far as I was only reading the entities (that have UUID
primary keys to be exact).
Recently I am implementing a POST
endpoint, that is to INSERT
a record. Now there is a problem with PK
id
generation.
The table is defined like this:
CREATE TABLE field (
id UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
...
);
Until now I had the entity defined like this:
@Entity
@Converter(name="uuidConverter", converterClass=UUIDEclipseConverter.class)
@Table(name="field")
public class Field {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Convert("uuidConverter")
@Column(nullable = false)
private UUID id;
...
And it works OK for reading the entites, relating to other entities even the UUIDs work as a part of composite keys of other tables.
But once I tried to insert an entity like this, the query fails. Three different failures for three different generation strategies:
GenerationType.IDENTITY
:
Crashes spectacularly with
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.9.payara-p1): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: lastval is not yet defined in this session
Error Code: 0
Call: select lastval()
Query: ValueReadQuery(name="SEQ_GEN_IDENTITY" sql="select lastval()")
...
- There is no sequence, so the origin of the error is understandable.
GenerationType.TABLE
:
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.9.payara-p1): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: relation "planner.sequence" does not exist
Position: 8
Error Code: 0
Call: UPDATE planner.SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?
bind => [2 parameters bound]
Query: DataModifyQuery(name="SEQ_GEN_TABLE" sql="UPDATE planner.SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?")
...
- Again, expecting a sequence, thus the failure.
GenerationType.SEQUENCE
: Without much hope I tried the last option.
Internal Exception: org.postgresql.util.PSQLException: ERROR: relation "planner.seq_gen_sequence" does not exist
Position: 16
Error Code: 0
Call: select nextval('planner.SEQ_GEN_SEQUENCE')
...
- Understandably a failure occurred.
So I wonder how to achive to utilize the DEFAULT
generation for a PK
ID
the RDBMS provides.
Another approaches I tried so far:
- I had an idea just to remove the
GeneratedValue
annotation. I must admit it sounded quite promising, but failed, because the Eclipselink doesn't know it should utilize theDEFAULT
generation the RDBMS provides. And so it tries toINSERT
NULL
value into aNOT NULL
column. - Then I tried to set both
insertable
andupdatable
column properties tofalse
which also fails, because JPA needs a writable PK identifier. - So I removed
updatable
column property and theINSERT
worked. But the JPA won't hydrate the autogenerated ID into the entity, so I cannot deterministically recognize which record had just been created, so not an ideal solution as well (I also need to return the ID to my API's consumer for it to be able to address the same record later)
For now I am using a workaround based on stripping the GeneratedValue
annotation and generating random UUID
at the application side via PrePersist
listener, which I don't like, but can live with should there be no better solution. Ie:
@Entity
@Converter(name="uuidConverter", converterClass=UUIDEclipseConverter.class)
@Table(name="field")
public class Field {
@Id
@Convert("uuidConverter")
@Column(nullable = false, columnDefinition="uuid")
private UUID id;
...
@PrePersist
private void prePersist() {
if (id != null)
return;
// TODO: I do not like this solution at all
id = UUID.randomUUID();
}
}
My question therefore is:
Is there a way to configure EclipseLink JPA (via entity annotation?) to utilize a DEFAULT
value provided for a PK
column by RDBMS (Posgres 14's gen_random_uuid()
function specifically) without the need to generate it manually on the application side?
There surely has to be an elegant solution, has it not?
Versions:
- Java openjdk version "1.8.0_312"
- Payara (full) 5.2021.10
- EclipseLink 2.7
- PostgreSQL 14.6
(no hibernate, no spring)
The whole stack is running in a docker environment which DNM IMO.