[Java] [Spring] [JDBC] [Oracle] How to call stored procedure - example 2

Assume I have a stored procedure as bellows:

create or replace PACKAGE PACKAGE1 AS 
     PROCEDURE TEST_EMPLOYEE 
    (
      I_GENDER IN VARCHAR2 DEFAULT 'M',
      O_RESULT OUT SYS_REFCURSOR,
      o_return_code OUT INT
    );
END PACKAGE1;

create or replace PACKAGE BODY PACKAGE1
AS
   PROCEDURE TEST_EMPLOYEE 
    (
      I_GENDER IN VARCHAR2 DEFAULT 'M',
      O_RESULT OUT SYS_REFCURSOR,
      o_return_code OUT INT 
    ) AS 
    BEGIN
      OPEN O_RESULT FOR 
      SELECT ID, NAME, PHONE, ADDRESS, GENDER
      FROM EMPLOYEE 
      WHERE GENDER = I_GENDER;
      o_return_code := 0;
    END TEST_EMPLOYEE;
END PACKAGE1;

Step1. Create a custom repository interface:

package com.example.jpa.repository.custom;

import java.util.Map;

public interface EmployeeRepositoryCustom {

    Map<String, Object> findEmployee(String gender);

}

Step2. Create a custom implementation class:

package com.example.jpa.repository.impl;

import com.example.jpa.entity.Employee;
import com.example.jpa.repository.custom.EmployeeRepositoryCustom;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;

import java.util.Map;

@Slf4j
public class EmployeeRepositoryImpl implements EmployeeRepositoryCustom {

    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;

    @Override
    public Map<String, Object> findEmployee(String gender) {
        SimpleJdbcCall jdbcCall
                = new SimpleJdbcCall(jdbcTemplate.getJdbcTemplate().getDataSource())
                .withCatalogName("PACKAGE1")
                .withProcedureName("TEST_EMPLOYEE")
                .returningResultSet("O_RESULT", 
                                    BeanPropertyRowMapper.newInstance(Employee.class));

        SqlParameterSource input = new MapSqlParameterSource().addValue("I_GENDER", gender);

        Map<String, Object> output = jdbcCall.execute(input);
        output.entrySet().forEach(entry -> 
            log.debug("key = {}, value = {}", entry.getKey(), entry.getValue()));

        return output;
    }
}

Step3. Create an repository interface

package com.example.jpa.repository;

import com.example.jpa.entity.Employee;
import com.example.jpa.repository.custom.EmployeeRepositoryCustom;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface EmployeeRepository extends CrudRepository<Employee, String>, EmployeeRepositoryCustom {
}

Step4. Create a test case:

package com.example.jpa;

import com.example.jpa.entity.Employee;
import com.example.jpa.repository.EmployeeRepository;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.builder.ToStringBuilder;
import org.junit.Assert;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

@SpringBootTest
@Slf4j
public class TestEmployeeRepository {

    @Autowired
    private EmployeeRepository employeeRepository;

    @Test
    public void testFindEmployee() {
        Map<String, Object> output = employeeRepository.findEmployee("F");

        List<Employee> employees = (List<Employee>) output.get("O_RESULT");
        for (int i = 0; i < employees.size(); i++) {
            log.debug("employee = {}", ToStringBuilder.reflectionToString(employees.get(i)));
        }

        int returnCode = ((BigDecimal) output.get("O_RETURN_CODE")).intValue();
        log.debug("returnCode = {}", returnCode);
    }

}

Step5. Check result:

[           main] c.e.j.r.impl.EmployeeRepositoryImpl      : key = O_RESULT, value = [com.example.jpa.entity.Employee@2adbd899, com.example.jpa.entity.Employee@d6831df]
[           main] c.e.j.r.impl.EmployeeRepositoryImpl      : key = O_RETURN_CODE, value = 0
[           main] com.example.jpa.TestEmployeeRepository   : employee = com.example.jpa.entity.Employee@77ccded4[id=2,name=Mandy,phone=0911111111,address=Taipei,gender=F]
[           main] com.example.jpa.TestEmployeeRepository   : employee = com.example.jpa.entity.Employee@2bb717d7[id=3,name=Gina,phone=0911111111,address=ChiaYi,gender=F]
[           main] com.example.jpa.TestEmployeeRepository   : returnCode = 0