[FIXED] Using SpringBatch JdbcCursorItemReader with List as NamedParameters

Issue

I am using below bean definition to configure a reader to read some data from the database table in a Spring Batch project. It is using a named param in SQL. I am passing A java.util.List as a parameter. However, I am getting Invalid Column type error when it tries to run the SQL.

If I just hard code one single value (namedParameters.put("keys", "138219"); ) instead of passing a list, it works.

@Bean
public JdbcCursorItemReader<MyDTO> myReader() {
JdbcCursorItemReader<MyDTO> itemReader = new JdbcCursorItemReader<>();
itemReader.setDataSource(myDatasource);
itemReader.setRowMapper(return new RowMapper<MyDTO>() {
    @Override
    public MyDTO mapRow(ResultSet resultSet, int rowNum) throws SQLException {
       return toMyDto(resultSet);
    }
};);


Map<String, Object> namedParameters = new HashMap<>();
List<Long> keys= //Some List
Map<String, List<Long>> singletonMap = Collections.singletonMap("keys", keys);
namedParameters.putAll(singletonMap);

itemReader.setSql(NamedParameterUtils.substituteNamedParameters("SELECT A FROM MYTABLE WHERE KEY IN (:keys)",new MapSqlParameterSource(namedParameters)));

ListPreparedStatementSetter listPreparedStatementSetter = new ListPreparedStatementSetter();
listPreparedStatementSetter.setParameters(
    Arrays.asList(NamedParameterUtils.buildValueArray("SELECT A FROM MYTABLE WHERE KEY IN (:keys)", namedParameters)));

itemReader.setPreparedStatementSetter(listPreparedStatementSetter);
return itemReader;
}

I referred sample code snippet here as a response to one of the questions asked - it is what seems to be working when we pass one value. However, my issue is with passing a list instead of one value in the param. This is where it seems to fail.

Solution

The ListPreparedStatementSetter is not aware of parameters types. If a parameter is an array or a collection, it will set it as is to the first placeholder, leaving other placeholders unset. Hence the error. In your example, if List<Long> keys = Arrays.asList(1, 2), your sql statement will be:

SELECT A FROM MYTABLE WHERE KEY IN (?, ?)

If you pass your singletonMap to the ListPreparedStatementSetter, it will set the value of keys (which is of type List) to the first placeholder and that's it. The second placeholder will still be unset and the preparation of the statement will fail.

You can flatten parameters before passing them to the ListPreparedStatementSetter and it should work fine. I added a sample with how to flatten parameters before passing them to the prepared statement setter here (See flatten method).

Hope this helps.

Answered By - Mahmoud Ben Hassine
Answer Checked By - Gilberto Lyons (JavaFixing Admin)