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.

Solution 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.

Solution 2

class Main {
    public static void main(String args[]) throws Exception {
        ApplicationContext ac = new
          ClassPathXmlApplicationContext("context.xml", Main.class);
        DataSource dataSource = (DataSource) ac.getBean("dataSource");
// DataSource mysqlDataSource = (DataSource) ac.getBean("mysqlDataSource");

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        String prasobhName = 
        jdbcTemplate.query(
           "select first_name from customer where last_name like ?",
            new PreparedStatementSetter() {
              public void setValues(PreparedStatement preparedStatement) throws
                SQLException {
                  preparedStatement.setString(1, "nair%");
              }
            }, 
            new ResultSetExtractor<Long>() {
              public Long extractData(ResultSet resultSet) throws SQLException,
                DataAccessException {
                  if (resultSet.next()) {
                      return resultSet.getLong(1);
                  }
                  return null;
              }
            }
        );
        System.out.println(machaceksName);
    }
}

Solution 3

Try the following:

PreparedStatementCreator creator = new PreparedStatementCreator() {
    @Override
    public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
        PreparedStatement updateSales = con.prepareStatement(
        "UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ? ");
        updateSales.setInt(1, 75); 
        updateSales.setString(2, "Colombian"); 
        return updateSales;
    }
};

Solution 4

I'd factor out the prepared statement handling to at least a method. In this case, because there are no results it is fairly simple (and assuming that the connection is an instance variable that doesn't change):

private PreparedStatement updateSales;
public void updateSales(int sales, String cof_name) throws SQLException {
    if (updateSales == null) {
        updateSales = con.prepareStatement(
            "UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?");
    }
    updateSales.setInt(1, sales);
    updateSales.setString(2, cof_name);
    updateSales.executeUpdate();
}

At that point, it is then just a matter of calling:

updateSales(75, "Colombian");

Which is pretty simple to integrate with other things, yes? And if you call the method many times, the update will only be constructed once and that will make things much faster. Well, assuming you don't do crazy things like doing each update in its own transaction...

Note that the types are fixed. This is because for any particular query/update, they should be fixed so as to allow the database to do its job efficiently. If you're just pulling arbitrary strings from a CSV file, pass them in as strings. There's also no locking; far better to keep individual connections to being used from a single thread instead.

View more solutions

Comments

[Admin]

This would work, but the values I want to set are outside the inner anonymous class. Inside the class should be something like updateSales.setString(2, fileRow.getName()) but I can't access fileRow form inside the class.

[Donal Fellows]

For queries that return a single value, it's pretty easy to use this technique too. The main complexity comes when you have queries that return many values; either return a ResultSet then or pass in a callback that will handle each returned row (with the values broken out of the ResultSet of course).

[Admin]

Sorry, but I don't know what that has got to do with my jdbc template problem. I can't feed a jdbc template query with a PreparedStatement. It seems that I need a PreparedStatementCreator or a PreparedStatementSetter.

[Admin]

But I want to perform a select on the database, not an update. In the Spring reference static.springsource.org/spring/docs/2.0.x/api/org/… is written, that with update only an insert, update or delete can be performed.

[linqu]

@user3211068 there is a query method you can for selects

[leo]

@mezmo would you mind to add a source for your statement? And is the same true for query(String sql, ...)?

[Bastian Voigt]

In the source it looks like JdbcTemplate simply creates a new PreparedStatement every time I call query("select..."). Does this mean the JDBC driver is responsible for reusing the compiled statements?

[mezmo]

Just going by my own experience, mainly with Oracle and DB2, the database itself will cache the prepared statement in some cases, and according to the Oracle documentation at least, the JDBC drivers have their own statement cache, and will move the statement to the cache when the the statement "close" is called. I'll add that link to my original answer....and thanks for the edit Bastian.

[Stian Storrvik]

the query part as java 8 lambda style: jdbcTemplate.query(sql, ps -> ps.setString(1, "value"), (rs, i) -> rs.getLong(1)

[Nic]

Does NamedParameterJdbcTemplate do the same?

[mezmo]

According to the docs, docs.spring.io/spring-framework/docs/current/javadoc-api/org‌​/…, yes.