How to JOIN two Entities without Mapped Relationship

Often you don’t want to map all relationships in an entity model Often you don’t want to map all relationships

blog-post-img

Often you don’t want to map all relationships in an entity model

Often you don’t want to map all relationships in an entity model (a blog post about this topic in detail will follow).

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. Either we want to read all orders or we want to display all items of an order but we never want to have all orders with all items.

On the database we have for sure the foreign key that stores the PurchaseOrder ID on the PurchaseOrderItem table. So we just map the purchaseOrderId in the PurchaseOrderItem entity:


@Entity
public class PurchaseOrderItem {

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

Now if we need to join the two tables anyway for example in a 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.

Programming Architect at 72® Services
Simon Martinelli ist ein versierter Experte für Java, Leistungsoptimierung, Anwendungsintegration, Softwarearchitektur und Systemdesign mit 27 Jahren Erfahrung als Entwickler, Architekt und technischer Projektmanager. Kontaktieren Sie mich hier oder buchen Sie einen Beratungstermin über Calendly.
Latest posts by Simon Martinelli (see all)
Simon Martinelli
Programming Architect 72® Services
Simon Martinelli is an accomplished expert in Java, performance optimization, application integration, software architecture and system design with 27 years of experience as a developer, architect and technical project manager. Contact me here or book a consulting appointment via Calendly.