I have a question regarding Spring Data JPA.
To make it as simple as possible I made up a very simple example.

We have the TestUser, that can have a FavouriteColor, but his favouriteColor can also be null.

TestUser.kt

@Entity
class TestUser(
    @Id
    @Column(name = "TestUserId")
    var userId: Long,
    @Column(name = "Name")
    var name: String,
    @Column(name = "FavouriteColorId")
    var favouriteColorId: Long? = null,
    @OneToOne
    @JoinColumn(
        name = "FavouriteColorId",
        referencedColumnName = "FavouriteColorId",
        insertable = false,
        updatable = false,
        nullable = true
    )
    var favouriteColor: FavouriteColor? = null
)

FavouriteColor.kt

@Entity
class FavouriteColor(
    @Id
    @Column(name = "FavouriteColorId")
    var favouriteColorId: Long,
    @Column(name = "ColorCode")
    var colorCode: String
)

When I search for the users that have a favourite Color by findTestUsersByFavouriteColorNotNull(), the size of the result is 0. Even if there is an User that has a favourite color. And when I use findAll() and then apply the filter, the result is correct.

StackOverflowTest.kt

@SpringBootTest
@Transactional
class StackOverflowTest {
    @Autowired
    lateinit var testUserRepository: TestUserRepository

    @Autowired
    lateinit var favouriteColorRepository: FavouriteColorRepository

    @Test
    fun testFilter() {
        val favouriteColor = FavouriteColor(favouriteColorId = 0L, colorCode = "#000000")
        favouriteColorRepository.save(favouriteColor)

        val user = testUserRepository.save(TestUser(userId = 0L, name = "Testuser"))
        user.favouriteColor = favouriteColor
        testUserRepository.save(user)

        val usersWithColor1 = testUserRepository.findAll().filter { it.favouriteColor != null }
        assert(usersWithColor1.size == 1) // This assertion is correct

        val usersWithColor2 = testUserRepository.findTestUsersByFavouriteColorIdIsNotNull()
        assert(usersWithColor2.size == 1) // This assertion fails

        val usersWithColor3 = testUserRepository.findTestUsersByFavouriteColorIsNotNull()
        assert(usersWithColor3.size == 1) // This assertion fails

    }
}

Update:
I added the Repository function findTestUsersByFavouriteColorIdNotNull() but it also does not work

Update2: I updated the functions to findTestUsersByFavouriteColorIdIsNotNull and findTestUsersByFavouriteColorIsNotNull, but the assertions are still failing

Can somebody explain me, why the findTestUsersByFavouriteColorNotNull() does not work ? And is there some way to get this function working in the tests?
Thanks :)

CodePudding user response：

(spring) data/transactional tests are tricky...!

My best "reputable source" on that:

TransactionalTestExecutionListener.

, but pragmatically I had good experience with:

• introducing an @TestConfiguration internal class TestConfig containing:
  • a (or several) @Bean InitializingBean testDataInitlzr(repo1, repo2, ...), which is also (by default) cleaned afterwards

...of course you have many other options (tx-flushing/scripts/flyway/liqui...)

Can look like (thx to jetbrains-converter):

@DataJpaTest(properties = ["logging.level.org.springframework.test=debug"])
class DataTest {
    @TestConfiguration
    internal class TestConfig {
        @Bean
        fun initStudents(studentRepo: StudentRepository): InitializingBean {
            return InitializingBean {
                studentRepo.save(Student("012345"))
                studentRepo.save(Student("677890"))
            }
        }
    }

    @Autowired
    var testee: StudentRepository? = null
    @Test
    fun smoke() {
        val studs: Stream<Student> = testee.findCustom()
        Assertions.assertThat(studs).isNotNull
        Assertions.assertThat(studs.count()).isGreaterThanOrEqualTo(2L)
    }
}

Original java version:

package com.example.springboot3;

import java.util.stream.Stream;
import static org.assertj.core.api.Assertions.assertThat;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;
import org.springframework.boot.test.context.TestConfiguration;
import org.springframework.context.annotation.Bean;

@DataJpaTest(properties = "logging.level.org.springframework.test=debug")
public class DataTest {

  @TestConfiguration
  static class TestConfig {

    @Bean
    InitializingBean initStudents(StudentRepository studentRepo) {
      return () -> {
        studentRepo.save(new Student("012345"));
        studentRepo.save(new Student("677890"));
      };
    }
  }

  @Autowired
  StudentRepository testee;

  @Test
  public void smoke() {
    final Stream<Student> studs = testee.findCustom();
    assertThat(studs).isNotNull();
    assertThat(studs.count()).isGreaterThanOrEqualTo(2L);
  }
}

(with embedded(test) db, spring-boot-3) I see following output:

...
o.s.t.c.transaction.TransactionContext   : Rolled back transaction (1) for test class [com.example.springboot3.DataTest]; test method [smoke]
...
Tests run: 1, Failures: 0, Errors: 0, Skipped: 0, Time elapsed: 4.539 s - in com.example.springboot3.DataTest
2022-12-12T23:57:02.220 01:00  INFO 23320 --- [ionShutdownHook] j.LocalContainerEntityManagerFactoryBean : Closing JPA EntityManagerFactory for persistence unit 'default'
2022-12-12T23:57:02.221 01:00  INFO 23320 --- [ionShutdownHook] .SchemaDropperImpl$DelayedDropActionImpl : HHH000477: Starting delayed evictData of schema as part of SessionFactory shut-down'
Hibernate: drop table if exists student cascade 

Results:

Tests run: 1, Failures: 0, Errors: 0, Skipped: 0

------------------------------------------------------------------------
BUILD SUCCESS
------------------------------------------------------------------------
Total time:  7.270 s
Finished at: 2022-12-12T23:57:02 01:00
------------------------------------------------------------------------

..so the whole "session factory" (embedded hbm2ddl in my case) gets "evicted".

Alternatively for InitilizingBean we could use a DataSourceScriptDatabaseInitializer (bean) with full spring.sql.* properties support/resolution. ..or any "db initializer" mentioned in howto.data-initialization.dependencies.initializer-detection.

I like the InitializingBean (for test and demo purposes) due to it's "preference in spring context lifecycle", and i prefer this to e.g. CommandLineRunner especially in the absence of any "command" (args).

For @Sql, db-unit, script higher level (as my) approaches (including db initialization), I found this article useful.

And not forget to mention: good-ole JdbcTestUtils.

CodePudding user response：

I'm suspecting that happen because you have 2 variables of the same column name

@Column(name = "FavouriteColorId")
var favouriteColorId: Long? = null,
@OneToOne
@JoinColumn(
    name = "FavouriteColorId",
    referencedColumnName = "FavouriteColorId",
    insertable = false,
    updatable = false,
    nullable = true
)
var favouriteColor: FavouriteColor? = null

Try removing one of the variable, and try again.