How to write test case for JPQL join queries in standard way?

Question

I have spent a lot of time identifying the unit test case for the below JPQL join query. following are the details of my implementations. I could not find the proper way to write a test case for the repository layer.

Repository Class

@Repository
public interface UserRepository extends JPARepository<User, Integer> {

    @Query("select u from User u join fetch u.venues uv where u.eventId = :eventId")
    User findUserByEventId(String eventId);
......
}

User Class

public class User{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int userId;
    
    private String firstName;
    
    private String lastName;
    
    @OneToMany(targetEntity = UserVenues.class, mappedBy = "user", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    private List<UserVenues> venues;
    
    private String eventId;
    
    ......
}

UserVenues

public class UserVenues{
    @Id
    private int id;
    
    @ManyToOne(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @JoinColumn(name = "user_Id",  referencedColumnName = "id")
    private User user;
    ......  
}   

I am kindly expecting a solution.

Answer 1

So, if you want to test some JPQL query, I think that writing the simple unit test here will not be helpful if at all possible. You will need the hibernate-core to render JPQL into SQL, execute the query, map the results etc. Mocking all of this is impractical.

So I think that in this case, an integration test with the database would be most suitable. You can set up the test database using either H2, or you can use testcontainers. Since you are using JPQL, the both approaches should work well, although I still recommend to use testcontainers in order to avoid having different compatiblity issues that you can face down the H2 road.

So having the testcontainers set up, you can do something like this:

@SpringBootTest
public class MyIntegrationTest {

    @Autowired
    private UserRepository userRepository;

    @Autowired
    private DataSource dataSource;

    @BeforeEach
    void setUp() throws SQLException {
        ScriptUtils.executeSqlScript(
          dataSource.getConnection(),
          new ByteArrayResource("""
            DELETE FROM user_venues;
            DELETE FROM users;
            
            INSERT INTO users(first_name, last_name. event_id) VALUES('bob', 'wasowski', '123');
            INSERT INTO user_venues(id, user_id) VALUES(1, (SELECT id FROM users WHERE event_id = '123' LIMIT 1));
            """.getBytes())
        );
    }

    @Test
    void testJoinQuery() {
        User something = userRepository.findUserByEventId("something");

        Assertions.assertThat(something).extracting(User::getFirstName).isEqualTo("Bob");
        Assertions.assertThat(something).extracting(it -> it.getVenues().size()).isEqualTo(2);
    }
}

Strictly speaking, @SpringBootTest is not that necessary, @DataJpaTest or smth like that should be suffice if you want to only test the repository layer. This is again just an example, in real world I would highly suggest you to extract the init sql queries into separate file and etc. This is just for you for demonstration purposes. So consider to write an integration test with spring boot for this task.

Hope it helped :)

P.S: I guess the definition UserRepository is a bit wrong - you should extend JpaRepository<User, Long> (notice first type parameter) if you want to query User entities.