Ordering Collections with JPA – Johannes Neubauer

Often there is the need to sort an entity or a collection, e.g. If you have a list of line items in a bill and do not want that the order may change or is semantically wrong. Unfortunately, using a java.util.List will not suffice, since the DB has not to retain the order of the list. You might recognize this only after a long time, since some RDBMS will return the rows by insertion order. There are different ways to introduce an ordering with JPA (see Java Persistence: Ordering).

In JPA 1.0 you may facilitate the @OrderBy annotation. You have to maintain the order column by yourself and it is visible in the entity. There are two common ways to implement an ordered collection with @OrderBy:

You may add a new column of a type, which can be used in an ORDER BY SQL-clause, for each ordered collection to the target entity (the @ManyToOne side) of a @OneToMany association.
Create an own entity for each ordered collection having an order column. This way you can order @ManyToMany associations as well, although this is a bit more tricky.

Here is a small code example, how an ordered one-to-many relation can be realized:

@Entity
@Table(name = "MYAPP_BILL")
public class DBBill implements Serializable {
  private Set<DBOrderedLineItems> lineItems;

  public DBBill() {
    this.lineItems = new LinkedHashSet<DBOrderedLineItems>();
  }

  @OneToMany
  @JoinTable(name = "MYAPP_BILL_LINE_ITEMS")
  @Fetch(FetchMode.SUBSELECT)
  @OrderBy("counter ASC")
  public Set<DBOrderedLineItems> getLineItems() {
    return this.lineItems;
  }

  @Transient
  public List<DBLineItem> getSortedLineItems() {
    final List<DBLineItem> sortedLineItems = new ArrayList<DBLineItem>(this.lineItems.size());
    // lineItems is a sorted set, so it iterates the line items ascending.
    for(DBOrderedLineItems orderedLineItem : this.lineItems) {
        sortedLineItems.add(orderedLineItem.getLineItem());
    }
    return sortedLineItems;
  }
  // ...
}

@Entity
@Table(name = "MYAPP_ORDERED_LINE_ITEM")
public class DBOrderedLineItem implements Serializable {
  /**
   * the line item to be ordered.
   */
  private DBLineItem lineItem;
  /**
   * The order assigned to the line item. Do not name it 'order', since some
   * persistence provider do not mask sql keywords!
   */
  private int counter;

  @ManyToOne(fetch = EAGER, optional = false, cascade = CascadeType.ALL)
  public DBLineItem getLineItem() {
    return this.lineItem;
  }
  // ...
}

The method getSortedLineItems() with the annotation @Transient is not managed by hibernate and retrieves the sorted values (DBLineItem) of the association without the intermediate ordered entities (DBOrderedLineItems). I’m returning a list here, since this is a list with ordered elements, so its the the most general interface describing the return values. In getter methods managed by hibernate I often use a java.util.Set or java.util.Collection as return type, though it is a java.util.LinkedHashSet with an ordering. This is due to hibernate would implement a java.util.List as a persistent bag, which has disadvantages (see Ordering Collections with JPA). This constraint is not given for a transient getter method.
The formerly shown approach sorts the entries during the retrieval of the collection in the corresponding SQL statement. If you are using hibernate you may use the annotation @Sort for sorting in java/memory. This way you can introduce a more complex order criterion then possible via ORDER BY. The following code snippet shows how to use it:

@Entity
@Table(name = "MYAPP_BILL")
public class DBBill implements Serializable {
  private SortedSet<DBOrderedLineItems> lineItems;

  public DBBill() {
    this.lineItems = new TreeSet<DBOrderedLineItems>();
  }

  @OneToMany
  @JoinTable(name = "MYAPP_BILL_LINE_ITEMS")
  @Fetch(FetchMode.SUBSELECT)
  @Sort(type=SortType.NATURAL)
  public SortedSet<DBOrderedLineItems> getLineItems() {
    return this.lineItems;
  }

  @Transient
  public List<DBLineItem> getSortedLineItems() {
    final List<DBLineItem> sortedLineItems = new ArrayList<DBLineItem>(this.lineItems.size());
    // lineItems is a sorted set, so it iterates the line items ascending.
    for(DBOrderedLineItems orderedLineItem : this.lineItems) {
        sortedLineItems.add(orderedLineItem.getLineItem());
    }
    return sortedLineItems;
  }
  // ...
}

@Entity
@Table(name = "MYAPP_ORDERED_LINE_ITEM")
public class DBOrderedLineItem implements Serializable, Comparable {
  private DBLineItem lineItem;
  private int counter;

  @ManyToOne(fetch = EAGER, optional = false, cascade = CascadeType.ALL)
  public DBLineItem getLineItem() {
    return this.lineItem;
  }

  @Override
  public int compareTo(DBOrderedLineItem that) {
    // compareTo should be consistent to equals
    // (see documentation of java.lang.Comparable
    // and java.util.TreeSet), so we sort them by
    // counter first and by id second.
    return this.counter > that.counter ? 1 : (this.counter == that.counter ?
        (this.id > that.id ? 1 : (this.id == that.id) ? 0 : -1)
        : -1);
    }

  @Override
  public boolean equals(Object obj) {
    if (this == obj) {
      return true;
    }
    if (obj == null || !(obj instanceof DBOrderedLineItem)) {
      return false;
    }
    DBOrderedLineItem that = (DBOrderedLineItem) obj;
    // EqualsBuilder is from an apache commons project...
    return new EqualsBuilder().append(this.id, that.id).isEquals();
  }

  @Override
  public int hashCode() {
    // HashCodeBuilder is from an apache commons project...
    return new HashCodeBuilder(31, 7).append(this.getClass()).append(id).toHashCode();
  }
  // ...
}

Last but not least JPA 2.0 introduces the annotation @OrderColumn. Do not use it in combination with @OrderBy. The new JPA 2.0 feature transparently maintains the order of the list. The order columns are added implicitly and are not visible in the entity.

Leave a Reply Cancel reply