July 20, 2010

Hibernate @OneToMany annotation filter by Latest date

When there is a @OneToMany relationship between domain objects, a common requirement is to fetch the latest child record based on some date column or a version number column. This can be easily implemented in Hibernate using @Filter annotation and some SQL as shown below.

Consider a case where a Person has more than one Mobile phone and he always uses the latest mobile phone.

Person object has a @OneToMany relationship to MobilePhone object .
@Table(name = "PERSON")
@FilterDef(name = Person.LATEST_PHONE_FILTER)
public class Person{
   public static final String LATEST_PHONE_FILTER = "latestPhoneFilter";

   @Column(name = "PERSON_ID")
   private Long id;

   @Column(name = "FIRST_NAME")   
   private String firstName;

   @Column(name = "LAST_NAME")
   private String lastName;

   @OneToMany(mappedBy = "owner", fetch = FetchType.EAGER)
   @Filter(name = LATEST_PHONE_FILTER, condition = "PURCHASE_DATE =
   private List<MobilePhone> mobilePhones;
   public MobilePhone getLatestMobilePhone(){
      return CollectionsUtil.isEmpty(mobilePhones) ? null: mobilePhones.get(0);
@Table(name = "MOBILE_PHONE")
public class MobilePhone{    
    @Column(name = "PURCHASE_DATE")
    private Date purchaseDate;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "PERSON_ID")
    private Person owner;

If you notice the SQL used in the filter, it has a condition like  M.PERSON_ID= PERSON_ID.
PERSON_ID column without alias name refers to the PERSON tables primary key. Hibernate takes care of giving proper alias name while generating SQL.

This configuration will always fetch only the latest mobile phone to the List of MobilePhone and the getLatestMobilePhone() method can return that object.