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:
- it takes care of quoting issues so that you don't have to.
- it gives you a substantial speed boost.
- it allows you to do fancy stuff like externalizing all the SQL queries so that
more than one database type can be supported easily.
This is a code example of how to use prepared statements properly.
// 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;
// "pre-compile" SQL query
st = dbc.prepareStatement(sql_query);
// set query variables
// 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
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
Note 1: the
ResultSet is never NULL
execute*() methods always return a
object unless there was some serious internal problem or error. Therefore the reference to the
ResultSet is never normally null. The result set can be empty, that is, no data
was found for your query, but the
ResultSet object itself still is not null. Instead,
check if data was returned with
ResultSet.next(), as in
if(rs.next()) (when a single row is expected)
while(rs.next()), which returns a boolean.
For more information see the API documentation
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
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
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.
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.
Copyright © 2000-2007 by Renaud Waldura.
Permission to make digital or hard copies of part or all of this work for personal
or classroom use is granted without fee, provided that copies are not made or distributed
for profit or commercial advantage, and that copies bear this notice and full citation
on the first page.
Copyright for components of this work owned by others than Renaud Waldura
must be honored. Abstracting with credit is permitted. To copy otherwise, to
republish, to post on servers, or to redistribute to lists, requires prior
specific permission and/or fee. Request permission to publish from
Last modified: 2002/08/01 01:17:48 $