CODEWITHSUNDEEP
oracle-databasehibernatejpaspring-data-jpadatabase-trigger
ssasi
ssasi

Reputation: 1854

Spring Data JPA + Oracle Trigger increments the ID twice

I use the following tech stack:

My actual code looks similar to this.

   /// My trigger looks like this
   CREATE OR REPLACE TRIGGER FILE_BRI
     BEFORE INSERT
     ON FILE
     FOR EACH ROW
     BEGIN
       SELECT FILE_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
     END;
   ///

   public class FILE implements Serializable {
          @Id
        @SequenceGenerator(
                name = "FILE_SEQ",
                sequenceName = "FILE_SEQ",
                allocationSize = 1)
        @GeneratedValue(
                strategy = GenerationType.SEQUENCE,
                generator = "FILE_SEQ"
        )
        private long id;
    }

    public class ServiceA () {

        @Transactional(propagation = REQUIRES_NEW, isolation = READ_COMMITTED) 
        public File insertFile() {
         // Below line returns the inserted File object with ID as '58496'
           return fileRepository.save(file)
        }

        @Transactional(propagation = REQUIRES_NEW, isolation = READ_COMMITTED) 
        public AccessControl insertAccessControl() {
        // Below line results in 'SQLIntegrityConstraintViolationException' (full error at the bottom of this post)
            return accessControlRepository.save(accessControlFile)
         }
     }

     Public class FileProcessor() {
       ServiceA serviceA;
       public void someMethod() {
         // insert the file and get the inserted record
           File insertedFile = serviceA.insertFile(file);

         // get the ID from the inserted file and make another insert into another table
           serviceA.insertAccessControl(insertedFile.getId()); // inserted file ID is '58496'
        }
     }

This is my investigation:

When I verified the ID of the inserted record in the table "FILE" is '58497', however repository.save() returned a different value. When I make the second insert on table "ACCESS_CONTROL_FILE" with FILE_ID as '58496' it results in the error below because the FILE with ID as '58496' does not exist.

Caused by: java.sql.SQLIntegrityConstraintViolationException: ORA-01400: cannot insert NULL into ("DB_OWNER"."ACCESS_CONTROL_FILE"."FILE_ID")

I'm puzzled as to why would repository.save() return a different ID(i.e. ID=58496) than what is actually inserted(ID=58497) in the database!

I've investigated all options that I could find on the internet related to 'Propagation and Isolation'.

Upvotes: 1

Views: 1751

Answers (1)

Ram
Ram

Reputation: 1803

As mentioned in comments, Looks like a database trigger is causing the issue. Disable the trigger to let JPA to manage the ID generation.

Upvotes: 2

Related Questions