java如何使用JPA条件查询编写此查询？

Question

有人能帮我获得下面提到的JPA查询的JPA条件查询吗

SELECT p,l FROM Person p 
LEFT JOIN Language l ON (p.language = l.language and l.locale like :locale) 
AND p.name like :name 
AND p.time BETWEEN :startDate 
AND :endDate order by name asc

Answer 1

假设这个人和语言有关系，下面是你在老年人冬眠时会做的：

Criteria criteria = entityManager.createCriteria(Person.class);
Criteria languageCriteria = criteria.createCriteria("language");

languageCriteria.add(Restrictions.like("locale", locale));

criteria.add(Restrictions.like("name", name));
criteria.add(Restrictions.between("time", startDate, endDate));

criteria.addOrder(Order.asc("name"));

我第一次尝试JPA 2.0：

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Person> criteria = builder.createCriteria(Person.class);
Root<Person> pRoot = criteria.from(Person.class);
Join<Person, Language> langJoin = criteria.join("language", JoinType.LEFT);

Predicate conjunction = builder.conjunction();

criteria.where(builder.and(
    builder.like(langJoin.get(Language_.locale), locale),
    builder.like(pRoot.get(Person_.name), name),
    builder.between(pRoot.get(Person_.time), startDate, endDate));

criteria.orderBy(builder.asc(pRoot.get(Person_.name)));

请让我知道这是否对你有效

编辑：更新查询，只使用一个where调用

Answer 2

虽然约翰卡尔给出的答案被接受了，但在我看来并不正确。用于CriteriaQuery的JavaDocs。你说：

Modify the query to restrict the query result according to the specified boolean expression. Replaces the previously added restriction(s), if any.

据我所知，以下每一行（给出限制）将覆盖前面给出的限制：

criteria.where(builder.like(langJoin.get(Language_.locale), locale));
criteria.where(builder.like(pRoot.get(Person_.name), name));
criteria.where(builder.between(pRoot.get(Person_.time), startDate, endDate));

这意味着最终只剩下最后一个限制（在开始日期和结束日期之间）

我再次建议对约翰卡尔的答案进行以下修改：

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Person> criteria = builder.createCriteria(Person.class);
Root<Person> pRoot = criteria.from(Person.class);
Join<Person, Language> langJoin = criteria.join("language", JoinType.LEFT);

Predicate[] restrictions = new Predicate[] { 
    builder.like(langJoin.get(Language_.locale), locale),
    builder.like(pRoot.get(Person_.name), name),
    builder.between(pRoot.get(Person_.time), startDate, endDate)
};

criteria.where(builder.and(restrictions));

criteria.orderBy(builder.asc(pRoot.get(Person_.name)));

然而，这段代码看起来真的很难看！请随意编辑，如果它是错误的，并评论它，如果你看到一个更好的解决方案！我会很优雅的