Affects Version/s: 1.11.1 (Ingalls SR1)
Fix Version/s: None
* Spring Boot 1.5.2
* Tomcat (embedded) 8.5.11
* JAX-RS 2.0.1 / Jersey 2.25.1
* Spring Data JPA 1.11.1
* Java Persistence API 2.1 / Hibernate JPA Provider
* jTDS JDBC Driver 1.3.1 / MS SQL Server 2014 DB
* SLF4J 1.7.24
* Project Lombok 1.16.16
Some databases: notably MS SQL and MySQL (but not Oracle) support SPs returning result sets (one or multiple) via simple SELECTs. In MS Transact-SQL this seems to be a pervasive pattern. JPA 2.1 spec explicitly supports mapping of SP result set(s) e.g. in 220.127.116.11 Named Stored Procedure Queries:
A stored procedure may return more than one result set. As with native queries, the mapping of result sets can be specified either in terms of a resultClasses or as a resultSetMappings annotation element. If there are multiple result sets, it is assumed that they will be mapped using the same mechanism — e.g., all via a set of result class mappings or all via a set of result set mappings. The order of the specification of these mappings must be the same as the order in which the result sets will be returned by the stored procedure invocation. If the stored procedure returns one or more result sets and no resultClasses or resultSetMappings element has been specified, any result set will be returned as a list of type Object. The combining of different strategies for the mapping of stored procedure result sets is undefined.
This feature does not seem to be supported by Spring Data JPA: e.g. see https://stackoverflow.com/questions/31097667/illegalargumentexception-type-cannot-be-null. I spent some time trying to make it work with both 1.5.2 and the current 2.0 snapshot to no avail.
Here is a test example working via pure JPA 2.1 (executed in Spring Boot 1.5.2 app):
1. I understand that Spring Data JPA tries to map an SP OUT parameter to a repository @Procedure method result. This is fine, but when such a method is expected to return a list (List<DomainObject>) it means that developers need to get a result set out of an SP. In MS SQL result sets can be returned implicitly without declaring any additional SP params.
2. Multiple result sets are used more rarely and support for those could be done separately from a single result set.