map sys\u refcursor参数

um6iljoc  于 2021-07-15  发布在  Java
关注(0)|答案(1)|浏览(326)

我在oracledb中有一个存储过程。它的输出参数是number(“return\u value”)和sys\u refcursor类型的三个参数(“one”、“two”…)。我知道如何调用此过程并获取数值参数:

SimpleJdbcCall call = new SimpleJdbcCall(jdbcTemplate)
        .withProcedureName("NAME")
        .withReturnValue();
Map<String, Object> response = call.execute(new MapSqlParameterSource(...));
Integer responseCode = (Integer) response.get("RETURN_VALUE");

但是如何将sys\u refcursor参数Map到所需类类型的某个列表?

List<DesiredClassType> list = (List<DesiredClassType>) response.get("ONE");
zsohkypk

zsohkypk1#

我通过使用callablestatement而不是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) {
            //...
        }
    }
}

相关问题