Spring boot oracle 11g sequence stored is different from the value shown in the model

Question

Hi i am using spring boot (2.7.9) and java 11. My database is Oracle 11g(Pretty old)

I have a table created as follows

CREATE SEQUENCE BOOK_ENTRY_SEQ
    MINVALUE 1
    MAXVALUE 999999999999999999999999999
    START WITH 1
    INCREMENT BY 1 CACHE 20
    NOORDER
    NOCYCLE
;

CREATE TABLE BOOK_ENTRY
(
    ID           NUMBER PRIMARY KEY,
    STATUS       VARCHAR2(100 BYTE) NOT NULL,
    MESSAGE      VARCHAR2(4000 BYTE)
);

CREATE OR REPLACE TRIGGER "BOOK_ENTRY_TRG"
    BEFORE INSERT
    ON BOOK_ENTRY
    REFERENCING NEW AS New OLD AS Old
    FOR EACH ROW
BEGIN
    :new.ID := BOOK_ENTRY_SEQ.nextval;
END BOOK_ENTRY_TRG;
/

Now in my spring boot application

i have the following model

@Entity
@Table(name = "BOOK_ENTRY")
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
@Data
@EqualsAndHashCode
public class BookEntry implements Serializable {
    @Id
    @NotNull
    @Column(name = "ID", updatable = false, insertable = false)
    @SequenceGenerator(name = "seq_generator", sequenceName = "BOOK_ENTRY_SEQ", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seq_generator")
    private Long id;

  
    @NotNull
    @Column(name = "STATUS")
    private String status;

    @Column(name = "MESSAGE")
    private String message;
}

Repository method is

public interface BookEntryRepository  extends JpaRepository<BookEntry, Long> {
}

And from the service class

private void createEntry() {
        BookEntry bookEntry = new bookEntry();
        
        bookEntry.setStatus("NEW");
        bookEntry.setMessage("Test 1");
        bookEntryRepository.save(bookEntry);
    }

When i do a debug and print the entry, id for example is 61 but when it is saved it is 62.

My guess is it pulls the sequence value once from jpa and then again the db trigger replaces it to the next value.

In this case how do we save with a null value so that the db trigger is the one updating the value?

Any thoughts will be appreciated.

Answer 1

@SequenceGenerator(name = "seq_generator", sequenceName = "BOOK_ENTRY_SEQ", allocationSize = 1)
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seq_generator")

Tells your Spring Boot application that the value is to be generated by the database sequence BOOK_ENTRY_SEQ and when you save the entity Spring Boot will talk to the database, find the next value for the sequence and set the id to that value and then insert the entity into the database.

When the database retrieves the entity, it runs the BEFORE INSERT trigger which states to ignore any old id values and generate the next BOOK_ENTRY_SEQ value and use that as the new id value.

Therefore, for each BOOK_ENTRY entity, you are going to generate the next value of the BOOK_ENTRY_SEQ sequence twice (once from Spring Boot and once from the database trigger).

You can either:

1. Try @GeneratedValue(strategy = GenerationType.IDENTITY), which tells Spring Boot that the database will be generating the value.

2. Drop the trigger and rely on Spring Boot to call the sequence (which works if you are always entering data through your application but not if you are also entering data manually and want to auto-generate the id column).

3. Change the trigger to see if the id IS NOT NULL and then ignore it:

   CREATE OR REPLACE TRIGGER "BOOK_ENTRY_TRG"
      BEFORE INSERT
      ON BOOK_ENTRY
      REFERENCING NEW AS New OLD AS Old
      FOR EACH ROW
   BEGIN
      IF :old.id IS NULL THEN
        :new.ID := BOOK_ENTRY_SEQ.nextval;
      END IF;
   END BOOK_ENTRY_TRG;
   /
   

   Which works until someone manually specifies a higher number than the sequence and then the sequence reaches that number and tries to insert a duplicate value.

4. Do not fix this issue; yes, the sequence gets called twice but you do not need to have sequential id values so it is only a (possibly) minor inefficiency rather than an actual problem.