java 如何动态排序与 JPA 或 HQL 的多对多关系？

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

提问by Indrek

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?

我可以很好地获取目录的产品，但我不能（动态）订购产品。我怎么能订购它们？我可能必须使用 order-by 子句编写多对多 HQL 查询？我想将 orderBy 字段名称字符串传递给查询，还是有更好的解决方案？

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

表有：products、product_catalog、product_product_catalog（关联表）

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

PS使用播放！我的实体的框架 JPASupport。

回答by Pascal Thivent

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":

如果您让 JPA 检索关联，则不可能。映射是静态的，你可以用映射做的最好的事情是在“检索时间”指定顺序：

9.1.28 OrderBy Annotation

The OrderByannotation 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() {...};
  ...
}

9.1.28 OrderBy 注解

的OrderBy注释指定的集合的元素的顺序在点值的关联时的关联检索。

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

值排序元素的语法是orderby_list，如下所示：

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

如果未指定 ASC 或 DESC，则假定为 ASC（升序）。

如果未指定排序元素，则假定按关联实体的主键排序。

属性或字段名称必须与关联类的持久属性或字段的名称相对应。排序中使用的属性或字段必须对应于支持比较运算符的列。

例子：

@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.

因此，要么使用自定义查找器，要么使用Comparator.