Hibernate Complex Join

Question

So we have 2 objects which each map to a db table.

public class Dispute {
  private Long disputeSeqNo;
  private List<StatusInfo> statusInfos;
}

public class StatusInfo {
  private Long statusSeqNo;
  private Date createdDt;
  private String statusCd;
  private Dispute dispute;
}

A Dispute can have 0 or more StatusInfo's associated with it.

Currently hibernate is returning all the StatusInfo objects for every Dispute. While in some cases this is desired. However I have a use case where:

• I only need to display the most recent StatusInfo for each Dispute.
• Additionally on this same display I need the ability to sort by the most recent StatusInfo.statusCd value. This sort needs to happen at the database, since my display is paginated (using hibernate pagination facilities). (It's really not an option to sort the data after retrieval as there will be many many rows of data.)

I know using just plain SQL I can do this, struggling to translate this into HQL.

Any thoughts on how to solve this with HQL? Where I still get back:

List<Dispute>

Appreciate your feedback.

Thanks!

Answer 1

The first thing you need to do is define a Hibernate filter below. You can simply add this annotation at the top of your Dispute class.

@FilterDef( name = "dispute-with-latest-statusinfo" )

Next you need to apply this named filter on your collection of StatusInfo as below:

@OneToMany( mappedBy = "dispute" )
@Filter( 
  name = "dispute-with-latest-statusinfo",
  condition = "statusinfo_id = (select max(si.statusinfo_id) from 
    StatusInfo si WHERE si.dispute_dispute_id = dispute_dispute_id")
private List<StatusInfo> statusInfos;

To satisfy your first requirement, you can obtain a list of Dispute instances with their latest StatusInfo by enabling that filter on your session and then executing a query:

session.enableFilter( "dispute-with-latest-statusinfo" );
List<Dispute> disputes = session
  .createQuery( "FROM Dispute d JOIN FETCH d.statusInfos", Dispute.class )
  .getResultList()

In order to sort your results as you asked:

session.enableFilter( "dispute-with-latest-statusinfo" );
List<Dispute> disputes = session
  .createQuery( "FROM Dispute d JOIN FETCH d.statusInfos si ORDER BY si.status DESC", Dispute.class )
  .getResultList()

Now if you don't like having to work with a List<StatusInfo> that contains a single element for these cases, you can add a transient method to help so that you can get the object or get null without having to worry with the collection semantics:

@Transient
public StatusInfo getLatestStatusInfo() {
  if ( statusInfos.isEmpty() ) {
    return null;
  }
  else if ( statusInfos.size() == 1 ) {
    return statusInfos.get( 0 );
  }
  else {
    // you could add logic here to do the sort in-memory 
    // for cases where you may have the full collection
    // but still want to obtain the last entry ...
  }
}