使用 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_twoo_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()。最后，获取结果。
我们可以在 doInCallableStatement() 方法中多次重复使用 CallableStatement 对象，以不同的参数执行存储过程。
5、使用 SimpleJdbcCall
SimpleJdbcCall 类在内部使用 JdbcTemplate 来执行存储过程和函数。它还支持 Fluent 风格的方法链，使其更易于理解和使用。
此外，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"));
}
首先，通过向 SimpleJdbcCall 类的构造函数传递 JdbcTemplate 对象来实例化该类。在底层，该 JdbcTemplate 对象会执行存储过程。然后，我们将存储过程名称传递给 SimpleJdbcCall#withProcedureName() 方法。
最后，通过将输入参数传递给 Map，并通过 SimpleJdbcCall#execute() 方法获取结果存储在 Map 对象中。结果根据 OUT 参数的名称作为 Key 存储。
有趣的是，由于 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) {
// 进行一些处理
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 对象时，参数的顺序必须与传递给存储过程的参数顺序相同。
7、总结
本文介绍了 JdbcTemplate 调用存储过程的功能，JdbcTemplate 是处理数据库中数据操作的核心类。它可以直接使用，也可以在封装类（如 SimpleJdbcCall 和 StoredProcedure）的帮助下隐式使用。
Ref：https://www.baeldung.com/spring-jdbctemplate-stored-procedure
