Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Stored Procedure returning multiple tables to spring jdbc template

Iam calling a stored procedure from my Spring DAO class using JdbcTemplate. My problem is that, stored procedure returns multiple tables. Is there a way to access multiple tables using Spring JdbcTemplate.

If I use jdbcTemplate.queryForList(myStoredProc, new Object[]{parameters} iam getting only first table from the result.

My database is SQL Server 2005.

Is there any method other than jdbcTemplate for my requirement?

like image 648
Krishna Avatar asked May 18 '11 05:05

Krishna


People also ask

Can a stored procedure return multiple tables?

Stored procedures contain IN and OUT parameters or both. They may return result sets in case you use SELECT statements. Stored procedures can return multiple result sets.

How can we retrieve multiple result sets from a stored procedure in JdbcTemplate?

You can use the resultSet. getMetaData() method to work out what columns are in the data: ResultSetMetaData meta = resultSet. getMetaData(); int colcount = meta.

Does JDBC template support stored procedure?

Stored procedures are commonly used to encapsulate complex SQL queries. Application developers can leverage the functionality of stored procedures with the Spring JDBC Templates API. In this example, we have a stored procedure named: getCars. This stored procedure will search for cars based on the given criteria.

How can we call stored procedure using JDBC template?

The SimpleJdbcCall class can be used to call a stored procedure with IN and OUT parameters. You can use this approach while working with either of the RDBMS such as Apache Derby, DB2, MySQL, Microsoft SQL Server, Oracle, and Sybase. DELIMITER $$ DROP PROCEDURE IF EXISTS 'TEST'. 'getRecord' $$ CREATE PROCEDURE 'TEST'.


2 Answers

The solution sinha referenced didn't work for me. I was able to solve this using JdbcTemplate#call(CallableStatementCreator, List<SqlParameter>). For example:

private static final String sql = "{call schema_name.the_stored_procedure(?, ?, ?)}";

// The input parameters of the stored procedure
private static final List<SqlParameter> declaredParams = Arrays.asList(
    new SqlParameter("nameOfFirstInputParam", Types.VARCHAR),
    new SqlParameter("nameOfSecondInputParam", Types.VARCHAR),
    new SqlParameter("nameOfThirdInputParam", Types.VARCHAR));

private static final CallableStatementCreatorFactory cscFactory
    = new CallableStatementCreatorFactory(sql, declaredParams);

// The result sets of the stored procedure
private static final List<SqlParameter> returnedParams = Arrays.<SqlParameter>asList(
    new SqlReturnResultSet("nameOfFirstResultSet", SomeRowMapper.INSTANCE),
    new SqlReturnResultSet("nameOfSecondResultSet", SomeOtherRowMapper.INSTANCE));

public static Map<String, Object> call(JdbcTemplate jdbcTemplate,
                                       String param0,
                                       String param1,
                                       String param2) {
  final Map<String, Object> actualParams = new HashMap<>();
  actualParams.put("nameOfFirstInputParam", param0);
  actualParams.put("nameOfSecondInputParam", param1);
  actualParams.put("nameOfThirdInputParam", param2);

  CallableStatementCreator csc = cscFactory.newCallableStatementCreator(actualParams);
  Map<String, Object> results = jdbcTemplate.call(csc, returnedParams);

  // The returned map will including a mapping for each result set.
  //
  // {
  //   "nameOfFirstResultSet" -> List<SomeObject>
  //   "nameOfSecondResultSet" -> List<SomeOtherObject>
  // }
  //
  // For this example, we just return the heterogeneous map.  In practice,
  // it's better to return an object with more type information.  In other
  // words, don't make client code cast the result set lists.  Encapsulate
  // that casting within this method.

  return results;
}
like image 117
Bobby Eickhoff Avatar answered Sep 19 '22 22:09

Bobby Eickhoff


See http://static.springsource.org/spring/docs/2.0.7/reference/jdbc.html#jdbc-StoredProcedure

The example given in this section is exactly for your case where the stored procedure returns multiple result-sets. Although the example given there is for Oracle, but it should work in the same way for MS SQL Server also.

like image 37
sinha Avatar answered Sep 18 '22 22:09

sinha