Prepared statements are a common way to execute parameterized queries in JDBC. For example, the following SQL might be used to retrieve a list of all users whose first or last name matches a particular character sequence:
SELECT * FROM user WHERE first_name LIKE ? or last_name LIKE ?
Parameter values are supplied at runtime via indexed setter methods defined by the
statement.setString(1, pattern); statement.setString(2, pattern);
This works fine for simple queries, but it becomes increasingly difficult to manage as the number of parameters grows. It is also redundant – although this query only requires a single argument, two parameter values must be supplied.
The Java Persistence API (JPA) provides a more convenient alternative using named parameters. For example, the above query might be written as follows in JPQL:
SELECT u FROM User u WHERE u.firstName LIKE :pattern or u.lastName LIKE :pattern
This is more readable and less verbose, as the caller only needs to provide the value of the "pattern" parameter once. It is also more resilient to changes, as the arguments are not dependent on ordinal position. Unfortunately, it requires a JPA-compliant object-relational mapping (ORM) framework such as Hibernate, a dependency that may not be satisfiable in all situations.
org.jtemplate.sql.Parameters class provided by the JTemplate framework brings named parameter support to JDBC. The
parse() method of this class is used to create a
Parameters instance from a JPA-like SQL query; for example:
SELECT * FROM user WHERE first_name LIKE :pattern or last_name LIKE :pattern
It takes a string or reader containing the query text as an argument:
Parameters parameters = Parameters.parse(sqlReader);
getSQL() method of the
Parameters class returns the processed query in standard JDBC syntax. This value can be used in a call to
PreparedStatement statement = connection.prepareStatement(parameters.getSQL());
Parameter values are specified via the
The values are applied to the statement via the
Once applied, the query can be executed:
ResultSet resultSet = statement.executeQuery();
Parameters is not limited to queries; it can also be used for updates.
A complete example using the
Parameters class can be found here. It is a simple REST service that allows a caller to search a database of pets by owner name.
See the JTemplate documentation for more information.