JPA CriteriaQuery ManyToMany Predicate

Question

歌曲和艺人之间存在着一种多对多的关系:

public class Song {
// ... other fields here
protected Collection<Artist> artists;

@JoinTable(
name = "song_artists",
schema = "playout",
joinColumns = @JoinColumn(name = "song"),
inverseJoinColumns = @JoinColumn(name = "artist"),
foreignKey = @ForeignKey(name = "fk_song_artists_song"),
inverseForeignKey = @ForeignKey(name = "fk_audio_artists_artist")
)
@ManyToMany
public Collection<Artist> getArtists(){
return artists;
}
}

artist类是一个基本的Entity

public class Artist {
}

给定歌曲x，显示歌曲x中涉及的任何艺术家的歌曲，一个原始的SQL查询应该是这样的

SELECT * FROM songs WHERE id IN((SELECT song FROM song_artists x WHERE x.artist = ?));

"在哪里?'将被一个逗号分隔的歌手id字符串所取代

如何使用JPA(特别是使用Hibernate)实现相同的结果

Song song = null; // get desired song
Collection<Artist> artists = song.getArtists();
CriteriaBuilder builder = entityManager.getCriteriaBuilder()
CriteriaQuery criteria = builder.createQuery(Song.class);
Root<Song> root = criteria.from(Song.class);
Subquery<Artist> subquery = null; // how to create an appropriate subquery from the join

我们如何在这里过滤结果(在"艺术家"中获得任何艺术家的更多歌曲;收集)?

非常感谢您的反馈

Answer 1

解决了!

如果你遇到类似的情况，我是这样做的:

Subquery<Artist> subquery = criteria.subquery(Artist.class);
subquery.from(Artist.class);
Join<Audio, Artist> join = subquery.correlate(root.join("artists", JoinType.LEFT));
subquery.select(builder.nullLiteral(Artist.class));
subquery.where(join.in(value.getArtists()));
criteria.select(root).where(
builder.and(
builder.notEqual(root, value), builder.exists(subquery)
)
);