java - 错误: Parameter out was not defined for stored procedure

Question

当我调用这个存储过程时:

procedure [dbo].[pTest]
     @Date varchar(max) =null
as
begin
select * from test
end

来自 java，使用 Spring Data JPA 1.10.2 和 com.microsoft.sqlserver 的 sqljdbc 4.2。我没有收到任何错误。但是当我向过程中添加另一个参数时

procedure [dbo].[pTest]
     @ReportData varbinary(max) out
     ,@Date varchar(max) =null
as
begin
select * from test
end

我收到以下错误:

2016-10-06 15:50:16.283 DEBUG 32604 --- [           main] c.m.s.jdbc.internals.SQLServerException  : *** SQLException:SQLServerCallableStatement:9 com.microsoft.sqlserver.jdbc.SQLServerException: Parameter out was not defined for stored procedure pTest. Parameter out was not defined for stored procedure pTest.
2016-10-06 15:50:16.289 DEBUG 32604 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : Error preparing CallableStatement [pTest]

com.microsoft.sqlserver.jdbc.SQLServerException: Parameter out was not defined for stored procedure pTest.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:191) ~[sqljdbc-4.2.jar:na]

如果我删除 @Date 参数并相应地修改代码，则不会出现错误。当我重新添加 @Date 参数以使其具有两个参数时，我再次收到错误。为什么？

两个参数的代码如下:

@Entity
@NamedStoredProcedureQuery(name = "Test.getTest", procedureName = "pTest", parameters = {
        @StoredProcedureParameter(mode = ParameterMode.IN, name = "Date", type = String.class),
        @StoredProcedureParameter(mode = ParameterMode.OUT, name = "ReportData", type = byte[].class)
          })
public class Test {

    // serves no purpose other than to meet
    // JPA requirement
    @Id
    private byte[] reportData;
}

Spring 数据存储库:

public interface TestRepository  extends Repository<Test, Long> {
    @Procedure("pTest")
    byte[] get(@Param("Date") String date);
}

测试代码:

@RunWith(SpringRunner.class)
@SpringBootTest
@Transactional(transactionManager="transactionManager")
public class TestRepositoryTest {

    @Autowired
    private TestRepository testRepository;

    @Test
    public void testGet() throws SQLException {

        byte[] blob = testRepository.get("hi");
        //error occurs
    }

}

Answer 1

发现问题:) @NamedStoredProcedureQuery 的属性名称需要与存储库方法名称匹配。所以改变这个:

@Entity
@NamedStoredProcedureQuery(name = "Test.getTest", procedureName = "pTest", parameters = {
        @StoredProcedureParameter(mode = ParameterMode.IN, name = "Date", type = String.class),
        @StoredProcedureParameter(mode = ParameterMode.OUT, name = "ReportData", type = byte[].class)
          })

对此:

@Entity
@NamedStoredProcedureQuery(name = "Test.get", procedureName = "pTest", parameters = {
        @StoredProcedureParameter(mode = ParameterMode.IN, name = "Date", type = String.class),
        @StoredProcedureParameter(mode = ParameterMode.OUT, name = "ReportData", type = byte[].class)
          })