How to test EntityManager query with H2 in memory DB-CodePudding

I have a Spring Boot project and I want to test some queries. I want to insert a predefined set of data and execute the Repository query to check the result is the desired one.

To do this I'm using in-memory H2 DB and the problem (I think) is not there, everything related with DB is ok. The main problem is I can't mock properly the EntityManager field in repository and the query is always null.

My repository is like this:

@Repository
public class MyRepositoryImpl implements MyRepository {

    @PersistenceContext
    private EntityManager entityManager;
    
    @Override
    public Result runQuery() {
        
        TypedQuery<Result> query = entityManager.createQuery(
                "SELECT ...", Result.class);
        return query.setParameter("...", "...") // here 'query' is always null
                .setMaxResults(1)
                .getResultStream()
                .findFirst()
                .orElseThrow(() -> new ResponseStatusException(HttpStatus.NOT_FOUND, "Entity not found"));
    }

}

It works nice when is executed out of tests, but trying to run this test file it throw an error:

@RunWith(SpringRunner.class)
public class MyRepositoryTest {

    @Mock
    EntityManager entityManager;

    @InjectMocks
    MyRepositoryImpl repository;

    @Test
    public void it_should_works() {
        Result r = repository.runQuery();
        assertNull(r);
    }
}

The repository is mocked and is not null, I can call the method. But inside the repository, as query field is null, it throw a NullPointerException when try to execute.

I've searched over the internet and I've found many ways to test the JPARepository and @Query inside the interface, but not an EntityManager query.

Also I've found a few ways to mock the result for the query, something like when(runQuery()).thenReturn(result) but I don't want that, I've the data in the memory DB so I want to execute the query and get the result.

So, now, the main problem I think is how to mock the EntityManager object properly inside repository class.

Thanks in advance.

Edit:

I've follow this link and is like another SO questions: It's only to mock the JpaRepository.

I've used this code:

@Test
public void it_should_works() {
    Result r = repository.findAll();
    assertNotNull(r);
}

And works perfectly, but using my own query fails with error:

org.springframework.orm.jpa.JpaSystemException: could not advance using next(); nested exception is org.hibernate.exception.GenericJDBCException: could not advance using next()

    ...

Caused by: org.h2.jdbc.JdbcSQLNonTransientException: El objeto ya está cerrado
The object is already closed [90007-200]

So the question is: It is related to my DB? Why using JpaRepository method it works but my own query don't?

Edit:

Solved adding @Transactional into repository.

CodePudding user response：

Since you're using an h2 in-memory database to run tests, and you want to actually use that database in your tests, you shouldn't really be mocking anything.

Your mocking doesn't work because the MyRepositoryImpl is typically initialized by Spring, and that process is much more complicated than plugging in an EntityManager.

I think what you want to do is more like what's described here https://www.baeldung.com/spring-testing-separate-data-source

So you would have a src/test/resources/application.properties file that overrides the datasource properties. Then you just @Autowired your repository into your test class like normal.