JPA - 可以在不提供可选参数的情况下调用存储过程吗? [英] JPA - Can a stored procedure be called without providing optional parameters?

问题描述

我正在调用带有许多可选参数的第三方存储过程.它位于 MS SQL Server 2008 R2 中，如下所示:

I'm calling a third party stored procedure with a lot of optional parameters. It's in MS SQL Server 2008 R2 and looks like this:

procedure [dbo].[pTest]
    @ReportDate varbinary(max) out, 
    @Optional1 varchar(max) = null,
    @Optional2 varbinary(max) = null,
    @Date varchar(max) = null,
    -- ~20 more optional params of various types 
as
begin
select * from test
end

我使用 Spring Data JPA 1.10.2 和 com.microsoft.sqlserver 的 sqljdbc 4.2 从 java 调用它.

I'm calling it from java using Spring Data JPA 1.10.2 with com.microsoft.sqlserver's sqljdbc 4.2.

我只想用两个参数 ReportDate 和 Date 调用过程.其余的参数可以保持默认值(即空).我的代码如下:

I only ever want to call the procedure with two params ReportDate and Date. The rest of the params can stay at the default value (i.e. null). My code is as follows:

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

          })
public class Test {

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

Spring 数据存储库:

Spring data repository:

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
    }

}

但我收到以下错误:

   o.h.engine.jdbc.spi.SqlExceptionHelper   : Error preparing CallableStatement [pTest]

com.microsoft.sqlserver.jdbc.SQLServerException: The index 4 is out of range.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:191) ~[sqljdbc-4.2.jar:na]

我得到这个是因为我没有指定 Optional1 和 Optional2.现在，如果我将 Optional1 和 Optional2 添加为 @StoredProcedureParameters 并作为 TestRepository 的 getTest 方法的参数，我将不再收到错误消息.但是，我必须传入一个非空值，否则我会收到另一个错误，抱怨我没有传递任何值.

I'm getting this because I did not specify Optional1 and Optional2. Now, if I add the Optional1 and Optional2 as @StoredProcedureParameters and as paramters to getTest method of the TestRepository, I no longer get the error. But then, I have to pass in a non-null values or otherwise I get another error complaining that I passed no value.

如何在不指定 Optional1 和 Optional2 的情况下最好地调用此存储过程?

How can I call this stored procedure preferably without specifying Optional1 and Optional2?

在某些情况下，我需要调用一个存储过程，其中第 15 个参数是我需要设置的全部参数.我真的不想在我的代码中定义其他 14 个参数.即使我这样做了，我也不能将它们设置为 null.

In some cases, I need to call a stored procedure where the 15th parameter is all I need to set. I really prefer not having to define the other 14 parameters in my code. Even if I did that, I cannot set them to null.

推荐答案

无法使用 Microsoft 的 JDBC 驱动程序.两个选项:

It's not possible using Microsoft's JDBC driver. Two options:

1. 使用 jTDS(如果存储过程返回 >8000 字节，则不可选)
2. 创建一个包装存储过程，它只包含所需的参数，并让它调用另一个存储过程.如果您无权在数据库上创建存储过程，这可能不是一个选项.

就我而言，选项 2 有效，因为我确实有权创建存储过程.

In my case, option 2 works because I do have permission to create stored procedures.

这篇关于JPA - 可以在不提供可选参数的情况下调用存储过程吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！