spring-data-jpahibernate-onetomany

JPA join query on one-to-many association

I have a one-to-many relationship with Customer and Order entity. This is of a composition relationship. I want to write a query to find out if there is a matching Order with the given orderId and the customerId. This method should be in the CustomerRepository

    @Query("select c from Customer c inner join c.orders o where c.id= ?1 and c.orders.")
    Optional<Customer> findCustomerByOrderId(long orderId, long customerId);

I am interested in knowing, if the orderid belongs to the customer. What should be the query and should the method return a count or a boolean from the performance point of view.

Solution

  • If you have an Order entity with an id property and customerId property, you can use spring JPA magic and add a method to your OrderRepository (if you have one):

    boolean existsByIdAndCustomerId(long orderId, long customerId)

    Performance will be better than getting the customer entity, since it requires less data transfer from the DB to your application.

    You can also share more detailed code if this does not answer your question.

    Your @Query can be updated as follows to get the customer

    @Query("select c from Customer c inner join c.orders o where c.id = :customerId and o.id = :orderId")
Optional<Customer> findCustomerByOrderId(long orderId, long customerId);

    Or if you want to return a count

    @Query("select count(c) from Customer c inner join c.orders o where c.id = :customerId and o.id = :orderId")
int countCustomerByOrderId(long orderId, long customerId);