javajpaeclipselinkjpql

How to join tables efficiently?

In Java, with JPA and EclipseLink. Lets say I have 3 tables: Supplier, Order and Customer

I want to get all customers that have an order by said supplier. Say suppliers and customers are not directly linked. They can only be linked by joining through orders:

One way to do it:

In JPA once you setup your Model/Entity you can do

EntityManager.find(Supplier.class, 1)

This will return the supplier and its orders, from there we can get to the orders and get all the customers linked to that supplier. But this way fetches all the data, while we only want the Customers, nothing else, it puts strain on the DB and the client bandwith.

What is a better JPA, JPQL way to fetch only the Customers data that is linked to a supplier? I know that I can do a raw query but that kind of defeats the purpose of JPA.

Solution

  • Assuming that you have defined a @ManyToOne relationship in Order to both Customer and Supplier, like this:

    @Entity
@Table("order")
public class Order {

   @Id
   @GeneratedValue
   private Long id;

   @ManyToOne
   @JoinColumn(name = "customer_id")
   private Customer customer;

   @ManyToOne
   @JoinColumn(name = "supplier_id")
   private Supplier supplier;
   
   ...
}

    You could have a JPQL query like this:

    List<Customer> customers = entityManager.createQuery("select distinct o.customer from Order o where o.supplier.id = :supplierId").getResultList();

    If you wanted the order data too, you could get just the Orders as follows, and then stream them to get the customers:

    List<Order> orders = entityManager.createQuery("select o from Order o where o.supplier.id = :supplierId").getResultList();

Map<Customer, List<Order>> customerOrders = orders.stream().collect(Collectors.groupingBy(Order::getCustomer));

//if you still need the customers on their own
Set<Customer> customers = customerOrders.keySet();