在JPA中按所需顺序对每个组的子行进行排序，并在每个组中采用所需的顶行数 [英] Sort child rows of each group in the desired order and take the desired number of top rows in each group in JPA

问题描述

是否可以按期望的顺序对每个组的子行(每个父行的子行)进行排序，并在JPA中获取每个组中期望的顶行数?

Is it possible to sort child rows of each group (child rows of each parent row) in the desired order and take the desired number of top rows in each group in JPA?

例如，我在MySQL数据库中有三个表.

For example, I have three tables in MySQL database.

• 类别
• sub_category
• 产品

这些表之间的关系很直观-按它们出现的顺序一对多.

The relationship between these tables is intuitive - one-to-many in the order in which they appear.

我正在sub_category表上执行以下条件查询.

I'm executing the following criteria query on the sub_category table.

CriteriaBuilder criteriaBuilder=entityManager.getCriteriaBuilder();
CriteriaQuery<SubCategory>criteriaQuery=criteriaBuilder.createQuery(SubCategory.class);
EntityType<SubCategory> entityType = entityManager.getMetamodel().entity(SubCategory.class);
Root<SubCategory> root = criteriaQuery.from(entityType);
criteriaQuery.distinct(true);

Join<SubCategory, Category> catJoin = root.join(SubCategory_.catId, JoinType.INNER);
SetJoin<SubCategory, Product> prodJoin = root.join(SubCategory_.productSet, JoinType.INNER);

List<Predicate>predicates=new ArrayList<Predicate>();
predicates.add(criteriaBuilder.isTrue(root.get(SubCategory_.visible)));
predicates.add(criteriaBuilder.isTrue(catJoin.get(Category_.visible)));
predicates.add(criteriaBuilder.isTrue(prodJoin.get(Product_.visible)));

criteriaQuery.where(predicates.toArray(new Predicate[0]));
criteriaQuery.orderBy(criteriaBuilder.desc(root.get(SubCategory_.subCatId)));               
List<SubCategory> list = entityManager.createQuery(criteriaQuery).getResultList();

该查询碰巧获取所有子类别，其中在所有这些表中，visible(MySQL中的TINYINT映射到相应JPA实体中的Boolean属性)为true.

This query happens to fetch all sub categories where visible (TINYINT in MySQL which is mapped to a Boolean property in the corresponding JPA entity) is true in all of these tables.

执行此查询后，每个子类别的一组产品将作为Set<Product>提供.

After executing this query, a set of products of each sub category will be available as a Set<Product>.

我要对每个子类别的一组产品进行排序后，获取一组每个子类别的前五名产品(不是每个子类别的所有产品)降序排列.

I want to fetch a group of top five products (not all products of each sub category) of each sub category after sorting a group of products of each sub category in descending order.

有可能吗?

_{我正在使用EclipseLink 2.3.2提供的JPA 2.0.}

推荐答案

不幸的是，AFAIK限制查询结果的唯一方法是使用

Unfortunately, AFAIK, the only way to limit the results of a query is to use Query#setMaxResults() and this is only applicable to a Query object, not to a subquery.

我建议使用一种解决方法，例如仅在查询中选择子类别.由于延迟加载，因此在返回结果时，JPA不应为每个结果获取所有产品(您可以通过一些日志记录进行检查)，从而避免了不必要的数据库加载.

I suggest to use a workaround, like selecting only the subcategories with your query. Thanks to lazy loading, when returning the results, JPA shouldn't fetch all products for each result (you can check this with some logging), therefore avoiding an unnecessary db load.

稍后，当您需要每个子类别的前5个产品时，而不是使用

Later, when you need the top 5 products for each subcategory, instead of using

SubCategory sc;
List<Product> list = sc.getProducts();

只需为每个子类别运行一个查询:

just run a query for each subcategory:

SubCategory sc;
List<Product> list = subCategoryService.getTopProducts(sc);

如果每次显示的子类别的数量都合理(例如，最多可以显示30个，可以显示在分页数据列表中的结果)，那么这应该不会明显降低性能.

This shouldn't have a visible worsening of the performances, if the subcategories shown each time are in a reasonable number (like up to 30, as for the results that can be displayed in a paginated data list).

这篇关于在JPA中按所需顺序对每个组的子行进行排序，并在每个组中采用所需的顶行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！