Before posting the question – I went through below URLS, but it was clear enough to me

• Using SpringBatch JdbcCursorItemReader with List as NamedParameters
• How to set list of parameters on prepared statement? etc

In my case, I must need to use SQL query with IN clause. I’m reading from relational DB and write it into CSV file using Spring Batch XML config approach.

I am getting below error

Caused by: org.springframework.dao.TransientDataAccessResourceException: Executing query; SQL [SELECT * FROM emp.employee where age in (?)]; Parameter index out of range (2 > number of parameters, which is 1).; nested exception is java.sql.SQLException: Parameter index out of range (2 > number of parameters, which is 1).
    at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:110) ~[spring-jdbc-5.3.21.jar:5.3.21]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70) ~[spring-jdbc-5.3.21.jar:5.3.21]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79) ~[spring-jdbc-5.3.21.jar:5.3.21]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79) ~[spring-jdbc-5.3.21.jar:5.3.21]
    at org.springframework.batch.item.database.AbstractCursorItemReader.translateSqlException(AbstractCursorItemReader.java:226) ~[spring-batch-infrastructure-4.3.6.jar:4.3.6]
    at org.springframework.batch.item.database.JdbcCursorItemReader.openCursor(JdbcCursorItemReader.java:133) ~[spring-batch-infrastructure-4.3.6.jar:4.3.6]
    at org.springframework.batch.item.database.AbstractCursorItemReader.doOpen(AbstractCursorItemReader.java:453) ~[spring-batch-infrastructure-4.3.6.jar:4.3.6]
    at org.springframework.batch.item.support.AbstractItemCountingItemStreamItemReader.open(AbstractItemCountingItemStreamItemReader.java:150) ~[spring-batch-infrastructure-4.3.6.jar:4.3.6]
    ... 20 common frames omitted
Caused by: java.sql.SQLException: Parameter index out of range (2 > number of parameters, which is 1).
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.jdbc.ClientPreparedStatement.checkBounds(ClientPreparedStatement.java:1345) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.jdbc.ClientPreparedStatement.getCoreParameterIndex(ClientPreparedStatement.java:1358) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.jdbc.ClientPreparedStatement.setString(ClientPreparedStatement.java:1714) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:398) ~[spring-jdbc-5.3.21.jar:5.3.21]
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:231) ~[spring-jdbc-5.3.21.jar:5.3.21]
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:162) ~[spring-jdbc-5.3.21.jar:5.3.21]
    at org.springframework.batch.item.database.support.ListPreparedStatementSetter.setValues(ListPreparedStatementSetter.java:60) ~[spring-batch-infrastructure-4.3.6.jar:4.3.6]
    at com.example.setter.EmployeePsSetter.setValues(EmployeePsSetter.java:18) ~[classes/:na]
    at org.springframework.batch.item.database.JdbcCursorItemReader.openCursor(JdbcCursorItemReader.java:126) ~[spring-batch-infrastructure-4.3.6.jar:4.3.6]
    ... 22 common frames omitted

Below is the complete code
Employee.java

@AllArgsConstructor(staticName = "create")
@NoArgsConstructor
@Data
@Builder
public class Employee {
    private Long empId;
    private String firstName;
    private String lastName;
    private Integer age;
    private String email;

    @Override
    public String toString() {
        return empId + "," + firstName + "," + lastName + "," + age + "," + email;
    }
}

EmployeeMapper.java

public class EmployeeMapper implements RowMapper<Employee> {

    @Override
    public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
        Employee result = new Employee();
        result.setEmpId(rs.getLong("empId"));
        result.setFirstName(rs.getString("firstName"));
        result.setLastName(rs.getString("lastName"));
        result.setAge(rs.getInt("age"));
        result.setEmail(rs.getString("email"));
        return result;
    }
}

xml

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:batch="http://www.springframework.org/schema/batch"
       xmlns:task="http://www.springframework.org/schema/task"
       xmlns:util="http://www.springframework.org/schema/util"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/batch
                         http://www.springframework.org/schema/batch/spring-batch-2.2.xsd
                         http://www.springframework.org/schema/beans
                         http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
                         http://www.springframework.org/schema/util
                         http://www.springframework.org/schema/util/spring-util-3.2.xsd">

    <!-- /questions/5598217/how-do-i-read-jvm-arguments-in-the-spring-applicationcontext-xml -->
    <bean id="applicationProperties" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="ignoreResourceNotFound" value="false" />
        <property name="ignoreUnresolvablePlaceholders" value="true" />
        <property name="searchSystemEnvironment" value="false" />
        <property name="locations">
            <list>
                <value>classpath:application.properties</value>
            </list>
        </property>
    </bean>

    <!-- JobRepository and JobLauncher are configuration/setup classes -->
    <bean id="jobRepository" class="org.springframework.batch.core.repository.support.MapJobRepositoryFactoryBean"/>

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

    <batch:job id="employeeJob">
        <batch:step id="step1">
            <batch:tasklet>
                <batch:chunk reader="employeeItemReader"
                             writer="cvsFileItemWriter"
                             commit-interval="1">
                </batch:chunk>
            </batch:tasklet>
        </batch:step>
    </batch:job>

    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/test"/>
        <property name="username" value="root"/>
        <property name="password" value="Password"/>
    </bean>

    <bean id="employeeSetter" class="org.springframework.batch.item.database.support.ListPreparedStatementSetter">
        <property name="parameters">
            <list>
                <value>22</value>
                <value>44</value>
            </list>
        </property>
    </bean>

    <bean id="employeeItemReader" class="org.springframework.batch.item.database.JdbcCursorItemReader">
        <property name="dataSource" ref="dataSource"/>
        <property name="sql" value="SELECT * FROM emp.employee where age in (?)"/>
        <property name="rowMapper">
            <bean class="com.example.mapper.EmployeeMapper"/>
        </property>
        <property name="preparedStatementSetter" ref="employeeSetter" />
    </bean>

    <bean id="cvsFileItemWriter" class="org.springframework.batch.item.file.FlatFileItemWriter">
        <property name="resource" value="file:cvs/employee.csv"/>
        <property name="shouldDeleteIfExists" value="true"/>

        <property name="lineAggregator">
            <bean class="org.springframework.batch.item.file.transform.DelimitedLineAggregator">
                <property name="delimiter" value=","/>
                <property name="fieldExtractor">
                    <bean class="org.springframework.batch.item.file.transform.BeanWrapperFieldExtractor">
                        <property name="names" value="empId, firstName, lastName, age, email"/>
                    </bean>
                </property>
            </bean>
        </property>
    </bean>
</beans>

MainApp.java

import org.springframework.batch.core.Job;
import org.springframework.batch.core.JobExecution;
import org.springframework.batch.core.JobExecutionException;
import org.springframework.batch.core.JobParameters;
import org.springframework.batch.core.launch.JobLauncher;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
public class MainApp implements CommandLineRunner {

    public static void main(String[] args) {
        SpringApplication.run(MainApp.class, args);
    }

    @Override
    public void run(String... args) throws Exception {
        ApplicationContext context = new ClassPathXmlApplicationContext("spring-batch-context.xml");

        JobLauncher jobLauncher = (JobLauncher) context.getBean("jobLauncher");
        Job job = (Job) context.getBean("employeeJob");

        try {
            JobExecution execution = jobLauncher.run(job, new JobParameters());
            System.out.println("Job Exit Status : "+ execution.getStatus());
        } catch (JobExecutionException e) {
            System.out.println("Job ExamResult failed");
            e.printStackTrace();
        }
    }
}

EmployeeSetter

import lombok.Setter;
import org.springframework.batch.item.database.support.ListPreparedStatementSetter;

import java.sql.PreparedStatement;
import java.sql.SQLException;

@Setter
public class EmployeePsSetter extends ListPreparedStatementSetter {

    @Override
    public void setValues(PreparedStatement ps) throws SQLException {
        String params = new StringBuilder("'").append("22").append("'").append(",'").append("44").append(",").toString();
        ps.setObject(1, params);
        super.setValues(ps);
    }
}