Named Parameters for PreparedStatement

http://www.javaworld.com/javaworld/jw-04-2007/jw-04-jdbc.html

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 tablegave times of 352 microseconds for NamedParameterStatement, 325microseconds for AttachableNamedParameterStatement (withoutsynchronization), and 332 microseconds for a raw PreparedStatement.Translating a query took approximately 6 microseconds. Mysteriously,AttachableNamedParameterStatement consistently outperformed a rawPreparedStatement by a small margin. Benchmarking Java code isnotoriously difficult because of garbage collection, just-in-timecompilation, etc., so these results should be taken with a grain ofsalt.

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();
*
* @author adam_crume
*/
public class NamedParameterStatement {
    /** The statement this object is wrapping. */
    private final PreparedStatement statement;

    /** Maps parameter names to arrays of ints which are the parameter indices. */
    private final Map indexMap;


    /**
     * Creates a NamedParameterStatement.  Wraps a call to
     * c.{@link Connection#prepareStatement(java.lang.String) prepareStatement}.
     * @param connection the database connection
     * @param query      the parameterized query
     * @throws SQLException if the statement could not be created
     */
    public NamedParameterStatement(Connection connection, String query) throws SQLException {
        indexMap=new HashMap();
        String parsedQuery=parse(query, indexMap);
        statement=connection.prepareStatement(parsedQuery);
    }


    /**
     * Parses a query with named parameters.  The parameter-index mappings are put into the map, and the
     * parsed query is returned.  DO NOT CALL FROM CLIENT CODE.  This method is non-private so JUnit code can
     * test it.
     * @param query    query to parse
     * @param paramMap map to hold parameter-index mappings
     * @return the parsed query
     */
    static final String parse(String query, Map paramMap) {
        // I was originally using regular expressions, but they didn't work well for ignoring
        // parameter-like strings inside quotes.
        int length=query.length();
        StringBuffer parsedQuery=new StringBuffer(length);
        boolean inSingleQuote=false;
        boolean inDoubleQuote=false;
        int index=1;

        for(int i=0;i<length;i++) {
            char c=query.charAt(i);
            if(inSingleQuote) {
                if(c==''') {
                    inSingleQuote=false;
                }
            } else if(inDoubleQuote) {
                if(c=='"') {
                    inDoubleQuote=false;
                }
            } else {
                if(c==''') {
                    inSingleQuote=true;
                } else if(c=='"') {
                    inDoubleQuote=true;
                } else if(c==':' && i+1<length &&

                        Character.isJavaIdentifierStart(query.charAt(i+1))) {
                    int j=i+2;
                    while(j<length && Character.isJavaIdentifierPart(query.charAt(j))) {
                        j++;
                    }
                    String name=query.substring(i+1,j);
                    c='?'; // replace the parameter with a question mark
                    i+=name.length(); // skip past the end if the parameter

                    List indexList=(List)paramMap.get(name);
                    if(indexList==null) {
                        indexList=new LinkedList();
                        paramMap.put(name, indexList);
                    }
                    indexList.add(new Integer(index));

                    index++;
                }
            }
            parsedQuery.append(c);
        }

        // replace the lists of Integer objects with arrays of ints
        for(Iterator itr=paramMap.entrySet().iterator(); itr.hasNext();) {
            Map.Entry entry=(Map.Entry)itr.next();
            List list=(List)entry.getValue();
            int[] indexes=new int[list.size()];
            int i=0;
            for(Iterator itr2=list.iterator(); itr2.hasNext();) {
                Integer x=(Integer)itr2.next();
                indexes[i++]=x.intValue();
            }
            entry.setValue(indexes);
        }

        return parsedQuery.toString();
    }


    /**
     * Returns the indexes for a parameter.
     * @param name parameter name
     * @return parameter indexes
     * @throws IllegalArgumentException if the parameter does not exist
     */
    private int[] getIndexes(String name) {
        int[] indexes=(int[])indexMap.get(name);
        if(indexes==null) {
            throw new IllegalArgumentException("Parameter not found: "+name);
        }
        return indexes;
    }


    /**
     * Sets a parameter.
     * @param name  parameter name
     * @param value parameter value
     * @throws SQLException if an error occurred
     * @throws IllegalArgumentException if the parameter does not exist
     * @see PreparedStatement#setObject(int, java.lang.Object)
     */
    public void setObject(String name, Object value) throws SQLException {
        int[] indexes=getIndexes(name);
        for(int i=0; i < indexes.length; i++) {
            statement.setObject(indexes[i], value);
        }
    }


    /**
     * Sets a parameter.
     * @param name  parameter name
     * @param value parameter value
     * @throws SQLException if an error occurred
     * @throws IllegalArgumentException if the parameter does not exist
     * @see PreparedStatement#setString(int, java.lang.String)
     */
    public void setString(String name, String value) throws SQLException {
        int[] indexes=getIndexes(name);
        for(int i=0; i < indexes.length; i++) {
            statement.setString(indexes[i], value);
        }
    }


    /**
     * Sets a parameter.
     * @param name  parameter name
     * @param value parameter value
     * @throws SQLException if an error occurred
     * @throws IllegalArgumentException if the parameter does not exist
     * @see PreparedStatement#setInt(int, int)
     */
    public void setInt(String name, int value) throws SQLException {
        int[] indexes=getIndexes(name);
        for(int i=0; i < indexes.length; i++) {
            statement.setInt(indexes[i], value);
        }
    }


    /**
     * Sets a parameter.
     * @param name  parameter name
     * @param value parameter value
     * @throws SQLException if an error occurred
     * @throws IllegalArgumentException if the parameter does not exist
     * @see PreparedStatement#setInt(int, int)
     */
    public void setLong(String name, long value) throws SQLException {
        int[] indexes=getIndexes(name);
        for(int i=0; i < indexes.length; i++) {
            statement.setLong(indexes[i], value);
        }
    }


    /**
     * Sets a parameter.
     * @param name  parameter name
     * @param value parameter value
     * @throws SQLException if an error occurred
     * @throws IllegalArgumentException if the parameter does not exist
     * @see PreparedStatement#setTimestamp(int, java.sql.Timestamp)
     */
    public void setTimestamp(String name, Timestamp value) throws SQLException {
        int[] indexes=getIndexes(name);
        for(int i=0; i < indexes.length; i++) {
            statement.setTimestamp(indexes[i], value);
        }
    }


    /**
     * Returns the underlying statement.
     * @return the statement
     */
    public PreparedStatement getStatement() {
        return statement;
    }


    /**
     * Executes the statement.
     * @return true if the first result is a {@link ResultSet}
     * @throws SQLException if an error occurred
     * @see PreparedStatement#execute()
     */
    public boolean execute() throws SQLException {
        return statement.execute();
    }


    /**
     * Executes the statement, which must be a query.
     * @return the query results
     * @throws SQLException if an error occurred
     * @see PreparedStatement#executeQuery()
     */
    public ResultSet executeQuery() throws SQLException {
        return statement.executeQuery();
    }


    /**
     * Executes the statement, which must be an SQL INSERT, UPDATE or DELETE statement;
     * or an SQL statement that returns nothing, such as a DDL statement.
     * @return number of rows affected
     * @throws SQLException if an error occurred
     * @see PreparedStatement#executeUpdate()
     */
    public int executeUpdate() throws SQLException {
        return statement.executeUpdate();
    }


    /**
     * Closes the statement.
     * @throws SQLException if an error occurred
     * @see Statement#close()
     */
    public void close() throws SQLException {
        statement.close();
    }


    /**
     * Adds the current set of parameters as a batch entry.
     * @throws SQLException if something went wrong
     */
    public void addBatch() throws SQLException {
        statement.addBatch();
    }


    /**
     * Executes all of the batched statements.
     * 
     * See {@link Statement#executeBatch()} for details.
     * @return update counts for each statement
     * @throws SQLException if something went wrong
     */
    public int[] executeBatch() throws SQLException {
        return statement.executeBatch();
    }
}

Adam has been writing Java professionally for about four years. He specializes in core J2SE and the internals of the Java virtual machine. He currently works at Central Technology Services writing web and desktop applications.