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")

score 0 · Answer 1 · answered Jul 07 '21 at 08:35

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 07 '21 at 08:35

S. Anushan

728
1
6
12

DB sequences are not accessible for user. This doesn't work: select MY_DB_SEQ.nextval from dual – user512514 Jul 07 '21 at 13:45
For this there can be two reason. – S. Anushan Jul 07 '21 at 14:05
1) the sequence not available. – S. Anushan Jul 07 '21 at 14:06
2) you don't have enough permission to read from database. – S. Anushan Jul 07 '21 at 14:06
1

Ask admin to grant permission to read values – S. Anushan Jul 07 '21 at 14:07
Thanks Selvakumar. I know the reasons of errors. I am looking for another alternative for this case. – user512514 Jul 07 '21 at 19:12

score 0 · Answer 2 · answered Jul 12 '21 at 19:46

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 '21 at 19:46

user512514

161
1
1
10

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

2 Answers2