title: How to join results of multiple tables in Spring JPA repository
body: <p>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.</p>
  <p>My Entity Classes - Book, Customer, Bookings</p>
  <hr />
  <p>Book.java - books available in the library</p>
  <pre><code>@Entity
  @Table(name = &quot;books&quot;)
  public class Book {
  
      @Id
      @GeneratedValue(strategy = GenerationType.IDENTITY)
      @Column(name = &quot;id&quot;, columnDefinition = &quot;int&quot;)
      private int id;
  
      @NotNull(message = &quot;Book name cannot be null&quot;)
      @Column(name = &quot;book_name&quot;, columnDefinition = &quot;VARCHAR(255)&quot;)
      private String bookName;
  
      @Column(name = &quot;author&quot;, columnDefinition = &quot;VARCHAR(255)&quot;)
      private String author;
  
      // getters and setters
  
      public Book() {}
  
      public Book(String bookName, String author) {
          this.bookName = bookName;
          this.author = author;
      }
  }
  </code></pre>
  <p>Customer.java - Customers registered in the library</p>
  <pre><code>@Entity
  @Table(name = &quot;customer&quot;, uniqueConstraints = {@UniqueConstraint(columnNames = {&quot;phone&quot;})})
  public class Customer {
      @Id
      @GeneratedValue(strategy = GenerationType.IDENTITY)
      @Column(name = &quot;id&quot;, columnDefinition = &quot;int&quot;)
      private int id;
  
      @NotNull(message = &quot;Customer name cannot be null&quot;)
      @Column(name = &quot;name&quot;, columnDefinition = &quot;VARCHAR(255)&quot;)
      private String name;
  
      @Column(name = &quot;phone&quot;, columnDefinition = &quot;VARCHAR(15)&quot;)
      private String phone;
  
      @Column(name = &quot;registered&quot;, columnDefinition = &quot;DATETIME&quot;)
      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;
      }
  }
  </code></pre>
  <p>Booking.java - All the bookings made by the customers</p>
  <pre><code>@Entity
  @Table(name = &quot;bookings&quot;)
  public class Booking {
      @Id
      @GeneratedValue(strategy = GenerationType.IDENTITY)
      @Column(name = &quot;id&quot;, columnDefinition = &quot;int&quot;)
      private int id;
  
      @NotNull(message = &quot;Book id cannot be null&quot;)
      @Column(name = &quot;book_id&quot;, columnDefinition = &quot;int&quot;)
      private int bookId;
  
      @NotNull(message = &quot;Customer id cannot be null&quot;)
      @Column(name = &quot;customer_id&quot;, columnDefinition = &quot;int&quot;)
      private int customerId;
  
      @Column(name = &quot;issue_date&quot;, columnDefinition = &quot;DATETIME&quot;)
      private String issueDate;
  
      @Column(name = &quot;return_date&quot;, columnDefinition = &quot;DATETIME&quot;)
      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;
      }
  }
  </code></pre>
  <p>Now the table schemas for the respective entities are as follows:</p>
  <pre>
  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
  
  </pre>
  <p>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.</p>
  <p>Booking Controller:</p>
  <pre><code>@RestController
  @RequestMapping(&quot;/bookings&quot;)
  public class BookingController {
      @Autowired
      BookingService bookingService;
  
      // some booking apis which return Booking objects
  
      @GetMapping
      public List&lt;Booking&gt; getAllBookingsBy(@RequestParam(&quot;phone&quot;) String phone,
                                           @RequestParam(&quot;authors&quot;) List&lt;String&gt; authors) {
          return bookingService.getAllBy(phone, authors);
      }
  
      @PostMapping
      public Booking addBooking(@RequestBody Booking booking) {
          bookingService.saveBooking(booking);
          return booking;
      }
  }
  </code></pre>
  <p>Booking Service class:</p>
  <pre><code>@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&lt;Booking&gt; getAllBy(String phone, List&lt;String&gt; authors) {
      return bookingRepository.queryBy(phone, authors);
      }
  
      public void saveBooking(Booking booking) {
          bookingRepository.save(booking);
      }
  }
  </code></pre>
  <p>Booking Repository Class:</p>
  <pre><code>@Repository
  public interface BookingRepository extends JpaRepository&lt;Booking, Integer&gt; {
      // some booking repository methods
  
      @Query(value = &quot;SELECT * FROM bookings bs WHERE &quot; +
              &quot;EXISTS (SELECT 1 FROM customer c WHERE bs.customer_id = c.id AND c.phone = :phone) &quot; +
              &quot;AND EXISTS (SELECT 1 FROM books b WHERE b.id = bs.book_id AND b.author IN :authors)&quot;,
              nativeQuery = true)
      List&lt;Booking&gt; queryBy(@Param(&quot;phone&quot;) String phone,
                              @Param(&quot;authors&quot;) List&lt;String&gt; authors);
  }
  </code></pre>
  <p>Now hitting the shown booking controller 'll return a booking object which looks like this :</p>
  <pre><code>[
      {
          &quot;id&quot;: 3,
          &quot;book_id&quot;: 5,
          &quot;customer_id&quot;: 2,
          &quot;issue_date&quot;: &quot;2019-02-04 01:45:21&quot;,
          &quot;return_date&quot;: null
      }
  ]
  </code></pre>
  <p>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:</p>
  <pre><code>[
      {
          &quot;id&quot;: 3,
          &quot;book_id&quot;: 5,
          &quot;customer_id&quot;: 2,
          &quot;issue_date&quot;: &quot;2019-02-04 01:45:21&quot;,
          &quot;return_date&quot;: null,
          &quot;customer_name&quot;: &quot;Cust 2&quot;,
          &quot;book_name&quot;: &quot;Book_2_2&quot;,
      }
  ]
  </code></pre>
  <p>Can someone please help in doing this? I'm stuck as I'm unable to proceed from here.</p>
  <p>###################
  EDIT:
  I added these unidirectional one-to-one associations in my Booking class:</p>
  <pre><code>@OneToOne
  @JoinColumn(name = &quot;book_id&quot;, insertable = false, updatable = false)
  private Book book;
  
  @OneToOne
  @JoinColumn(name = &quot;customer_id&quot;, insertable = false, updatable = false)
  private Customer customer;
  </code></pre>
  <p>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:</p>
  <pre><code>[
      {
          &quot;id&quot;: 3,
          &quot;book_id&quot;: 5,
          &quot;book&quot;: {
              &quot;id&quot;: 5,
              &quot;book_name&quot;: &quot;Book_2_2&quot;,
              &quot;author&quot;: &quot;author_2&quot;
          },
          &quot;customer_id&quot;: 2,
          &quot;customer&quot;: {
              &quot;id&quot;: 2,
              &quot;name&quot;: &quot;Cust 2&quot;,
              &quot;phone&quot;: &quot;98765431&quot;,
              &quot;registered&quot;: &quot;2019-02-04 01:13:16&quot;
          },
          &quot;issue_date&quot;: &quot;2019-02-04 01:45:21&quot;,
          &quot;return_date&quot;: null
      }
  ]
  </code></pre>
  <p>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.</p>