Announcement Announcement Module
No announcement yet.
What is proper way to use PreparedStatementCreator of Spring JDBC? Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • What is proper way to use PreparedStatementCreator of Spring JDBC?

    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 ( {
                        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) {
            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++){
            } catch (SQLException e) {
    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?