mysql - 春季 JPA | 在多对多关系中搜索

问题描述

我有 2 个表Book，并且Author在多对多关系中使用一个候选键表author_book，该表具有author和的主键Book。

我正在尝试从给定作者那里获取所有书籍AUTHOR_ID，也许在它有效之后我会尝试按名称搜索

create table author_book(
    `AUTHOR_ID` INT,
    `BOOK_ID` INT
);
`
create table author(
    `AUTHOR_ID` int auto_increment,
    `FIRST_NAME` varchar(255),
    `LAST_NAME` varchar(255),
    `EMAIL` varchar(30),
    `CONTACT_NO` VARCHAR(10),
    primary key(AUTHOR_ID)
);
create table book(
    `BOOK_ID` int auto_increment,
    `TITLE` varchar(255),
    `SUBJECT` varchar(255),
    `PUBLISHED_YEAR` int,
    `ISBN` varchar(30),
    `QUANTITY` int,
    `SHELF_DETAILS` varchar(255),    
    `PUBLISHER_ID` int,
    `BOOK_COST` INT,
    primary key(BOOK_ID)
);

我决定AUTHOR_NAME使用CONCAT

我尝试使用此查询按 author_id 进行搜索

@Query("select b from Books b where b.bookId in (select a.bookId from author_book a where a.author_id = :Id)")

但是我收到了author_book未映射的错误，所以我需要为这个类创建另一个实体来执行这两个类的任何操作吗？我也不确定嵌套查询是否适用于 JPQL，这基本上是一种预感。

我的Books实体

@Entity
@Table(name = "book")
public class Books implements Serializable{
    private static final long serialVersionUID = 1L;
    
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="BOOK_ID")
    private int bookId;
    
    @Column(name="TITLE")
    private String title;
    
    @Column(name="SUBJECT")
    private String subject;
    
    @Column(name="PUBLISHED_YEAR")
    private int publishedYear;
    
    @Column(name="ISBN")
    private String isbn;
    
    @Column(name="QUANTITY")
    private int quantity;
    
    @Column(name="SHELF_DETAILS")
    private String shelfDetails;
    
    @Column(name="BOOK_COST")
    private int bookCost;
    
    @JsonIgnore
    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "author_book", 
            joinColumns = { @JoinColumn(name = "BOOK_ID") }, 
            inverseJoinColumns = { @JoinColumn(name = "AUTHOR_ID") })
    private Set<Authors> authors = new HashSet<Authors>();
    
    @JsonIgnore
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name="PUBLISHER_ID")
    private Publishers publisher;

// getters and setters
}

我的Authors实体

@Entity
@Table(name="author")
public class Authors {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="AUTHOR_ID")
    private int authorId;
    
    @Column(name="FIRST_NAME")
    private String firstName;
    
    @Column(name="LAST_NAME")
    private String lastName;
    
    @Column(name="EMAIL")
    private String email;
    
    @Column(name="CONTACT_NO")
    private String contactNo;
    
    @JsonIgnore
    @ManyToMany(fetch = FetchType.LAZY, mappedBy = "authors")
    private Set<Books> books = new HashSet<Books>();

//  Getters and setters
}

标签： mysqlormspring-data-jpajpql

mysql - 春季 JPA | 在多对多关系中搜索

问题描述

解决方案

推荐阅读