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.

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