Optimize with a SATA RAID Storage Solution
Range of capacities as low as $1250 per TB. Ideal if you currently rely on servers/disks/JBODs
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.)
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.
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.
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
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