What is proper way to use PreparedStatementCreator

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.

Answer 2

Prepared Statements are usually cached by underlying connection pool, so you don't need to worry about creating a new one every time or not.

So I think that your actually usage is correct.

JdbcTemplate closes the statement after executing it, so if you really want to reuse the same prepared statement you could proxy the statement and intercept the close method in the statement creator

For example (not tested, only as example):

public abstract class ReusablePreparedStatementCreator implements PreparedStatementCreator {

    private PreparedStatement statement;

    public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
        if (statement != null)
            return statement;

        PreparedStatement ps = doPreparedStatement(conn);

        ProxyFactory pf = new ProxyFactory(ps);
        MethodInterceptor closeMethodInterceptor = new MethodInterceptor() {

            @Override
            public Object invoke(MethodInvocation invocation) throws Throwable {
                return null;  // don't close statement
            }
        };

        NameMatchMethodPointcutAdvisor closeAdvisor = new NameMatchMethodPointcutAdvisor();
        closeAdvisor.setMappedName("close");
        closeAdvisor.setAdvice(closeMethodInterceptor);
        pf.addAdvisor(closeAdvisor);

        statement = (PreparedStatement) pf.getProxy();

        return statement;       
    }

    public abstract PreparedStatement doPreparedStatement(Connection conn) throws SQLException;

    public void close() {
        try {
            PreparedStatement ps = (PreparedStatement) ((Advised) statement).getTargetSource().getTarget();
            ps.close();
        } catch (Exception e) {
            // handle exception
        }
    }

}

Answer 3

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

I'm not sure why that's so shocking since it's your own code that creates a new PreparedStatement each time by calling connection.prepareStatement(query);. If you want to reuse the same one, then you shouldn't create a new one.