spring Obtaining a SqlRowSet from SimpleJdbcCall SimpleJdbcCall creation

Example

Typically, you will want to create your SimpleJdbcCalls in a Service.

This example assumes your procedure has a single output parameter that is a cursor; you will need to adjust your declareParameters to match your procedure.

@Service
public class MyService() {

@Autowired
    private DataSource dataSource;
    
    // Autowire your configuration, for example
    @Value("${db.procedure.schema}")
    String schema;
    
    private SimpleJdbcCall myProcCall;
    
    // create SimpleJdbcCall after properties are configured
    @PostConstruct
    void initialize() {
        this.myProcCall = new SimpleJdbcCall(dataSource)
                        .withProcedureName("my_procedure_name")
                        .withCatalogName("my_package")
                        .withSchemaName(schema)
                        .declareParameters(new SqlOutParameter(
                            "out_param_name",
                            Types.REF_CURSOR, 
                            new SqlRowSetResultSetExtractor()));
    }

    public SqlRowSet myProc() {
        Map<String, Object> out = this.myProcCall.execute();
        return (SqlRowSet) out.get("out_param_name");
    }

}

There are many options you can use here:

• withoutProcedureColumnMetaDataAccess() needed if you have overloaded procedure names or just don't want SimpleJdbcCall to validate against the database.
• withReturnValue() if procedure has a return value. First value given to declareParameters defines the return value. Also, if your procedure is a function, use withFunctionName and executeFunction when executing.
• withNamedBinding() if you want to give arguments using names instead of position.
• useInParameterNames() defines the argument order. I think this may be required if you pass in your arguments as a list instead of a map of argument name to value. Though it may only be required if you use withoutProcedureColumnMetaDataAccess()