JdbcCursorItemReader and 'IN' parameter lists

[cousinsj]

Hi

Spring's JdbcTemplate supports providing an array of arguments to be populated into a sql statement containing an "in"

eg "SELECT column FROM tble WHERE id IN ( :values )"

I've made use of this via the JdbcTemplate.queryForList().

I would like to do something similar using the JdbcCursorItemReader?

Looking at the exposed PreparedStatementSetter I am not sure that it is possible/supported.

Is it possible to do so with the JCIR?
thanks

[trisberg]

It's not possible since there is no support for named parameters. What is your use case?

[cousinsj]

The use case is to select entities using criteria that is based upon a configurable list of values.

basically where column equals an item in the configured list.

I guess I could code the number of currently configured values, resulting in a fairly brittle solution. Or dynamically generating the placeholders based upon the configuration.

Are there other options?

thanks

[lucasward]

The only thing that doesn't work is named parameters, can't you use normal '?' substitution based on an index? That's what the current implementation of StepExecutionPreparedStatementSetter uses.

[cousinsj]

I can use the normal ? substitution but will require me to dynamically generate placeholders based upon the application configuration.
I was trying to avoid that if it was possible / supported.

thanks

[lucasward]

This shouldn't be String manipulation. It should be setting it on the prepared statement, I don't understand how that's any more or less dynamic than the named parameter approach? Have you looked at the implementation of StepExecutionPreparedStatementSetter.

[cousinsj]

I hope I am not misunderstanding something here.

I have looked at the StepExecutionPreparedStatementSetter impl, and understand that the parameters are being set upon the "ps" passed to the PreparedStatementSetter impl. A value for each parameter key set via the list set on the object.

I understand this to require that a "?" placeholder must exist for each paramater key in the sql given to the JCIR, which then creates the PreparedStatement.

Correct?

If the number of parameters is to be dynamic then the sql supplied to the JCIR must have a ? for each parameter - correct?

[trisberg]

The use case is to select entities using criteria that is based upon a configurable list of values.

basically where column equals an item in the configured list.

If you have a large number of values then including them in an IN clause is not a good solution - the JDBC driver doesn't have to support more than a hundred values and batch jobs typically exceed this. A better solution is to put the list of values in a table (value_list) and use that in your query -

select * from my_table
where id in (select id from value_list)

This way there is no limitation for the number of values and no need to use a variable parameter list for your SQL.

[jonnio]

how about

SELECT * FROM tble t
INNER JOIN valuelist val ON t.id=val.id

That will beat a subselect every time.

[trisberg]

That will beat a subselect every time.

What do you base that on?

Your query would work as well, but Oracle's optimizer is pretty smart and would handle both queries in a similar if not identical manner.