4

I have defined my table like this:

CREATE TABLE MY_ENTITY (
  ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY
);

Using @GeneratedValue(strategy = GenerationType.SEQUENCE) produces no errors. After inserting some data with fixed ids manually and updating the sequence, I noticed that the ID's are not actually generated by Oracle. I updated the sequence like this:

ALTER TABLE MY_ENTITY MODIFY (ID GENERATED AS IDENTITY START WITH LIMIT VALUE);

How can I get Hibernate/JPA to use the Oracle 12c Identity generator?

chris
  • 2,467
  • 2
  • 25
  • 25

1 Answers1

11

To use the Oracle 12c Identity Generation, use at least Hibernate 5.3. Example:

<dependency>
  <groupId>org.hibernate</groupId>
  <artifactId>hibernate-core</artifactId>
  <version>5.4.4.Final</version>
</dependency>

Your application.properties will need to provide the Oracle12cDialect.

spring.jpa.database-platform=org.hibernate.dialect.Oracle12cDialect
spring.datasource.platform=oracle
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.url=jdbc:oracle:thin:@hostname:1521:MYSID
spring.datasource.username=MY_USER
spring.datasource.password=$ecret
spring.jpa.hibernate.ddl-auto=none

Given a table with auto generated primary key:

CREATE TABLE MY_ENTITY (
  ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY
);

Use GenerationType.IDENTITY as strategy on the JPA entity.

@Entity
@Table(name = "MY_ENTITY")
public class MyEntity {

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

    // the rest of your properties

}

To check if it's working, update your auto generated sequence with something like:

ALTER TABLE MY_ENTITY MODIFY (ID GENERATED AS IDENTITY START WITH 10000);

When inserting new entries, their ID's should be like 10001, 10002, etc.

chris
  • 2,467
  • 2
  • 25
  • 25
  • Take note about your db is an **Oracle 12c**, your property jpa.database-platform is **Oracle12cDialect** and your identity is a **NUMBER** in column **ID**. Good response @chris, congrats. – Guillermo Diaz Nov 14 '19 at 06:51
  • DEFAULT ON NULL is critical here. Using id DECIMAL(19) DEFAULT my_sequence.nextval would not work. – JRA_TLL Sep 02 '20 at 10:14
  • @JRA_TLL How can I annotate the id field in order to get the exact ddl:GENERATED BY DEFAULT ON NULL AS IDENTITY? I want to use ddl-auto=create – brsgrlr Dec 08 '20 at 18:26
  • @brsgrlr: Can´t tell, never used ddl-auto=create – JRA_TLL Dec 10 '20 at 07:36
  • @brsgrlr: ddl-auto=create is not recommended for production. I'd recommend using Flyway or Liquibase instead. – chris Dec 11 '20 at 09:17