Announcement Announcement Module
Collapse
No announcement yet.
What is proper way to use PreparedStatementCreator of Spring JDBC? Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • 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,

    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?
Working...
X