Named Parameters for PreparedStatement

Making JDBC code easier to read and write

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.

A test of the above queries on my computer against a small table gave times of 352 microseconds for NamedParameterStatement, 325 microseconds for AttachableNamedParameterStatement (without synchronization), and 332 microseconds for a raw PreparedStatement. Translating a query took approximately 6 microseconds. Mysteriously, AttachableNamedParameterStatement consistently outperformed a raw PreparedStatement by a small margin. Benchmarking Java code is notoriously difficult because of garbage collection, just-in-time compilation, etc., so these results should be taken with a grain of salt.

In general, performance is not a concern, especially if the actual query is doing anything significant.

Conclusion

NamedParameterStatement is effective as a drop-in replacement for PreparedStatement with no configuration necessary. Programmer productivity can be boosted by the easier interface offered by the new class. Perhaps even more importantly, maintenance is easier because code is more readable.

Code

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;


/**
* This class wraps around a {@link PreparedStatement} and allows the 
programmer to set parameters by name instead
* of by index.  This eliminates any confusion as to which parameter index 
represents what.  This also means that
* rearranging the SQL statement or adding a parameter doesn't involve 
renumbering your indices.
* Code such as this:
*
* Connection con=getConnection();
* String query="select * from my_table where name=? or address=?";
* PreparedStatement p=con.prepareStatement(query);
* p.setString(1, "bob");
* p.setString(2, "123 terrace ct");
* ResultSet rs=p.executeQuery();
*
* can be replaced with:
*
* Connection con=getConnection();
* String query="select * from my_table where name=:name or 
address=:address";
* NamedParameterStatement p=new NamedParameterStatement(con, query);
* p.setString("name", "bob");
* p.setString("address", "123 terrace ct");
* ResultSet rs=p.executeQuery();
*
1 2 Page
Join the discussion
Be the first to comment on this article. Our Commenting Policies
See more