Use different ID generator in Jakarta JPA depending on DB product

Question

I migrate an application from Java EE 7/Glassfish 4 to Jakarta EE 9.1/Glassfish 6.1. Our product supports Oracle and MSSQL databases.

For Oracle we use sequences to generate IDs. For MSSQL we use identity columns.

This is the annotation we had for Glassfish 4. Which was working for both Oracle and MSSQL. Glassfish 4 was ignoring sequences for MSSQL and was automatically picking identity columns.

@Entity
@Table(name="tbl_metadata")
public class MetaData {
    
    @Id
    @SequenceGenerator(name = "METADATA_SEQUENCE_GENERATOR", sequenceName = "TBL_METADATA_ID_SEQ", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "METADATA_SEQUENCE_GENERATOR")
    @Column(name="id")
    private long id;

}

This is how an entity is persisted.

public ENTITY saveAndFlush(ENTITY entity) {
    entityManager().persist(entity);
    entityManager().flush();
    return entity;
}

With Glassfish 6.1 I see this error when the programm flushes an entity on MSSQL.

Caused by: jakarta.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services -
3.0.2.v202107160933): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: com.microsoft.sqlserver.jdbc.SQLServerException: Ein expliziter Wert für die Identitätsspalte kann nicht in der tbl_logs-Tabelle eingefügt werden, wenn IDENTITY_INSERT auf OFF festgelegt ist. Error Code: 544 Call: INSERT INTO tbl_logs (id, str_message, dte_time, int_d1, id_line, id_user) VALUES (?, ?, ?, ?, ?, ?)   bind => [6 parameters bound] Query: InsertObjectQuery(de.pharmacontrol.pilot.entities.LogEntry@5950c7bd)    at org.eclipse.persistence.internal.jpa.EntityManagerImpl.flush(EntityManagerImpl.java:980)     at com.sun.enterprise.container.common.impl.EntityManagerWrapper.flush(EntityManagerWrapper.java:412)

Identity insert is OFF and ID should come from DB but JPA constructs a query which tries to insert an ID.

I changed annotations to the below.

@Entity
@Table(name="tbl_metadata")
public class MetaData {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false)
    private long id;
}

It works fine on MSSQL but fails on Oracle. Then following works on Oracle but not MSSQL.

@Entity
@Table(name="tbl_metadata")
public class MetaData {
    
    @Id
    @SequenceGenerator(name = "METADATA_SEQUENCE_GENERATOR", sequenceName = "TBL_METADATA_ID_SEQ", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "METADATA_SEQUENCE_GENERATOR")
    @Column(name="id")
    private long id;
}

How can I annotate my IDs so that they work with identity columns on MSSQL and sequences on Oracle?

Answer 1

Using EclipseLink 3.0.2 I ended up with annotations

@Entity
@Table(name="tbl_metadata")
public class MetaData {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY, generator = "TBL_METADATA_ID_SEQ")
    @Column(name="id")
    private long id;

}

Instead of referencing a @SequenceGenerator in GeneratedValue.generator property, GeneratedValue.generator references the sequence name in Oracle database. This works on Microsoft SQL Server with identity columns and Oracle sequences.