在Spring中调用Oracle过程以使用SimpleJdbcCall返回行 [英] Calling Oracle procedure that returns rows using SimpleJdbcCall in Spring

问题描述

我写了以下代码

       MapSqlParameterSource in = new MapSqlParameterSource();
       in.addValue("V_OPP_ID", bean.getOpportunityId());
       in.addValue("V_NAME",bean.getName());
       in.addValue("V_FROM_DATE", bean.getStdate());
       in.addValue("V_TO_DATE", bean.getEddate());
       in.addValue("V_USERTYPE", bean.getUserType());
       jdbcCall.execute(in);

在这里，jdbcCall.execute(in)返回与Arraylist对应的结果集/表.如何提取此ArrayList

Here the jdbcCall.execute(in) returns me resultset/table corresponding to Arraylist. How do i extract this ArrayList

使用jdbcCall是否正确?如果不是，建议什么?

Is using jdbcCall a correct Approach ? If not what is Adviced ?

推荐答案

这是我用于调用函数的代码:

This is the code I use for a call to a function:

RowMapper<String> rm = new ParameterizedRowMapper<String>() {
    @Override
    public String mapRow(ResultSet rs, int rowNum) throws SQLException {
        return rs.getString(1);
    }
};
myStoredProcedure = new SimpleJdbcCall(DataSourceConnection.getDataSource())
        .withCatalogName("PACKAGE")
        .withFunctionName("GET_ALIAS")
        .returningResultSet("return", rm);

MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue("P_ID",userStr);
params.addValue("P_DOMAIN_ALIAS", domain[0]);
List<String> list = myStoredProcedure.executeFunction(List.class,params);

如果您无法使用元数据，则代码如下:

and if you are not able to use the metadata then this is the code:

RowMapper<String> rm = new ParameterizedRowMapper<String>() {
    @Override
    public String mapRow(ResultSet rs, int rowNum) throws SQLException {
        return rs.getString(1);
    }
};
SqlParameter emailParam = new SqlParameter("P_ID", OracleTypes.VARCHAR);
SqlParameter domainParam = new SqlParameter("P_DOMAIN_ALIAS", OracleTypes.VARCHAR);
SqlOutParameter resultParam = new SqlOutParameter("return", OracleTypes.CURSOR);
myStoredProcedure = new SimpleJdbcCall(DataSourceConnection.getDataSource())
        .withCatalogName("PACKAGE")
        .withFunctionName("GET_ALIAS")
        .withoutProcedureColumnMetaDataAccess()
        .returningResultSet("return", rm)
        .declareParameters(resultParam, emailParam, domainParam);

MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue("P_ID",userStr);
params.addValue("P_DOMAIN_ALIAS", domain[0]);
List<String> list = myStoredProcedure.executeFunction(List.class,params);

这篇关于在Spring中调用Oracle过程以使用SimpleJdbcCall返回行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！