Prepared Statements (SAP Library - Developing Well Performing Portal Applications)

Prepared Statements

To invoke an SQL statement with different parameters, JDBC offers the prepared statements feature. Prepared statements are SQL statements that contain question marks for every parameter. The SQL statement has to be precompiled by the DBMS and on every execution the current values are filled in. Prepared statements have the ability to insert also values that cannot be expressed in SQL (as string), like BLOBS.

Example without prepared statements:

     Collection newMembers = …;
     Connection conn = …;
     Statement stmt = conn.createStatement();
     for(final Iterator i = newMembers.iterator(); i.hasNext();) {
        Member m = (Member) i.next();
        stmt.executeUpdate("INSERT INTO Members VALUES ('"
          +m.getName()+"',  '"
          +m.getAddress()+"', '"
          +m.getCity()+"')");
     }
     stmt.close();

The example has following flaws:

· A new string in created in the loop.

· Performance is lost because the database does not know that there are more statements of the same structure.

Example of recommended implementation:

By applying prepared statements we can save the entire string processing and our database (or driver) can perform the operations quicker. The best solution is to prepare the statement once and not each time before the execution.

     Collection newMembers = …;
     Connection conn = …;
     PreparedStatement stmt = conn.prepareStatement
               ("INSERT INTO Members VALUES      (?,?,?)");
     for(final Iterator i = newMembers.iterator(); i.hasNext();) {
        Member m = (Member) i.next();
        stmt.setString(1, m.getName());
        stmt.setString(2, m.getAddress());
        stmt.setString(3, m.getCity());
        stmt.executeUpdate();
     }
     stmt.close();

Process documentation

The JLin rule Candidates for PreparedStatements checks where prepared statements could be used.