jpa条件生成器查询

5gfr0r5j 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(305)

我需要使用jpa criteria builder创建下面的sql连接条件，

SELECT * FROM student s1
INNER JOIN
(SELECT subject,teacher,MIN(marks) AS marks FROM student GROUP BY subject, teacher) s2
ON s1.subject = s2.subject
AND s1.teacher = s2.teacher
AND s1.marks = s2.marks

下面是实体类和jpa查询生成器。

@Entity
@JsonIgnoreProperties(ignoreUnknown = true)
public class Student implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @Column(name="id")
    Long id;

    @Column(name="name")
    String name;

    @Column(name="subject")
    public
    String subject;

    @Column(name="teacher")
    String teacher;

    @Column(name="marks")
    String marks;

    @JsonIgnore
    @ManyToOne
    @JoinColumns({
    @JoinColumn(insertable=false, updatable=false, name="subject",referencedColumnName="subject"),
    @JoinColumn(insertable=false, updatable=false, name="teacher",referencedColumnName="teacher"),
    @JoinColumn(insertable=false, updatable=false, name="marks",referencedColumnName="marks")
    })
    Student studentSelf;

    @JsonIgnore
    @OneToMany(cascade = CascadeType.ALL, mappedBy="studentSelf")
    Set<Student> studentref;

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Student> query = cb.createQuery(Student.class);
Root<Student> mainStudent = query.from(Student.class);
List<Predicate> predicates = new ArrayList<>();

Join<Student, Student> studentJoin = mainStudent.join("studentSelf", JoinType.INNER);

List<Student> list = entityManager.createQuery(query).getResultList();

我可以用连接条件构建查询，但无法创建内部select查询。如何为join子句创建内部select查询？
需求描述：以下是所需的输入表和输出。

sql spring-boot spring-data-jpa self-join criteria-api

来源：https://stackoverflow.com/questions/62978164/jpa-criteria-builder-query-with-inner-select-statement

2条答案

按热度按时间

qacovj5a1#

我没有试过这个，它失败得很惨，只有一个例外。但你能试试吗？
拆下 studentSelf 以及 studentRef 如果只是为了这个查询而添加它们@多对一 on studentself也不是，因为它会指向许多记录
我认为下面的查询相当于您试图实现的目标。

SELECT * 
    FROM student s1
    WHERE s1.marks 
    IN
    (
      SELECT MIN(s2.marks) 
      FROM student s2
      where s1.subject = s2.subject
      AND  s1.teacher = s2.teacher
    )

我想把它翻译成 CriteriaQuery ```
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery query = cb.createQuery(Student.class);
Root mainStudent = query.from(Student.class);

Subquery<Student> subQuery = query.subquery(Student.class);
Root<Student> subQueryRoot = subQuery.from(Student.class);

Predicate predicate1 = cb.equal(mainStudent.get("teacher"), 
                                subQueryRoot.get("teacher"));
Predicate predicate2 = cb.equal(mainStudent.get("subject"), 
                                subQueryRoot.get("subject"));
Predicate finalPredicate = cb.and(predicate1, predicate2);

subQuery.select(cb.min(subQueryRoot.get("marks"))).where(finalPredicate);

query.select(mainStudent).where(mainStudent.get("marks").in(subQuery));
em.createQuery(issueQuery).getResultList();

更新
更新 `subquery.correlate(mainStudent);` 至 `subQuery.from(Student.class);` 基于 `vinay-s-g` 评论

赞(0）回复(0）举报 2021-07-24

rdrgkggo2#

对上述答案做了一些小改动，

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<Student> query = cb.createQuery(Student.class);
        Root<Student> mainStudent = query.from(Student.class);

        Subquery<Student> subQuery = query.subquery(Student.class);
        Root subQueryRoot = subQuery.from(Student.class);

        Predicate predicate1 = cb.equal(mainStudent.get("teacher"), 
                                        subQueryRoot.get("teacher"));
        Predicate predicate2 = cb.equal(mainStudent.get("subject"), 
                                        subQueryRoot.get("subject"));
        Predicate finalPredicate = cb.and(predicate1, predicate2);

        subQuery.select(cb.min(subQueryRoot.get("marks"))).where(finalPredicate);

        query.select(mainStudent).where(mainStudent.get("marks").in(subQuery));
        return entityManager.createQuery(query).getResultList();

赞(0）回复(0）举报 2021-07-24

我来回答

jpa条件生成器查询

2条答案

相关问题

热门标签

最新问答