Spring Boot JDBC call Oracle Function

Spring Boot JDBC呼叫Oracle Stored Procedure的Function（函式）。

範例一。例如有一個Oracle的Function FN_QUERY_PAGE如下。FN_PAGE_QUERY回傳REF_CURSOR。

FN_PAGE_QUERY

CREATE OR REPLACE FUNCTION FN_PAGE_QUERY (
    i_query_sql IN VARCHAR2, -- 原sql
    i_page_no IN NUMBER, -- 頁號
    i_page_size IN NUMBER -- 每頁筆數
) 
RETURN TYPES.REF_CURSOR
AS
    rc TYPES.REF_CURSOR;
    offset INTEGER;
    end_row_num INTEGER;
BEGIN
    offset := ((i_page_no - 1) * i_page_size) + 1;
    end_row_num := i_page_no * i_page_size + 1;

    open rc for 
        'select * from ( select rownum rn, t.* from ( ' || i_query_sql ||' ) t ) 
        where rn >= ' || offset  || ' and rn < ' || end_row_num;

    RETURN rc;
END FN_PAGE_QUERY;

使用SimpleJdbcCall來呼叫FN_QUERY_PAGE，回傳的REF_CURSOR用List<Map<String, Object>>來接。

package com.abc.demo.dao;

import.com.abc.demo.entity.Employee;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.stereotype.Repository;

import java.util.*;

@Repository
public class EmployeeDao {

    private JdbcTemplate jdbcTemplate;

    public void callFunction() {
        SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate).withFunctionName("FN_PAGE_QUERY");

        String querySql = "select * from employee";
        int pageNo = 1; // 第一頁
        int pageSize = 10; // 每頁10筆
        MapSqlParameterSource in = new MapSqlParameterSource();
        in.addValue("i_query_sql", querySql);
        in.addValue("i_page_no", pageNo);
        in.addValue("i_page_size", pageSize);
        List<Map<String, Object>> list = jdbcCall.executeFunction(List.class, in);
        
        System.out.println(list.size()); // 10
        
        for (Map<String, Object> row : list) {
            System.out.println(row.get("ID"));
            System.out.println(row.get("NAME"));
            System.out.println(row.get("AGE"));
        }

    }

}

範例二。例如Oracle現有一個函數fn_get_ship_date如下。

fn_get_ship_day

CREATE OR REPLACE FUNCTION fn_get_ship_date (
    i_current_date IN DATE,
    i_workdays IN INTEGER
)
    RETURN DATE
AS
    ...
BEGIN
    ...
END;

使用SimpleJdbcCall.executeFunction()來呼叫fn_get_ship_date函數，回傳型態為Date。

package com.abc.demo.dao;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.stereotype.Repository;

import java.util.Date;

public class ShippingDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    public Date callFnGetShipDay(Date currentDate, int workdays) {
        if (currentDate == null || workdays < 0) {
          throw new IllegalArgumentException();
        }
        
        SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate)
                .withFunctionName("fn_get_ship_date");
                
        MapSqlParameterSource params = new MapSqlParameterSource()
                .addValue("i_current_date", currentDate)
                .addValue("i_workdays", workdays);
        
        return jdbcCall.executeFunction(Date.class, params);
    }

}

• Spring Boot JDBC Stored Procedure Examples
• Spring Data JPA calling Oracle Function