Article : Named Parameters for PreparedStatement

Adam Crume has written Named Parameters for PreparedStatement for JavaWorld.com. He has developed a custom JDBC statement called NamedParameterStatement.

In past if you have worked with JDBC than you may know that keeping track of the indices of PreparedStatement is very difficult for larger queries having tens of parameters.
If you needs to insert or delete a parameter in the middle of a query, you must renumber all the parameters following it.

To make writing parameterized query easier, Adam has created NamedParameterStatement.
With NamedParameterStatement, instead of question marks, parameters are represented as a colon followed by an identifier.

So developer don't need to remember index of a parameters, insted parameters can be set using identifier.

Internally NamedParameterStatement uses PreparedStatement and replaces parameter identifiers with question marks.

Here is an example of how NamedParameterStatement can make developers life easier.


String query = "select * from users where userid = :userid;
NamedParameterStatement p = new NamedParameterStatement(con, query);
p.setString("userid", userid);


Look at following insert query.

StringBuffer query = new StringBuffer("INSERT INTO users (userid, first_name, last_name, street, city, country, zip_code));
query.append("VALUES (:userid, :fname, :lname, :street, :city, :country)");
NamedParameterStatement statement= new NamedParameterStatement(conn,query.toString());
statement.setString("userid", userid);
statement.setString("fname", fname );
statement.setString("lname", ;name );
statement.setString("street", street );
statement.setString("city", city);
statement.setString("country", country);
statement.executeUpdate();


Now if you want to insert a new parameter 'middle_initial' at third position, it can be done as in the following.

StringBuffer query = new StringBuffer("INSERT INTO users (userid, first_name,middle_initial, last_name, street, city,country, zip_code));
query.append("VALUES (:userid, :fname, :middleinitial, :lname, :street, :city, :country)");
NamedParameterStatement statement= new NamedParameterStatement(conn,query.toString());
statement.setString("userid", userID);
statement.setString("fname", firstName);
statement.setString("lname", lastName);
statement.setString("street", street );
statement.setString("city", city);
statement.setString("country", country);
statement.setString("middleinitial", middleInitial);
statement.executeUpdate();


You don't need to renumber parameters following middle_initial, insted new parameter is added at the end.

This code is not only flexible but it is more understandable also.