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
