如何通过PostgreSQL与JPA实现薪资范围查询及聚合结果返回

步骤1：解析请求参数为范围对象 #

先把请求里的字符串格式范围（如"500-10000"）转换成自定义范围类，方便后续处理：

public class SalaryRange {
    private Integer min;
    private Integer max;

    // 从字符串解析范围，比如拆分"500-10000"赋值
    public SalaryRange(String rangeStr) {
        String[] parts = rangeStr.split("-");
        this.min = Integer.parseInt(parts[0].trim());
        this.max = Integer.parseInt(parts[1].trim());
    }

    // getter方法
}

场景A：传入非空范围列表——单次查询匹配所有区间 #

如果需要查询所有薪资落在任意区间内的记录，用Spring Data JPA Specification构建动态OR条件，一次执行查询：

1. 让Repository继承JpaSpecificationExecutor：

public interface EmployeeSalaryRepo extends JpaRepository<EmployeeRoleAndSalary, Long>, JpaSpecificationExecutor<EmployeeRoleAndSalary> {
}

2. 构建Specification：

public Specification<EmployeeRoleAndSalary> buildSalaryRangeSpec(List<SalaryRange> ranges) {
    return (root, query, cb) -> {
        List<Predicate> predicates = new ArrayList<>();
        for (SalaryRange range : ranges) {
            // 将salary字段转为Integer后做区间匹配
            Predicate between = cb.between(root.get("salary").as(Integer.class), range.getMin(), range.getMax());
            predicates.add(between);
        }
        return cb.or(predicates.toArray(new Predicate[0]));
    };
}

3. 调用查询：

List<EmployeeRoleAndSalary> result = salaryRepo.findAll(buildSalaryRangeSpec(parsedRanges));

如果需要按每个区间分组统计数量，用原生SQL+UNION ALL拼接所有区间的统计逻辑，单次执行：

// 动态构建SQL
StringBuilder sqlBuilder = new StringBuilder();
for (int i = 0; i < ranges.size(); i++) {
    SalaryRange r = ranges.get(i);
    if (i > 0) {
        sqlBuilder.append(" UNION ALL ");
    }
    sqlBuilder.append(String.format(
        "SELECT '%d-%d' AS salary_range, COUNT(id) AS emp_count FROM employee_role_and_salary WHERE CAST(salary AS INTEGER) BETWEEN %d AND %d",
        r.getMin(), r.getMax(), r.getMin(), r.getMax()
    ));
}

// 用EntityManager执行原生查询
Query query = entityManager.createNativeQuery(sqlBuilder.toString(), SalaryRangeStats.class);
List<SalaryRangeStats> stats = query.getResultList();

其中SalaryRangeStats是用于接收结果的DTO或接口投影。

场景B：范围列表为空——按默认10000区间分组统计 #

用PostgreSQL的generate_series生成自动覆盖薪资最大值的区间，结合左连接完成统计，直接在Repository中定义原生查询：

// 定义投影接口接收结果
public interface DefaultRangeStats {
    String getSalaryRange();
    Long getEmpCount();
}

@Query(value = """
    WITH intervals AS (
        SELECT 
            i * 10000 + 1 AS min_sal,
            (i + 1) * 10000 AS max_sal
        FROM generate_series(0, (SELECT COALESCE(MAX(CAST(salary AS INTEGER))/10000, 0) FROM employee_role_and_salary)) AS i
    )
    SELECT 
        CONCAT(intervals.min_sal, '-', intervals.max_sal) AS salary_range,
        COUNT(ers.id) AS emp_count
    FROM intervals
    LEFT JOIN employee_role_and_salary ers ON CAST(ers.salary AS INTEGER) BETWEEN intervals.min_sal AND intervals.max_sal
    GROUP BY intervals.min_sal, intervals.max_sal
    ORDER BY intervals.min_sal
""", nativeQuery = true)
List<DefaultRangeStats> getDefaultSalaryRangeStats();

方案1：自定义DTO+JPQL SELECT NEW语法 #

这是最稳定的方式，确保DTO有全参构造器：

1. 定义DTO类：

public class SalaryStats {
    private Integer maxSalary;
    private Integer minSalary;
    private Integer salaryDiff;

    // 必须是全参构造器，参数顺序要和查询结果一致
    public SalaryStats(Integer maxSalary, Integer minSalary, Integer salaryDiff) {
        this.maxSalary = maxSalary;
        this.minSalary = minSalary;
        this.salaryDiff = salaryDiff;
    }

    // getter方法
}

2. 在Repository中定义JPQL查询：

@Query("SELECT NEW com.yourpackage.SalaryStats(" +
        "MAX(CAST(e.salary AS INTEGER)), " +
        "MIN(CAST(e.salary AS INTEGER)), " +
        "MAX(CAST(e.salary AS INTEGER)) - MIN(CAST(e.salary AS INTEGER))" +
        ") FROM EmployeeRoleAndSalary e")
SalaryStats getSalaryStats();

注意：如果实体类中salary本身是Integer类型，去掉CAST(...)即可。

方案2：接口投影 #

如果之前投影报错，大概率是别名和getter的驼峰映射不匹配：

1. 定义投影接口：

public interface SalaryStatsProjection {
    Integer getMaxSalary();
    Integer getMinSalary();
    Integer getSalaryDiff();
}

2. 用原生SQL或JPQL查询，确保别名和接口getter对应：

// 原生SQL版本（和你提供的SQL一致）
@Query(value = """
    SELECT 
        max(cast(salary as INTEGER)) as maxSalary, 
        min(cast(salary as INTEGER)) as minSalary, 
        max(cast(salary as INTEGER)) - min(cast(salary as INTEGER)) as salaryDiff 
    FROM employee_role_and_salary
""", nativeQuery = true)
SalaryStatsProjection getSalaryStats();

别名maxSalary对应接口的getMaxSalary()，JPA会自动处理驼峰转换。

方案3：Tuple返回（快速调试用） #

如果不想定义DTO或接口，直接用Tuple接收结果：

@Query("SELECT " +
        "MAX(CAST(e.salary AS INTEGER)) as maxSalary, " +
        "MIN(CAST(e.salary AS INTEGER)) as minSalary, " +
        "(MAX(CAST(e.salary AS INTEGER)) - MIN(CAST(e.salary AS INTEGER))) as salaryDiff " +
        "FROM EmployeeRoleAndSalary e")
Tuple getSalaryStats();

调用时取值：

Tuple stats = salaryRepo.getSalaryStats();
Integer max = stats.get("maxSalary", Integer.class);
Integer min = stats.get("minSalary", Integer.class);
Integer diff = stats.get("salaryDiff", Integer.class);

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