Uploaded image for project: 'Spring Data JPA'
  1. Spring Data JPA
  2. DATAJPA-1092

Support JPA 2.1 stored procedures returning result sets

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 1.11.1 (Ingalls SR1)
    • Fix Version/s: None
    • Component/s: Core
    • Labels:
      None
    • Environment:

      Description

      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 3.10.17.1 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):

      CREATE PROCEDURE tmp_demo (@arg INT)
      AS
      BEGIN
        SELECT @arg + 1 AS simple_result;
      END;
      

      CustomDemoRepository:

      import DemoResult;
      import java.util.List;
      import javax.persistence.EntityManager;
      import javax.persistence.ParameterMode;
      import javax.persistence.StoredProcedureQuery;
      import lombok.Data;
      import lombok.RequiredArgsConstructor;
      import org.springframework.beans.factory.annotation.Autowired;
      import org.springframework.stereotype.Service;
      
      @Service
      @Data
      @RequiredArgsConstructor(onConstructor = @__(@Autowired))
      public class CustomDemoRepository {
      
        //region Injected beans (via a RequiredArgsConstructor)
        private final EntityManager em;
        //endregion
      
        public List<DemoResult> execStoredProdDirectly(Integer arg) {
          StoredProcedureQuery spq = em.createStoredProcedureQuery("tmp_demo", DemoResult.class);
          spq.registerStoredProcedureParameter("arg", Integer.class, ParameterMode.IN);
          spq.setParameter("arg", 42);
          return spq.getResultList();
        }
      }
      

      DemoResult:

      import java.io.Serializable;
      import javax.persistence.Entity;
      import javax.persistence.Id;
      import lombok.Data;
      import lombok.NoArgsConstructor;
      
      @Entity
      @Data // = @ToString @EqualsAndHashCode @Getter-s @Setter-s (non-final) @RequiredArgsConstructor
      @NoArgsConstructor
      public class DemoResult implements Serializable {
      
        @Id private Integer simpleResult;
      }
      

      My thoughts:

      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.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              olivergierke Oliver Drotbohm
              Reporter:
              dkroot Dmitriy Korobskiy
              Last updater:
              Spring Issues Spring Issues
              Votes:
              2 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated: