I’m having trouble fetching multiple result sets from a stored procedure using Spring Data JPA. I have a stored procedure that returns two result sets: one for Employees and one for Departments. However, I’m unable to fetch these result sets properly. Here’s what I’ve done so far:

1. Stored Procedure

Here’s the SQL code for my stored procedure:

DELIMITER //

CREATE PROCEDURE SearchEmployees()
BEGIN
    SELECT * FROM Employees;
    SELECT * FROM Departments;
END //

DELIMITER ;

2. Entity Classes

Employee Entity:

@Entity
@Table(name = "Employees")
@Setter
@Getter
@NamedStoredProcedureQuery(
        name = "EmployeeDeptProc",
        procedureName = "SearchEmployees",
        resultClasses = {
                Employee.class, Department.class
        }
)
public class Employee {

    @Id
    private Integer EmployeeID;
    private String FirstName;
    private String LastName;

    @ManyToOne
    @JoinColumn(name = "DepartmentID")
    private Department DepartmentID;
    private String Position;
    private LocalDate HireDate;
    private BigDecimal Salary;
}

Department Entity:

@Entity
@Table(name = "Departments")
@Setter
@Getter
public class Department {

    @Id
    private Integer DepartmentID;
    private String DepartmentName;
}

3. Repository

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

4. Controller

public List<Object> getEmployeeData() {
    return employeeRepository.getEmployees();
}

Issue

When I call the getEmployeeData() method from the controller, I only get the first result set (Employees). I am unable to retrieve the second result set (Departments).

Things I’ve Checked:

1. I have specified the physical naming strategy in application.properties as:

   spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
   

2. I have verified that the stored procedure is correctly defined in the database.
   And I found many latest techniques for this for getting only one resultset but not for getting many different resultsets. If I am using entity manager also I am unable to fetch multiple resultsets.
   Any help or guidance would be greatly appreciated!

3. How can I properly fetch multiple resultsets from a stored procedure using Spring Data JPA?

4. Is there a better approach to handle this situation?

By using the latest JPA techniques. Could you please provide some techniques in the latest spring boot for real-time projects?