How do you map the output of a Spring stored procedure execute?

Admin 1
Viewed 5041

I am using Spring and stored procedures to retrieve data from a mySQL database. I have the stored procedure and parameters working OK but I'm having problems mapping the result set. At the moment I have some truly ugly code to get the values and I'm sure there has to be a better, cleaner and more elegant way. Can anyone guide me to a better solution?

After the stored procedure class, I have:

List<String> outList = new ArrayList<String>();

Map<String,Object> outMap = execute(parameters_map);

List list = (List) outMap.get("#result-set-1");

for (Object object : list) {
    Map map2 = (Map) object;
    list.add(map2.get("runname"));  
}

return outList;

runname is the column from the database query.

Is there a better way to achieve this?

stored-procedures spring
2 Answers

Example from spring docs using RowMapper:

public class JdbcActorDao implements ActorDao {

private SimpleJdbcCall procReadAllActors;

public void setDataSource(DataSource dataSource) {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    jdbcTemplate.setResultsMapCaseInsensitive(true);
    this.procReadAllActors = new SimpleJdbcCall(jdbcTemplate)
            .withProcedureName("read_all_actors")
            .returningResultSet("actors",
            BeanPropertyRowMapper.newInstance(Actor.class));
}

public List getActorsList() {
    Map m = procReadAllActors.execute(new HashMap<String, Object>(0));
    return (List) m.get("actors");
}

// ... additional methods

}

user_108967020

took a while to interpret the Spring docs but I finally got there. My solution:

    SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
                                                      .withProcedureName("DistinctRunNames")
                     .withoutProcedureColumnMetaDataAccess();
    simpleJdbcCall.addDeclaredParameter(new SqlParameter("environment", Types.VARCHAR));
    simpleJdbcCall.addDeclaredParameter(new SqlParameter("username", Types.VARCHAR));
    simpleJdbcCall.addDeclaredParameter(new SqlParameter("test_suite", Types.VARCHAR));
   SqlParameterSource parameters = new MapSqlParameterSource().addValue("environment", environment)
                                                    .addValue("username", username).addValue("test_suite", testSuite);
    Map map = simpleJdbcCall.returningResultSet("runnames", new ParameterizedRowMapper<RunNameBean>() {
           public RunNameBean mapRow(ResultSet rs, int rowNum) throws SQLException {
                  RunNameBean runNameBean = new RunNameBean();
       runNameBean.setName(rs.getString("runname"));
       return runNameBean;
       }
          }).execute(parameters);
    return (List) map.get("runnames");

Had problems with expected parameters versus actual, had to break up the simpleJdbcCall object. Maps the results into a list beautifully. Thank you for answers, helped me to learn about Spring mapping.

Admin1
Related