create or replace PACKAGE PACKAGE1 AS PROCEDURE TEST_HELLO ( I_NAME IN VARCHAR2, outParam1 OUT VARCHAR2, o_return_code OUT INT ); END PACKAGE1; create or replace PACKAGE BODY PACKAGE1 AS PROCEDURE TEST_HELLO ( I_NAME IN VARCHAR2, outParam1 OUT VARCHAR2, o_return_code OUT INT ) AS BEGIN outParam1 := 'Hello World! ' || I_NAME; DBMS_OUTPUT.put_line (outParam1); o_return_code := 0; END TEST_HELLO; END PACKAGE1;
Step1. Create a custom repository interface:
package com.example.jpa.repository.custom; import java.util.Map; public interface EmployeeRepositoryCustom { Map<String, Object> sayHello(String name); }
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> sayHello(String name) { SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate.getJdbcTemplate().getDataSource()) .withCatalogName("PACKAGE1") .withProcedureName("TEST_HELLO"); Map<String, Object> output = jdbcCall.execute(name); 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 testSayHello() { String name = "Albert Kuo"; Map<String, Object> output = employeeRepository.sayHello(name); String message = (String) output.get("OUTPARAM1"); int returnCode = ((BigDecimal) output.get("O_RETURN_CODE")).intValue(); log.debug("message = {}, returnCode = {}", message, returnCode); Assert.assertEquals(0, returnCode); Assert.assertEquals("Hello World! " + name, message); } }
Step5. Check result:
[ main] c.e.j.r.impl.EmployeeRepositoryImpl : key = OUTPARAM1, value = Hello World! Albert Kuo [ main] c.e.j.r.impl.EmployeeRepositoryImpl : key = O_RETURN_CODE, value = 0 [ main] com.example.jpa.TestEmployeeRepository : message = Hello World! Albert Kuo, returnCode = 0
