How to join results of multiple tables in Spring JPA repository

Question

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.

My Entity Classes - Book, Customer, Bookings

---

Book.java - books available in the library

@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

@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

@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 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 one-to-one associations in my Booking class:

@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:

[     {         "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.

Answer 1

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.

First you need to separate what you will deserialize as JSON and what you will use as persistence layer towards your spring data.

1. Make a @OneToOne/@OneToMany relationship from Booking to Book as a start.
2. Change your query to do eager fetching on the entity/collection you have mapped as Book.
3. Make a POJO and annotate it with JSON annotations the way you want it to be returned by the controller.
4. Map between your persistence object / Booking with hidrated collection on Book and your newly created POJO

Actualy if you map as OneToOne the default initialization becomes EAGER so your query becomes a bit 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