时间戳

首页 > 解决方案 > JPA Criteria API：使用 IF 条件聚合多个列

java - JPA Criteria API：使用 IF 条件聚合多个列

问题描述

我有以下 MySQL 架构 -

注意：我知道这不是一个理想的模式，但我正在使用遗留代码，所以我对完全更改模式没有太多控制权

CREATE TABLE `student` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    PRIMARY KEY (`id`)
);

CREATE TABLE `subject` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `student_id` INT(10) NOT NULL DEFAULT '0',
    `title` VARCHAR(255) NOT NULL DEFAULT '0' COLLATE 'utf8mb4_unicode_ci',
    `marks` INT(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
);

样本数据 -

INSERT INTO `student` (`id`, `name`) VALUES
    (1, 'Foo'),
    (2, 'Bar'),
    (3, 'Baz');

INSERT INTO `subject` (`id`, `student_id`, `title`, `marks`) VALUES
    (1, 1, 'math', 25),
    (2, 1, 'physics', 35),
    (3, 1, 'chemistry', 45),
    (4, 2, 'math', 85),
    (5, 2, 'physics', 65),
    (6, 2, 'chemistry', 65);

我尝试使用JPA Criteria API编写的查询-

SELECT st.name
     , max(if(sub.title = 'math', sub.marks, 0)) as math
     , max(if(sub.title = 'physics', sub.marks, 0)) as physics
     , max(if(sub.title = 'chemistry', sub.marks, 0)) as chemistry
FROM student st
LEFT OUTER JOIN subject sub ON st.id = sub.student_id
GROUP BY st.name;

样本结果 -

| name | math | physics | chemistry | 
| ---- | -----| --------| --------- | 
| Bar  | 85   | 65      | 65        | 
| Baz  | 0    | 0       | 0         | 
| Foo  | 25   | 35      | 45        |

到目前为止我尝试过的 -

CriteriaBuilder criteriaBuilder = this.entityManager.getCriteriaBuilder();
CriteriaQuery<StudentMark> dataQuery = criteriaBuilder.createQuery(StudentMark.class);

Root<Student> root = dataQuery.from(Student.class);
Join<Student, Subject> studentSubjectJoin = root.join(ReflectUtil.getFieldName(Student.class, Student::getSubjectMarks), JoinType.LEFT);

dataQuery.multiselect(root.get(ReflectUtil.getFieldName(Student.class, Student::getName)),
        criteriaBuilder.max(studentSubjectJoin.get(ReflectUtil.getFieldName(Subject.class, Subject::getValue))),
        criteriaBuilder.max(studentSubjectJoin.get(ReflectUtil.getFieldName(Subject.class, Subject::getValue))),
        criteriaBuilder.max(studentSubjectJoin.get(ReflectUtil.getFieldName(Subject.class, Subject::getValue))));

dataQuery.groupBy(root.get(ReflectUtil.getFieldName(Student.class, Student::getStudentId)));

this.entityManager.createQuery(dataQuery);

我无法弄清楚如何根据 SQL 查询在MAX聚合函数中编写IF 。

标签： javamysqlhibernatejpacriteria-api

解决方案

该查询相当于：

SELECT st.name
     , MAX(CASE WHEN sub.title = 'math' THEN sub.marks ELSE 0 END) as math
     , MAX(CASE WHEN sub.title = 'physics' THEN sub.marks ELSE 0 END) as physics
     , MAX(CASE WHEN sub.title = 'chemistry' THEN sub.marks ELSE 0 END) as chemistry
FROM student st
LEFT OUTER JOIN subject sub ON st.id = sub.student_id
GROUP BY st.name;

在标准 API 中：

dataQuery.multiselect(root.get(Student_.name),
    criteriaBuilder.max(
        criteriaBuilder.selectCase()
            .when(criteriaBuilder.equal(studentSubjectJoin.get(Subject_.title), criteriaBuilder.literal("math")), studentSubjectJoin.get(Subject_.marks))
            .otherwise(criteriaBuilder.literal(0))
    ).alias("math"),
    criteriaBuilder.max(...)
    ...
);

（为了清晰起见，我使用了静态元模型表示法而不是反射调用；作为旁注，您可能需要考虑这样做，它大大提高了可读性）

推荐阅读