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.

java spring

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Java Spring Boot First App

Step by Step to your First Spring App

Spring | Spring JDBC Tutorial | Java Spring Tutorial Spring Training

Spring | Spring JDBC Tutorial | Java Spring Tutorial Spring Training . Spring Framework Certification Training This post Spring JDBC Tutorial video will help you

Top 10 Advanced Java and Spring Boot Courses for Full-Stack Java Developers

These are best online courses to learn Spring Boot, Advanced Java, Docker, React, Microservices, DEvops, and Angular to become full stack Java developer.

Java Core (2020) | Java for beginners | Brush up your Java Skills | Get Hired

In this video we will learn about all the major concepts that come under Java Core . The course is very carefully designed and is made with keeping simplicit...

Spring Boot Java Tutorial - REST API using PostgreSQL and JWT

Learn how to create a Rest API using Spring Boot. Learn to use Spring Boot and Java to create a complete REST API. We will use PostgreSQL as the relational database and Spring JdbcTemplate for interacting with that. Apart from this, we will add authentication using JWT (JSON Web Tokens).