spring batch - SqlPagingQueryProviderFactoryBean to support In clause

Question

I want to write an SQL for SqlPagingQueryProviderFactoryBean. I will pass the parameter for an IN clause. I am not getting the result when I am passing it as a parameter(?). But I am getting the correct result when I am hard coding the values.

Please guide me on this.

Answer 1

You cannot have a single place holder and replace it with an array due to sql injection security policy, However the gettter/setters of sqlPagingQueryProvider properties selectclause, fromClause and whereCLause are String and not preparedStatement. The PreparedStatement would be constructed later by spring batch during post construct method. Hence you can send the where clause as String with values(Prepared) and pass it to job as parameter. Hence your code would something of this sort.

String topics = { "topic1", "topic2", "topic3", "topic4"};

StringBuilder str = new StringBuilder("('");

for (String topic : topics) {
    str.append(topic + "','");
}
str.setLength(str.length() - 2);
str.append("')");


final JobParameters jobParameters = new JobParametersBuilder()
                .addLong("time", System.nanoTime())
                .addString("inputsTopics", str.toString())
                .toJobParameters();

And your pagingreader bean would look like below and make sure you set scope to step

<bean id="sqlPagingReader" class="<your extended prging prvder>.KPPageingProvider" scope="step" >
        <property name="dataSource" ref="dataSource" />
        <property name="selectClause" value="u.topic,cu.first_name ,cu.last_name, cu.email" />
        <property name="fromClause" value="ACTIVE_USER_VIEWS_BY_TOPIC u inner join cl_user cu on u.user_id=cu.id" />
        <property name="whereClause" value="u.topic in #{jobParameters['inputsTopics']}" ></property>
</bean>