jpa 来自组合Spring数据规范的查询在同一表上具有多个联接

gijlo24d  于 2022-12-04  发布在  Spring
关注(0)|答案(5)|浏览(109)

如果我的术语不正确,请原谅。
我们使用Spring Data 、JpaRepositories和条件查询作为从数据库中查询数据的方法。
我遇到了一个问题,当我合并两个规范时,例如下面的代码示例中的hasCityAndTimeZone中的hasTimeZone和hasCity,它会对同一个表执行两次连接,因此下面的查询将类似于

select * from Staff, Location, Location

有没有办法让这两个规范使用相同的连接,而不是各自定义自己的本质上相同的连接?
对不起,代码可能不完整,我只是想展示一个快速的例子。

class Staff {
    private Integer id;
    private Location location;
}

class Location {
    private Integer id; 
    private Integer timeZone;
    private Integer city;
}

class StaffSpecs {
    public static Specification<Staff> hasTimeZone(Integer timeZone) {
        return new Specification<Staff>() {
            @Override
            public Predicate toPredicate(Root<Staff> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                Path<Integer> timeZonePath = root.join(Staff_.location).get(Location_.timeZone);
                return cb.equal(timeZonePath, timeZone);
            }
        }
    }

    public static Specification<Staff> hasCity(Integer city) {
        return new Specification<Staff>() {
            @Override
            public Predicate toPredicate(Root<Staff> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                Path<Integer> cityPath = root.join(Staff_.location).get(Location_.city);
                return cb.equal(cityPath, city);
            }
        }
    }

    public static Specification<Staff> hasCityAndTimeZone(Integer city, Integer timeZone) {
        return where(hasCity(city)).and(hasTimeZone(timeZone));
    }
}
hc2pp10m

hc2pp10m1#

不幸的是,没有现成的方法。Spring Data在内部使用QueryUtils.getOrCreateJoin(…)中的一些连接重用。您可以找出根上可能已经存在的连接,并在适当的地方重用它们:

private static Join<?, ?> getOrCreateJoin(From<?, ?> from, String attribute) {

  for (Join<?, ?> join : from.getJoins()) {

    boolean sameName = join.getAttribute().getName().equals(attribute);

    if (sameName && join.getJoinType().equals(JoinType.LEFT)) {
      return join;
    }
  }

  return from.join(attribute, JoinType.LEFT);
}

注意,只有当我们知道自己添加了哪些连接时,这才有效。当使用Specifications时,你也应该这样做,但我只是想确保没有人认为这是所有情况下的通用解决方案。

3yhwsihp

3yhwsihp2#

根据@奥利弗的回答,我创建了Specification接口的扩展
第1001章:我的JoinableSpecification.java

public interface JoinableSpecification<T> extends Specification<T>{

  /**
   * Allow reuse of join when possible
   * @param <K>
   * @param <Z>
   * @param query
   * @return
   */

  @SuppressWarnings("unchecked")
  public default <K, Z> ListJoin<K, Z> joinList(From<?, K> from, ListAttribute<K,Z> attribute,JoinType joinType) {

    for (Join<K, ?> join : from.getJoins()) {

      boolean sameName = join.getAttribute().getName().equals(attribute.getName());

      if (sameName && join.getJoinType().equals(joinType)) {

        return (ListJoin<K, Z>) join; //TODO verify Z type it should be of Z after all its ListAttribute<K,Z>
      }
    }
    return from.join(attribute, joinType);
  }

  /**
   * Allow reuse of join when possible
   * @param <K>
   * @param <Z>
   * @param query
   * @return
   */
  @SuppressWarnings("unchecked")
  public default <K, Z> SetJoin<K, Z> joinList(From<?, K> from, SetAttribute<K,Z> attribute,JoinType joinType) {

    for (Join<K, ?> join : from.getJoins()) {

      boolean sameName = join.getAttribute().getName().equals(attribute.getName());

      if (sameName && join.getJoinType().equals(joinType)) {
        return (SetJoin<K, Z>) join; //TODO verify Z type it should be of Z after all its ListAttribute<K,Z>
      }
    }
    return from.join(attribute, joinType);
  }

  /**
   * Allow reuse of join when possible
   * @param <K>
   * @param <Z>
   * @param query
   * @return
   */
  @SuppressWarnings("unchecked")
  public default <K, Z> Join<K, Z> joinList(From<?, K> from, SingularAttribute<K,Z> attribute,JoinType joinType) {

    for (Join<K, ?> join : from.getJoins()) {

      boolean sameName = join.getAttribute().getName().equals(attribute.getName());

      if (sameName && join.getJoinType().equals(joinType)) {
        return (Join<K, Z>) join; //TODO verify Z type it should be of Z after all its ListAttribute<K,Z>
      }
    }
    return from.join(attribute, joinType);
  }

}

如何使用

class StaffSpecs {
 public static Specification<Staff> hasTimeZone(Integer timeZone) {
    return new JoinableSpecification<Staff>() {
        @Override
        public Predicate toPredicate(Root<Staff> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
            Path<Integer> timeZonePath = this.joinList(root,Staff_.location,JoinType.INNER).get(Location_.timeZone);
            return cb.equal(timeZonePath, timeZone);
        }
    }
}

 public static Specification<Staff> hasCity(Integer city) {
    return new JoinableSpecification<Staff>() {
        @Override
        public Predicate toPredicate(Root<Staff> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
            Path<Integer> cityPath = this.joinList(root,Staff_.location,JoinType.INNER).get(Location_.city);
            return cb.equal(cityPath, city);
        }
    }
}
gev0vcfq

gev0vcfq3#

private static Join<?, ?> getOrCreateJoin(From<?, ?> from, String attribute) {

    for (Join<?, ?> join : from.getJoins()) {

        boolean sameName = join.getAttribute().getName().equals(attribute);

        if (sameName && join.getJoinType().equals(JoinType.LEFT)) {
            return join;
        }
    }

    return from.join(attribute, JoinType.LEFT);
}

在自定义规范中

@Override
public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder builder) {

    query.distinct(true);

    String[] parts = criteria.getKey().split("\\.");
    Path<?> path = root;
    for (String part : parts) {
        if(path.get(part).getJavaType() == Set.class){
            path = getOrCreateJoin(root, part);
        }else{
            path = path.get(part);
            }
        }
    }

....

if (path.getJavaType() == String.class) {
            return builder.like(path.as(String.class), "%" + criteria.getValue().toString() + "%");

....

vu8f3i0k

vu8f3i0k4#

这是一个老问题,但我写了这个答案的人谁有这个问题。
我实现了一个通用方法来搜索连接别名,如果不存在具有此别名的连接,则它将创建具有给定pathFunction的连接。
正如奥利弗所说,您可以使用已定义连接,但如果您有一个实体的多个连接,则需要知道已定义连接的别名。

//Get or create join with name of alias    
protected <F extends From<FF, FR>, FF, FR, J extends Join<JF, JR>, JF, JR>J getOrCreateCriteriaJoin(F from, String alias, BiFunction<F, CriteriaBuilder, J> pathFunction) {

    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    Set<Join<FR, ?>> joins = from.getJoins();
    Optional<J> optionalJoin = findJoin((Set) joins, alias);

    return optionalJoin.orElseGet(() -> {
            J join = pathFunction.apply(from, criteriaBuilder);
            join.alias(alias);
            return join;
        }
    );
}

//Recursively searches for 'alias' named join
protected Optional<Join> findJoin(Set<Join> joins, String alias) {

    List<Join> joinList = new ArrayList<>(joins);

    for (Join j : joinList) {
        if (j.getAlias() != null && j.getAlias().equals(alias)) {
            return Optional.of(j);
        }
    }

    //  Breadth first search
    for (Join j : joinList) {
        Optional<Join> res = findJoin(j.getJoins(), alias);

        if (res.isPresent()) {
            return res;
        }
    }

    return Optional.empty();
}

示例用法;

private Join<E, ExampleEntity> getOrCreateExampleEntityJoin(Root<E> mainRoot, String alias) {
    return getOrCreateCriteriaJoin(mainRoot, alias, (root, cb) -> root.join(ExampleEntity_.someFieldName));
}

specification = (root, query, criteriaBuilder) -> criteriaBuilder.equal(getOrCreateExampleEntityJoin(root, "exampleAlias").get(ExampleEntity_.someAnOtherField), "ExampleData");
kg7wmglp

kg7wmglp5#

我稍微修改了实现,这样就不需要复制粘贴别名和函数

abstract class ReusableJoinSpecification<T> implements Specification<T> {

protected <F extends From<FF, FR>, FF, FR, J extends Join<JF, JR>, JF, JR> J getOrCreateJoin(F from,
                                                                                             JoinData<F, J> joinData) {

    Set<Join<FR, ?>> joins = from.getJoins();
    //noinspection unchecked
    Optional<J> optionalJoin = (Optional<J>) findJoin(joins, joinData.getAlias());

    return optionalJoin.orElseGet(() -> {
                J join = joinData.getCreationFunction().apply(from);
                join.alias(joinData.getAlias());
                return join;
            }
    );
}

private Optional<Join<?, ?>> findJoin(@NotNull Set<? extends Join<?, ?>> joins, @NotNull String alias) {

    List<Join<?, ?>> joinList = new ArrayList<>(joins);

    for (Join<?, ?> join : joinList) {
        if (alias.equals(join.getAlias())) {
            return Optional.of(join);
        }
    }

    for (Join<?, ?> j : joinList) {
        Optional<Join<?, ?>> res = findJoin(j.getJoins(), alias);

        if (res.isPresent()) {
            return res;
        }
    }

    return Optional.empty();
}

连接数据:

@Data
class JoinData<F extends From<?, ?>, J extends Join<?, ?>> {
    @NotNull
    private final String alias;
    @NotNull
    private final Function<F, J> creationFunction;
}

用法:

private final JoinData<Root<Project>, Join<Project, Contractor>> contractorJoinData =
        new JoinData<>("contractor", root -> root.join(Project_.contractor));

private final JoinData<Join<Project, Contractor>, Join<Contractor, Address>> contractorLegalAddressJoinData =
        new JoinData<>("contractorLegalAddress", root -> root.join(Contractor_.legalAddress));

public Specification<Project> contractorLegalAddressCityLike(String address) {
    if (address == null)
        return null;

    return new ReusableJoinSpecification<>() {
        @Override
        public Predicate toPredicate(Root<Project> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {

            Join<Project, Contractor> contractorJoin = getOrCreateJoin(root, contractorJoinData);
            Join<Contractor, Address> contractorAddressJoin = getOrCreateJoin(contractorJoin, contractorLegalAddressJoinData);

            return criteriaBuilder.like(contractorAddressJoin.get(Address_.city), simpleLikePattern(address));
        }
    };
}

更新日期:

我做了一个小工具,使它更容易重用连接,在检查filter-dto字段是否为空时去掉“if”,并使用一个类型检查:
https://github.com/koval666/springspecwrapper

相关问题