Java 带有 QueryDslPredicateExecutor 并加入集合的 Spring-Data-JPA

Spring-Data-JPA with QueryDslPredicateExecutor and Joining into a collection

提问by digitaljoel

Let's say I have a data model like this (pseudocode):

假设我有一个这样的数据模型（伪代码）：

@Entity
Person {
    @OneToMany
    List<PersonAttribute> attributes;
}

@Entity
PersonAttribute {
    @ManyToOne
    AttributeName attributeName;

    String attributeValue;
}

@Entity
AttributeName {
    String name;
}

I have a Spring-Data-JPA repository defined such as:

我定义了一个 Spring-Data-JPA 存储库，例如：

public interface PersonRepository extends PagingAndSortingRepository<Person, Long>, QueryDslPredicateExecutor<Person>{}

I see in the QueryDSL documentation that there is a mechanism to Join from the Person to the PersonAttribute, but it looks like you need access to the QueryDsl Query object, which the client of the repository wouldn't have.

我在 QueryDSL 文档中看到有一种机制可以从 Person 加入到 PersonAttribute，但看起来您需要访问 QueryDsl Query 对象，而存储库的客户端则没有。

What I would like to do with my Predicate is to find all those Persons that have an AttributeValue (there's one join) with a value of "blue" and an AttributeName (there's another join) with a name of "eyecolor". I'm not sure how I would do that with an any()and enforce that I only get those with eye_color=blue and not those with shoe_color=blue.

我想用我的 Predicate 做的是找到所有那些具有值为“blue”的 AttributeValue（有一个连接）和一个名为“eyecolor”的 AttributeName（还有另一个连接）的人。我不确定我将如何使用 anany()并强制我只获取具有 eye_color=blue 的那些而不是具有 shoes_color=blue 的那些。

I was hoping I could do something like this:

我希望我能做这样的事情：

QPerson person = QPerson.person;
QPersonAttribute attribute = person.attributes.any();

Predicate predicate = person.name.toLowerCase().startsWith("jo")
    .and(attribute.attributeName().name.toLowerCase().eq("eye color")
          .and(attribute.attributeValue.toLowerCase().eq("blue")));

but with the any()in there it just matches anything with an attribute value of "blue" and anything with an "eye color" attribute regardless of color. How I can make those conditions apply to the same attribute within the set?

但是any()在那里它只会​​匹配具有“蓝色”属性值的任何东西以及具有“眼睛颜色”属性的任何东西，而不管颜色如何。 如何使这些条件适用于集合中的相同属性？

采纳答案by Timo Westk?mper

You can't directly join a column in a predicate but you can create an any() expressions like this

您不能直接在谓词中加入列，但您可以创建这样的 any() 表达式

QPerson.person.attributes.any().attributeValue.eq("X")

This approach has the restriction that the join expression QPerson.person.attributes.any()can be used in only one filter. It has though the benefit that this expression is internally converted into a subquery which doesn't conflict with paging.

这种方法有一个限制，即连接表达式QPerson.person.attributes.any()只能在一个过滤器中使用。它的好处是这个表达式在内部被转换成一个不与分页冲突的子查询。

For multiple restrictions you will need to construct a subquery expression explicitly like this

对于多个限制，您需要像这样显式地构造一个子查询表达式

QPersonAttribute attribute = QPersonAttribute.personAttribute;
new JPASubQuery().from(attribute)
    .where(attribute.in(person.attributes),
           attribute.attributeName().name.toLowerCase().eq("eye color"),
           attribute.attributeValue.toLowerCase().eq("blue"))
     .exists()

In addition to QueryDslPredicateExecutoryou can also use Querydsl queries via Spring Data like this

此外，QueryDslPredicateExecutor您还可以像这样通过 Spring Data 使用 Querydsl 查询

public class CustomerRepositoryImpl
 extends QueryDslRepositorySupport
 implements CustomerRepositoryCustom {

    public Iterable<Customer> findAllLongtermCustomersWithBirthday() {
        QCustomer customer = QCustomer.customer;
        return from(customer)
           .where(hasBirthday().and(isLongTermCustomer()))
           .list(customer);
    }
}

Example taken from here https://blog.42.nl/articles/spring-data-jpa-with-querydsl-repositories-made-easy/

示例取自此处https://blog.42.nl/articles/spring-data-jpa-with-querydsl-repositories-made-easy/

回答by Micha? Stochmal

In order to perform more complex queries I've created my custom QueryDslRepositorywith support of JPQL queries and spring data JPA pagination.

为了执行更复杂的查询，我创建QueryDslRepository了支持 JPQL 查询和 spring 数据 JPA 分页的自定义。

Interface:

界面：

public interface QueryDslRepository<T> {

    Page<T> findAll(JPQLQuery<T> jpqlQuery, Pageable pageable);

}

Implementation:

执行：

@Repository
public class QueryDslRepositoryImpl<T> implements QueryDslRepository<T> {

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    @SuppressWarnings("unchecked")
    public Page<T> findAll(JPQLQuery jpqlQuery, Pageable pageable) {
        Assert.notNull(jpqlQuery, "JPQLQuery must not be null!");
        Assert.notNull(pageable, "Pageable must not be null!");

        Querydsl querydsl = new Querydsl(entityManager, new PathBuilderFactory()
                                         .create(jpqlQuery.getType()));

        JPQLQuery<T> countQuery = ((AbstractJPAQuery) jpqlQuery).clone(entityManager);
        AbstractJPAQuery query = (AbstractJPAQuery) querydsl.applyPagination(pageable, jpqlQuery);
        return PageableExecutionUtils.getPage(
                  // Clone query in order to provide entity manager instance.
                  query.clone(entityManager).fetch(), 
                  pageable, 
                  countQuery::fetchCount);
    }

}

Example of use:

使用示例：

@Repository
public interface CustomerRepository extends JpaRepository<Customer, Long>, QueryDslRepository<Customer>,
        QuerydslPredicateExecutor<Customer> {

}

Actual repository invocation:

实际存储库调用：

 BooleanBuilder predicates = new BooleanBuilder();
 predicates = predicates.and(QCustomer.customer.active.eq(true));

 JPQLQuery<Customer> q = new JPAQuery<Customer>()
            .select(QCustomer.customer)
            // You can use .join() method here.
            .where(predicates);

 Page<Customer> result = customerRepository.findAll(q, Pageable.unpaged());