calling stored proc in jdbc template which returns multiple rows

Question

If I am calling stored proc Using Jdbc template which returns multiple rows of Data. For Example Stored Procedure returns 200 records based on some business rules. How to iterate and process those 1 at a time, can we use Custom row mapper for it?

Here I am able to call it for 1 Employee. But need it as a List.

  SimpleJdbcCall jdbcCall = new 
           SimpleJdbcCall(dataSource).withProcedureName("getEmployeeeRecord");
  SqlParameterSource in = new MapSqlParameterSource().addValue("emp_id", id);
        Map<String, Object> out = jdbcCall.execute(in);
        Employee employee = new Employee();
        employee.setId(id);
        employee.setName((String) out.get("out_name"));
        employee.setAge((Integer) out.get("out_age"));
        return employee ;

As far as I can tell from the API, it isn't supported. It only supports single row results, or result sets in parameters. If you want to process a stored procedure that returns a result set, then you should probably use `JdbcTemplate` directly (but that API also seems to suggest that it doesn't really expect callable statements to return result sets). — Mark Rotteveel, Sep 20 '19 at 09:54

score 0 · Answer 1 · answered Oct 15 '20 at 17:40

I made something like this:

@PostConstruct
public void init() {
    jdbcCall = new SimpleJdbcCall(jdbcTemplate)
            .withProcedureName("name_of_procedure")
            .withCatalogName("catalog_name")
            .withSchemaName("schema_name")
            .declareParameters(
                    new SqlParameter("@NIT", Types.VARCHAR));
}

public List<Student> invocar(Param parameters) {
    jdbcCall.setFunction(false);
    List<Student> listResult = new ArrayList<>();
    MapSqlParameterSource valueParams = new MapSqlParameterSource();
    valueParams.addValue("@NIT", parameters.getNit());
    

    Map<String, Object> resultStoreProcedure = jdbcCall.execute(valueParams);

    List<LinkedCaseInsensitiveMap<String>> linkedCaseInsensitiveMapList = 
    (ArrayList<LinkedCaseInsensitiveMap<String>>) resultStoreProcedure.get("#result- 
     set-1");
    for (LinkedCaseInsensitiveMap<String> linkedCaseInsensitiveMap : 
      linkedCaseInsensitiveMapList) {
        listResult.add(ResumenInversionesSalidaSpTo
                .builder()
                .name(linkedCaseInsensitiveMap.get("name"))
                .lastName(linkedCaseInsensitiveMap.get("last_name"))
                .age(linkedCaseInsensitiveMap.get("age"))
                .nit(linkedCaseInsensitiveMap.get("nit"))
                .build());
    }
    return listResult;
}

It works against a SQL server database.

calling stored proc in jdbc template which returns multiple rows

1 Answers1