Hibernate Tip: Sort and Order

Let’s introduce another hibernate performance tip. Do you remember the model of previous hibernate post? We had a starship and officer related with a one to many association.
 
 
 
 
 
 
 
 

@Entity
public class Starship {

 @Id @GeneratedValue(strategy=GenerationType.SEQUENCE) 
 private Long id;
 public Long getId() {return id;}
 protected void setId(Long id) {this.id = id;}

 @OneToMany(mappedBy="starship", cascade={CascadeType.ALL}) 
 private List<Officer> officers = new ArrayList<Officer>();
 public List<Officer> getOfficers() {return Collections.unmodifiableList(officers);}
 protected void setOfficers(List<Officer> officers) {this.officers = officers;}
 public void addOfficer(Officer officer) {
  officer.setStarship(this);
  this.officers.add(officer);
 }

        //more code
}

@Entity
public class Officer {

 @Id @GeneratedValue(strategy=GenerationType.SEQUENCE) 
 private Long id;
 public Long getId() {return id;}
 protected void setId(Long id) {this.id = id;}

 @ManyToOne private Starship starship; 
 public Starship getStarship() {return starship;}
 protected void setStarship(Starship starship) {this.starship = starship;}

        //more code
}

Now we have next requirement:
We shall get all officers assigned to a starship by alphabetical order.
To solve this requirement we can:

1. implementing an HQL query with order by clause.
2. using sort approach.
3. using order approach.

The first solution is good in terms of performance, but implies more work as a developers because we should write a query finding all officers of given starship ordered by name and then create a finder method in DAO layer (in case you are using DAO pattern).
Let’s explore the second solution, we could use SortedSet class as association, and make Officer implements Comparable, so Officer has natural order. This solution implies less work than the first one, but requires using @Sort hibernate annotation on association definition.So let’s going to modify previous model to meet our new requirement.Note that there is no equivalent annotation in JPAspecification.
First we are going to implement

@Entity
public class Officer implements Comparable<Officer>{


        //getters, setters, equals, ... code

 public int compareTo(Officer officer) {
  return this.name.compareTo(officer.getName());
 }

}

Comparable interface in Officer class.

We are ordering officer by name by simply comparing name field. Next step is annotating association with @Sort.

@Entity
public class Starship {

        //more code

 @OneToMany(mappedBy="starship", cascade={CascadeType.ALL})
 @Sort(type=SortType.NATURAL)
 private SortedSet>Officer< officers = new TreeSet>Officer<();
 public SortedSet>Officer< getOfficers() {return Collections.unmodifiableSortedSet(officers);}
 protected void setOfficers(SortedSet>Officer< officers) {this.officers = officers;}
 public void addOfficer(Officer officer) {
  officer.setStarship(this);
  this.officers.add(officer);
 }
}

Notice that now officers association is implemented using SortedSet instead of a List . Furthermore we are adding @Sort annotation to relationship, stating that officers should be natural ordered. Before finishing this post we will insist more in @Sort topic, but for now it is sufficient.

And finally a method that gets all officers of given starship ordered by name, printing them in log file.

EntityManager entityManager = this.entityManagerFactory.createEntityManager();
EntityTransaction transaction = entityManager.getTransaction();

transaction.begin();
log.info("Before Find Starship By Id");

Starship newStarship = entityManager.find(Starship.class, starshipId);
SortedSet<Officer> officers = newStarship.getOfficers();
 
for (Officer officer : officers) {
 log.info("Officer name {} with rank {}", officer.getName(), officer.getRank());
}
  
log.info("After Find Starship By Id and Before Commit");

transaction.commit();
entityManager.close();

All officers are sorted by their names, but let’s examine which queries are sent to RDBMS.

Hibernate: select starship0_.id as id1_0_, starship0_.affiliationEnum as affiliat2_1_0_, starship0_.launched as launched1_0_, starship0_.height as height1_0_, starship0_.length as length1_0_, starship0_.power as power1_0_, starship0_.width as width1_0_, starship0_.registry as registry1_0_, starship0_.starshipClassEnum as starship9_1_0_ 
from Starship starship0_ where starship0_.id=?

Hibernate: select officers0_.starship_id as starship7_1_1_, officers0_.id as id1_, officers0_.id as id0_0_, officers0_.affiliationEnum as affiliat2_0_0_, officers0_.homePlanet as homePlanet0_0_, officers0_.name as name0_0_, officers0_.rank as rank0_0_, officers0_.speciesEnum as speciesE6_0_0_, officers0_.starship_id as starship7_0_0_ 
from Officer officers0_ where officers0_.starship_id=?

First query is resulting of calling find method on EntityManager instance finding starship.

Because one to many relationships are lazy by default when we call getOfficers method and we access first time to SortedSet, second query is executed to retrieve all officers. See that no order by clause is present on query, but looking carefully on output, officers are retrieved in alphabetical order.

<Officer name Beverly Crusher with rank COMMANDER>
<Officer name Data with rank LIEUTENANT_COMMANDER>
<Officer name Deanna Troi with rank COMMANDER>
<Officer name Geordi La Forge with rank LIEUTENANT>
<Officer name Jean-Luc Picard with rank CAPTAIN>
<Officer name William Riker with rank COMMANDER>
<Officer name Worf with rank LIEUTENANT>
 

So who is sorting officer entities? The explanation is on @Sort annotation. In hibernate a sorted collection is sorted in memory being Java the responsible of sorting data using compareTo method.
Obviously this method is not the best performance-way to sort a collection of elements. It is likely that we’ll need a hybrid solution between using SQL clause and using annotation instead of writing a query.

And this leads us to explain the third possibility, using ordering approach. @OrderBy annotation, available as hibernate annotation and JPA annotation, let us specifies how to order a collection by adding “ order by” clause to generated SQL.

Keep in mind that using javax.persistence.OrderBy allows us to specify the order of the collection via object properties, meanwhile org.hibernate.annotations.OrderBy order a collection appending directly the fragment of SQL(not HQL) to order by clause.
Now Officer class should not be touched, we don’t need to implement compareTo method nor a java.util.Comparator . We only need to annotate officers field with @OrderBy annotation. Since in this case we are ordering by simple attribute, JPA annotation is used to maintain fully compatibility to other “ JPA ready ” ORM engines. By default ascendent order is assumed.

@Entity
public class Starship {

        //code

 @OneToMany(mappedBy="starship", cascade={CascadeType.ALL})
 @OrderBy("name")
 private List<Officer> officers = new ArrayList<Officer>();
 public List<Officer> getOfficers() {return Collections.unmodifiableList(officers);}
 protected void setOfficers(List<Officer> officers) {this.officers = officers;}
 public void addOfficer(Officer officer) {
  officer.setStarship(this);
  this.officers.add(officer);
 }
} 

And if we rerun get all officers method, next queries are sent:

Hibernate: select starship0_.id as id1_0_, starship0_.affiliationEnum as affiliat2_1_0_, starship0_.launched as launched1_0_, starship0_.height as height1_0_, starship0_.length as length1_0_, starship0_.power as power1_0_, starship0_.width as width1_0_, starship0_.registry as registry1_0_, starship0_.starshipClassEnum as starship9_1_0_ 
from Starship starship0_ where starship0_.id=?

Hibernate: select officers0_.starship_id as starship7_1_1_, officers0_.id as id1_, officers0_.id as id0_0_, officers0_.affiliationEnum as affiliat2_0_0_, officers0_.homePlanet as homePlanet0_0_, officers0_.name as name0_0_, officers0_.rank as rank0_0_, officers0_.speciesEnum as speciesE6_0_0_, officers0_.starship_id as starship7_0_0_ 
from Officer officers0_ where officers0_.starship_id=? order by officers0_.name asc

Both queries are still executed but note that now select query contains order by clause too.

With this solution you are saving process time allowing RDBMS sorting data in a fast-way, rather than ordering data in Java once received.
Furthermore OrderBy annotation does not force you to use SortedSet or SortedMap collection. You can use any collection like HashMap, HashSet, or even a Bag, because hibernate will use internally a LinkedHashMap, LinkedHashSet or ArrayList respectively.

In this example we have seen the importance of choosing correctly an order strategy. Whenever possible you should try to take advantage of capabilities of RDBMS, so your first option should be using OrderBy annotaion ( hibernate or JPA), instead of Sort. But sometimes OrderBy clause will not be enough. In this case, I recommend you using Sort annotation with custom type (using java.util.Comparator class), instead of relaying on natural order to avoid touching model classes.

@Sort(type=SortType.COMPARATOR, comparator=TimeComparator.class)

I wish this post helped you to understand differences between “sort” and “order” in hibernate.

Keep learning.

Reference: Hibernate Tip: Sort and Order from our JCG partner Alex Soto at the One Jar To Rule Them All blog.