Spring Data JPA调用存储过程无法获取多结果集的技术问询

问题背景 #

调用返回Employees和Departments两个结果集的存储过程时，Spring Data JPA的@Procedure注解仅能获取第一个结果集，无法获取第二个。

一、正确获取多结果集的方案 #

Spring Data JPA原生注解不支持自动映射多个不同实体的结果集，需要手动通过EntityManager结合JDBC API，或直接使用Spring JDBC Template来处理。

方案1：基于EntityManager手动处理 #

通过EntityManager获取底层JDBC连接，手动遍历存储过程返回的多个结果集：

1. 定义自定义Repository接口

public interface EmployeeCustomRepository {
    Map<String, List<?>> getEmployeesAndDepartments();
}

2. 实现自定义Repository

@Repository
public class EmployeeRepositoryImpl implements EmployeeCustomRepository {

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public Map<String, List<?>> getEmployeesAndDepartments() {
        Map<String, List<?>> resultMap = new HashMap<>();
        Session session = entityManager.unwrap(Session.class);
        
        session.doWork(connection -> {
            try (CallableStatement callableStmt = connection.prepareCall("{call SearchEmployees()}")) {
                boolean hasNextResult = callableStmt.execute();
                
                // 处理第一个结果集：Employees
                if (hasNextResult) {
                    List<Employee> employees = new ArrayList<>();
                    ResultSet rs = callableStmt.getResultSet();
                    while (rs.next()) {
                        Employee emp = new Employee();
                        emp.setEmployeeID(rs.getInt("EmployeeID"));
                        emp.setFirstName(rs.getString("FirstName"));
                        emp.setLastName(rs.getString("LastName"));
                        emp.setPosition(rs.getString("Position"));
                        emp.setHireDate(rs.getObject("HireDate", LocalDate.class));
                        emp.setSalary(rs.getBigDecimal("Salary"));
                        // 可手动关联Department或后续统一处理关联关系
                        employees.add(emp);
                    }
                    resultMap.put("employees", employees);
                }
                
                // 切换到第二个结果集：Departments
                hasNextResult = callableStmt.getMoreResults();
                if (hasNextResult) {
                    List<Department> departments = new ArrayList<>();
                    ResultSet rs = callableStmt.getResultSet();
                    while (rs.next()) {
                        Department dept = new Department();
                        dept.setDepartmentID(rs.getInt("DepartmentID"));
                        dept.setDepartmentName(rs.getString("DepartmentName"));
                        departments.add(dept);
                    }
                    resultMap.put("departments", departments);
                }
            } catch (SQLException e) {
                throw new RuntimeException("执行存储过程失败", e);
            }
        });
        
        return resultMap;
    }
}

3. 让主Repository继承自定义接口

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Integer>, EmployeeCustomRepository {
}

4. 控制器调用

public Map<String, List<?>> getEmployeeData() {
    return employeeRepository.getEmployeesAndDepartments();
}

方案2：使用Spring JDBC Template（生产环境推荐） #

JDBC Template对多结果集的处理更直接，代码更简洁，性能表现更稳定：

@Repository
public class EmployeeDeptRepository {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public Map<String, List<?>> getEmployeesAndDepartments() {
        return jdbcTemplate.execute("{call SearchEmployees()}", (CallableStatementCallback<Map<String, List<?>>>) cs -> {
            Map<String, List<?>> resultMap = new HashMap<>();
            boolean hasNextResult = cs.execute();
            
            // 处理Employees结果集
            if (hasNextResult) {
                List<Employee> employees = new ArrayList<>();
                ResultSet rs = cs.getResultSet();
                while (rs.next()) {
                    Employee emp = populateEmployee(rs);
                    employees.add(emp);
                }
                resultMap.put("employees", employees);
            }
            
            // 处理Departments结果集
            hasNextResult = cs.getMoreResults();
            if (hasNextResult) {
                List<Department> departments = new ArrayList<>();
                ResultSet rs = cs.getResultSet();
                while (rs.next()) {
                    Department dept = new Department();
                    dept.setDepartmentID(rs.getInt("DepartmentID"));
                    dept.setDepartmentName(rs.getString("DepartmentName"));
                    departments.add(dept);
                }
                resultMap.put("departments", departments);
            }
            
            return resultMap;
        });
    }

    // 抽取实体映射逻辑，提高代码复用性
    private Employee populateEmployee(ResultSet rs) throws SQLException {
        Employee emp = new Employee();
        emp.setEmployeeID(rs.getInt("EmployeeID"));
        emp.setFirstName(rs.getString("FirstName"));
        emp.setLastName(rs.getString("LastName"));
        emp.setPosition(rs.getString("Position"));
        emp.setHireDate(rs.getObject("HireDate", LocalDate.class));
        emp.setSalary(rs.getBigDecimal("Salary"));
        return emp;
    }
}

二、更好的处理方式（生产环境最佳实践） #

1. 拆分存储过程 #

将返回多结果集的存储过程拆分为两个独立的存储过程：GetEmployees()和GetDepartments()，然后通过Spring Data JPA的@Procedure分别调用：

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Integer> {
    @Procedure(procedureName = "GetEmployees")
    List<Employee> getEmployees();
}

@Repository
public interface DepartmentRepository extends JpaRepository<Department, Integer> {
    @Procedure(procedureName = "GetDepartments")
    List<Department> getDepartments();
}

这种方式符合单一职责原则，代码更易维护、测试，且原生支持Spring Data JPA的分页、排序等特性。

2. 服务层封装结果 #

如果必须保留单个存储过程，可在服务层调用多个独立查询（或拆分后的存储过程），将结果封装到统一的响应DTO中：

@Service
public class EmployeeDeptService {

    @Autowired
    private EmployeeRepository employeeRepo;
    
    @Autowired
    private DepartmentRepository deptRepo;

    public EmployeeDeptResponse getEmployeeDeptData() {
        EmployeeDeptResponse response = new EmployeeDeptResponse();
        response.setEmployees(employeeRepo.getEmployees());
        response.setDepartments(deptRepo.getDepartments());
        return response;
    }
}

// 响应DTO
@Setter
@Getter
public class EmployeeDeptResponse {
    private List<Employee> employees;
    private List<Department> departments;
}

3. 避免多结果集存储过程 #

除非业务强制要求，否则尽量避免返回多结果集的存储过程：

• 多结果集难以在ORM框架中自动映射，增加代码复杂度
• 拆分后的查询更易优化、监控和扩展

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