JDBC and Prepared Statements

Over the next few months, we'll be switching all of our SQL queries over to "parameterized" SQL. This means changing our JDBC calls slightly, as we'll then "prepare" a SQL statement for execution before executing it. It also means we can give parameters to the query at runtime, changing them as often as we want without writing a new SQL statement.

SQL is a language, like any other, and therefore gets compiled into a pseudo-bytecode. Preparing statements is a good idea for the following reasons:

1. it takes care of quoting issues so that you don't have to.
2. it gives you a substantial speed boost.
3. it allows you to do fancy stuff like externalizing all the SQL queries so that more than one database type can be supported easily.

Sample Code

This is a code example of how to use prepared statements properly.

import java.sql.*;

// grab connection from pool 
Connection dbc = openConnection(); 
  
// this string should be a local constant 
String sql_query = "SELECT firstname, lastname, email FROM customers WHERE customerID = ?"; 
PreparedStatement st = null; 
ResultSet rs = null; 

try 
{ 
    // "pre-compile" SQL query 
    st = dbc.prepareStatement(sql_query); 
  
    // set query variables 
    st.setString(1, customerID); 
  
    // execute it 
    rs = st.executeQuery(); 
  
    while (rs.next()) // see note 1 below 
    { 
        // get results 
        String firstname = rs.getString(1);
        String lastname = rs.getString(2); 

        String email = rs.getString(3); 
        if (rs.wasNull()) email = null;
  
        // do something with the results
        // ... 
    } 
} 
catch (SQLException e) 
{ 
    // helpful in debugging what exactly crashed 
    e.setNextException( new SQLException("Faulty statement was: " + sql_query) ); 
    
    // re-throw exception since we can't handle it here
    throw e;
} 
finally 
{ 
    try 
    { 
        if (rs != null) rs.close(); // see also note 2 below 
        if (st != null) st.close(); 
    } 
    catch (SQLException e) 
    {
        // Ignore closing error.
        // Not ignoring this error would *hide* the
        // original exception; we don't want that!
    } 
  
    // return database connection to the pool 
    closeConnection(dbc); 
} 

Note 1: the ResultSet is never NULL

Note 2: Statements are closed before being executed

All of the methods for executing statements close the calling Statement object's current result set if there is one open. This means that any processing of the current ResultSet object needs to be completed before a Statement object is re-executed.

Note 3: Prepared statements are connection-dependent

Prepared statements are linked to the database connection used to create them. They are usually not shareable among multiple clients. Do not prepare a statement, cache it somehow, and then re-execute that same prepared statement with a different connection in scope. This confuses the heck out of the database driver, and Does Not Work.

Typically, you could want to do this in a servlet, prepare all your statements in the constructor of the servlet or its init() method. Because servlets are multithreaded, there will typically be multiple threads executing that prepared statement afterwards. Disaster! The servlet will get empty or null result sets, indicating a driver failure.

Implementation Notes

MySQL doesn't actually know how to prepare statements. Using prepared statements doesn't yield any performance improvements in MySQL. But many other database engines do! Besides, preparing statements frees the developer from the burden of having to properly quote values. This should be reason enough to use them.

References

• jGuru FAQ about JDBC.
• Prepared Statements in a J2EE environment at TheServerSide.