title: Autoincrement best practice JPA Oracle DB
body: <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 &lt; 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>