Spring Boot JPA Specification实现员工记录查询及SQL优化问询

一、原SQL的合理性分析 #

你的SQL逻辑是正确的，能够满足需求：先返回指定EMP_ID的ACTIVE状态记录，无ACTIVE时返回版本号最高的记录。但效率存在优化空间——针对同一个EMP_ID执行了3次独立查询（主查询、exists子查询、max版本子查询），数据量较大时会重复扫描表，增加数据库开销。

二、更高效的SQL实现方式 #

1. 窗口函数写法（推荐，支持MySQL 8.0+、PostgreSQL、Oracle等） #

利用ROW_NUMBER()窗口函数对同一EMP_ID的记录排序：ACTIVE状态优先级最高（排号为1），非ACTIVE记录按版本号降序排列，最后取排号为1的记录。仅需扫描一次表，效率最优：

SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY EMP_ID 
                              ORDER BY CASE WHEN EMP_STATUS = 'ACTIVE' THEN 0 ELSE 1 END, 
                                       EMP_VERSION DESC) AS rn
    FROM myschema.employee
    WHERE EMP_ID = '123'
) t
WHERE rn = 1;

2. UNION ALL写法（兼容老版本数据库） #

先查询ACTIVE状态记录，若有结果直接返回；若无结果，再查询该EMP_ID下版本号最大的记录。数据库会优先执行第一个查询，避免不必要的计算：

SELECT * FROM myschema.employee 
WHERE EMP_ID = '123' AND EMP_STATUS = 'ACTIVE'
UNION ALL
SELECT * FROM myschema.employee 
WHERE EMP_ID = '123' 
  AND NOT EXISTS (SELECT 1 FROM myschema.employee WHERE EMP_ID = '123' AND EMP_STATUS = 'ACTIVE')
  AND EMP_VERSION = (SELECT MAX(EMP_VERSION) FROM myschema.employee WHERE EMP_ID = '123')
LIMIT 1;

三、Hibernate Specification实现 #

假设实体类为Employee，对应字段：empId（EMP_ID）、empVersion（EMP_VERSION）、empStatus（EMP_STATUS）、empName（EMP_NAME），以下是两种对应上述SQL的实现：

1. 对应窗口函数的Specification #

import org.springframework.data.jpa.domain.Specification;
import javax.persistence.criteria.*;

public class EmployeeSpecifications {

    public static Specification<Employee> findPreferredEmployee(String empId) {
        return (root, query, cb) -> {
            // 构建子查询并添加行号排序规则
            Subquery<Employee> subquery = query.subquery(Employee.class);
            Root<Employee> subRoot = subquery.from(Employee.class);

            // 定义优先级：ACTIVE状态排第一，其余按版本号降序
            Expression<Integer> priority = cb.selectCase()
                    .when(cb.equal(subRoot.get("empStatus"), "ACTIVE"), 0)
                    .otherwise(1);
            Expression<Integer> rowNumber = cb.rowNumber()
                    .over()
                    .partitionBy(subRoot.get("empId"))
                    .orderBy(priority, cb.desc(subRoot.get("empVersion")));

            // 子查询筛选指定EMP_ID的记录
            subquery.select(cb.construct(Employee.class,
                            subRoot.get("empId"),
                            subRoot.get("empVersion"),
                            subRoot.get("empStatus"),
                            subRoot.get("empName")))
                    .where(cb.equal(subRoot.get("empId"), empId));

            // 主查询取行号为1的记录
            query.select(root)
                    .from(subquery)
                    .where(cb.equal(rowNumber, 1));

            return cb.conjunction();
        };
    }
}

2. 对应UNION ALL的Specification #

import org.springframework.data.jpa.domain.Specification;
import javax.persistence.criteria.*;

public class EmployeeSpecifications {

    public static Specification<Employee> findPreferredEmployee(String empId) {
        return (root, query, cb) -> {
            // 构建ACTIVE状态查询
            CriteriaQuery<Employee> activeQuery = cb.createQuery(Employee.class);
            Root<Employee> activeRoot = activeQuery.from(Employee.class);
            activeQuery.select(activeRoot)
                    .where(cb.and(
                            cb.equal(activeRoot.get("empId"), empId),
                            cb.equal(activeRoot.get("empStatus"), "ACTIVE")
                    ));

            // 构建最大版本查询（无ACTIVE时触发）
            CriteriaQuery<Employee> maxVersionQuery = cb.createQuery(Employee.class);
            Root<Employee> maxRoot = maxVersionQuery.from(Employee.class);

            // 子查询：获取该EMP_ID的最大版本号
            Subquery<Integer> maxVersionSubquery = maxVersionQuery.subquery(Integer.class);
            Root<Employee> maxSubRoot = maxVersionSubquery.from(Employee.class);
            maxVersionSubquery.select(cb.max(maxSubRoot.get("empVersion")))
                    .where(cb.equal(maxSubRoot.get("empId"), empId));

            // 子查询：判断是否存在ACTIVE记录
            Subquery<Long> existsActiveSubquery = maxVersionQuery.subquery(Long.class);
            Root<Employee> existsRoot = existsActiveSubquery.from(Employee.class);
            existsActiveSubquery.select(cb.count(existsRoot))
                    .where(cb.and(
                            cb.equal(existsRoot.get("empId"), empId),
                            cb.equal(existsRoot.get("empStatus"), "ACTIVE")
                    ));

            maxVersionQuery.select(maxRoot)
                    .where(cb.and(
                            cb.equal(maxRoot.get("empId"), empId),
                            cb.equal(maxRoot.get("empVersion"), maxVersionSubquery),
                            cb.equal(existsActiveSubquery, 0L)
                    ));

            // 合并两个查询，仅取第一条结果
            query.union(activeQuery, maxVersionQuery);
            query.setMaxResults(1);

            return cb.conjunction();
        };
    }
}

内容的提问来源于stack exchange，提问作者Lolly