Spring JPARepository查询多对多交集表

Question

我有三个实体类，如下所示（示例取自https://hellokoding.com/jpa-many-to-many-extra-columns-relationship-mapping-example-with-spring-boot-maven-and-mysql/）。 Book类。

@Entity
public class Book{
    private int id;
    private String name;
    private Set<BookPublisher> bookPublishers;

    public Book() {
    }

    public Book(String name) {
        this.name = name;
        bookPublishers = new HashSet<>();
    }

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    @OneToMany(mappedBy = "book", cascade = CascadeType.ALL, orphanRemoval = true)
    public Set<BookPublisher>   getBookPublishers() {
        return bookPublishers;
    }

    public void setBookPublishers(Set<BookPublisher> bookPublishers) {
        this.bookPublishers = bookPublishers;
    }
}

出版商类。

@Entity
public class Publisher {
    private int id;
    private String name;
    private Set<BookPublisher> bookPublishers;

    public Publisher(){

    }

    public Publisher(String name){
        this.name = name;
    }

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    @OneToMany(mappedBy = "publisher")
    public Set<BookPublisher> getBookPublishers() {
        return bookPublishers;
    }

    public void setBookPublishers(Set<BookPublisher> bookPublishers) {
        this.bookPublishers = bookPublishers;
    }
}

交叉表

@Entity
@Table(name = "book_publisher")
public class BookPublisher implements Serializable{
    private Book book;
    private Publisher publisher;
    private Date publishedDate;

    @Id
    @ManyToOne
    @JoinColumn(name = "book_id")
    public Book getBook() {
        return book;
    }

    public void setBook(Book book) {
        this.book = book;
    }

    @Id
    @ManyToOne
    @JoinColumn(name = "publisher_id")
    public Publisher getPublisher() {
        return publisher;
    }

    public void setPublisher(Publisher publisher) {
        this.publisher = publisher;
    }

    @Column(name = "published_date")
    public Date getPublishedDate() {
        return publishedDate;
    }

    public void setPublishedDate(Date publishedDate) {
        this.publishedDate = publishedDate;
    }
}

我想查询两件事情：

1. 获取属于特定出版商的书籍列表，例如获取与出版商100相关联的所有书籍
2. 获取未与特定出版商相关联的书籍列表，例如获取所有未与出版商100相关联的书籍

如果可能的话，我想使用简单的JPARepository查询来实现，比如findByXYZIn(...)等等。

请告诉我是否可以使用JPA存储库查询查询多对多关系，如果可以，是否可以直接进行，还是需要对实体类进行任何更改。

- Avi

Answer 1

在BookRepository中

获取出版商的书籍

findBooksByBookPublishersPublisherId(Long publisherId)

获取非该出版商所出版的书籍

findBooksByBookPublishersPublisherIdNot(Long publisherId)

在我看来，对于您的情况，Publication是比BookPublisher更合适的名称，因为Publisher本身可以是BookPublisher（出版书籍的出版商）

- Antoniossss

---

太好了，这正是我在寻找的。只是对JPA方法的命名约定感到困惑。谢谢。 - Avi

Answer 2

你可以使用命名查询来满足你的需求：

@Query("select b from Book b where b.publisher.idd = ?1")
Book findByPublisherId(int id);

@Query("select b from Book b where b.publisher.idd <> ?1")
Book findByDifferentPublisherId(int id);

请查看使用@Query Spring 文档以获取更多细节。

- cнŝdk

Answer 3

我不确定您是否可以仅通过方法名称实现它。但是您肯定可以使用JPA查询。类似于这样："SELECT b FROM Book b JOIN b.bookPublishers bp JOIN bp.publisher p WHERE p.id = ?1"。对于第二种情况，可以使用不等于符号。

- VDanyliuk