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);
}
}
沒有留言:
張貼留言