What is proper way to use PreparedStatementCreator of Spring JDBC?

Question

As per my understanding the use of PreparedStatement in Java is we can use it multiple times. But I have some confusion using PreparedStatementCreator of Spring JDBC.

For example consider following code,

public class SpringTest {

    JdbcTemplate jdbcTemplate; 
    PreparedStatementCreator preparedStatementCreator; 
    ResultSetExtractor<String> resultSetExtractor;

    public SpringTest() throws SQLException {

        jdbcTemplate = new JdbcTemplate(OracleUtil.getDataSource());

        preparedStatementCreator = new PreparedStatementCreator() {
            String query = "select NAME from TABLE1  where ID=?";
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                return connection.prepareStatement(query);
            }
        };

        resultSetExtractor  = new ResultSetExtractor<String>() {
            public String extractData(ResultSet resultSet) throws SQLException,
            DataAccessException {
                if (resultSet.next()) {
                    return resultSet.getString(1);
                }
                return null;
            }
        };
    }
    public String getNameFromId(int id){
        return jdbcTemplate.query(preparedStatementCreator, new Table1Setter(id), resultSetExtractor);
    }

    private static class Table1Setter implements PreparedStatementSetter{

        private int id;
        public Table1Setter(int id) {
            this.id =id;
        }
        @Override
        public void setValues(PreparedStatement preparedStatement) throws SQLException {
            preparedStatement.setInt(1, id);
        }
    }
    public static void main(String[] args) {
        try {
            SpringTest  springTest = new SpringTest();

            for(int i=0;i<10;i++){
                System.out.println(springTest.getNameFromId(i));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

As per this code when I called springTest.getNameFromId(int id) method, it returns name from given id, Here I've used PreparedStatementCreator for creating PreparedStatement and PreparedStatementSetter for setting input parameters and I got result from ResultSetExtractor. But performance is very slow.

After debugging and looking into what happens inside PreparedStatementCreator and JdbcTemplate I got to know that PreparedStatementCreator creates each and every time new PreparedStatement...!!!

Each and every time when I am calls method jdbcTemplate.query(preparedStatementCreator, preparedStatementSetter, resultSetExtractor), it creates new PreparedStatement and this slow downs performance.

Is this right way to use PreparedStatementCreator? Because in this code I unable to reuse PreparedStatement. And if this is right way to use PreparedStatementCreator than how to get benefit of re-usability of PreparedStatement?

Answer 1

You are on the right way to use PreparedStatementCreator.

1. In each new transaction, you should create brand new PreparedStatement instance, it's definitely correct. PreparedStatementCreator is mainly designed to wrap the code block to create PreparedStatement instance easily, not saying that you should resue the new instance each itme.
2. PreparedStatement is mainly designed to send the templated and pre-compiled SQL statement DBMS which will save some pre-compiled time for SQL execution.

To summarize, what you did is correct. use PreparedStatement will have better performance than Statement.