CODEWITHSUNDEEP
springspring-batch
user1052610
user1052610

Reputation: 4719

Using Spring Batch JdbcCursorItemReader with NamedParameters

The Spring Batch JdbcCursorItemReader can accept a preparedStatementSetter:

<bean id="reader" class="org.springframework.batch.item.database.JdbcCursorItemReader">
   <property name="dataSource" ref="..." />
   <property name="sql" value="SELECT * FROM test WHERE col1 = ?">
   <property name="rowMapper" ref="..." />
   <property name="preparedStatementSetter" ref="..." />
</bean>

This works well if the sql uses ? as placeholder(s), as in the above example. However, our pre-existing sql uses named parameters, e.g. SELECT * FROM test WHERE col1 = :param .

Is there a way to get a JdbcCursorItemReader to work with a NamedPreparedStatementSetter rather than a simple PreparedStatementSetter?

Thanks

Upvotes: 7

Views: 33148

Answers (5)

panser
panser

Reputation: 2129

in my case I reuse ArgumentPreparedStatementSetter from spring-jdbc

    private static final String SQL = "SELECT * FROM payments.transactions WHERE time_stamp >= ? AND time_stamp <= ?";
...
        Object[] args = new Object[2];
        args[0] = new Date(Instant.now().minus(7, ChronoUnit.DAYS).toEpochMilli());
        args[1] = new Date();
        ArgumentPreparedStatementSetter argumentPreparedStatementSetter =
                new ArgumentPreparedStatementSetter(args);

        return new JdbcCursorItemReaderBuilder<>()
                .name("dbReader")
                .sql(SQL)
                .preparedStatementSetter(argumentPreparedStatementSetter)
        ...

Upvotes: 0

Guoqiang
Guoqiang

Reputation: 357

original solution in https://jira.spring.io/browse/BATCH-2521, but which does not support id in (:ids) clause.

here is an enhancement.

import lombok.Setter;
import lombok.extern.slf4j.Slf4j;
import lombok.val;

import org.springframework.batch.item.database.JdbcCursorItemReader;
import org.springframework.jdbc.core.PreparedStatementCreatorFactory;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterUtils;

import java.util.Map;

@Slf4j
public class NamedParameterJdbcCursorItemReader<T> extends JdbcCursorItemReader<T> {

    protected void setNamedParametersSql(String sql, Map<String, Object> parameters) {
        val parsedSql = NamedParameterUtils.parseSqlStatement(sql);
        val paramSource = new MapSqlParameterSource(parameters);

        val sqlToUse = NamedParameterUtils.substituteNamedParameters(parsedSql, paramSource);
        val declaredParams = NamedParameterUtils.buildSqlParameterList(parsedSql, paramSource);
        val params = NamedParameterUtils.buildValueArray(parsedSql, paramSource, null);
        val pscf = new PreparedStatementCreatorFactory(sql, declaredParams);
        val pss = pscf.newPreparedStatementSetter(params);

        log.info("sql: {}", sqlToUse);
        log.info("parameters: {}", parameters);

        setSql(sqlToUse);
        setPreparedStatementSetter(pss);
    }
}

Usage:

@Slf4j
public class UserItemJdbcReader extends NamedParameterJdbcCursorItemReader<UserEntity> {

    @PostConstruct
    public void init() {
        val sql = "SELECT * FROM users WHERE id IN (:ids)";

        val parameters = new HashMap<String, Object>(4);
        parameters.put("ids", Arrays.asList(1,2,3));

        setDataSource(dataSource);
        setRowMapper(new UserRowMapper());
        setNamedParametersSql(sql, parameters);
    }
}

Upvotes: 1

Wellington Souza
Wellington Souza

Reputation: 2358

Once we don't have an official solution from spring, we can fix this problem using a simple approach:

  1. Define one interface to provide the SqlParameters:
import org.springframework.jdbc.core.namedparam.SqlParameterSource;

public interface SqlParameterSourceProvider { 
    SqlParameterSource getSqlParameterSource();
}
  1. Extending the JdbcCursorItemReader and adding the namedParameter features.
import org.springframework.batch.item.database.JdbcCursorItemReader;
import org.springframework.jdbc.core.SqlTypeValue;
import org.springframework.jdbc.core.StatementCreatorUtils;
import org.springframework.jdbc.core.namedparam.*;
import org.springframework.util.Assert;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.*;


public class NamedParameterJdbcCursorItemReader<T> extends JdbcCursorItemReader<T> {

    private SqlParameterSourceProvider parameterSourceProvider;
    private String paramedSql;

    public NamedParameterJdbcCursorItemReader(SqlParameterSourceProvider parameterSourceProvider) {
        this.parameterSourceProvider = parameterSourceProvider;
    }

    @Override
    public void setSql(String sql) {
        Assert.notNull(parameterSourceProvider, "You have to set parameterSourceProvider before the SQL statement");
        Assert.notNull(sql, "sql must not be null");
        paramedSql = sql;
        super.setSql(NamedParameterUtils.substituteNamedParameters(sql, parameterSourceProvider.getSqlParameterSource()));
    }

    @Override
    protected void applyStatementSettings(PreparedStatement stmt) throws SQLException {
        final ParsedSql parsedSql = NamedParameterUtils.parseSqlStatement(paramedSql);

        final List<?> parameters = Arrays.asList(NamedParameterUtils.buildValueArray(parsedSql, parameterSourceProvider.getSqlParameterSource(), null));
        for (int i = 0; i < parameters.size(); i++) {
            StatementCreatorUtils.setParameterValue(stmt, i + 1, SqlTypeValue.TYPE_UNKNOWN, parameters.get(i));
        }
    }
}
  1. Creating the concrete class that implements the interface SqlParameterSourceProvider and has the state with the updated value of the parameters to be used in your query.
public class MyCustomSqlParameterSourceProvider implements SqlParameterSourceProvider {

    private Map<String, Object> params;

    public void updateParams(Map<String, Object> params) {
        this.params = params;
    }

    @Override
    public SqlParameterSource getSqlParameterSource() {
        final MapSqlParameterSource paramSource = new MapSqlParameterSource();
        paramSource.addValues(params);
        return paramSource;
    }
}
  1. Finally, update the spring configuration.
<bean id="reader" class="org.wisecoding.stackoverflow.NamedParameterJdbcCursorItemReader">
    <constructor-arg ref="sqlParameterSourceProvider"/>        
    <property name="dataSource" ref="..." />
    <property name="sql" value=SELECT * FROM test WHERE col1 = :param" />
    <property name="rowMapper" ref="..." />
    <property name="preparedStatementSetter" ref="..." />
</bean>

<bean id="sqlParameterSourceProvider" class="org.wisecoding.stackoverflow.MyCustomSqlParameterSourceProvider">
</bean>

Upvotes: 3

Braj
Braj

Reputation: 46841

You can try with jobParameters. In this case you don't need any PreparedStatementSetter.

<bean id="reader" class="org.springframework.batch.item.database.JdbcCursorItemReader">
   <property name="dataSource" ref="..." />
   <property name="sql" value="SELECT * FROM test WHERE col1 = #{jobParameters['col1']">
   <property name="rowMapper" ref="..." />
   <property name="preparedStatementSetter" ref="..." />
</bean>

pass the value when running the job

JobParameters param = new JobParametersBuilder().addString("col1", "value1").toJobParameters();

JobExecution execution = jobLauncher.run(job, param);

Upvotes: 3

Michael Minella
Michael Minella

Reputation: 21463

Currently, there is not a way to do this. The JdbcCursorItemReader uses raw JDBC (PreparedStatement) instead of the Spring JdbcTemplate under the hood (since there is no way to get the underlying ResultSet when using JdbcTemplate). If you'd like to contribute this as a new feature, or request it as a new feature, feel free to do so at jira.spring.io

Upvotes: 2

Related Questions