将使用多个关系的SQL查询转换为JPQL查询

尽管您提供了SQL查询,我还是重新考虑了您的需求并将其转换为JPQL。到目前为止我所了解的你 . 如果这是正确的,您可以通过

SELECT m FROM Movie m JOIN m.genres mg 
WHERE mg.id IN 
         (SELECT g.id FROM Genre g JOIN g.movies gm WHERE gm.id = :movieId) 
AND m.id <> :movieId 
GROUP BY m ORDER BY count(*) DESC

这将生成如下SQL

 select distinct movie0_.id as id1_1_, movie0_.name as name2_1_ 
 from movie movie0_ inner join movie_genre genres1_ on movie0_.id=genres1_.movie_id inner join genre genre2_ on genres1_.genre_id=genre2_.id 
 where (genre2_.id in 
            (select genre3_.id from genre genre3_ inner join movie_genre movies4_ on genre3_.id=movies4_.genre_id inner join movie movie5_
             on  movies4_.movie_id=movie5_.id where movie5_.id=?)) 
 and movie0_.id <> ? 
 group by movie0_.id order by count(*) desc

在JPQL中查询集合关联

ManyToMany 关联,因此您没有具有映射实体的联接表,您需要与集合字段联接。JPA供应商会自动将其转换为与join表连接。

就像你在质疑 某些类型的电影 ,这个就可以了

 SELECT m FROM Movie m JOIN m.genres mg WHERE mg.id = :genreId 

movie_genre

这里公平地讨论了这种情况

The best way to map a many-to-many association with extra columns when using JPA and Hibernate

编写JPQL语句不需要模型电影类型。Hibernate在执行JPQL语句时隐式地知道这一点。

SELECT m from Movie m left join m.genres g where g.name = 'Action'

双向“m.genres g”适用于所有双向JPQL语句,包括关联模型是隐式的而不是实际存在的多对多语句。

示例代码:

@Entity
@Table(name = "MOVIE")
public class Movie {

@Id
@GeneratedValue
private Long id;

@Column
private String name;

@ManyToMany(cascade = {CascadeType.ALL})
@JoinTable(name = "movie_genre",
        joinColumns = @JoinColumn(name = "movie_id"),
        inverseJoinColumns = {@JoinColumn(name = "genre_id")}
)
private Set<Genre> genres = new HashSet<>();

public Long getId() {
    return id;
}

public void setId(Long id) {
    this.id = id;
}

public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}


public Set<Genre> getGenres() {
    return genres;
}

public void setGenres(Set<Genre> genres) {
    this.genres = genres;
}
}

@Entity
@Table(name = "GENRE")
public class Genre {

@Id
@GeneratedValue
private Long id;

@Column
private String name;


@ManyToMany(mappedBy = "genres", cascade = {CascadeType.ALL})
private Set<Movie> movies = new HashSet<>();

public Long getId() {
    return id;
}

public void setId(Long id) {
    this.id = id;
}

public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}

public Set<Movie> getMovies() {
    return movies;
}

public void setMovies(Set<Movie> movies) {
    this.movies = movies;
}
}

    @PersistenceContext
EntityManager entityManager;

@Test
@Transactional
public void test() {
    Set<Movie> actionMovies = new HashSet<>();
    Set<Movie> dramaMovies = new HashSet<>();
    Set<Genre> dramaGenres = new HashSet<>();
    Set<Genre> actionGenres = new HashSet<>();
    Set<Genre> generes = new HashSet<>();

    Movie actionMovie = new Movie();
    actionMovie.setName("Batman");
    actionMovies.add(actionMovie);

    Movie dramaMovie = new Movie();
    dramaMovie.setName("Forest Gump");
    dramaMovies.add(dramaMovie);

    Genre actionGenre = new Genre();
    actionGenre.setName("Action");
    generes.add(actionGenre);
    actionGenres.add(actionGenre);

    Genre dramaGenre = new Genre();
    dramaGenre.setName("Drama");
    generes.add(dramaGenre);
    dramaGenres.add(dramaGenre);

    //Bidirectional sets
    actionGenre.setMovies(actionMovies);
    dramaGenre.setMovies(dramaMovies);
    actionMovie.setGenres(actionGenres);
    dramaMovie.setGenres(dramaGenres);

    genreRepository.saveAll(generes);

    //Example JPQL join through not present association model.
    Query query = entityManager.createQuery("SELECT m from Movie m left join m.genres g where g.name = 'Action'");
    List<Movie> resultList = query.getResultList();
    assertEquals("Batman",resultList.get(0).getName());


}