Java POJO映射含IF条件SELECT语句的非SELECT DISTINCT方案咨询

可行映射方案如下： #

1. SQL层GROUP BY聚合方案 #

你当前左连接产生的重复行可以通过分组替代SELECT DISTINCT，直接按教师唯一标识（teacher_id）+ 其他非聚合查询字段分组，三个薪资字段用SUM/MAX聚合即可，SQL示例：

SELECT 
  t.teacher_id,
  -- 其他需要查询的教师基础字段
  SUM(IF(job = 'teacher' AND teachers_department.id = 1, teacher_salary, 0)) AS teacher_salary_by_department_one,
  SUM(IF(job = 'teacher' AND teachers_department.id = 2, teacher_salary, 0)) AS teacher_salary_by_department_two,
  SUM(IF(job = 'teacher' AND teachers_department.id = 3, teacher_salary, 0)) AS teacher_salary_by_department_three
FROM 主表 t
LEFT JOIN teacher_salary ts ON t.teacher_id = ts.teacher_id
LEFT JOIN teachers_department td ON ts.department_id = td.id
GROUP BY t.teacher_id -- 补充所有非聚合的查询字段

单教师单部门薪资的场景下，SUM和MAX效果一致，分组后每行对应唯一教师，ORM框架可直接按字段名自动映射到POJO属性。

2. ORM框架自定义结果映射方案 #

MyBatis场景

可通过<resultMap>显式绑定字段与POJO属性，也可以先关联查询出教师的所有薪资集合，再通过POJO的get方法动态计算三个部门薪资，无需在SQL中写IF逻辑：

<resultMap id="TeacherPojoMap" type="com.xxx.TeacherPojo">
  <id property="teacherId" column="teacher_id"/>
  <!-- 其他基础字段映射 -->
  <collection property="salaryList" ofType="com.xxx.TeacherSalary" 
              column="teacher_id" select="querySalaryByTeacherId"/>
</resultMap>

POJO中补充逻辑：

// 临时存储薪资集合，不需要可设置为transient
private List<TeacherSalary> salaryList;

public BigDecimal getTeacher_salary_by_department_one() {
  return salaryList.stream()
    .filter(s -> "teacher".equals(s.getJob()) && s.getDepartmentId() == 1)
    .map(TeacherSalary::getTeacherSalary)
    .findFirst()
    .orElse(BigDecimal.ZERO);
}
// 另外两个部门薪资同理实现对应get方法

JPA/Hibernate场景

直接在POJO字段上使用@Formula注解注入计算逻辑，查询时框架会自动拼接成查询字段完成映射：

@Formula("IF(job = 'teacher' AND teachers_department.id = 1, teacher_salary, 0)")
private BigDecimal teacher_salary_by_department_one;

@Formula("IF(job = 'teacher' AND teachers_department.id = 2, teacher_salary, 0)")
private BigDecimal teacher_salary_by_department_two;

@Formula("IF(job = 'teacher' AND teachers_department.id = 3, teacher_salary, 0)")
private BigDecimal teacher_salary_by_department_three;

确保实体类的表关联配置正确即可正常使用。

3. Java服务层手动聚合方案 #

先查询所有关联后的原始数据，在代码层按teacher_id分组后遍历计算三个字段的值，示例逻辑：

// 查询原始关联数据
List<Map<String, Object>> rawRecords = jdbcTemplate.queryForList(rawQuerySql);
// 按教师ID分组
Map<Long, List<Map<String, Object>>> teacherGroup = rawRecords.stream()
  .collect(Collectors.groupingBy(record -> (Long) record.get("teacher_id")));

List<TeacherPojo> result = new ArrayList<>();
for (Map.Entry<Long, List<Map<String, Object>>> entry : teacherGroup.entrySet()) {
  TeacherPojo pojo = new TeacherPojo();
  pojo.setTeacherId(entry.getKey());
  BigDecimal dep1Salary = BigDecimal.ZERO, dep2Salary = BigDecimal.ZERO, dep3Salary = BigDecimal.ZERO;
  
  for (Map<String, Object> record : entry.getValue()) {
    String job = (String) record.get("job");
    Integer depId = (Integer) record.get("department_id");
    BigDecimal salary = (BigDecimal) record.get("teacher_salary");
    if ("teacher".equals(job) && depId == 1) {
      dep1Salary = salary;
    } else if ("teacher".equals(job) && depId == 2) {
      dep2Salary = salary;
    } else if ("teacher".equals(job) && depId == 3) {
      dep3Salary = salary;
    }
  }
  
  pojo.setTeacher_salary_by_department_one(dep1Salary);
  pojo.setTeacher_salary_by_department_two(dep2Salary);
  pojo.setTeacher_salary_by_department_three(dep3Salary);
  result.add(pojo);
}

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