SQL parameters

[telematica]

Hi,

I've been looking around and I haven't found a way to feed parameters into the query in JdbcCursorItemReader. The idea would be to have some SQL query like:

SELECT a, b
FROM myTab
WHERE number BETWEEN ? AND ?

Assuming a, b and number are fields in table myTab, I would like my step to have an ItemReader that only processes the rows in a range set by two parameters. And I would like to set those parameters at run time, not specify them in the XML description of the job.

Is it possible to do it? If so, am I all wrong trying to use JdbcCursorItemReader? Furthermore, where (which method in with class) should I set the list of parameters?

Thanks in advance.

[lucasward]

You could do something like:

SELECT a, b
FROM myTab
WHERE number BETWEEN ${min} AND ${max}

You could then use a PropertPlaceholderConfigurer to replace them at runtime. This would work well from the command-line, but probably less so if you were kicking the job off from a web request. If the later, you would probably need to create some type of factory/Proxy for the item reader that pulled from JobParameters to create the sql query.

[dkaminsky]

This would diminish the power of the JdbcTemplate though, forcing the end user to format parameters, e.g. putting ' marks around Strings, formatting date objects, etc. I think this is a valid concern.

[lucasward]

I'm not sure I understand, JdbcTemplate isn't being used. It would more than likely be a Spring Batch ItemReader. However, there isn't much way around this, even the approach with property placeholder is having to use string manipulation. We could try and use some kind of approach with prepared statements, but it's only marginally better, as it would still require some type of runtime wrapper.

[telematica]

Thanks for the help, although I don't see how any of those would actually make it easy enough to have everything as detailed as possible in the xml job definition.

Right now we've thought about a different approach: composite ItemReaders.

First, we've extended JdbcCursorItemReader, say ParameterizedJdbcCursorItemReader, providing the functionality of a parameterized query in the sql property. Plus, we've included a Collection<String> property that can be set via the appropriate setter.

Besides, this ParameterizedJdbcCursorItemReader has a delegate ItemReader (here's the composition) that can fetch the parameters it needs. That way, when you call read(), it will see if it has its parameters already set. If it hasn't, then it will ask his delegate for them, set them and start iterating. When it runs out of items, it will ask his delegate for another set of parameters and keep going until his delegate returns no more. That will mean they are both exhausted and they should finish.

This idea, polished and redesigned a bit to be as general and reusable as possible, is what we're thinking of. Any suggestion, commentary and/or advice will be much appreciated.

[lucasward]

My original reaction is that it seems very heavy-weight. I understand why you might want to pull some values from JobParameters (or the system properties) and use them in your sql statement, but I don't understand why you would want to continually pull new parameters, closing and reopening the cursor for each one. It smells funny to me.

If anything should be 'composite' I would think about making a factory for the sql statement that will replace the values, or even better, a custom form of the property placeholder configurer.