SQL parameters

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:

Code:

---

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.

You could do something like:

Code:

---

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.

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.

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.

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.

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.

I can see what you say about the heavyweight process of that solution. And we still have the problem of using the SQL query as a String instead of having something similar to a good old PreparedStatement that cant set its parameters in a type-safe manner. All of these provided by the limitations of extending JdbcCursorItemReader.

On the other hand, we've come across another example in which we'd need the composite solution because we'd be using a different set of parameters dynamically as we're executing the step, during the same execution of that step.

We're considering re-writing (instead of extending, as we currently do) JdbcCursorItemReader to allow a PreparedStatement.

I understand your point about using String manipulation for a sql statement. PreparedStatements were created for exactly this purpose. The property placeholder configurer approach is a decent work around, but there should probably be a prepared statement that lets you wire in a prepared statement setter, or something else. I've created an issue for it in jira:

http://jira.springframework.org/browse/BATCH-377

We definitely won't have time to get to it for R1, but it's on the radar for 1.1.

Sorry, forgot to answer the other part:

Quote:

---

Originally Posted by telematica

On the other hand, we've come across another example in which we'd need the composite solution because we'd be using a different set of parameters dynamically as we're executing the step, during the same execution of that step.

---

I still don't understand the use case for this, it seems really weird to keep using different values in the middle of a step. Perhaps using separate steps would be better?

The example we found involves something like doing a JOIN between tables, except that one of them isn't a table, but a file. In the example we must only process rows from a table that have a field that's related to some values of the file.

Of course we've thought of reading the file into a temporal table as a first step and then go on working with that, but we're trying to see different approaches to solve the same problems and we're also trying to stick as much as possible to the way the examples we have are solved.

Thanks for the interest :-)