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 .
@Entity
@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 =
   (select max(M.PURCHASE_DATE) from MOBILE_PHONE M where M.PERSON_ID= PERSON_ID")
   private List<MobilePhone> mobilePhones;
   
   public MobilePhone getLatestMobilePhone(){
      return CollectionsUtil.isEmpty(mobilePhones) ? null: mobilePhones.get(0);
   }
}
@Entity
@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.

5 comments:

  1. Well, that's great. I am gonna implement this code at right now, I am sure this will work out. I appreciate for sharing it with us!

    ReplyDelete
  2. this is cool, but if I had a collection with cascade and orphan removal, and then call delete on the parent - will it delete all the chilren or just the filtered one ?

    @OneToMany(mappedBy = "id.lead", cascade = CascadeType.ALL, orphanRemoval = true)
    private Set leadEvents = new HashSet();

    ReplyDelete
  3. Good question from @Anonymous. Similar question is if I add an item to to cascaded collection and call merge on the parent. Hibernate is going to insert the child, but what about the filtered children, isn't hibernate going to accidentally delete them?

    ReplyDelete
  4. PERSON_ID without alias is referring to the PERSON_ID in MobilePhone. Please help

    ReplyDelete