Spring Boot Data JPA从存储过程接收多个Out参数

Question

我试图通过Spring Boot Data JPA(V2.2.6)调用具有多个out参数的存储过程，但收到一个错误：

DEBUG [http-nio-8080-exec-1] org.hibernate.engine.jdbc.spi.SqlStatementLogger: {call TEST_SP(?,?,?)}
RACE [http-nio-8080-exec-1] org.hibernate.type.descriptor.sql.BasicBinder: binding parameter [DOC_NAME] as [VARCHAR] - [ololo]
ERROR [http-nio-8080-exec-1] org.apache.juli.logging.DirectJDKLog: Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessApiUsageException: OUT/INOUT parameter not available: DOC_ID; nested exception is java.lang.IllegalArgumentException: OUT/INOUT parameter not available: DOC_ID] with root cause
java.lang.IllegalArgumentException: OUT/INOUT parameter not available: DOC_ID

CREATE PROCEDURE [dbo].[TEST_SP]
    @DOC_ID bigint output,
    @DOC_GUID nvarchar(255) output,
    @DOC_NAME nvarchar(255)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT @DOC_ID = 6666, @DOC_GUID = @DOC_NAME
END

@Data
@Entity
@NamedStoredProcedureQuery(name = "SomeEntity.test", procedureName = "TEST_SP", parameters = {
        @StoredProcedureParameter(mode = ParameterMode.OUT, name = "DOC_ID", type = Long.class),
        @StoredProcedureParameter(mode = ParameterMode.OUT, name = "DOC_GUID", type = String.class),
        @StoredProcedureParameter(mode = ParameterMode.IN, name = "DOC_NAME", type = String.class)
})
@Table(name = "DOCUMENT_DATA")
public class SomeEntity {
    // some variables
}

public interface SomeRepository extends JpaRepository<SomeEntity, Long> {

    @Procedure(name = "SomeEntity.test")
    Map<String, Object> testSp(@Param("DOC_NAME") String docName);
}

@RestController
public class Controller {

    @Autowired
    SomeRepository repository;

    @GetMapping("test")
    String test() {
        return repository.testSp("ololo").toString();
    }
}

作为示例，我使用了以下链接:https://github.com/spring-projects/spring-data-jpa/blob/master/src/test/java/org/springframework/data/jpa/doma/doma/sample/userrepository.java#l77 https://github.com/spring-projects/spring-data-jpa-jpa/doma/doma/sample/sample/userrepository.java#l77

UPD：我试过Oracle11g

CREATE OR REPLACE PROCEDURE test_sp(x OUT INTEGER, y OUT INTEGER) AS
BEGIN
  x := 17; y := 93;
END;


@NamedStoredProcedureQuery(name = "EstatementsDataEntity.test", procedureName = "test_sp", parameters = {
        @StoredProcedureParameter(mode = ParameterMode.OUT, name = "x", type = Integer.class),
        @StoredProcedureParameter(mode = ParameterMode.OUT, name = "y", type = Integer.class)
})

但收到几乎相同的错误：

java.lang.IllegalArgumentException: OUT/INOUT parameter not available: 1

Answer 1

我找不到带有注释的工作解决方案，并提交了一个bug DATAJPA-1722

但是我可以使用EntityManager解决这个问题：

@SpringBootTest
class DemoApplicationTests {

    @PersistenceContext
    private EntityManager em;

    @Test
    void callStoredProcedureUsingEntitiManager() {
        StoredProcedureQuery proc = em.createStoredProcedureQuery("test_sp");
        proc.registerStoredProcedureParameter("x", Integer.class, ParameterMode.OUT);
        proc.registerStoredProcedureParameter("y", Integer.class, ParameterMode.OUT);
        proc.execute();
        assertThat(proc.getOutputParameterValue("x")).isEqualTo(17);
        assertThat(proc.getOutputParameterValue("y")).isEqualTo(93);
    }

}