PostgreSQL 的 Function。
首先我們先建立一個測試用的 Table：
兩個 PostgreSQL Function，如以下 SQL 語法：
CREATE TABLE IF NOT EXISTS public.testTable
(
id integer NOT NULL,
title text NOT NULL,
PRIMARY KEY(id)
)
並且塞入一些資料：
INSERT INTO public.testTable(id, title) VALUES(1, '111'); INSERT INTO public.testTable(id, title) VALUES(2, '222'); INSERT INTO public.testTable(id, title) VALUES(3, '333');
然後建立三個 Function，以下三個 Function 的作用基本一樣，都是接受傳入的
param_id 變數，然後進行
SELECT * FROM testTable WHERE id = param_id
的查詢，不同的是傳回的 type 不同，呼叫 Functoin 的方式也有所不同，
可以注意到的是，有兩個 Function 的名稱一樣 (都取名為
queryUsingReturnedRefcursor)，不過 Function 簽名不同
(傳入參數不同)，這是為了演示 PostgreSQL 能夠支持同名 Function 故意取名的，類似
Java 的 Function Overload：
-
queryUsingReturnedSetof(param_id integer)：
CREATE OR REPLACE FUNCTION queryUsingReturnedSetof(param_id integer) RETURNS SETOF testTable AS $$ SELECT * FROM testTable WHERE id = param_id; $$ LANGUAGE sql;
說明：
此 Function 傳回值 type 為 SETOF，相當於傳回 Table 的資料行集合，呼叫 Function 的方式為如下 SQL 語法：
SELECT * FROM queryUsingReturnedSetof(2); -
queryUsingReturnedRefcursor(param_id integer)：
CREATE OR REPLACE FUNCTION queryUsingReturnedRefcursor(param_id integer) RETURNS refcursor AS $$ DECLARE ref refcursor; BEGIN OPEN ref FOR SELECT * FROM testTable WHERE id = param_id; RETURN ref; END; $$ LANGUAGE plpgsql;
說明：
此 Function 傳回值 type 為 refcursor，為一個 cursor ，不是 Table 的資料行集合，直接呼叫的話只會得到一個 cursor ，必須要配合 FETCH 指令才能遍歷每一行的資料，例如以下語法：
SELECT queryUsingReturnedRefcursor(2); FETCH ALL IN "<unnamed portal 6>";
其中，<unnamed portal 6> 是被回傳的 cursor 名稱，不過在實際上每次執行 Function 後回傳的 cursor name 是會變化的，每次都不一樣，
而執行完 Function 後， cursor 就消失了，導致執行 FETCH 時會有
ERROR: cursor "<unnamed portal xxx>" does not exist
的錯誤，所以上面那樣的語法沒辦法正確地得到查詢結果資料行，
要解決上面的問題，可以看下一個 Functoin 的說明，
其想法是把想要的 cursor name 傳進 Function 以指定回傳的 cursor name。
不過如果是用 Java 去呼叫的話，是可以成功執行並得到資料行的，
之後可以看到我們可以用 Java 去指定 cursor name。 -
queryUsingReturnedRefcursor(ref refcursor, param_id integer)：
CREATE OR REPLACE FUNCTION queryUsingReturnedRefcursor(ref refcursor, param_id integer) RETURNS refcursor AS $$ BEGIN OPEN ref FOR SELECT * FROM testTable WHERE id = param_id; RETURN ref; END; $$ LANGUAGE plpgsql;
說明：
此 Function 傳回值 type 為 refcursor，跟上一個 Fucntion 一樣，只差在多傳進一個指定的 cursor name，這樣我們就可以用已知的 cursor name 來取得資料行，呼叫方式如下：
SELECT queryUsingReturnedRefcursor('xxx_cur', 2); FETCH ALL IN "xxx_cur";
其中 xxx_cur 可以隨意更換成想要的 cursor name。
接下來是 Java EE Spring 的部份了。
下圖為測試專案的檔案結構，紅框處的是主要專案檔案：
因為重要的地方主要是 DAO 使用 JdbcTemplate 的部份，
為了簡單起見，所以這裡只展示 DAO 的部份，也就是 com.dao.MyDAO.java，
其他 Java EE Spring、Data Source 、使用到的 lib (使用 Maven POM 檔設定)
等的設定可以參考文章最後的源碼下載分享
MyDAO.java ：
package com.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.stereotype.Repository;
@Repository
public class MyDAO {
@Autowired
@Qualifier("postgresql_JdbcTemplate")
private JdbcTemplate postgresql_JdbcTemplate;
public List<Map> queryUsingReturnedSetof(int id) {
List<Map> dataList = postgresql_JdbcTemplate.query("SELECT * FROM queryUsingReturnedSetof(?);", new RowMapper<Map>() {
@Override
public Map mapRow(ResultSet rs, int rowNumber) throws SQLException {
Map<String, Object> map = new HashMap<>();
map.put("id", rs.getInt("id"));
map.put("title", rs.getString("title"));
return map;
}
}, id);
//for test
for (Map data : dataList) {
System.out.println(data.get("id") + ", " + data.get("title"));
}
return dataList;
}
public List<Map> queryUsingReturnedRefcursor(int id) {
String customCursorName = "xxx_cur";
SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(postgresql_JdbcTemplate)
.withProcedureName("queryUsingReturnedRefcursor")
.declareParameters(new SqlParameter("id", Types.INTEGER))
.withoutProcedureColumnMetaDataAccess()
.returningResultSet(customCursorName, new RowMapper<Map>() {
@Override
public Map mapRow(ResultSet rs, int rowNumber) throws SQLException {
Map<String, Object> map = new HashMap<>();
map.put("id", rs.getInt("id"));
map.put("title", rs.getInt("title"));
return map;
}
});
Map returnedMapContainsCursorName = simpleJdbcCall.execute(new MapSqlParameterSource().addValue("id", id));
List<Map> dataList = (List) returnedMapContainsCursorName.get(customCursorName);
//for test
for (Map data : dataList) {
System.out.println(data.get("id") + ", " + data.get("title"));
}
return dataList;
}
}
說明：
queryUsingReturnedSetof(int id) 對應到 PostgreSQL 中的 queryUsingReturnedSetof(param_id integer) 這個 Function。
queryUsingReturnedRefcursor(int id) 對應到 PostgreSQL 中的 queryUsingReturnedRefcursor(param_id integer) 這個 Function，可以看到它可以讓我們指定一個回傳的 refCursor name，
simpleJdbcCall.execute() 並不直接回傳 data list 資料，而是一個 Map，
就像上面我們直接用 Sql 語法查詢那樣回傳的 refCursor 的資料，
我們需要再用指定的 refCursor name 當作 Key 從 Map 中才能獲取真正的 data list 資料。
源碼下載分享：
參考資料：
- PostgreSQL - How to Return a Result Set from a Stored Procedure
- PostgreSql JDBC - Calling Stored Functions and Procedures | pgJDBC
- Spring JDBC Template for calling Stored Procedures
- Postgresql stored procedure return table all columns
- postgresql/lightdb PL/pgSQL return setof my_type、resturn setof record和TABLE的区别及动态SQL执行
沒有留言 :
張貼留言