I have stored procedure in Oracle DB. It's output parameters are Number ("RETURN_VALUE") and three parameters of SYS_REFCURSOR type ("ONE", "TWO"...). I know how to call this procedure and to get numeric parameter:
SimpleJdbcCall call = new SimpleJdbcCall(jdbcTemplate)
.withProcedureName("NAME")
.withReturnValue();
Map<String, Object> response = call.execute(new MapSqlParameterSource(...));
Integer responseCode = (Integer) response.get("RETURN_VALUE");
But how to map SYS_REFCURSOR parameter to some List of DesiredClassType?
List<DesiredClassType> list = (List<DesiredClassType>) response.get("ONE");
I found a solution for this problem by using a CallableStatement instead of SimpleJdbcCall.
@Component
public class DaoExample {
private static final String PROCEDURE_CALL_SQL = "{call SCHEME.PROC_NAME(?,?,?,?,?,?,?,?,?)}";
private DataSource dataSource;
DaoExample(DataSource dataSource) {
this.dataSource = dataSource;
}
public void callProcedure() {
try (Connection con = dataSource.getConnection();
CallableStatement stmt = con.prepareCall(PROCEDURE_CALL_SQL)) {
con.setAutoCommit(false);
// set input parameters
stmt.setInt("input_parameter_name1", 1);
stmt.setString("input_parameter_name2", "2");
stmt.setNull("input_parameter_name3", OracleTypes.VARCHAR);
stmt.setDate("input_parameter_name4", Date.valueOf("2021-04-23"));
// set output parameters
stmt.registerOutParameter("output_parameter_name1", OracleTypes.NUMERIC);
stmt.registerOutParameter("output_parameter_name2", OracleTypes.CURSOR);
stmt.registerOutParameter("output_parameter_name3", OracleTypes.CURSOR);
stmt.registerOutParameter("output_parameter_name4", OracleTypes.CURSOR);
stmt.registerOutParameter("output_parameter_name5", OracleTypes.CURSOR);
stmt.execute();
// example of getting an int parameter value
Integer responseCode = stmt.getInt("output_parameter_name1");
// example of getting a set of values and mapping them to a List<Account>
ResultSet accountsRs = (ResultSet) stmt.getObject("output_parameter_name2");
List<Account> accounts = new ArrayList<>();
while (accountsRs != null && accountsRs.next()) {
accounts.add(new Account()
.setAccountNumber(accountsRs.getString("STRING"))
.setAccountCloseDate(accountsRs.getDate("DATE") == null
? null
: accountsRs.getDate("DATE").toLocalDate())
.setAccountBalance(accountsRs.getBigDecimal("BIG_DECIMAL")));
}
//...
} catch (Exception e) {
//...
}
}
}