How to join results of multiple tables in Spring JPA repository

How to join results of multiple tables in Spring JPA repository

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.

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

}

Booking Service class:

@Service
public class BookService {
    @Autowired
    private BookRepository bookRepository;

// 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);

} }

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&lt;Booking&gt; queryBy(@Param("phone") String phone,
                        @Param("authors") List&lt;String&gt; 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.

Angular 9 Tutorial: Learn to Build a CRUD Angular App Quickly

What's new in Bootstrap 5 and when Bootstrap 5 release date?

Brave, Chrome, Firefox, Opera or Edge: Which is Better and Faster?

How to Build Progressive Web Apps (PWA) using Angular 9

What is new features in Javascript ES2020 ECMAScript 2020

Spring Boot Tutorials - Spring Boot Full Course

Spring Boot Tutorials | Full Course - What is Spring? Spring Boot is an open source Java-based framework used to create a Micro Service. Spring Boot contains a comprehensive infrastructure support for developing a micro service and enables you to develop enterprise-ready applications that you can “just run”.

Java Spring - Limiting Query Result with Spring Data JPA

Java Spring - Limiting Query Result with Spring Data JPA

Full-stack Reactive Java with Spring Framework, Spring Boot and Project Reactor

Reactive programming offers Java developers a way to build message-driven, elastic, resilient, and responsive services...yet many Java developers don't know where to begin.