Using prepared statements with JDBCTemplate

Question

I'm using the JDBC template and want to read from a database using prepared statements. I iterate over many lines in a .csv file, and on every line I execute some SQL select queries with corresponding values.

I want to speed up my reading from the database but I don't know how to get the JDBC template to work with prepared statements.

There is the PreparedStatementCreator and the PreparedStatementSetter. As in this example both of them are created with anonymous inner classes. But inside the PreparedStatementSetter class I don't have access to the values I want to set in the prepared statement.

Since I'm iterating through a .csv file, I can't hard code them as a String because I don't know them. I also can't pass them to the PreparedStatementSetter because there are no arguments for the constructor. And setting my values to final would be dumb too.

I was used to the creation of prepared statements being fairly simple. Something like

PreparedStatement updateSales = con.prepareStatement(
    "UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ? ");
updateSales.setInt(1, 75); 
updateSales.setString(2, "Colombian"); 
updateSales.executeUpdate():

as in this Java tutorial.

Answer 1

By default, the JDBCTemplate does its own PreparedStatement internally, if you just use the .update(String sql, Object ... args) form. Spring, and your database, will manage the compiled query for you, so you don't have to worry about opening, closing, resource protection, etc. One of the saving graces of Spring. A link to Spring 2.5's documentation on this. Hope it makes things clearer. Also, statement caching can be done at the JDBC level, as in the case of at least some of Oracle's JDBC drivers. That will go into a lot more detail than I can competently.