java 存储过程返回多个表到 spring jdbc 模板
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6040385/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Stored Procedure returning multiple tables to spring jdbc template
提问by Krishna
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.
我正在使用 JdbcTemplate 从我的 Spring DAO 类调用存储过程。我的问题是，存储过程返回多个表。有没有办法使用 Spring JdbcTemplate 访问多个表。
If I use
jdbcTemplate.queryForList(myStoredProc, new Object[]{parameters}iam getting only first table from the result.
如果我使用
jdbcTemplate.queryForList(myStoredProc, new Object[]{parameters}iam 只从结果中获取第一个表。
My database is SQL Server 2005.
我的数据库是 SQL Server 2005。
Is there any method other than jdbcTemplate for my requirement. If yes, please let me know.
除了 jdbcTemplate 之外，还有什么方法可以满足我的要求。如果是，请告诉我。
Thanks in advance....
提前致谢....
采纳答案by sinha
See http://static.springsource.org/spring/docs/2.0.7/reference/jdbc.html#jdbc-StoredProcedure
见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.
本节中给出的示例完全适用于存储过程返回多个结果集的情况。尽管那里给出的示例适用于 Oracle，但它也应该以相同的方式用于 MS SQL Server。
回答by Bobby Eickhoff
The solution sinha referenced didn't work for me. I was able to solve this using JdbcTemplate#call(CallableStatementCreator, List<SqlParameter>). For example:
sinha 引用的解决方案对我不起作用。我能够使用JdbcTemplate#call(CallableStatementCreator, List<SqlParameter>). 例如：
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;
}
