Announcement Announcement Module
Collapse
No announcement yet.
Getting Jdbc template to work with prepared statements Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Getting Jdbc template to work with prepared statements

    Hi.

    I am using the Jdbc template as my database interface and now I want to read from my database using prepared statements.

    In my program I iterate over lots of lines in a csv file and on every line I execute between 3 to 5 sql select queries with it the line's values. These select queries are the same for every line. So that would make 5 prepared statements as a whole.

    Currently I sometimes use queryForObject or queryForMap. I'd like to have the same functionality, just with prepared statements to speed up the whole progress.

    Now the problem: I just can't get the Jdbc template to work with prepared statements. Actually I even don't know how to do it.

    There is the PreparedStatementCreator and the PreparedStatementSetter. As in this example both of them are created with an 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 the values as a String. I also can't pass them to the PreparedStatementSetter because there are no arguments for the constructor. And setting the variables to final would in my opinion be dumb too.

    I was used to the creation of prepared statements being fairly simple like this

    Code:
    PreparedStatement updateSales = con.prepareStatement(
        "UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ? ");
    updateSales.setInt(1, 75); 
    updateSales.setString(2, "Colombian"); 
    updateSales.executeUpdate();
    as in the Java tutorial. But instead the jdbc template really is a pain so far.

    I'd want my code to behave something like this.

    Code:
    PreparedStatement ps = "select NAME, ADDRESS, AGE from TEST where NAME = ?";
    LineIterator i = new LineIterator("pathToFile.txt");
    while(i.hasNext(){
    ps.setString(1, i.getValue());
    Object myObject = jdbcTemplate.queryForObject(ps);
    }
    I hope this pseudo code is helpful for you. Maybe you have some advice for me.

    Thank you so far
    Bernissimo

  • #2
    Have you tried batchUpdates?
    http://static.springsource.org/sprin...-advanced-jdbc

    Comment


    • #3
      Check this code,

      Code:
      public void saveStudentData() throws SQLException{
      		String sql = "insert into student (id,name,marks) values (?,?,?)";
      		Object[] args = {5,"Suman",99};
      		executeUdpdateSQL(sql,args);
      	}
      	
      	public void updateStudentData() throws SQLException{
      		String sql = "update student set name=?, marks = ? where id = ?";
      		Object[] args = {"Ayush Suman",999,5};
      		executeUdpdateSQL(sql,args);
      	}
      	
      	public void executeUdpdateSQL(String sql, Object[] args) throws SQLException {
      		jdbc.update(sql, args);
      	}

      Using the above code you can use spring jdbcoperation for preparedstatement. You can also check this example.

      Thanks,

      Ayush Suman

      http://ayushsuman.blogspot.com

      Comment


      • #4
        Thank you for your efforts, but I've found out why my select queries where that slow. It had nothing to do with prepared statements.
        I wanted to search for equal values in my WHERE clauses, but instead of using simply =, I used the LIKE operator. Now the database always searched for a pattern instead for equal values. Having corrected this, my queries are now much faster.
        Last edited by Bernissimo; Jan 4th, 2011, 12:20 AM.

        Comment

        Working...
        X