如何使用Spring启动/百里叶从数据库相关的许多关系中从交叉表中获取数据

Question

有两个表Books和User。它们通过多对多关系连接，并在它们之间使用列book_id、reader_id、开始日期和结束日期的交叉表。我需要将此表中的数据User.login、Books.title、start_date和end_date。如果我有以下实体，如何从此表中获取数据：

图书实体:

@Entity
@Table(name = "books")
public class Book {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id")
private Long id;

@Column(name = "title")
private String title;

@Column(name = "publishing_year")
private Integer publishingYear;

@Column(name = "sell_cost")
private BigDecimal sellCost;

@Column(name = "rent_cost")
private BigDecimal rentCost;

@Column(name = "amount")
private Integer amount;

//=======Relationships=======

@ManyToMany(cascade = {CascadeType.DETACH,CascadeType.MERGE,CascadeType.PERSIST,CascadeType.REFRESH,CascadeType.REMOVE})
@JoinTable(name = "rented_books",
joinColumns = @JoinColumn(name = "book_id"),
inverseJoinColumns = @JoinColumn(name = "reader_id"))
private List<User> usersRented;
}

用户实体：

@Entity
@Table(name = "users")
public class User {

@Id
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

@Column(name = "username")
private String login;

@Column(name = "password")
private String password;

@Column(name = "name")
private String name;

@Column(name = "surname")
private String surname;

@Column(name = "wallet")
private Integer wallet;

@Column(name = "enabled")
private boolean enabled;

//=======Relationships=======

@ManyToMany(mappedBy = "usersRented")
private List<Book> rentedBooks;

图书库

@Repository
public interface BookRepository  extends CrudRepository<Book,Long> {

@Query(value = "SELECT b.title,rb.start_date,rb.expiration_date FROM books b INNER JOIN rented_books rb ON rb.book_id = b.id INNER JOIN users u ON u.id = rb.reader_id WHERE u.id = ?1",nativeQuery = true)
Page<Book> findAllRentedBookByUser(Long id, Pageable pageable);

}

但此查询不起作用，抛出此错误：

java.sql.SQLException：未找到列id。

尽管在 DBMS 中此查询工作正常

Answer 1

您将返回 Book 实体，因此需要为该实体选择所有字段。不能返回部分填充的实体，因为 JPA 不支持从本机查询填充部分实体。
根据 JPA 规范：

3.10.16.1从本机查询返回托管实体

当要从本机查询返回实体时，SQL语句应该选择映射到实体对象的所有列。这应该包括相关实体的外键列。当可用数据不足时获得的结果是未定义的

正确查询示例：

@Repository
public interface BookRepository  extends CrudRepository<Book,Long> {
    @Query(value = "SELECT b.* FROM books b INNER JOIN rented_books rb ON rb.book_id = b.id INNER JOIN users u ON u.id = rb.reader_id WHERE u.id = ?1", nativeQuery = true)
    Page<Book> findAllRentedBookByUser(Long id, Pageable pageable);
}

如果要返回自定义列集，则需要创建一个额外的简单POJO类并为其定义映射
示例：如何修复Spring boot中Nativequery中的转换错误

1.创建自定义POJO类

import java.util.Date;

public class BookDetails {
   private String title;
   private Date start_date;
   private Date expiration_date;

   public BookDetails(String title, Date start_date, Date expiration_date) {
        this.title = title;
        this.start_date = start_date;
        this.expiration_date = expiration_date;
    }

    public String getTitle() {
        return this.title;
    }

    public Date getStart_date() {
        return this.start_date;
    }

    public Date getExpiration_date() {
        return this.expiration_date;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public void setStart_date(Date start_date) {
        this.start_date = start_date;
    }

    public void setExpiration_date(Date expiration_date) {
        this.expiration_date = expiration_date;
    }
}

2. 为 POJO 类定义@NamedNativeQuery和映射

import javax.persistence.*;
import java.math.BigDecimal;
import java.util.List;

@Entity
@Table(name = "books")
@NamedNativeQuery(name ="BookDetailsByUser",
        query =
                " SELECT b.title, rb.start_date, rb.expiration_date " +
                " FROM books b INNER JOIN rented_books rb ON rb.book_id = b.id INNER JOIN users u ON u.id = rb.reader_id " +
                " WHERE u.id = ?1 ",
        resultSetMapping = "BookDetailsMapping"
)
@SqlResultSetMapping(name="BookDetailsMapping",
        classes = {
                @ConstructorResult(targetClass = BookDetails.class,
                        columns = {@ColumnResult(name = "title"),
                                   @ColumnResult(name = "start_date"),
                                   @ColumnResult(name = "expiration_date")
                        })
        })
public class Book {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @Column(name = "title")
    private String title;

    @Column(name = "publishing_year")
    private Integer publishingYear;

    @Column(name = "sell_cost")
    private BigDecimal sellCost;

    @Column(name = "rent_cost")
    private BigDecimal rentCost;

    @Column(name = "amount")
    private Integer amount;

//=======Relationships=======

    @ManyToMany(cascade = {CascadeType.DETACH,CascadeType.MERGE,CascadeType.PERSIST,CascadeType.REFRESH,CascadeType.REMOVE})
    @JoinTable(name = "rented_books",
            joinColumns = @JoinColumn(name = "book_id"),
            inverseJoinColumns = @JoinColumn(name = "reader_id"))
    private List<User> usersRented;
}

3.在存储库中使用命名查询

@Repository
public interface BookRepository  extends CrudRepository<Book,Long> {
    @Query(name = "BookDetailsByUser", nativeQuery = true)
    Page<BookDetails> findBookDetailsByUser(Long id, Pageable pageable);
}