How to map the latest child of a collection with JPA and Hibernate

Question

I have a Product entity class and I want it to join with the Price table.

My goal is to persist the old prices for reporting and when I get the Product entity it should be mapped with latest price according to the latest date.

Please explain me how I can achieve this in Hibernate JPA relation. If possible share a code snippet.

Answer 1

Your domain module can use @JoinFormula, like this:

@Entity
public class Product {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    private String name;

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "product", orphanRemoval = true)
    private List<Price> prices = new ArrayList<>();
    
    @ManyToOne
    @JoinFormula(
        "(SELECT id FROM price ORDER BY created_on DESC LIMIT 1)"
    )
    private Price latestPrice;

    public void setName(String name) {
        this.name = name;
    }

    public List<Price> getPrices() {
        return prices;
    }

    public void addPrice(BigDecimal priceValue) {
        Price price = new Price();
        price.setPrice(priceValue);
        prices.add(price);
        price.setProduct(this);
        latestPrice = price;
    }
    
    public Price getLatestPrice() {
        return latestPrice;
    }
}

@Entity(name = "Price")
public class Price {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @ManyToOne
    private Product product;
    
    @Column(name = "created_on", nullable=false, updatable=false)
    private Date createdOn;

    private BigDecimal price;

    public void setProduct(Product product) {
        this.product = product;
    }

    public BigDecimal getPrice() {
        return price;
    }

    public void setPrice(BigDecimal price) {
        this.price = price;
    }
    
    @PrePersist
    public void prePersist() {
        createdOn = new Date();        
    }
}

This is how you'd update a Product's price:

Long id = ...;
BigDecimal newPriceValue = ...;

Product product = entityManager.find(Product, id);
Price oldPrice = product.getLatestPrice();    

product.addPrice(newPriceValue);

Answer 2

I really like the answer of @vlad-mihalcea, unfortunately I also need to support Oracle. I ended up with a less elegant solution but it works with Oracle:

...
    @ManyToOne
    @JoinFormula(
        "(SELECT p.id FROM price p WHERE p.product_id = id and p.created_on = (select max(p2.created_on) from price p2 where p2.product_id = id))"
    )
    private Price latestPrice;
...