StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POAutoincrement best practice JPA Oracle DB
text
Body
copied!<p>I have an issue with autoincrement ids in Oracle using JPA. If I use sequences like this:</p> <pre><code>public class Customer { @Id @GeneratedValue(strategy = GenerationType.AUTO, generator = "customerSequence") @SequenceGenerator(name = "customerSequence", sequenceName = "CUSTOMER_SEQ") private int id; // getters and setters } </code></pre> <p>I have to use sequences all the time. If for example DBA does not use it and supply actual id values while doing INSERTs like this:</p> <pre><code>INSERT INTO CUSTOMER (ID, NAME) VALUES (1, 'Paul'); INSERT INTO CUSTOMER (ID, NAME) VALUES (2, 'John'); INSERT INTO CUSTOMER (ID, NAME) VALUES (3, 'Mark'); // bunch of other statements like this </code></pre> <p>Then sequence value remains 0. And so <code>CUSTOMER_SEQ.NEXTVAL</code> returns 1 which is actually already in the database.</p> <p>I need the best way to achieve flexibility. When id is provided, the sequence should be incremented so the numbers will not collide.</p> <p>P.S. I tried to use triggers like this:</p> <pre><code>CREATE OR REPLACE TRIGGER CUSTOMER_INSERT BEFORE INSERT ON "CUSTOMER" FOR EACH ROW DECLARE MAX_ID NUMBER; CURRENT_SEQ_VAL NUMBER; BEGIN IF :NEW.ID IS NULL THEN SELECT CUSTOMER_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL; ELSE SELECT GREATEST(NVL(MAX(ID), 0), :NEW.ID) INTO MAX_ID FROM "CUSTOMER"; SELECT CUSTOMER_SEQ.NEXTVAL INTO CURRENT_SEQ_VAL FROM DUAL; WHILE CURRENT_SEQ_VAL < MAX_ID LOOP SELECT CUSTOMER_SEQ.NEXTVAL INTO CURRENT_SEQ_VAL FROM DUAL; END LOOP; END IF; END; </code></pre> <p>When i do inserts using raw SQL it works fine, however when I use JPA sometimes I get too big id's and sometimes strange errors like these two: </p> <pre><code>ORA-04091: table CUSTOMER is mutating, trigger/function may not see it ORA-04088: error during execution of trigger 'CUSTOMER_INSERT' </code></pre> <p>Probably I am doing something wrong here. Could someone help me to find the solution?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload