Check created queries with Entity Manager when unit testing in Spring Boot

Question

I'm currently building an API using springboot. In my service layer, I used entity manager to create queries. As an example,

public Object editFeedback(Long user_id, Long reservation_id, String feedback){
    String s = "select r from  Reservation  r left join fetch r.driverAssignment where r.id=:id and r.reservation_done_by.id=:user_id ";
    List<Reservation> reservations = em.createQuery(s).setParameter("id", reservation_id).setParameter("user_id", user_id).getResultList();
    if (reservations.size() == 0)
        return new DefaultResponseDTO(201, ResponseStatus.INVALID_INPUTS, "Wrong reservation id");
    Reservation reservation = reservations.get(0);
    reservation.setFeedback(feedback);
    reservationRepository.save(reservation);
    return new DefaultResponseDTO(200, ResponseStatus.OK, "Successfully done");

}

My question is when writing unit tests for methods in the service layer, how can I check those queries are working correctly without affecting or fetching data from the original database?

For additional details, If I get data from the original database to check the query, then the output changes from time to time. So, I have to mock the Entity manager. Then the queries don't get checked.

  Query query = Mockito.mock(Query.class);
  Mockito.when(em.createQuery(Mockito.anyString())).thenReturn(query);
  Mockito.when(query.getResultList()).thenReturn(sampleReservations.getReservationsForUserReservations());

Is there any proper way to check that queries are working fine using a mocked entity manager? or If I have understood unit testing wrongly or any other suggestions?

Answer 1

In a service layer test you should mock the respository and prevent any database access. In such a test you are usually more concerned about the service itself. This also makes it easier to test scenarios that would otherwise be much harder to test (e.g. database failures).

To test your queries you should have separate integration test work either with the service layer or directly with the repositories and use test containers for the database (https://www.testcontainers.org/).