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?
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.
You can use the resultSet. getMetaData() method to work out what columns are in the data: ResultSetMetaData meta = resultSet. getMetaData(); int colcount = meta.
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.
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'.
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;
}
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With