Java 如何在 Spring JPA 存储库中加入多个表的结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/54507552/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to join results of multiple tables in Spring JPA repository
提问by user3248186
I'm new to Spring and I'm unable to figure out how to join multiple tables to return some result. I tried to implement a small Library application as shown below.
我是 Spring 的新手，无法弄清楚如何加入多个表以返回一些结果。我尝试实现一个小型图书馆应用程序，如下所示。
My Entity Classes - Book, Customer, Bookings
我的实体类 - 预订、客户、预订
Book.java - books available in the library
Book.java - 图书馆中的书籍
@Entity
@Table(name = "books")
public class Book {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id", columnDefinition = "int")
private int id;
@NotNull(message = "Book name cannot be null")
@Column(name = "book_name", columnDefinition = "VARCHAR(255)")
private String bookName;
@Column(name = "author", columnDefinition = "VARCHAR(255)")
private String author;
// getters and setters
public Book() {}
public Book(String bookName, String author) {
this.bookName = bookName;
this.author = author;
}
}
Customer.java - Customers registered in the library
Customer.java - 在库中注册的客户
@Entity
@Table(name = "customer", uniqueConstraints = {@UniqueConstraint(columnNames = {"phone"})})
public class Customer {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id", columnDefinition = "int")
private int id;
@NotNull(message = "Customer name cannot be null")
@Column(name = "name", columnDefinition = "VARCHAR(255)")
private String name;
@Column(name = "phone", columnDefinition = "VARCHAR(15)")
private String phone;
@Column(name = "registered", columnDefinition = "DATETIME")
private String registered;
// getters and setters
public Customer() {}
public Customer(String name, String phone, String registered) {
this.name = name;
this.phone = phone;
this.registered = registered;
}
}
Booking.java - All the bookings made by the customers
Booking.java - 客户进行的所有预订
@Entity
@Table(name = "bookings")
public class Booking {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id", columnDefinition = "int")
private int id;
@NotNull(message = "Book id cannot be null")
@Column(name = "book_id", columnDefinition = "int")
private int bookId;
@NotNull(message = "Customer id cannot be null")
@Column(name = "customer_id", columnDefinition = "int")
private int customerId;
@Column(name = "issue_date", columnDefinition = "DATETIME")
private String issueDate;
@Column(name = "return_date", columnDefinition = "DATETIME")
private String returnDate;
// getters and setters
public Booking() {}
public Booking(int bookId, int customerId, String issueDate) {
this.bookId = bookId;
this.customerId = customerId;
this.issueDate = issueDate;
}
}
Now the table schemas for the respective entities are as follows:
现在各个实体的表模式如下：
books: +-----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | book_name | varchar(255) | NO | | NULL | | | author | varchar(255) | YES | | NULL | | +-----------+--------------+------+-----+---------+----------------+ id - primary key customer: +------------+--------------+------+-----+-------------------+-------------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+-------------------+-------------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(255) | NO | | NULL | | | registered | datetime | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED | | phone | varchar(15) | YES | UNI | NULL | | +------------+--------------+------+-----+-------------------+-------------------+ id - primary key bookings: +-------------+----------+------+-----+-------------------+-------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------+------+-----+-------------------+-------------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | book_id | int(11) | NO | MUL | NULL | | | customer_id | int(11) | NO | MUL | NULL | | | issue_date | datetime | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED | | return_date | datetime | YES | | NULL | | +-------------+----------+------+-----+-------------------+-------------------+ id - primary key book_id - foreign key references books.id customer_id - foreign key references customer.id
Now What I want to do is given some booking critieria like customer phone or author name etc., I want to return all the bookings related to that order. I'll show a sample Booking api to explain.
现在我想要做的是提供一些预订标准，如客户电话或作者姓名等，我想返回与该订单相关的所有预订。我将展示一个示例 Booking api 来解释。
Booking Controller:
预订控制器：
@RestController
@RequestMapping("/bookings")
public class BookingController {
@Autowired
BookingService bookingService;
// some booking apis which return Booking objects
@GetMapping
public List<Booking> getAllBookingsBy(@RequestParam("phone") String phone,
@RequestParam("authors") List<String> authors) {
return bookingService.getAllBy(phone, authors);
}
@PostMapping
public Booking addBooking(@RequestBody Booking booking) {
bookingService.saveBooking(booking);
return booking;
}
}
Booking Service class:
预订服务类：
@Service
public class BookingService {
@Autowired
private BookingRepository bookingRepository;
// some booking service methods
// get all bookings booked by a customer with matching phone number and books written by a given list of authors
public List<Booking> getAllBy(String phone, List<String> authors) {
return bookingRepository.queryBy(phone, authors);
}
public void saveBooking(Booking booking) {
bookingRepository.save(booking);
}
}
Booking Repository Class:
预订存储库类：
@Repository
public interface BookingRepository extends JpaRepository<Booking, Integer> {
// some booking repository methods
@Query(value = "SELECT * FROM bookings bs WHERE " +
"EXISTS (SELECT 1 FROM customer c WHERE bs.customer_id = c.id AND c.phone = :phone) " +
"AND EXISTS (SELECT 1 FROM books b WHERE b.id = bs.book_id AND b.author IN :authors)",
nativeQuery = true)
List<Booking> queryBy(@Param("phone") String phone,
@Param("authors") List<String> authors);
}
Now hitting the shown booking controller 'll return a booking object which looks like this :
现在点击显示的预订控制器将返回一个预订对象，如下所示：
[
{
"id": 3,
"book_id": 5,
"customer_id": 2,
"issue_date": "2019-02-04 01:45:21",
"return_date": null
}
]
But I don't want it like that, I want to return along with them the name of the customer for that booking and also the name of the book. So I want the booking objects returned by the controller to look like this:
但我不想那样，我想与他们一起返回该预订的客户姓名以及书名。所以我希望控制器返回的预订对象看起来像这样：
[
{
"id": 3,
"book_id": 5,
"customer_id": 2,
"issue_date": "2019-02-04 01:45:21",
"return_date": null,
"customer_name": "Cust 2",
"book_name": "Book_2_2",
}
]
Can someone please help in doing this? I'm stuck as I'm unable to proceed from here.
有人可以帮忙做这件事吗？我被卡住了，因为我无法从这里继续。
#EDIT: I added these unidirectional onetoone associations in my Booking class:
编辑：我在 Booking 类中添加了这些单向 onetoone 关联：
@OneToOne
@JoinColumn(name = "book_id", insertable = false, updatable = false)
private Book book;
@OneToOne
@JoinColumn(name = "customer_id", insertable = false, updatable = false)
private Customer customer;
But now when I hit my controller, I get the whole Book and Customer objects in my Booking object. So what can I do to just return the bookname and customer name in the booking object? Here's how my Booking object returned looks like now:
但是现在当我点击我的控制器时，我在我的 Booking 对象中得到了整个 Book 和 Customer 对象。那么我该怎么做才能只返回预订对象中的书名和客户名呢？这是我的 Booking 对象返回的样子：
[
{
"id": 3,
"book_id": 5,
"book": {
"id": 5,
"book_name": "Book_2_2",
"author": "author_2"
},
"customer_id": 2,
"customer": {
"id": 2,
"name": "Cust 2",
"phone": "98765431",
"registered": "2019-02-04 01:13:16"
},
"issue_date": "2019-02-04 01:45:21",
"return_date": null
}
]
Also now my save() api in my booking controller isn't working because when I'm sending an object of type Booking to it, the bookId and customerId are somehow turning up as 0, which didn't happen before I added these changes.
现在我的预订控制器中的 save() api 不起作用，因为当我向它发送一个 Booking 类型的对象时，bookId 和 customerId 不知何故变成了 0，这在我添加这些更改之前没有发生.
回答by Sharon Ben Asher
The query that you have is not the best way to join tables. A more intuitive way is like that
您拥有的查询不是连接表的最佳方式。更直观的方式是这样的
SELECT * FROM bookings
WHERE customer_id in (SELECT id FROM customer WHERE phone = :phone)
AND book_id in (SELECT id FROM books WHERE author IN :authors)
回答by Alexandar Petrov
What you do is wrong. You are returning Booking and you expect that it magicaly deserialize into an entity that contains join information like Book Name. But in your select query on the repository you have selected the Booking. The way things are at your implementation the Booking does not hold information about the Book.
你的做法是错误的。您正在返回 Booking，并且您希望它神奇地反序列化为一个包含连接信息（如 Book Name）的实体。但是在您对存储库的选择查询中，您选择了预订。在您的实现中，Booking 不包含有关 Book 的信息。
First you need to separate what you will deserialize as JSON and what you will use as persistence layer towards your spring data.
首先，您需要将将反序列化为 JSON 的内容与将用作 Spring 数据的持久层的内容分开。
- Make a
@OneToOne/@OneToManyrelationship from Booking to Book as a start. - Change your query to do eager fetching on the entity/collection you have mapped as Book.
- Make a POJO and annotate it with JSON annotations the way you want it to be returned by the controller.
- Map between your persistence object / Booking with hidrated collection on Book and your newly created POJO
- 建立从 Booking 到 Book的
@OneToOne/@OneToMany关系作为开始。 - 更改您的查询以对您映射为 Book 的实体/集合进行急切提取。
- 制作一个 POJO 并按照您希望控制器返回的方式使用 JSON 注释对其进行注释。
- 在你的持久性对象/Booking with hidrated collection on Book 和你新创建的 POJO 之间映射
Actualy if you map as OneToOne the default initialization becomes EAGER so your query becomes a bit unnessesary.
实际上，如果您映射为 OneToOne，则默认初始化将变为 EAGER，因此您的查询变得有点 unnessesary。
If we presume you have your mappings right in the persistent layer your query will look like this:
如果我们假设您在持久层中有正确的映射，您的查询将如下所示：
@Query(value = "SELECT * FROM bookings bs WHERE " +
"bs.customer.phone = :phone) " +
"AND bs.book.author IN :authors)")
Here is your mapping documentation from Hibernate> http://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#associations
这是您的 Hibernate 映射文档> http://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#associations
回答by Dheeraj Kadam
You can implement it as per the below steps.
您可以按照以下步骤实施它。
- Create a new interface with the getters for all fields that you need in response.
- In your query string inside @Query, you'll need to provide names to your columns in select. Note: These names need to be in sync with the getters you create in your interface.
- Use this interface as a return type of you repository method.
- 使用 getter 为您需要响应的所有字段创建一个新接口。
- 在@Query 中的查询字符串中，您需要为 select 中的列提供名称。注意：这些名称需要与您在界面中创建的 getter 同步。
- 将此接口用作存储库方法的返回类型。
For more info, you can refer Projections in spring data rest. https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#projections
有关更多信息，您可以参考 spring 数据休息中的投影。 https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#projections
