Spring Batch StoredProcedureItemReader仅使用输出参数读取StoredProc（没有输入参数）

Question

<bean id="spItemReader">
    class="org.springframework.batch.item.database.StoredProcedureItemReader" >
    <property name="dataSource" ref="dataSource"/>
    <property name="procedureName" value="mynamespace.spname"/>
    <property name="refCursorPosition" value="1"></property>
    <property name="rowMapper">
        <bean class="MyRowMapper"/>
    </property>
    <property name="parameters">
        <list>
            <bean class="org.springframework.jdbc.core.SqlParameter">
                <constructor-arg index="0" value="column1"/>
                <constructor-arg index="1">
                    <util:constant static-field="java.sql.Types.SMALLINT"/>
                </constructor-arg>
            </bean>
            <bean class="org.springframework.jdbc.core.SqlParameter">
                <constructor-arg index="0" value="P_column2"/>
                <constructor-arg index="1">
                    <util:constant static-field="java.sql.Types.VARCHAR"/>
                </constructor-arg>
            </bean>
            <bean class="org.springframework.jdbc.core.SqlParameter">
                <constructor-arg index="0" value="P_column3"/>
                <constructor-arg index="1">
                    <util:constant static-field="java.sql.Types.INTEGER"/>
                </constructor-arg>
            </bean>
            <bean class="org.springframework.jdbc.core.SqlParameter">
                <constructor-arg index="0" value="P_column4"/>
                <constructor-arg index="1">
                    <util:constant static-field="java.sql.Types.CHAR"/>
                </constructor-arg>
            </bean>
        </list>
    </property>
</bean>

我将dataSource作为org.springframework.jdbc.dataSource.DriverManagerDataSource的实例，并以com.ibm.db2.jcc.db2Driver作为驱动程序类连接到DB2。

我的作业配置如下：

<job id="testJob" xmlns="http://www.springframework.org/schema/batch">
    <step id="step1">
        <tasklet>
            <chunk reader="spItemReader" writer="eventItemWriter"
                commit-interval="1" />
        </tasklet>
    </step>
</job>

<bean id="jobRepository"
    class="org.springframework.batch.core.repository.support.MapJobRepositoryFactoryBean">
    <property name="transactionManager" ref="transactionManager" />
</bean>

<bean id="transactionManager"
    class="org.springframework.batch.support.transaction.ResourcelessTransactionManager" />

<bean id="jobLauncher"
    class="org.springframework.batch.core.launch.support.SimpleJobLauncher">
    <property name="jobRepository" ref="jobRepository" />
</bean>

我有writer作为StaxEventitemWriter和正确的Unmarshaller配置。

我不确定我遗漏了什么，请告诉我该如何修复这个问题。如果需要更多的信息，请让我知道。

Answer 1

最后，我自己找到了一个解决办法。Spring Batch API提供的StoredProcedureItemReader提供了PreparedStatementSetter，它必须被类型化为CallableStatement。因为StoredProcedures不能使用PreparedStatements。我们必须通过为PreparedStatementSetter提供自定义实现来重写setValues方法。下面是配置的外观。

注意：property name=“PreparedStatementSetter”ref=“ParamSet”

代码：

<bean id="spItemReader"
 class="org.springframework.batch.item.database.StoredProcedureItemReader" >
<property name="dataSource" ref="dataSource"/>
<property name="procedureName" value="mynamespace.spname"/>
<property name="refCursorPosition" value="1"></property>
 <property name="preparedStatementSetter" ref="paramSet" ></property>  
<property name="rowMapper">
    <bean class="MyRowMapper"/>
</property>
<property name="parameters">
    <list>
        <bean class="org.springframework.jdbc.core.SqlParameter">
            <constructor-arg index="0" value="column1"/>
            <constructor-arg index="1">
                <util:constant static-field="java.sql.Types.SMALLINT"/>
            </constructor-arg>
        </bean>
        <bean class="org.springframework.jdbc.core.SqlParameter">
            <constructor-arg index="0" value="P_column2"/>
            <constructor-arg index="1">
                <util:constant static-field="java.sql.Types.VARCHAR"/>
            </constructor-arg>
        </bean>
        <bean class="org.springframework.jdbc.core.SqlParameter">
            <constructor-arg index="0" value="P_column3"/>
            <constructor-arg index="1">
                <util:constant static-field="java.sql.Types.INTEGER"/>
            </constructor-arg>
        </bean>
        <bean class="org.springframework.jdbc.core.SqlParameter">
            <constructor-arg index="0" value="P_column4"/>
            <constructor-arg index="1">
                <util:constant static-field="java.sql.Types.CHAR"/>
            </constructor-arg>
        </bean>
    </list>
</property>
</bean>

<bean id="paramSet" class="com.jpmc.ib.asup.batch.CustomSPParamSetter" ></bean>

public class CustomSPParamSetter implements PreparedStatementSetter{

@Override
public void setValues(PreparedStatement ps) throws SQLException {

    CallableStatement eventCallableSt=(CallableStatement)ps;
    eventCallableSt.registerOutParameter(1, java.sql.Types.SMALLINT);
    eventCallableSt.registerOutParameter(2, java.sql.Types.VARCHAR);
    eventCallableSt.registerOutParameter(3, java.sql.Types.INTEGER);
    eventCallableSt.registerOutParameter(4, java.sql.Types.CHAR);
}
}