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

User · Accepted Answer

I ve tried a select statement now with a PreparedStatement  but it turned out that it was not faster than the Jdbc template  Maybe  as mezmo suggested  it automatically creates prepared statements   Anyway  the reason for my sql SELECTs being so slow was another one  In the WHERE clause I always used the operator LIKE  when all I wanted to do was finding an exact match  As I ve found out LIKE searches for a pattern and therefore is pretty slow    I m using the operator   now and it s much faster

User · Answer

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

User · Answer

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 lt Long gt                    public Long extractData ResultSet resultSet  throws SQLException                  DataAccessException                     if  resultSet next                            return resultSet getLong 1                                         return null                                                   System out println machaceksName

User · Answer

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

User · Answer

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

[java] Using prepared statements with JDBCTemplate

Examples related to java

Examples related to spring

Examples related to jdbc

Examples related to prepared-statement

Examples related to jdbctemplate