How to dynamically order many-to-many relationship with JPA or HQL?

Question

I have a mapping like this:

@ManyToMany(cascade = CascadeType.PERSIST)
    @JoinTable(
            name="product_product_catalog",
            joinColumns={@JoinColumn(name="product_catalog", referencedColumnName="product_catalog")},
            inverseJoinColumns={@JoinColumn(name="product", referencedColumnName="product")})
    public List<Product> products = new ArrayList<Product>();

I can fetch the products for the catalog nicely, but I can't (dynamically) order the products. How could I order them? I probably have to write a many-to-many HQL query with the order-by clause? I though of passing the orderBy field name string to the query, or is there a better solution?

Tables are: products, product_catalog, product_product_catalog (associative table)

P.S. Using Play! Framework JPASupport for my entities.

Answer 1

Yes use order by clause, let's say you want to order by field1: select p from Product p order by p.field1

Answer 2

I can fetch the products for the catalog nicely, but I can't (dynamically) order the products. How could I order them?

Not possible if you let JPA retrieve the association. Mappings are static, the best thing you can do with mappings is to specify the order at "retrieve time":

9.1.28 OrderBy Annotation

The OrderBy annotation specifies the ordering of the elements of a collection valued association at the point when the association is retrieved.

@Target({METHOD, FIELD}) @Retention(RUNTIME)
public @interface OrderBy {
   String value() default "";
}

The syntax of the value ordering element is an orderby_list, as follows:

orderby_list::= orderby_item [,orderby_item]*
orderby_item::= property_or_field_name [ASC | DESC]

If ASC or DESC is not specified, ASC (ascending order) is assumed.

If the ordering element is not specified, ordering by the primary key of the associated entity is assumed.

The property or field name must correspond to that of a persistent property or field of the associated class. The properties or fields used in the ordering must correspond to columns for which comparison operators are supported.

Example:

@Entity public class Course {
  ...
  @ManyToMany
  @OrderBy("lastname ASC")
  public List<Student> getStudents() {...};
  ...
}

So either use a custom finder or sort your list from Java using a Comparator.