Spring Batch StoredProcedureItemReader to read StoredProc with only output parameters(no input Params)

Question

I have SP with only 4 output params and no input params, and my StoredProcedureItemReader conf look like this

<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>

I have the dataSource as instance of org.springframework.jdbc.datasource.DriverManagerDataSource and connecting to DB2 with com.ibm.db2.jcc.DB2Driver as driver class.

And my Job configuration is as below:

<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>

I have writer as StaxEventItemWriter and proper unmarshaller configuration.

The Problem is When i run the Batch, I keep getting :

Caused by: org.springframework.jdbc.BadSqlGrammarException: Executing stored procedure; bad SQL grammar [{call mynamespace.spname(?, ?, ?, ?)}]; nested exception is com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=PROCEDURE;BSCPROC.PKA109, DRIVER=4.19.26

I'm not sure what i'm missing, please advice how should i fix this. Let me also know if any more information is required.

Answer 1

Finally I myself have found a resolution. The Spring Batch API provided StoredProcedureItemReader provides PreparedStatementSetter, which has to be typecast to CallableStatement. Because StoredProcedures will not work with PreparedStatements. We will have to override the setValues methods by providing custom implementation for PreparedStatementSetter. And here is how the config should look.

Note: property name="preparedStatementSetter" ref="paramSet"

Code:

<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>

PreparedStatementSetter Implementation:

Code:

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);
}
}

Its not logical to declare to SP parameters in both spring config file and the PreparedStatementSetter. But this is the correct working solution for me.