I have a composite unique constraint in the database and i wish to violate it inside the transaction but it will be upheld after i call commit.

It's pretty basic database transaction, but i am not aware how to achieve it using JPA.

I have already tried @org.springframework.transaction.annotation.Transactional on my @org.springframework.stereotype.Service class and also at the method level.

but the moment i call userRepository.save(userEntity) it throws

java.sql.SQLIntegrityConstraintViolationException: (conn=380) Duplicate entry ...'

controller

    @PostMapping("/save")
    @Transactional
    fun createUser(
        @RequestHeader("token", required = true) subject: String,
    ): ResponseEntity<*> {
        val id = extractId(subject)
        return userService.save(id)
    }

service

    @Transactional
    fun save(id: String): ResponseEntity<*> {
     val validatedUser =  getValidatedUser(id)
     return ResponseEntity<User>(validatedUser, HttpStatus.CREATED)
    }

   @Transactional
   fun getValidatedUser(id: String): User? {
      var list = listOf(User(id))
      val newUser = //some logic to generate user
      userRepository.saveAll(list.plus(newUser)) // <--- where error happens
   }

CodePudding user response：

transactional should only be called on a single function. Can you show us your schema and user repository please

CodePudding user response：

1. Your database has composite unique constraint. So you can't insert the same data.

2. This error means that the program inserted the same data at runtime.This is not allowed.

3. I think there may be a problem of duplicate ID here.

   var list = listOf(User(id))

4. Because of 3, two user objects with the same ID are created in the next step of the code.So there was an error inserting into database.

5. You can output user object at the console and find out if there is the same userId object.

CodePudding user response：

// RollbackFor=Exception.class needs to be specified
@Transactional(rollbackFor = Exception.class)
fun save(id: String): ResponseEntity<*> {
 val validatedUser =  getValidatedUser(id)
 return ResponseEntity<User>(validatedUser, HttpStatus.CREATED)
}