CODEWITHSUNDEEP
sqlspringjpaspring-data
Senio Vak
Senio Vak

Reputation: 87

Spring Data JPA - Many to many query

I have two entities Person and Movie. 

@Entity
public class Person {
..some fields

@ManyToMany(fetch = FetchType.LAZY, mappedBy = "actors")
@OrderBy("id")
private Set<Movie> moviesActor = new TreeSet<>();

}

@Entity
public class Movie {
..fields
@JoinTable(name = "actor_movie",
            joinColumns = { @JoinColumn(name = "movie_id") },
            inverseJoinColumns = { @JoinColumn(name = "actor_id") })
    private Set<Person> actors = new TreeSet<>();
}

There is many to many relationship so there is new table actor_movie to keep it. And how can I get every person that has any movie in its set? So what I want is to achieve is get every person that exists in actor_movie table. I tried used Spring data jpa but couldn't find right query.

Upvotes: 3

Views: 9709

Answers (4)

Ghasem Sadeghi
Ghasem Sadeghi

Reputation: 1854

Best Practices in entity relations:

  1. Always use fetch = FetchType.LAZY.
  2. When you want to fetch another side of the relation too, use JOIN FETCH Query.
    This resolves LazyInitializationException of hibernate also.
  3. Always use spring.jpa.open-in-view=false

Example:
By Spring Data JPA with Hibernate as JPA Provider.

Entities:

public class Blog{
   ...
   @ManyToMany(fetch = FetchType.LAZY) //default is LAZY in ManyToMany
    @JoinTable(name="blog_tag",
        joinColumns = @JoinColumn(name = "blog_id"),
        inverseJoinColumns = @JoinColumn(name = "tag_id"))
    @OrderBy //order by tag id
    private Set<Tag> tags = new HashSet<>();

    //2 utility methods in owner side
    public void addTag(Tag tag){
        tags.add(tag);
        tag.getBlogs().add(this);
    }
    public void removeTag(Tag tag){
        tags.remove(tag);
        tag.getBlogs().remove(this);
    }
    
    //override equals & hashcode 

}


public class Tag {
    ...
    @ManyToMany(mappedBy = "tags")
    private Set<Blog> blogs = new HashSet<>();

    //override equals & hashcode 
}

Now suppose, you want to fetch a Blog containing Tag items:

Repository:

@Repository
public interface BlogRepository extends JpaRepository<Blog, Long> {
    @Query("select b from Blog b join fetch b.tags where b.name = :name")
    Blog getBlog(@Param("name") String blogName);
}

service:

public interface BlogService {
    Blog getBlog(String blogName);
}

@Service
public class BlogServiceImpl implements BlogService{

    @Autowired
    private BlogRepository blogRepository;

    @Override
    public Blog getBlog(String blogName) {
        return blogRepository.getBlog(blogName);
    }

}

Upvotes: 2

AdagioDev
AdagioDev

Reputation: 566

You can use join directely :

@Query("SELECT p FROM Person p  JOIN  p.moviesActor movie");

List findPersonHasMovie();

Upvotes: 1

Carlos N.
Carlos N.

Reputation: 1307

Since you are using Fetch type lazy, you need to use join fetch to get moviesActor.

You can use jpql with spring data. I have not tested the queries below, but should work.

public interface PersonRepository extends JpaRepository<Person, Long> { //Long if Person.id is of type Long

  @Query("SELECT p FROM Person p LEFT JOIN FETCH p.moviesActor WHERE size(p.moviesActor) > 0");
  List<Person> findActors1();

  // Or

  @Query("SELECT p FROM Person p JOIN FETCH p.moviesActor");
  List<Person> findActors2();

}

More about jpql size() operator here: https://www.thoughts-on-java.org/jpql/

Upvotes: 1

Dherik
Dherik

Reputation: 19120

You only need a single JOIN between Person and Movie. As Hibernate abstracts the existence of the middle table, you don't need to worry about it.

So, with Spring Data Repository:

class PersonRepository extends CrudRepository<Person, Long> {

    List<Person> findByMoviesActor();
}

With Jpql:

SELECT person FROM Person person JOIN person.moviesActor movie

Upvotes: 1

Related Questions