Spring Data Specifications多子属性查询重复关联问题及解决

问题场景 #

在Spring Boot Data项目中使用Specifications过滤请求数据时，当基于同一子实体的多个属性（foo和baa）查询一对多关联的父实体，会出现错误：只要父实体下的子实体分别匹配单个属性条件，就会被错误返回，而非要求同一个子实体同时满足所有属性条件。

示例场景 #

父实体仅包含name属性，子实体包含foo和baa两个布尔属性：

• 父实体a
  • 子实体1：foo=true, baa=false
  • 子实体2：foo=false, baa=true
• 父实体b
  • 子实体：foo=false, baa=false
• 父实体c
  • 子实体：foo=true, baa=true

当查询条件为foo=true 且 baa=true时，预期仅返回父实体c，但实际返回了a和c。

实体定义（省略getter/setter、构造方法及ID） #

@Entity
@Table(name="parent")
public class ParentEntity {
  private String name;

  @OneToMany(mappedBy = "parent")
  private List<ChildEntity> children;
}

@Entity
@Table(name="child")
public class ChildEntity {
  @ManyToOne
  @JoinColumn(name="parent_id", nullable = false)
  private ParentEntity parent;

  private boolean foo;
  private boolean baa;
}

原实现问题分析 #

原代码通过JpaSpecificationExecutor组合两个Specification（用and连接），代码如下：

public static Specification<ParentEntity> empty() {
    return (root, query, criteriaBuilder) -> null;
}
public static Specification<ParentEntity> byFoo(Boolean foo) {
    if(foo == null) {
        return empty();
    }
    return (root, query, criteriaBuilder) -> criteriaBuilder.equal(
            root.join(ParentEntity_.CHILDREN, JoinType.INNER).get(ChildEntity_.FOO),
            foo
    );
}

public static Specification<ParentEntity> byBaa(Boolean baa) {
    if (baa == null) {
        return empty();
    }
    return  (root, query, criteriaBuilder) -> criteriaBuilder.equal(
            root.join(ParentEntity_.CHILDREN, JoinType.INNER).get(ChildEntity_.BAA),
            baa
    );
}

List<ParentEntity> list(Boolean foo, Boolean baa) {
  Specification<ParentEntity> filter = byFoo(foo).and(byBaa(baa));
  return repository.findAll(filter);
}

这段代码生成的SQL会两次关联child表：

select p1_0.id,p1_0.name from parent p1_0 
  join child c1_0 on p1_0.id=c1_0.parent_id 
  join child c2_0 on p1_0.id=c2_0.parent_id 
where c1_0.foo=? and c2_0.baa=?

这就导致：只要父实体下存在一个子实体匹配foo=true，同时存在另一个子实体匹配baa=true，就会被查询出来，不符合“同一个子实体同时满足两个条件”的需求。

解决方案 #

方案1：单一Specification实现（扩展性有限） #

将所有子实体属性条件放在同一个Specification中，只做一次关联：

public static Specification<ParentEntity> combined(Boolean foo, Boolean baa) {
    if (foo == null && baa == null) {
        return empty();
    }

    return (root, query, criteriaBuilder) -> {
        Join<Object, Object> join = root.join(ParentEntity_.CHILDREN, JoinType.INNER);
        List<Predicate> predicates = new ArrayList<>();
        if (foo != null) {
            predicates.add(criteriaBuilder.equal(join.get(ChildEntity_.FOO),foo));
        }
        if (baa != null) {
            predicates.add(criteriaBuilder.equal(join.get(ChildEntity_.BAA),baa));
        }
        return criteriaBuilder.and(predicates.toArray(Predicate[]::new));
    };
}

方案2：自定义FromSpecification接口（高扩展性） #

定义一个基于From的Specification接口，让子实体的条件可以复用，且仅关联一次子表：

public interface FromSpecification<T,X> extends Serializable {
  long serialVersionUID = 1L;

  @Nullable
  Predicate toPredicate(From<?,?> from, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder);

  default FromSpecification<T,X> and(@Nullable FromSpecification<T, ? super X> other) {
      if (other == null) {
          return this;
      }
      return (from, query, criteriaBuilder) -> criteriaBuilder.and(
            toPredicate(from, query, criteriaBuilder), other.toPredicate(from, query, criteriaBuilder)
      );
  }

  default FromSpecification<T,X> or(@Nullable FromSpecification<T,? super X> other) {
      if (other == null) {
          return this;
      }
      return (from, query, criteriaBuilder) -> criteriaBuilder.or(
            toPredicate(from, query, criteriaBuilder), other.toPredicate(from, query, criteriaBuilder)
      );
  }

  default Specification<T> asSpecification() {
      return this::toPredicate;
  }
}

使用方式

public static FromSpecification<ParentEntity, ChildEntity> fromChildByFoo(Boolean foo) {
    if (foo == null) {
        return (from, query, criteriaBuilder) -> null;
    }
    return (from, query, criteriaBuilder) -> criteriaBuilder.equal(from.get(ChildEntity_.FOO), foo);
}
public static FromSpecification<ParentEntity, ChildEntity> fromChildByBaa(Boolean baa) {
    if (baa == null) {
        return (from, query, criteriaBuilder) -> null;
    }
    return (from, query, criteriaBuilder) -> criteriaBuilder.equal(from.get(ChildEntity_.BAA), baa);
}

Specification<ParentEntity> byFrom(Boolean foo, Boolean baa) {
    if (foo == null && baa == null) {
        return (root, query, criteriaBuilder) -> null;
    }
    return (root, query, criteriaBuilder) -> fromChildByFoo(foo).and(fromChildByBaa(baa))
            .toPredicate(root.join(ParentEntity_.CHILDREN), query, criteriaBuilder);
}

内容的提问来源于stack exchange，提问作者Aracos