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
– Lajos Arpad, Commented May 14, 2019 at 16:29
@LajosArpad It’s a typo, otherwise I would get hibernate exception — Sergii Bishyr
– Sergii Bishyr, Commented May 14, 2019 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
– Lajos Arpad, Commented May 14, 2019 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
– Sergii Bishyr, Commented May 14, 2019 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
– Lajos Arpad, Commented May 14, 2019 at 16:50

K.Nicholas · Accepted Answer · 2019-05-14 18:40:29Z

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);
}

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related