将单个Join实例传递给多个Specification实例？ [英] Pass single Join instance to multiple Specification instances?

问题描述

相关：提示HINT_PASS_DISTINCT_THROUGH可将PageRequest的每页返回的实体数量降低到低于配置的页面大小（PostgreSQL）

我正在建立基于JPA规范的利用jpa规范（基于RSQL过滤器字符串构造）的存储库实现，以过滤结果，定义结果排序并通过 distinct删除任何重复项，否则由于连接的表而将返回这些重复项。 JPA规范构建器方法联接了几个表并设置了 distinct标志：

I'm setting up a JPA Specification based repository implementation that utilizes jpa specifications(constructed based on RSQL filter strings) to filter the results, define result ordering and remove any duplicates via "distinct" that would otherwise be returned due to joined tables. The JPA Specification builder method joins several tables and sets the "distinct" flag:

public final class MySpec implements Specification<Tag>
{
    @Override
    public Predicate toPredicate(
        final Root<Tag> root,
        final CriteriaQuery<?> query,
        final CriteriaBuilder builder)
    {

        final Join<Tag, Label> labelsJoin = root.join("labels", JoinType.INNER);
        final Join<Label, LabelIdentity> labelIdentityJoin = labelsJoin.join("labelIdentity", JoinType.INNER);
        final Predicate labelKeyPredicate = builder.equal(labelIdentityJoin.get("key"), property);

        query.distinct(true);

        return builder.and(
                    labelKeyPredicate,
                    builder.like(labelsJoin.get("value"), argument.replace('*', '%')));
    }
}

为了允许按联接的表列进行排序，将 HINT_PASS_DISTINCT_THROUGH提示应​​用于相关的存储库方法（否则，按联接表列排序将返回错误，类似于必须在SELECT DISTINCT查询中包括排序列）。

To allow sorting by joined table columns, I've applied the "HINT_PASS_DISTINCT_THROUGH" hint to the relevant repository method(otherwise, sorting by joined table columns returns an error along the lines of "sort column must be included in the SELECT DISTINCT query").

进行这些更改之后，似乎可以按要求进行过滤和排序。但是，该提示似乎导致已经构造了结果页面之后才应用与众不同的过滤，从而将页面中返回的实体数从配置的 size PageRequest参数减少到过滤重复项后剩下的内容

After those changes, filtering and sorting seems to work as required. However, the hint seems to cause "distinct" filtering to be applied after the result page is already constructed, thus reducing the number of returned entities in the page from the configured "size" PageRequest argument, to whatever is left after the duplicates are filtered out.

我的问题是：

是否有可能消除使用distinct的需要（从而解决分页问题），通过在不同的 Specification 实例之间重用 Join 实例来实现？例如，构造 Join 实例，并将相同的 Join 实例传递到每个新的Specification实例中（例如，通过构造函数） ？

Is it possible to remove the need to use distinct (and thus solve the paging issue) by somehow reusing the Join instances amoung different Specification instances? For example construct the Join instances, and pass the same Join instance into each new Specification instance (e.g. via the constuctor)?

例如，我尝试创建以下内容，然后将此 JoinCache 实例传递给但是，每个 Specification 实例都有错误的别名错误，因此不确定是否还支持这样的东西？

For example, I've tried to create something like the following, and then passed this JoinCache instance into each Specification instance, however, I got errors about incorrect alias, so not sure if something like this is even supported?

public class JoinCache
{
    private final CriteriaBuilder criteriaBuilder;

    private final CriteriaQuery<Tag> criteriaQuery;

    private final Root<Tag> tagRoot;

    private final Join<Tag, Label> labelJoin;

    private final Join<Label, LabelIdentity> labelIdentityJoin;

    public JoinCache(final CriteriaBuilder criteriaBuilder)
    {
        this.criteriaBuilder = criteriaBuilder;
        this.criteriaQuery = this.criteriaBuilder.createQuery(Tag.class);
        this.tagRoot = criteriaQuery.from(Tag.class);
        this.labelJoin = tagRoot.join("labels", JoinType.INNER);
        this.labelIdentityJoin = labelJoin.join("labelIdentity", JoinType.INNER);
    }

    public Join<Tag, Label> getLabelJoin()
    {
        return labelJoin;
    }

    public Join<Label, LabelIdentity> getLabelIdentityJoin()
    {
        return labelIdentityJoin;
    }

    public CriteriaBuilder getCriteriaBuilder()
    {
        return criteriaBuilder;
    }

    public CriteriaQuery<Tag> getCriteriaQuery()
    {
        return criteriaQuery;
    }

    public Root<Tag> getTagRoot()
    {
        return tagRoot;
    } 
}

更新

使用子查询而不是联接的另一种方法（从而避免了使用完全不同的需求），但是，我认为JPA规范不支持按子查询排序/排序：

An alternative approach using subqueries instead of joins (thus avoiding the need to use distinct at all), however, I believe order by/sorting in subqueries are not supported in JPA specifications:

https：//hibernate.atlassian。 net / browse / HHH-256

public class MySpec implements Specification<Tag>
{
    @Override
    public Predicate toPredicate(
        final Root<Tag> root,
        final CriteriaQuery<?> query,
        final CriteriaBuilder builder)
    {
        final String argument = arguments.get(0);

        final Subquery<Label> subQuery = query.subquery(Label.class);

        final Root<Label> subRoot = subQuery.from(Label.class);

        final Predicate tagPredicate = builder.equal(subRoot.get("tag"), root);

        final Predicate labelKeyPredicate = builder.equal(subRoot.get("labelIdentity").get("key"), "owner");

        subQuery.select(subRoot).where(tagPredicate, labelKeyPredicate, builder.like(subRoot.get("value"), argument.replace('*', '%'));

        return builder.exists(subQuery);
    }
}

推荐答案

创建对外部查询有副作用的可重用谓词是一种不好的做法（我的意思是 query.distinct（true） ）。您可以使用子查询和存在谓词获得相同的结果。

It is a bad practice to create reusable predicates with side effects on external query (I mean query.distinct(true)). You can achieve the same result using subquery and exists predicate.

假设 Tag 实体具有 @Id Long id 字段

public final class MySpec implements Specification<Tag> {

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

        Subquery<Long> subquery = query.subquery(Long.class); // if entity id has Long type
        Root<Tag> subRoot = subquery.from(Tag.class);

        final Join<Tag, Label> label = subRoot.join("labels", JoinType.INNER);
        final Join<Label, LabelIdentity> labelIdentity = label.join("labelIdentity", JoinType.INNER);

        final Predicate externalQueryJoinPredicate =
            builder.equal(subRoot.get("id"), root.get("id"));
        final Predicate labelKeyPredicate = 
            builder.equal(labelIdentity.get("key"), property);
        final Predicate labelValuePredicate = 
            builder.like(label.get("value"), argument.replace('*', '%'));

        subquery.select(subRoot.get("id")).where( 
            externalQueryJoinPredicate, labelKeyPredicate, labelValuePredicate);

        return builder.exists(subquery);
     }
}

这篇关于将单个Join实例传递给多个Specification实例？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！