java - 在 Spring 中调用 SimpleJdbcCall 中的过程时出现 SQLServerException

Question

@Transactional
public void importarAdiantamento(LayoutArquivo arquivo, Usuario usuario) throws Exception {

    try {

        DateFormat fmt = new SimpleDateFormat("dd/MM/yyyy");    
        SqlParameterSource in = new MapSqlParameterSource()
        .addValue("DATAVCM", new java.sql.Date(fmt.parse(arquivo.getVencimento()).getTime()))
        .addValue("VALOR", arquivo.getValor())
        .addValue("MOTIVO", arquivo.getCodMotivo())
        .addValue("EMPRESA", arquivo.getCodEmpresa())
        .addValue("NPARCELA", 1)
        .addValue("TOTALPARCELA", 1)
        .addValue("DOCUMENTO", arquivo.getDocumento())
        .addValue("FUNCIONARIO", arquivo.getCodFunc())
        .addValue("HISTORICO", "VALE IMPORTADO")
        .addValue("EXPORTAR", 0)
        .addValue("USUARIO", usuario.getCodigousuario())
        .addValue("DUPLIC", 0)
        .addValue("PROTOCOLO", "0");


        SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate).withCatalogName("dbo").withProcedureName(AdiantamentoQueries.importarAdiantamento());

        jdbcCall.execute(in);
    } catch (Exception e) {
        throw new Exception(e);
    }
}

public class AdiantamentoQueries {

public static String importarAdiantamento() {
    return "AD_INSERT_ADIANTAMENTO_PARCELAS ?,?,?,?,?,?,?,?,?,?,?,?,?";
}
}

抛出的异常是:

java.lang.IllegalArgumentException: Erro ao importar Adiantamentos: org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{call dbo.AD_INSERT_ADIANTAMENTO_PARCELAS ?,?,?,?,?,?,?,?,?,?,?,?,?()}]; SQL state [null]; error code [0]; O valor não está definido para o número de parâmetro 1.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: O valor não está definido para o número de parâmetro 1.
    at br.com.adiantamento.controller.ImportacaoController.importarAdiantamento(ImportacaoController.java:172) ~[classes/:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_60]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_60]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_60]
    at java.lang.reflect.Method.invoke(Method.java:497) ~[na:1.8.0_60]
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205) ~[spring-web-4.3.14.RELEASE.jar:4.3.14.RELEASE]
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133) ~[spring-web-4.3.14.RELEASE.jar:4.3.14.RELEASE]
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97) ~[spring-webmvc-4.3.14.RELEASE.jar:4.3.14.RELEASE]
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827) ~[spring-webmvc-4.3.14.RELEASE.jar:4.3.14.RELEASE]
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738) ~[spring-webmvc-4.3.14.RELEASE.jar:4.3.14.RELEASE]
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85) ~[spring-webmvc-4.3.14.RELEASE.jar:4.3.14.RELEASE]
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967) ~[spring-webmvc-4.3.14.RELEASE.jar:4.3.14.RELEASE]
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901) ~[spring-webmvc-4.3.14.RELEASE.jar:4.3.14.RELEASE]
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970) ~[spring-webmvc-4.3.14.RELEASE.jar:4.3.14.RELEASE]
    at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872) ~[spring-webmvc-4.3.14.RELEASE.jar:4.3.14.RELEASE]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:661) ~[tomcat-embed-core-8.5.27.jar:8.5.27]
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846) ~[spring-webmvc-4.3.14.RELEASE.jar:4.3.14.RELEASE]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:742) ~[tomcat-embed-core-8.5.27.jar:8.5.27]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) ~[tomcat-embed-core-8.5.27.jar:8.5.27]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.27.jar:8.5.27]
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) ~[tomcat-embed-websocket-8.5.27.jar:8.5.27]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.27.jar:8.5.27]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.27.jar:8.5.27]
    at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99) ~[spring-web-4.3.14.RELEASE.jar:4.3.14.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-4.3.14.RELEASE.jar:4.3.14.RELEASE]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.27.jar:8.5.27]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.27.jar:8.5.27]
    at org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:108) ~[spring-web-4.3.14.RELEASE.jar:4.3.14.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-4.3.14.RELEASE.jar:4.3.14.RELEASE]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.27.jar:8.5.27]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.27.jar:8.5.27]
    at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:81) ~[spring-web-4.3.14.RELEASE.jar:4.3.14.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-4.3.14.RELEASE.jar:4.3.14.RELEASE]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.27.jar:8.5.27]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.27.jar:8.5.27]
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197) ~[spring-web-4.3.14.RELEASE.jar:4.3.14.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-4.3.14.RELEASE.jar:4.3.14.RELEASE]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.27.jar:8.5.27]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.27.jar:8.5.27]
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199) ~[tomcat-embed-core-8.5.27.jar:8.5.27]
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) [tomcat-embed-core-8.5.27.jar:8.5.27]
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:504) [tomcat-embed-core-8.5.27.jar:8.5.27]
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140) [tomcat-embed-core-8.5.27.jar:8.5.27]
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81) [tomcat-embed-core-8.5.27.jar:8.5.27]
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87) [tomcat-embed-core-8.5.27.jar:8.5.27]
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342) [tomcat-embed-core-8.5.27.jar:8.5.27]
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:803) [tomcat-embed-core-8.5.27.jar:8.5.27]
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) [tomcat-embed-core-8.5.27.jar:8.5.27]
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:790) [tomcat-embed-core-8.5.27.jar:8.5.27]
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1459) [tomcat-embed-core-8.5.27.jar:8.5.27]
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-embed-core-8.5.27.jar:8.5.27]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_60]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_60]
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-embed-core-8.5.27.jar:8.5.27]
    at java.lang.Thread.run(Thread.java:745) [na:1.8.0_60]

The value is not set for parameter number 1 .; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: The value is not set to parameter number 1.

我看到很多关于使用 SimpleJdbcCall spring 的教程和答案。但没有成功，我正在尝试这种方式，并且有异常(exception)。

另一个疑问，我的参数DUPLIC是一个out参数，如何在我的代码中使用这个out参数？

Obs:使用的数据库是SQL Server

编辑:程序如下:

ALTER PROCEDURE [dbo].[AD_INSERT_ADIANTAMENTO_PARCELAS]
    (@DATAVCM DATE,
     @VALOR NUMERIC(18,2),
     @MOTIVO INT,
     @EMPRESA INT,
     @NPARCELA INT,
     @TOTALPARCELA INT,
     @DOCUMENTO VARCHAR(100),
     @FUNCIONARIO INT,
     @HISTORICO VARCHAR(100),
     @EXPORTAR INT,
     @USUARIO INT,
     @DUPLIC INT OUTPUT,
     @PROTOCOLO VARCHAR(100))
AS
...

Answer 1

这是示例代码。

declareParameters - Declare all input and output parameters of the procedure. Use SqlOutParameter class for declaring the output parameter

您可以将DUPLIC定义为NUMBER(即输出参数)并使用getInt获取值。

示例代码:-

SimpleJdbcCall insertParcelas = new SimpleJdbcCall(jdbcTemplate)
        .withSchemaName("dbo")
        .withCatalogName("dbo")     
        .withProcedureName("AD_INSERT_ADIANTAMENTO_PARCELAS")
        .declareParameters(             
                new SqlParameter("DATAVCM", OracleTypes.DATE),
                new SqlParameter("VALOR", OracleTypes.VARCHAR),
                ....
                new SqlOutParameter("DUPLIC", OracleTypes.NUMBER));

DateFormat fmt = new SimpleDateFormat("dd/MM/yyyy");    
SqlParameterSource in = new MapSqlParameterSource()
    .addValue("DATAVCM", new java.sql.Date(fmt.parse(arquivo.getVencimento()).getTime()))
    .addValue("VALOR", arquivo.getValor())
    .addValue("MOTIVO", arquivo.getCodMotivo())
    .addValue("EMPRESA", arquivo.getCodEmpresa())
    .addValue("NPARCELA", 1)
    .addValue("TOTALPARCELA", 1)
    .addValue("DOCUMENTO", arquivo.getDocumento())
    .addValue("FUNCIONARIO", arquivo.getCodFunc())
    .addValue("HISTORICO", "VALE IMPORTADO")
    .addValue("EXPORTAR", 0)
    .addValue("USUARIO", usuario.getCodigousuario())
    .addValue("DUPLIC", 0)
    .addValue("PROTOCOLO", "0");

Map<String, Object> out = insertParcelas.execute(in);

if (out != null && !out.isEmpty()) {
    Sysout.out.println(out.getInt("DUPLIC"));
}