JPA CriteriaQuery ManyToMany谓词

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类是一个基本的实体

public class Artist {
}

给定Song x，显示Song 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

我们如何过滤这里的结果(从" artists“集合中的任何一个艺术家那里获得更多的歌曲)？

我们将非常感谢您的反馈

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