CRUD repository does not respect UNIQUE constraint

Question

I have the following JPA entity

@Entity
class UserEntity {

    companion object {
        fun fromParameters(uuid: String, email: String, password: String, firstName: String, lastName: String) =
            UserEntity().apply {
                this.uuid = uuid
                this.email = email
                this.password = password
                this.firstName = firstName
                this.lastName = lastName
            }
    }

    @Id
    lateinit var uuid: String

    @Column(nullable = false, unique = true)
    lateinit var email: String

    @Column(nullable = false)
    lateinit var password: String

    @Column(nullable = false)
    lateinit var firstName: String

    @Column(nullable = false)
    lateinit var lastName: String
}

And this is my test to check the UNIQUE constraint, inserting another user with the same email.

@RunWith(SpringRunner::class)
@DataJpaTest
@AutoConfigureTestEntityManager
class TestUserCrudRepository {

    @Autowired
    private lateinit var userCrudRepository: UserCrudRepository

    private val testUserEntity = UserEntity.fromParameters(
        UUID.randomUUID().toString(),
        "test@test.com",
        "password".toHash(),
        "Caetano",
        "Veloso"
    )

    @Test
    fun `when email already exists it should throw error`() {
        with (userCrudRepository) {
            save(testUserEntity)
            val newUserEntity = with (testUserEntity) { UserEntity.fromParameters(UUID.randomUUID().toString(), email, password, firstName, lastName) }
            shouldThrow<SQLException> { save(newUserEntity) }
        }
    }
}

The new entity always gets inserted with a duplicate email without any exception being thrown.

Expected exception java.sql.SQLException but no exception was thrown

I can see in the log that the table is created correctly with the given constraint.

Hibernate: drop table user_entity if exists
Hibernate: create table user_entity (uuid varchar(255) not null, email varchar(255) not null, first_name varchar(255) not null, last_name varchar(255) not null, password varchar(255) not null, primary key (uuid))
Hibernate: alter table user_entity add constraint UK_4xad1enskw4j1t2866f7sodrx unique (email)

Thanks in advance!

Denis Zavedeev · Accepted Answer · 2019-05-11T19:12:14.400

This happens because no insert statement issued.

Hibernate does not flush session unless it has a good reason to do so.

@DataJpaTest is @Transactional. This means that the transaction a @Test method executed within is rolled back after the method returns.
UserEntity mapping also encourages hibernate to delay the insert (try using @GeneratedValue(strategy = IDENTITY) on id property to force issuing inserts)

Not diving into too much details the following happens when you run test:

Spring's test infrastructure begins transaction
@Test method runs
save(testUserEntity) - Hibernate realizes that there is no reason to hit the database and delays the insert
shouldThrow<SQLException> { save(newUserEntity) } - same as previous
@Test method returns
Transaction rolls back. Hibernate does execute inserts because there is no reason to.

How to fix it?

The most simple way to do it is to use JpaRepository#flush:

with (userCrudRepository) {
    save(testUserEntity)
    val newUserEntity = with (testUserEntity) { UserEntity.fromParameters(UUID.randomUUID().toString(), email, password, firstName, lastName) }
    save(newUserEntity)
    assertThrows<DataIntegrityViolationException> {
        flush()
    }
}

Note that there is no flush method in CrudRepository

I guess that you have extended CrudRepository... You may want to extend JpaRepository instead.

See: What is difference between CrudRepository and JpaRepository interfaces in Spring Data JPA?

Note on exception

You are expecting an SQLException to be thrown.

But note that DataIntegrityViolationException will be thrown instead.

See: Consistent Exception Hierarchy

Excellent answer, I just started on Spring Boot 3 days ago :) Annotating the test with `@Commit` does not solve my problem because I'm checking the exception before the test method ends. I extended `JpaRepository` as you suggest and caught the exception on `flush()` and used `@Rollback` on the test to avoid it throwing an `UnexpectedRollbackException`. Maybe you want to update your answer with these. — m0skit0, May 11 '19 at 19:03
Oh, I just missed some moments. `@Commit` will throw exception *outside* of `@Test` (I removed this part of the answer). You do not need to use `@Rollback`. Wrapping `flush` into [`assertThrows`](https://junit.org/junit5/docs/5.3.0/api/org/junit/jupiter/api/Assertions.html#assertThrows(java.lang.Class,org.junit.jupiter.api.function.Executable)) should be enough. I was able to get `UnexpectedRollbackException` only if `@Commit` is present on `@Test`. Can you please elaborate more on this? — Denis Zavedeev, May 11 '19 at 19:27

CRUD repository does not respect UNIQUE constraint

1 Answers1

How to fix it?