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

Assume I have a stored procedure as bellows:

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