JOIN FETCH in spring-data-jpa does not fetch lazy associations

Question

I'm having a problem with fetching lazy @ManyToMany association in spring-data-jpa. I have a list of movies, that has an association with users that has made this movie favorite and the list of genres:

@Entity
@Table("movie")
public class Movie {
...

  @ManyToMany(fetch = FetchType.EAGER)
  @JoinTable(...)
  private List<Genre> genres;

  @ManyToMany
  @JoinTable(name = "users_movies",
            joinColumns = @JoinColumn(name = "movie_id", referencedColumnName = "id"),
            inverseJoinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"))
  private List<User> users = new ArrayList<>;
}

Genres also have an association with users that have marked this genre as a favorite:

@Entity
@Table("genre")
public class Genre {
....
  @ManyToMany
  @JoinTable(name = "users_genres",
            joinColumns = @JoinColumn(name = "genre_id", referencedColumnName = "id"),
            inverseJoinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"))
  private List<User> users;
}

And I have a repository with the following method/query that has to return all the movies by the favorite genres and to load the user of the movies so I can display if the user has marked the movie as a favorite:

@Query(value = "SELECT movie FROM Movie movie " +
               "JOIN movie.genres genre JOIN genre.users grenreUser " +
               "LEFT JOIN FETCH movie.users " +
               "WHERE grenreUser.id = :userId",
      countQuery = "SELECT COUNT(movie) FROM Movie movie " +
                   "JOIN movie.genres genre JOIN genre.users grenreUser " +
                   "LEFT JOIN movie.users " +
                   "WHERE grenreUser.id = :userId")
Page<Movie> getAllMoviesByFavoriteGenres(@Param("userId") String userId, Pageable);

But here I'm having a problem, movie.getUsers() is empty after executing this query.

I found that using @EntityGraph could help, but it didn't the movie.getUsers() association is still empty.

I also have tried to make this association as FetchType.EAGER just for the sake of testing, but it still is empty.

I'm out of ideas so I will appreciate any help.

UPD: It could not be the issue with users not be present in DB, as I have another query that extracts only the movies that are marked favorites by the user in the favorites user genre. So if the user would have not been present in DB, the query would return me the empty result, but I do get result although the movie.getUsers() is empty. Here is the method/query:

@Query(value = "SELECT movie FROM Movie movie " +
                   "JOIN movie.genres genre " +
                   "JOIN FETCH movie.users user " +
                   "WHERE user.id = :userId AND :userId MEMBER OF genre.users",
          countQuery = "*same but with count*")
Page<Movie> getAllFavoriteMoviesByFavoriteGenres(@Param("userId") String userId, Pageable);

Are you sure it should be Movie.users? In the example Movie is a class and there is a user (not users) non-static list member. — Lajos Arpad, May 14 '19 at 16:29
@LajosArpad It’s a typo, otherwise I would get hibernate exception — Sergii Bishyr, May 14 '19 at 16:31
Okay, I understand, but even after the edit, you have Movie.users. Do you have a static member called users, or do you mean instance-level users, like m.users, where m is an instance of Movie? — Lajos Arpad, May 14 '19 at 16:48
@LajosArpad sorry for the confusion, I was referring to the non-static member of the Movie class. Will edit that as well — Sergii Bishyr, May 14 '19 at 16:49
Thank you. If you run your query directly with the userId value you are testing with, do you get values for users? — Lajos Arpad, May 14 '19 at 16:50
@LajosArpad do you mean if I'm running the SQL that hibernate generates on DB directly? In this case yes, I'm getting the users data. But it's not present in the entity after when running in from Java — Sergii Bishyr, May 14 '19 at 16:52
Interesting. Is this helpful: https://stackoverflow.com/questions/15287230/hibernate-onetomany-manytoone-mapping-giving-null ? — Lajos Arpad, May 14 '19 at 16:56
@K.Nicholas I do, but I have the only one-directional mapping here. So the user does not have the mapping to Movie and Genre entries — Sergii Bishyr, May 14 '19 at 17:08
@K.Nicholas as I know, it should work with one-directional mapping, shouldn’t it? — Sergii Bishyr, May 14 '19 at 17:11
It's empty means that the query cannot find users that has nothing to do with lazy loading. Have you had a look at the SQL hibernate creates? And have you executed it directly on the DB to see the results? — Simon Martinelli, May 14 '19 at 17:17
@SimonMartinelli updated the question to show why this is not the case — Sergii Bishyr, May 14 '19 at 18:17
Have you had a look at the generated SQLs? Set logging.level.org.hibernate=debug — Simon Martinelli, May 14 '19 at 18:21

K.Nicholas · Answer 1 · 2019-05-14T18:40:29.367

Yes, unidirectional mapping should work. I am not having any problem with a basic example of what you are doing. This should be a good starting point to help you figure out what the differences are. I notice that I don't join movie.users in the countQuery.

@Entity
public class Movie {
    @Id @GeneratedValue private Long id;
    @ManyToMany
    private Set<Genre> genres;
    @ManyToMany
    private Set<User> users;

@Entity
public class Genre {
    @Id @GeneratedValue private Long id;        
    @ManyToMany
    private Set<User> users;

@Entity
public class User {
    @Id @GeneratedValue private Long id;

And the repository:

public interface MovieRepository extends JpaRepository<Movie, Long> {
    @Query(value = "SELECT movie FROM Movie movie " +
            "JOIN movie.genres genres JOIN genres.users users LEFT JOIN FETCH movie.users " +
            "WHERE users.id = :userId", 
    countQuery = "SELECT COUNT(movie) FROM Movie movie " +
            "JOIN movie.genres genres JOIN genres.users users " +
            "WHERE users.id = :userId")
    Page<Movie> getAllMoviesByFavoriteGenres(@Param("userId") Long userId, Pageable page);

and to use it:

@Override
public void run(String... args) throws Exception {
    create();
    System.out.println("something");
    Page<Movie> movies = movieRepo.getAllMoviesByFavoriteGenres(1L, PageRequest.of(0, 10));
    movies.forEach(System.out::println);
}

private void create() {
    User u1 = new User();
    userRepo.save(u1);

    Set<User> users = Collections.singleton(u1);

    Genre g1 = new Genre();
    g1.setUsers(users);
    genreRepo.save(g1);

    Set<Genre> genres = Collections.singleton(g1);

    Movie m1 = new Movie();
    m1.setGenres(genres);
    m1.setUsers(users);
    movieRepo.save(m1);
}

JOIN FETCH in spring-data-jpa does not fetch lazy associations

1 Answers1