-1

Info: Oracle DB 19. Hibernate is v5.5.4 with org.hibernate.dialect.Oracle12cDialect. JDBC driver is v12.2.0.1.

Question: I want to save an entity with JPA/Hibernate (DB is Oracle 11g), that has an autogenerated ID column (here: PROT_ID).

CREATE TABLE SOME_PROTOCOL(
  PROT_ID       NUMBER(18) GENERATED ALWAYS AS IDENTITY (START WITH 123 MAXVALUE 99999) NOT NULL,
  MORE_COLS     VARCHAR2(500 CHAR) NOT NULL);

To add a new record, I have to skip the ID column, like that:

insert into SOME_PROTOCOL (MORE_COLS) values ('Some Value');

This is my Entity class:

@Entity
@Table(name = "SOME_PROTOCOL")
public class SomeProtocol {
  @Id 
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name = "PROT_ID", insertable = false)
  private Long id;
  // Getter, Setter, other columns
}

Saving the entity with

SomeProtocol s = new SomeProtocol();
s.setMoreCols("whatever");
hibernateSession.save(s);

leads to this error:

ERROR: Invalid argument(s) in call
Hibernate: insert into APPL_PROTOCOL (PROT_ID, MORE_COLS) values (default, ?)

Ok, JPA doesn't skip the ID column, but sets default as a value.

I tried some more with @Column(insertable=false) or GenerationType.AUTO, but to no avail.

How can I save an entity class with an autogenerated ID column?

Solution: We changed the ID generation for that table, we now use an external sequence (previously it was auto-generated). Hibernate can save the entity now via hibernateSession.save.

  @SequenceGenerator(name = "SEQ_APPL_PROT_GENERATOR", sequenceName = "SEQ_APPL_PROTOCOL_ID", allocationSize = 1)
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SEQ_APPL_PROT_GENERATOR")
  @Column(name = "PROT_ID")
  private Long id;
Bernie
  • 1,607
  • 1
  • 18
  • 30
  • it seems there is some confusion with oracle versions: identity columns is a feature of 12c (and `insert into(..) values(default,...)` is valid syntax in 12c) – Andrey B. Panfilov Dec 17 '22 at 14:19
  • I updated the ticket with version information. Does the version matter in this case? – Bernie Dec 17 '22 at 15:08
  • Why did you pick strategy = GenerationType.IDENTITY, and how are tables being created? IDENTITY requires JPA to let the database assign the value on the insert statement, requiring JPA to leave it blank and look up the value assigned from the database after the insert. Oracle generally uses SEQUENCE objects for assigning identity, so I'm surprised this was accepted at all - maybe try using SEQUENCE instead. – Chris Dec 18 '22 at 17:38
  • Thanks to all for your input. I posted the solution (ID no longer autogenerated, now it's sequence-based). – Bernie Dec 19 '22 at 09:55

1 Answers1

0

I think the insertable = false in your @Column annotation might be the problem. Please try without that and let us know how that works.

You can read more about this attribute on Please explain about insertable=false and updatable=false in reference to the JPA @Column annotation.

Kaj Hejer
  • 955
  • 4
  • 18
  • Thanks. It didn't work. So I tried ```@Column(name = "PROT_ID") @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id;``` , but JPA still wants to do ```insert into APPL_PROTOCOL (PROT_ID, more_cols) values (default, ?) ``` I tried GenerationType.AUTO too, just to see what happens, but then it wants to do a sequence. – Bernie Dec 17 '22 at 15:05