Why does JPA @TableGenerator seem to be generating the wrong SQL?-CodePudding

Trying to use @TableGenerator for my primary key, usually I use Auto but have discovered this may not be a great idea (with: Jakarta EE 10 Payara 6, PostgreSQL 13). I am wondering if this is a bug somewhere as the error states column "seq_name" is missing, unless the example I am using is incorrect there should not be a column by that name?

Can anyone throw any light on this issue for me?

@Id
@Column(name="userRoleID")
@TableGenerator(name = "role_generation",
        table = "id_generation",
        pkColumnValue = "gen_name",
        valueColumnName = "gen_val")
@GeneratedValue(generator = "role_generation")
private long userRoleID;



CREATE TABLE id_generation(gen_name varchar(80), gen_val INTEGER, CONSTRAINT pk_id_generation primary key(gen_name));

from persistance.xml

<properties>
        <property name="eclipselink.logging.level" value="FINE"/>
        <property name="eclipselink.target-database" value="PostgreSQL"/>
        <property name="eclipselink.ddl-generation" value="create-tables"/>
        <property name="eclipselink.ddl-generation.output-mode" value="database"/>
        <property name="jakarta.persistence.schema-generation.database.action" value="create"/>
    </properties>

Error from payara Logs

Local Exception Stack: 
Exception [EclipseLink-4002] (Eclipse Persistence Services - 4.0.0-M3.payara-p1.v202206011138): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: column "seq_name" does not exist
  Hint: Perhaps you meant to reference the column "id_generation.gen_name".
  Position: 55
Error Code: 0
Call: UPDATE id_generation SET gen_val = gen_val   ? WHERE SEQ_NAME = ?
    bind => [2 parameters bound]
Query: DataModifyQuery(name="gen_name" sql="UPDATE id_generation SET gen_val = gen_val   ? WHERE SEQ_NAME = ?")

CodePudding user response：

So with a bit of trial and error I solved it, the example I had was incorrect and here is the way I got it working as I wanted.

@Column(name="userRoleID")
@TableGenerator(name = "role_generation",
        table = "id_generation",
        pkColumnName = "class_generation",
        pkColumnValue = "role_generation",
        valueColumnName = "gen_val")
@GeneratedValue(generator = "role_generation")
private long userRoleID;

The database table (PostgreSQL) looks like this

 class_generation | gen_val 
 role_generation  |      50

I hope this helps someone in the future