Spring Boot entity with Pk using Oracle DB Sequence in a trigger

Question

I need a help on persisting an entity in a Oracle DB table that uses trigger and sequence for PK. By now, I tried these from other stackoverflow questions:

@Id 
@GeneratedValue(generator="increment")
@GenericGenerator(name="increment", strategy="increment")

This approach works. It finds the max value of PK and increment the value in 1. But, this doesn't update the Db sequence causing "constraint violation" error at some point.

@Id 
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="id_sequence")
@SequenceGenerator(name="id_sequence", sequenceName="MY_DB_SEQ")

This approach doesn't work for me either. Sadly, the Db sequence is not accessible and causing error when it is trying to run internally "select MY_DB_SEQ.nextval from dual". Why is not accesible? Go and ask DB admin :)

It looks like the only option I have is passing null in the Entity PK attribute so that the DB trigger, which uses the a DB sequence to get the nextval when the ID is null, assigns the PK value in DB record.

How can I pass a null value for @Id? Of course this is throwing error because is needed. Is there any other annotation I can use for this?

If this is not possible, what other ways I should try?

Thanks for your help.

UPDATE

I couldn't find another way for this, having a DB Seq that is not accessible for user, and when it requires to pass NULL to PK in order to have DB use the trigger which check NULL value in PK to run seq nexval. After granting access to DB Seq, this approach of course works.

@Id 
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="id_sequence")
@SequenceGenerator(name="id_sequence", sequenceName="MY_DB_SEQ")

S. Anushan · Accepted Answer · 2021-07-07 08:35:49Z

0

Did you create the sequence in the database as below?

CREATE SEQUENCE id_seq INCREMENT BY 1 START WITH 0 MINVALUE 0 MAXVALUE 100 NOCYCLE NOCACHE;

if not create first.

or else add the below properties in the application but it's not good practice.

spring.jpa.hibernate.ddl-auto = update

answered Jul 7, 2021 at 8:35

S. Anushan

7861 gold badge6 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Mikaelangel Over a year ago

DB sequences are not accessible for user. This doesn't work: select MY_DB_SEQ.nextval from dual

S. Anushan Over a year ago

For this there can be two reason.

S. Anushan Over a year ago

1) the sequence not available.

S. Anushan Over a year ago

2) you don't have enough permission to read from database.

S. Anushan Over a year ago

Ask admin to grant permission to read values

|

Mikaelangel · Accepted Answer · 2021-07-12 19:46:21Z

0

As of now, I couldn't find any solution to pass NULL to PK in Entity. In DB, the DB Sequences were granted access. Spring Boot can now read the sequence and gran the next value.

answered Jul 12, 2021 at 19:46

Mikaelangel

1811 gold badge1 silver badge13 bronze badges

Collectives™ on Stack Overflow

Spring Boot entity with Pk using Oracle DB Sequence in a trigger

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related