如何使用Spring Boot / Thymeleaf从数据库相关的多对多关系中获取交叉表数据 &g

Question

有两个表Books和Users。它们通过多对多关系连接，并且在它们之间有一个交叉表，列为book_id、reader_id、开始日期和结束日期。我需要从这个表中获取数据作为用户。登录、书籍。标题，开始日期和结束日期。如果我有以下实体，我如何从这个表中获取数据:

实体书:

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