java - 带有 Spring boot jpa 错误 : "Unable to extract OUT/INOUT parameter value" 的 SQL 服务器过程调用

Question

我们正在尝试调用 SQL 服务器过程调用(3 个参数):

ALTER PROCEDURE [mvm].[CHECK_USER]
   @IN_USER char(40),
   @IN_PASS char(40),
   @OUT_OK  int output
AS
BEGIN TRY ...
...
SET @OUT_OK = 0
...
RETURN @OUT_OK (Yes! It has a return)

在 Java Spring Boot 应用程序中，我们将这些添加到 pom.xml

<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

我们在存储库中做了两个方法:

方法一(注解):

@Entity
@NamedStoredProcedureQueries({
    @NamedStoredProcedureQuery(
        name = "CheckUser",
        procedureName = "mvm.CHECK_USER",
        parameters = {
          @StoredProcedureParameter(mode = ParameterMode.IN, /*name = "@IN_USER",*/ type = String.class),
          @StoredProcedureParameter(mode = ParameterMode.IN, /*name = "@IN_PASS",*/ type = String.class),
          @StoredProcedureParameter(mode = ParameterMode.INOUT /*name = "@OUT_OK",*/, type = Long.class  )
        })
})
public class User implements Serializable {

    @Id //To avoid build fail
    private int id; 
}

进而:

public interface TableCheckUser extends CrudRepository<User, Long> {

    @Procedure(name = "CheckUser")
    Long checkUser(String user, String password, Long value);

}

在这种情况下，我们有错误:

org.springframework.dao.InvalidDataAccessApiUsageException: Could not locate parameter registered using that position [4]; nested exception is java.lang.IllegalArgumentException: Could not locate parameter registered using that position [4]

方法 2(已写):

@Repository
public class ProcedureInvoker {

    private final EntityManager entityManager;

    @Autowired
    public ProcedureInvoker(final EntityManager entityManager) {
        this.entityManager = entityManager;
    }
public long getCheckUserValue(String user, String password) {

        StoredProcedureQuery storedProcedureQuery = entityManager.createStoredProcedureQuery("mvm.CHECK_USER");


        storedProcedureQuery.registerStoredProcedureParameter(1, String.class, ParameterMode.IN);
        storedProcedureQuery.registerStoredProcedureParameter(2, String.class, ParameterMode.IN);
        storedProcedureQuery.registerStoredProcedureParameter(3, Long.class, ParameterMode.INOUT);


        storedProcedureQuery.setParameter(1, user);
        storedProcedureQuery.setParameter(2, password);
        storedProcedureQuery.setParameter(3, new Long(3));

        storedProcedureQuery.execute();

//Another try (with fail) Call to stored procedure [mvm.checkUser] returned no results
        //Object myobj = storedProcedureQuery.getSingleResult();
        //final Long otherlong = (Long) myobj;

        final Long outputValue2 = (Long) storedProcedureQuery.getOutputParameterValue(3);

        return outputValue2; 
    }

我们得到:

2018-09-28 09:36:15.697 TRACE -- [nio-8080-exec-4] o.h.t.d.s.BasicBinder                    : binding parameter [1] as [VARCHAR] - [myuser]
2018-09-28 09:36:15.770 TRACE --- [nio-8080-exec-4] o.h.t.d.s.BasicBinder                    : binding parameter [2] as [VARCHAR] - [mypass]
2018-09-28 09:36:15.844 TRACE --- [nio-8080-exec-4] o.h.t.d.s.BasicBinder                    : binding parameter [3] as [BIGINT] - [3]
2018-09-28 09:36:15.977 DEBUG --- [nio-8080-exec-4] o.h.r.i.OutputsImpl                      : Building Return [isResultSet=false, updateCount=-1, extendedReturn=false
2018-09-28 09:36:17.323 DEBUG --- [l-1 housekeeper] c.z.h.p.HikariPool                       : HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
2018-09-28 09:36:22.940 DEBUG --- [nio-8080-exec-4] o.h.e.j.s.SqlExceptionHelper             : Unable to extract OUT/INOUT parameter value [n/a]
com.microsoft.sqlserver.jdbc.SQLServerException: The statement is closed.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:227)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.checkClosed(SQLServerStatement.java:1014)
    at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.getLong(SQLServerCallableStatement.java:651)
    at com.zaxxer.hikari.pool.HikariProxyCallableStatement.getLong(HikariProxyCallableStatement.java)

我们尝试了几种方法(添加参数，删除输出参数)，但有错误。

谢谢!

Answer 1

解决了!!

我不需要 execute() 行。
它仅适用于此:

   StoredProcedureQuery storedProcedureQuery = entityManager.createStoredProcedureQuery("mvm.CHECK_USER");


        storedProcedureQuery.registerStoredProcedureParameter(1, String.class, ParameterMode.IN);
        storedProcedureQuery.registerStoredProcedureParameter(2, String.class, ParameterMode.IN);
        storedProcedureQuery.registerStoredProcedureParameter(3, Long.class, ParameterMode.INOUT);


        storedProcedureQuery.setParameter(1, user);
        storedProcedureQuery.setParameter(2, password);
        storedProcedureQuery.setParameter(3, new Long(3));

        Long outputValue2 = (Long) storedProcedureQuery.getOutputParameterValue(3);