Named Parameters for PreparedStatement

Making JDBC code easier to read and write

By Adam Crume, JavaWorld.com, 04/03/07

Problems with PreparedStatement syntax

The problems with PreparedStatement stem from its syntax for parameters. Parameters are anonymous and accessed by index as in the following:

PreparedStatement p = con.prepareStatement("select * from people where 
(first_name = ? or last_name = ?) and address = ?");
p.setString(1, name);
p.setString(2, name);
p.setString(3, address);

For small queries with one or two parameters, this is not an issue. However, for larger queries, keeping track of the indices becomes very difficult. The developer must carefully read through the SQL statement and count question marks to determine where a parameter is inserted. If a parameter is removed or inserted, all following parameter indices must be renumbered. Obvioulsy this can be problematic if the parameter is toward the beginning of the statement and there are several parameters, or if a query is restructured so the parameters appear in a different order.

Another inconvenience is setting multiple parameters which may be logically the same. This can happen in queries such as select * from people where first_name=? or last_name=?. (This particular query could be rewritten as select * from people where ? in (first_name, last_name), but some queries are not as easily transformed.)

Workarounds

One workaround to avoid renumbering indices is to use a counter to keep track of the index:

PreparedStatement p = con.prepareStatement("select * from people where 
(first_name = ? or last_name = ?) and address = ?");
int i = 1;
p.setString(i++, name);
p.setString(i++, name);
p.setString(i++, address);

This is particularly effective for stability when parameters are being inserted and deleted. However, the code is still no more readable, and the programmer must still take care to ensure that the parameters are listed in the same order they are used in the query.

NamedParameterStatement

This brings us to my class which is called (for lack of a better name) NamedParameterStatement. The syntax is the same as PreparedStatement except that, instead of question marks, parameters are represented as a colon followed by an identifier.

String query = "select * from people where (first_name = :name or last_name 
= :name) and address = :address");
NamedParameterStatement p = new NamedParameterStatement(con, query);
p.setString("name", name);
p.setString("address", address);

Behind the scenes, the class works by replacing the parameter markers with questions marks and creating a PreparedStatement. A mapping is kept between parameter names and their indices. This mapping is referred to when the parameters are injected. The two classes are mutually compatible so programmers may use PreparedStatement for some queries and NamedParameterStatement for others as they see fit.

Performance

The time spent translating the query and looking up parameter indices is actually minimal compared to the time it takes to execute the query. If translation time is a concern, the class could be modified to have attach(Connection) and detach() methods. The NamedParameterStatement could be created beforehand, attached to the connection, used and detached. However, the time taken to cache (and synchronize, if necessary) these objects would probably exceed the time needed to create new ones.