JPA规范+对多对多字段进行动态排序，没有重复项 [英] JPA Specification + dynamic sorting for many-to-many field without duplicates

问题描述

我有一个具有很多字段，多对一和多对多关系的类，我需要按某些列(来自该类以及所连接的类的字段)添加动态过滤器，并按以下方式添加排序这些字段也是如此.

I have a class with a lot of fields, many-to-one and many-to-many relationship and I need to add dynamic filters by some columns (from this class and by fields from connected class) and add sorting by those fields as well.

我在按多对多字段进行过滤和排序时遇到问题

I have an issue with filtering and sorting by many-to-many field

@Entity
class EntityA {
...
@ManyToMany
@JoinTable (
        name = "EntityA_EntityB",
        joinColumns = { @JoinColumn(name = "EntityB") },
        inverseJoinColumns = { @JoinColumn(name = "entityb_id") }
)
private List<EntityB> bEntities;
...
}

而且我有按EntityB.name过滤EntityA的规范(我设置了CriteriaQuery.distinct(true)来防止重复，而我没有这个)

And I have Specification to filter EntityA by EntityB.name (I set criteriaQuery.distinct(true) to prevent duplicates, which I have without this)

public class EntityASpecifications {
//other specifications
...

public static Specification<EntityA> entityBNameContains(String query) {
    return (root, criteriaQuery, criteriaBuilder) -> {

        if (query == null) {
            return criteriaBuilder.conjunction();
        }
        criteriaQuery.distinct(true);

        return getContainsPredicate(criteriaBuilder, root.join("bEntities").get("name"), query);
    };
}

private static Predicate getContainsPredicate(CriteriaBuilder criteriaBuilder, Expression<String> field, String query) {

    return (query == null) ? criteriaBuilder.conjunction() : criteriaBuilder.like(criteriaBuilder.lower(field), getContainsPattern(query));
}

private static String getContainsPattern(String searchTerm) {

    return (searchTerm.isEmpty()) ? "%" : "%" + searchTerm.toLowerCase() + "%";
}
}

它工作正常，问题是当我尝试同时使用排序和此过滤器时

It works fine, the issue is that when I'm trying to use sorting and this filter at the same time

entityARepository.findAll(EntityASpecifications.entityBNameContains("name"), PageRequest.of(page, size, Sort.Direction.ASC, sortColumnName));

对于作为EntityB.name连接到EntityA的字段，它也会失败(另外，我还有其他一些带有@ManyToOne的字段也失败了)，并带有下一个异常:

It fails for fields connected to EntityA as EntityB.name (also I have some other fields with @ManyToOne that fail) with the next Exception:

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: ORDER BY items must appear in the select list if SELECT DISTINCT is specified

如果我删除criteriaQuery.distinct(true)；一切都会好起来的，但是我会重复一些，但我不想拥有它们

If I remove criteriaQuery.distinct(true); everything will be okay, but I will have duplicates and I don't want to have them

如何解决它，同时没有重复的结果?

How to fix it and don't have duplicate results at the same time?

推荐答案

获得EntityA重复结果的原因是您将EntityA与EntityB结合在一起，并且您的谓词基于EntityB中的文件.因此，如果有多个满足您条件的EntityB条目并且它们都属于同一个EntityA，则您将获得EntityA的多个条目.因此，解决方案是使用存在"而不是将两个表连接在一起，您将不再需要使用distinct.您的规范如下所示:

The reason you are getting duplicate results for EntityA is that you are joining EntityA with EntityB and your predicate is based on a filed in EntityB. So if there are multiple entries of EntityB that satisfy your condition and they all belong to same EntityA, you will get multiple entries of EntityA. So the solution is to use "exists" instead of joining the two tables and you won't need to use distinct any more. your specification can look like this :

 public class EntityASpecifications {
 //other specifications
 ...

   public static Specification<EntityA> entityBNameContains(String query) {
       return (root, criteriaQuery, criteriaBuilder) -> {

          if (query == null) {
              return criteriaBuilder.conjunction();
          }
          Subquery< EntityB> subquery = query.subquery(EntityB.class);
          Root< EntityB> subqueryRoot = subquery.from(EntityB.class);
          subquery.select(subqueryRoot);

          subquery.where(criteriaBuilder.and(criteriaBuilder.equal(root, subqueryRoot.get("entitya_id")),
                                criteriaBuilder.like(criteriaBuilder.lower("name"), getContainsPattern(query)))
                   );

          return criteriaBuilder.exists(subquery);

       };
   } 
   private static String getContainsPattern(String searchTerm) {
     return (searchTerm.isEmpty()) ? "%" : "%" + searchTerm.toLowerCase() + "%";
 }
} 

这篇关于JPA规范+对多对多字段进行动态排序，没有重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！