Using Spring Batch JdbcCursorItemReader with NamedParameters [BATCH-2521]

[spring-projects-issues]

Wellington Baltazar de Souza opened BATCH-2521 and commented

Find attached the source files adding this feature.

---

Affects: 3.0.7

Reference URL: http://stackoverflow.com/questions/23222353/using-spring-batch-jdbccursoritemreader-with-namedparameters/38644424#38644424

1 votes, 1 watchers

[spring-projects-issues]

Mahmoud Ben Hassine commented

Hi Wellington Baltazar de Souza

Find attached the source files adding this feature.

There are no attached files to this ticket. That said, after reading the SO thread you linked here, there is no need for a new feature in Spring Batch to support named parameters with the JdbcCursorItemReader. We can achieve the desired functionality thanks to the NamedParameterUtils class from spring-jdbc. Here is an example:

@Bean
public JdbcCursorItemReader<Person> itemReader() {
	String sql = "select * from person where id = :id and name = :name";
	Map<String, Object> namedParameters = new HashMap<String, Object>() {{
		put("id", 1);
		put("name", "foo");
	}};
	return new JdbcCursorItemReaderBuilder<Person>()
	  .name("personItemReader")
	  .dataSource(dataSource())
	  .rowMapper(rowMapper())
	  .sql(NamedParameterUtils.substituteNamedParameters(sql, new MapSqlParameterSource(namedParameters)))
	  .preparedStatementSetter(new ListPreparedStatementSetter(Arrays.asList(NamedParameterUtils.buildValueArray(sql, namedParameters))))
	  .build();
}

The NamedParameterUtils allows to transform named parameters into JDBC placeholders and get the corresponding values indices.
I shared a small project to test this out here.

@ Wellington Baltazar de Souza What do you think?

[fmbenhassine]

The solution proposed in the linked SO thread suggests to

1. introduce a new concept (SqlParameterSourceProvider)
2. implement it with named parameters
3. extend the JdbcCursorItemReader to use the the custom implementation.

This is a bit heavy compared to the following two lines used to transform named parameters to jdbc placeholders:

String preparedSql = NamedParameterUtils.substituteNamedParameters(sql, new MapSqlParameterSource(namedParameters));
PreparedStatementSetter preparedStatementSetter = new ArgumentPreparedStatementSetter(NamedParameterUtils.buildValueArray(sql, namedParameters));

So as mentioned in my previous comment, I see no need to introduce a new concept to support this feature (trying to avoid API bloat) thanks to the NamedParameterUtils class from spring-jdbc.

A complete example can be found in this repo (which also contains an example of how to use a list as a named parameter).

[logie2882]

@bean
public JdbcCursorItemReader itemReader() {
String sql = "select * from person where id IN(:id) and name IN (:name)";
Map<String, List> map= new HashMap<>() {{
map.put("id", Array.asList("22522"));
map.put("name", "21,22");
}};
List parameters = map.values().parallelStream.flatMap(c-->c.stream()).collect(Collectors.toList());
ArgumentPreparedStatementSetter argumentPreparedStatementSetter = new ArgumentPreparedStatementSetter(map.toArray());

return new JdbcCursorItemReaderBuilder<Person>()
  .name("personItemReader")
  .dataSource(dataSource())
  .rowMapper(rowMapper())
  .sql(sql )
  .preparedStatementSetter(argumentPreparedStatementSetter)
  .build();

}

Problem is that its not taking map values of "21 , 20" its failing. Can you please help me on this.

[fmbenhassine]

@logie2882 you are putting multiple values as a String here: map.put("name", "21,22");. You need to split those values and pass them as a list (as you did for id), something like:

- map.put("name", "21,22");
+ map.put("name", Arrays.asList("21,22".split(",")));

Can you please help me on this.

Sure, splitting values as shown above should help (I tested this on the same example). If you need further support, please use SO (https://github.com/spring-projects/spring-batch#getting-help). Thank you.

[logie2882]

@Bean
public JdbcCursorItemReader itemReader() {
String sql = "select * from person where id IN(:id) and name IN (:name)";
Map<String, List> map= new HashMap<>() {{
map.put("id", Arrays.asList("22522"));
map.put("name", Arrays.asList("21,22".split(","));
}};
List parameters = map.values().parallelStream.flatMap(c-->c.stream()).collect(Collectors.toList());
ArgumentPreparedStatementSetter argumentPreparedStatementSetter = new ArgumentPreparedStatementSetter(map.toArray());

return new JdbcCursorItemReaderBuilder<Person>()
  .name("personItemReader")
  .dataSource(dataSource())
  .rowMapper(rowMapper())
  .sql(sql )
  .preparedStatementSetter(argumentPreparedStatementSetter)
  .build();
}

Tried the above said solution , When i pass a single string its working fine. When passing multiple string value i am getting exception java.sql.SqlException:Invalid column index.

The reason for failing is only two IN params but the argumentPreparedStatementSetter is creating 3 param values the query formed at runtime isselect * from person where id IN('22522') and name IN ('21')AND(' 22')which is not an excepted case it should create a query select * from person where id IN('22522') and name IN ('21,22') the excepted query.

I am using spring core jar 4.1.1 RELEASE.

[fmbenhassine]

As mentioned previously, if you need further support, please use SO.

Tried the above said solution , When i pass a single string its working fine. When passing multiple string value i am getting exception java.sql.SqlException:Invalid column index. The reason for failing is only two IN params but the argumentPreparedStatementSetter is creating 3 param values

ArgumentPreparedStatementSetter is a class from Spring Framework, not Spring Batch. So there is nothing we can do on Spring Batch side. You need to flatten the arguments yourself. I already answered this question on SO with a complete code example, see https://stackoverflow.com/questions/54782690.