Hibernate Identity differences when moving from Oracle To Postgres-CodePudding

I am switching a Java application using Hibernate from Oracle to Postgres and encountering a issue with Id GeneratedValues.

The Domain objects have Ids configured like this:

    @Id @GeneratedValue(strategy = GenerationType.AUTO) @Column(name = "id")
    private Long id;

Under Oracle there was a sequence called "HIBERNATE_SEQUENCE" that provided this. I have created this sequence in Postgres like this:

 CREATE SEQUENCE HIBERNATE_SEQUENCE MINVALUE 1 MAXVALUE 9223372036854775807 INCREMENT BY 1 START WITH 50000 CACHE 20   NO CYCLE ;

However, when persisting an object I'm getting an error of:

 org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table "hibernate_sequence"

I have tried:

switching the "GenerationType" to "SEQUENCE"
creating the sequence in lower case (hibernate_sequence)

But I get the same error in both cases.

CodePudding user response：

You have to configure Hibernate to speak the PostgreSQL dialect of SQL.

In Oracle you get the next value of a sequence with a pseudo-column (hibernate_sequence.nextval), while in PostgreSQL you use a function (nextval(hibernate_sequence)). Using the Oracle syntax with PostgreSQL will cause the error you quote.

CodePudding user response：

The main problem I had was not setting the hibernate.dialect property correctly.

It was

<property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect"/>

and changing it to

<property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQL95Dialect"/>

fixed it and started using the correct sequence function as Laurenze Able pointed out