使用 Spring Batch 中的 IN 和 OUT 参数调用存储过程 [英] Calling stored procedure with an IN and OUT Parameter from Spring Batch

问题描述

我试图从 Spring Batch 执行一个存储过程，该存储过程有两个参数，一个 IN 参数和一个 OUT 参数.我想要的是在调用存储过程时获取结果集和out参数.

I am attempting to execute a stored procedure from Spring Batch, the stored procedure has two parameters, an IN parameter and OUT parameter. What I want is to get the result set and the out parameter when the stored procedure is called.

我参考了StoredProcedureItemReader 和 StoredProcedureItemReaderBuilder

我可以用这个来调用只有IN参数的存储过程，但是注册OUT参数后就不能调用了.

I can use this to call a stored procedure that has only IN parameter, however, I can't call after registering OUT parameter.

如果我们引用我们可以使用的原始 JDBC 模板，则可以使用 https://docs.oracle.com/javase/7/docs/api/java/sql/CallableStatement.html

If we refer raw JDBC template we could use, it is possible to call a Store Procedure with IN and OUT variables using https://docs.oracle.com/javase/7/docs/api/java/sql/CallableStatement.html

而且我相信 StoredProcedureItemReader 或 StoredProcedureIteamReaderBuilder 在幕后使用 CallableStatement.

And I believe that StoredProcedureItemReader or StoredProcedureIteamReaderBuilder uses CallableStatement behind the scenes.

我的问题是，如何使用 StoredProcedureItemReaderBuilder

My question is, how do I register OUT parameters to execute within Spring Batch using StoredProcedureItemReaderBuilder

这是我尝试过的示例代码

Here's a sample code I tried

@StepScope
    @Bean
    public StoredProcedureItemReader<MyRow> rowReader(@Value("#{stepExecutionContext[tableName]}") String tableName) {
        return new StoredProcedureItemReaderBuilder<MyRow>()
                .procedureName("GetNameCountByFname")
                .parameters(
                        new SqlParameter[]{
                                new SqlParameter("fname", Types.VARCHAR),
                                new SqlOutParameter("total", Types.INTEGER)
                        }).
                        preparedStatementSetter(
                                new PreparedStatementSetter() {
                                    @Override
                                    public void setValues(PreparedStatement ps)
                                            throws SQLException {
                                        ps.setString(1, "bob");
                                    }
                                }
                .rowMapper(new MyRowMapper(tableName))
                .name(tableName + "_read")
                .dataSource(dataSource)
                .build();
    }

出现以下错误:

Caused by: java.lang.ArrayIndexOutOfBoundsException: -1
    at java.util.ArrayList.elementData(ArrayList.java:422) ~[na:1.8.0_251]
    at java.util.ArrayList.get(ArrayList.java:435) ~[na:1.8.0_251]
    at com.mysql.cj.jdbc.CallableStatement$CallableStatementParamInfo.getParameter(CallableStatement.java:283) ~[mysql-connector-java-8.0.20.jar:8.0.20]
    at com.mysql.cj.jdbc.CallableStatement.checkIsOutputParam(CallableStatement.java:634) ~[mysql-connector-java-8.0.20.jar:8.0.20]
    at com.mysql.cj.jdbc.CallableStatement.getObject(CallableStatement.java:1356) ~[mysql-connector-java-8.0.20.jar:8.0.20]

调用下面的存储过程

DELIMITER $$

CREATE PROCEDURE GetNameCountByFname(
    IN  fname VARCHAR(25),
    OUT total INT
)
BEGIN
    SELECT COUNT(*)
    INTO total
    FROM `first`
    WHERE `name` = fname;
END$$

DELIMITER ;

推荐答案

我的问题是，如何使用 StoredProcedureItemReaderBuilder 注册 OUT 参数以在 Spring Batch 中执行

My question is, how do I register OUT parameters to execute within Spring Batch using StoredProcedureItemReaderBuilder

这不可能.此功能已被请求但被拒绝.请在 https://github.com/spring-projects/spring 中找到更多详细信息-batch/issues/2024.

That's not possible. This feature has been already requested but was rejected. Please find more details in https://github.com/spring-projects/spring-batch/issues/2024.

这篇关于使用 Spring Batch 中的 IN 和 OUT 参数调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！