使用 Spring JdbcTemplate 的預存程序
1. 概述
在本教程中，我們將討論 Spring JDBC 框架的JdbcTemplate類別執行資料庫預存程序的能力。資料庫預存程序與函數類似。函數支援輸入參數並具有返回類型，而過程同時支援輸入和輸出參數。
2. 前提條件
讓我們考慮PostgreSQL資料庫中的一個簡單預存程序：
CREATE OR REPLACE PROCEDURE sum_two_numbers(
IN num1 INTEGER,
IN num2 INTEGER,
OUT result INTEGER
)
LANGUAGE plpgsql
AS '
BEGIN
sum_result := num1 + num2;
END;
';預存程序sum_two_numbers接受兩個輸入數字並在輸出參數sum_result中傳回它們的總和。通常，預存程序可以支援多個輸入和輸出參數。然而，對於這個例子，我們考慮了單一輸出參數。
3.使用JdbcTemplate#call()方法
讓我們看看如何使用JdbcTemplate#call()方法呼叫資料庫預存程序：
void givenStoredProc_whenCallableStatement_thenExecProcUsingJdbcTemplateCallMethod() {
List<SqlParameter> procedureParams = List.of(new SqlParameter("num1", Types.INTEGER),
new SqlParameter("num2", Types.NUMERIC),
new SqlOutParameter("result", Types.NUMERIC)
);
Map<String, Object> resultMap = jdbcTemplate.call(new CallableStatementCreator() {
@Override
public CallableStatement createCallableStatement(Connection con) throws SQLException {
CallableStatement callableStatement = con.prepareCall("call sum_two_numbers(?, ?, ?)");
callableStatement.registerOutParameter(3, Types.NUMERIC);
callableStatement.setInt(1, 4);
callableStatement.setInt(2, 5);
return callableStatement;
}
}, procedureParams);
assertEquals(new BigDecimal(9), resultMap.get("result"));
}首先，我們藉助SqlParameter類別定義預存程序sum_two_numbers()的IN參數num1和num2 。然後，我們使用SqlOutParameter定義OUT參數result 。
隨後，我們將CallableStatementCreater物件和procedureParams中的List<SqlParameter>傳遞給JdbcTemplate#call()方法。
在CallableStatementCreator#createCallableStatement()方法中，我們透過呼叫Connection#prepareCall()方法來建立CallableStatement物件。與PreparedStatement類似，我們在CallableStatment物件中設定IN參數。
但是，我們必須使用registerOutParameter()方法來註冊OUT參數。
最後，我們從resultMap中的Map物件檢索結果。
4.使用JdbcTemplate#execute()方法
在某些情況下，我們可能需要對CallableStatement進行更多控制。因此， Spring框架提供了類似PreparedStatementCallback的CallableStatementCallback介面。讓我們看看如何在JdbcTemplate#execute()方法中使用它：
void givenStoredProc_whenCallableStatement_thenExecProcUsingJdbcTemplateExecuteMethod() {
String command = jdbcTemplate.execute(new CallableStatementCreator() {
@Override
public CallableStatement createCallableStatement(Connection con) throws SQLException {
CallableStatement callableStatement = con.prepareCall("call sum_two_numbers(?, ?, ?)");
return callableStatement;
}
}, new CallableStatementCallback<String>() {
@Override
public String doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
cs.setInt(1, 4);
cs.setInt(2, 5);
cs.registerOutParameter(3, Types.NUMERIC);
cs.execute();
BigDecimal result = cs.getBigDecimal(3);
assertEquals(new BigDecimal(9), result);
String command = "4 + 5 = " + cs.getBigDecimal(3);
return command;
}
});
assertEquals("4 + 5 = 9", command);
}CallableStatementCreator物件參數建立CallableStatement物件。稍後，它可以在CallableStatementCallback#doInCallableStatement()方法中使用。
在此方法中，我們在CallableStatement物件中設定IN和OUT參數，然後呼叫CallableStatement#execute() 。最後，我們取得結果並形成command 4 + 5 = 9 。
我們可以多次重複使用doInCallableStatement()方法中的CallableStatement物件來執行具有不同參數的預存程序。
5.使用SimpleJdbcCall
SimpleJdbcCall類別內部使用JdbcTemplate來執行預存程序和函數。它還支援流暢風格的方法鏈接，使其更易於理解和使用。
此外， SimpleJdbcCall設計用於在多執行緒場景中工作。因此，它允許多個線程安全、並發訪問，而不需要任何外部同步。
讓我們看看如何在此類的幫助下呼叫預存程序sum_two_numbers ：
void givenStoredProc_whenJdbcTemplate_thenCreateSimpleJdbcCallAndExecProc() {
SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate).withProcedureName("sum_two_numbers");
Map<String, Integer> inParams = new HashMap<>();
inParams.put("num1", 4);
inParams.put("num2", 5);
Map<String, Object> resultMap = simpleJdbcCall.execute(inParams);
assertEquals(new BigDecimal(9), resultMap.get("result"));
}首先，我們透過將JdbcTemplate物件傳遞給其建構函式來實例化SimpleJdbcCall類別。在底層，這個JdbcTemplate物件執行預存程序。然後，我們將過程名稱傳遞給SimpleJdbcCall#withProcedureName()方法。
最後，我們透過將Map中的輸入參數傳遞給SimpleJdbcCall#execute()方法來取得Map物件中的結果。結果儲存在具有OUT參數名稱的鍵上。
有趣的是，不需要定義預存程序參數的元數據，因為SimpleJdbcCall類別可以讀取資料庫元資料。此支援僅限於少數資料庫，例如 Derby、MySQL、Microsoft SQL Server、Oracle、DB2、Sybase 和 PostgreSQL。
因此，對於其他人，我們需要在SimpleJdbcCall#declareParameters()方法中明確定義參數：
@Test
void givenStoredProc_whenJdbcTemplateAndDisableMetadata_thenCreateSimpleJdbcCallAndExecProc() {
SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
.withProcedureName("sum_two_numbers")
.withoutProcedureColumnMetaDataAccess();
simpleJdbcCall.declareParameters(new SqlParameter("num1", Types.NUMERIC),
new SqlParameter("num2", Types.NUMERIC),
new SqlOutParameter("result", Types.NUMERIC));
Map<String, Integer> inParams = new HashMap<>();
inParams.put("num1", 4);
inParams.put("num2", 5);
Map<String, Object> resultMap = simpleJdbcCall.execute(inParams);
assertEquals(new BigDecimal(9), resultMap.get("result"));
}我們透過呼叫SimpleJdbcCall#withoutProcedureColumnMetaDataAccess()方法停用了資料庫元資料處理。其餘步驟與以前相同。
6. 使用StoredProcedure
StoredProcedure是一個抽象類，我們可以重寫它的execute()方法來進行額外的處理：
public class StoredProcedureImpl extends StoredProcedure {
public StoredProcedureImpl(JdbcTemplate jdbcTemplate, String procName) {
super(jdbcTemplate, procName);
}
private String doSomeProcess(Object procName) {
//do some processing
return null;
}
@Override
public Map<String, Object> execute(Map<String, ?> inParams) throws DataAccessException {
doSomeProcess(inParams);
return super.execute(inParams);
}
}讓我們看看如何使用這個類別：
@Test
void givenStoredProc_whenJdbcTemplate_thenCreateStoredProcedureAndExecProc() {
StoredProcedure storedProcedure = new StoredProcedureImpl(jdbcTemplate, "sum_two_numbers");
storedProcedure.declareParameter(new SqlParameter("num1", Types.NUMERIC));
storedProcedure.declareParameter(new SqlParameter("num2", Types.NUMERIC));
storedProcedure.declareParameter(new SqlOutParameter("result", Types.NUMERIC));
Map<String, Integer> inParams = new HashMap<>();
inParams.put("num1", 4);
inParams.put("num2", 5);
Map<String, Object> resultMap = storedProcedure.execute(inParams);
assertEquals(new BigDecimal(9), resultMap.get("result"));
}與SimpleJdbcCall,我們首先透過傳入JdbcTemplate和預存程序名稱來實例化StoredProcedure的子類別。然後我們設定參數，執行預存過程，並在Map中取得結果。
此外，我們必須記住按照參數傳遞給預存程序的順序聲明SqlParameter物件。
七、結論
在本文中，我們討論了JdbcTemplate執行預存程序的功能。 JdbcTemplate一直是資料庫中處理資料操作的核心類別。它可以在SimpleJdbcCall和StoredProcedure等包裝類別的幫助下直接或隱含使用。
與往常一樣，本文中使用的程式碼可以在 GitHub 上找到。