How to call Stored Procedure from Spring Boot

1. This is stored procedure for getting a single employee by passing employee id
create or replace
PROCEDURE get_employee (
   employeeId IN VARCHAR2,
   employee OUT SYS_REFCURSOR )
AS
BEGIN
    OPEN employee FOR
    SELECT *
    FROM employee007
    WHERE employee_id = employeeId;
END;

2. Create Employee class. The important thing to note is we should have 2 constructors, default and with parameters. 

@Entity
@Table(name = "employee007")
@NamedStoredProcedureQuery(name = "getEmployeeFromStoredProcedure",
procedureName = "get_employee",
resultClasses = com.practice.employee.Employee.class)
public class Employee {
@Id
String employeeId;
String name;
String address;
String emailId;
       //default constructor
       //constructor with parameters
       //setters & getters
}

3. Create CustomEmployeeRepository interface

public interface CustomEmployeeRepository {
public Employee getEmployeeFromStoredProcedure(String empId);
}

4. Create EmployeeRepository interface

public interface EmployeeRepository extends JpaRepository<Employee, String>, CustomEmployeeRepository{}

5. Create EmployeeRepositoryImpl class

public class EmployeeRepositoryImpl implements CustomEmployeeRepository {

@PersistenceContext
private EntityManager em;

@Override
public Employee getEmployeeFromStoredProcedure(String empId) {
StoredProcedureQuery query = em.createStoredProcedureQuery("getEmployeeFromStoredProcedure")
.registerStoredProcedureParameter(1, String.class, ParameterMode.IN)
.registerStoredProcedureParameter(2, Class.class, ParameterMode.REF_CURSOR).setParameter(1, empId);
query.execute();
List<Object[]> employees = query.getResultList();
Object[] params = employees.get(0);
return new Employee(params[0].toString(), params[1].toString(), params[2].toString(), params[3].toString());
}
}

6. Create EmployeeService interface

public interface EmployeeService {
public Employee getEmployeeFromStoredProcedure(String empId);
}

7. Create EmployeeServiceImpl class

@Service
public class EmployeeServiceImpl implements EmployeeService{
@Autowired
EmployeeRepository employeeRepository;

@Override
public Employee getEmployeeFromStoredProcedure(String empId) {
return employeeRepository.getEmployeeFromStoredProcedure(empId);
}
}

8. Create EmployeeController class

@RestController
public class EmployeeController {
@Autowired
private EmployeeService employeeService;

//getting data from stored procedure
@RequestMapping("/employees/storedprocedure/{id}")
public Employee getEmployeeFromStoredProcedure(@PathVariable String id) {
return employeeService.getEmployee(id).get();
}
}

9. Create SpringBootStoredProcedureApplication class

public interface CustomEmployeeRepository {
public Employee getEmployeeFromStoredProcedure(String empId);
}

10. Make the call from browser http://localhost:8080/employees/storedprocedure/5
It will print Employee with id 5 if available