How to JOIN two Entities without Mapped Relationship

blog-post-img

Entity relationships are a cornerstone of JPA, but sometimes you might encounter situations where you need to join entities that don’t have a pre-defined mapping. Fortunately, JPA 2.1 provides a powerful feature called JOIN ON to join unrelated entities.

Joining Unmapped Entities

Often you don’t want to map all relationships in an entity model. Let’s have a look at this class diagram:

There is no mapping between PurchaseOrder and PurchaseOrderItem because there is no use case for this relationship. We want to read all orders or display all items of an order, but we never want to have all orders with all items.

We have a foreign key on the database that stores the PurchaseOrder ID on the PurchaseOrderItem table. So we map the purchaseOrderId in the PurchaseOrderItem entity:

@Entity
public class PurchaseOrderItem {

    @Id
    @GeneratedValue
    private Integer id;
    private Integer purchaseOrderId;
    @ManyToOne
    private Product product;

// Getters/Setters and more ...
}

Now if we need to join the two tables anyway, for example, in an aggregation to get the sum of the item price, we can do that because of the JPA 2.1 extension JOIN ON:

SELECT NEW entity.PurchaseOrderInfo(p.id, sum(i.product.price))
FROM PurchaseOrder p
JOIN PurchaseOrderItem i ON p.id = i.purchaseOrderId
GROUP BY p.id

This is supported in EclipseLink and Hibernate >= 5.1.

Conclusion

JPA 2.1’s JOIN ON empowers you to join unrelated entities for complex queries, even without mapped relationships. Remember that using JOIN ON requires careful consideration of performance implications, especially for large datasets. Always strive to optimize your queries and ensure they efficiently retrieve the desired data.

Simon Martinelli
Follow Me