将SQL'IN'子查询转换为JPA标准查询的问题 [英] Problem with translating SQL 'IN' subquery into a JPA Criteria Query

问题描述

我正在尝试将此SQL查询转换为JPA标准查询：

I'm trying to translate this SQL query into a JPA Criteria Query:

select distinct student0_.name 
from vnic03.student student0_
where (exists(select teacher0_.social_number
          from vnic03.teacher teacher0_
          where teacher0.social_number = ?
            and teacher0_.school_id in (select school0_.id
                                               from vnic03.school school0_
                                               where school0_.student_id = student0_.id)))  

这些是表（我已经简化并重命名了它们以便在此处发布它们，实际上它们有几百万个条目）：

These are the tables (I have simplified and renamed them for posting them here, in reallity they have several million entries):

现在我有以下代码：

CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery<String> searchQuery = criteriaBuilder.createQuery(String.class);
Root<Student> root = searchQuery.from(Student.class);
List<Predicate> restrictions = new ArrayList<>();

Subquery<Teacher> subQuery = searchQuery.subquery(Teacher.class);
Root<Teacher> fromSchoolSubQuery = subQuery.from(Teacher.class);
List<Predicate> subRestrictions = new ArrayList<>();

Subquery<School> subQuery2 = searchQuery.subquery(School.class);
Root<School> fromSchoolSubSubQuery = subQuery2.from(School.class);
List<Predicate> subSubRestrictions = new ArrayList<>();

subRestrictions.add(criteriaBuilder.equal(fromSchoolSubQuery.get(Social_number), userInput));
subRestrictions.add(criteriaBuilder.equal(fromSchoolSubQuery.get(School_ID), subQuery2.select(fromSchoolSubSubQuery.get(School_ID)).where(criteriaBuilder.equal(fromSchoolSubSubQuery.get(Student_ID), root.get(student_ID)))));

restrictions.add(criteriaBuilder.exists(subQuery.select(
fromSchoolSubQuery.get(Social_number)).where(
subRestrictions.toArray(new Predicate[0]))));

searchQuery.distinct(true)
            .select(root.get(name))
            .where( restrictions.toArray(new Predicate[restrictions.size()]) );

TypedQuery<String> query = em.createQuery(searchQuery)
List<String> nameList = query.getResultList();

但这可转换为：

select distinct student0_.name 
from vnic03.student student0_
where (exists(select teacher0_.social_number
          from vnic03.teacher teacher0_
          where teacher0.social_number = ?
          and teacher0_.school_id = (select school0_.id
                                           from vnic03.school school0_
                                           where school0_.student_id = student0_.id)))

所以我只需要将 = 替换为 in 在最后的和部分中。我在其他SO问题中发现了这样的问题：

So I just need to replace the = by in in the last and part. I found in other SO questions something like this:

CriteriaBuilder.In<String> in = criteriaBuilder.in( ??? );

或

Path<Object> path = root.get(student_ID);
CriteriaBuilder.In<Object> in = criteriaBuilder.in(path);

但我只是不知道如何正确使用它...

but I just don't know how to use it properly...

因此，如果您只知道翻译此部分，它可能已经为我解决了：

So if you know how to translate only this part, it would solve it for me probably already:

where teacher0_.school_id **in** (select school0_.id
from vnic03.school school0_
where school0_.student_id = student0_.id))) 

推荐答案

阅读本文第5章后，我找到了一个解决方案： https://www.baeldung.com/jpa-criteria-api-in-expressions

I found a Solution after reading chapter 5 in this article: https://www.baeldung.com/jpa-criteria-api-in-expressions

Subquery<School> subQueryForInExpression = searchQuery.subquery(School.class);
Root<School> fromSchoolSubQuery = subQueryForInExpression.from(School.class);

subQueryForInExpression.select(fromSchoolSubQuery.get(student_id)).where(criteriaBuilder.equal(fromSchoolSubQuery.get(school_id), root.get(student_id)));

subQueryForInExpression 表示在 IN 表达式中选择子查询：

The subQueryForInExpression represents the Select subquery in the IN Expression:

select school0_.id
from vnic03.school school0_
where school0_.student_id = student0_.id

现在我们必须在subRestrictions中添加 in 表达式，这是通过 CriterisBuilder.in（...）完成的.value（subQueryForInExpression）：

Now we have to add the in Expression to the subRestrictions, this is done with CriterisBuilder.in(...).value(subQueryForInExpression):

subRestrictions.add(criteriaBuilder.in(fromSchoolSubQuery.get(school_id)).value(subQueryForInExpression));

这篇关于将SQL'IN'子查询转换为JPA标准查询的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！