Named Parameters in JDBC Queries

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 PreparedStatement class:

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.

The 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);

The getSQL() method of the Parameters class returns the processed query in standard JDBC syntax. This value can be used in a call to Connection#prepareStatement():

PreparedStatement statement = connection.prepareStatement(parameters.getSQL());

Parameter values are specified via the put() method:

parameters.put("pattern", pattern);

The values are applied to the statement via the apply() method:

parameters.apply(statement);

Once applied, the query can be executed:

ResultSet resultSet = statement.executeQuery();

Note that 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. JTemplate is distributed as a 32KB JAR file and is also available via Maven:

<dependency>
    <groupId>org.jtemplate</groupId>
    <artifactId>jtemplate</artifactId>
    <version>1.7.6</version>
</dependency>

For more ways to simplify Java development, please see my projects on GitHub:

  • HTTP-RPC – Lightweight multi-platform REST
  • JTemplate – Data-driven presentation templates for Java

JTemplate: Data-Driven Presentation Templates for Java

Templates are documents that describe an output format such as HTML, XML, or CSV. They allow the ultimate representation of a data structure to be specified independently of the data itself, promoting a clear separation of responsibility.

The CTemplate system defines a set of "markers" that are replaced with values supplied by the data structure (which CTemplate calls a "data dictionary") when a template is processed. In JTemplate, the data dictionary is provided by an instance of java.util.Map whose entries represent the values supplied by the dictionary.

For example, the contents of the following map might represent the result of some simple statistical calculations:

{
    "count": 3, 
    "sum": 9.0,
    "average": 3.0
}

A template for transforming this data into HTML is shown below:

<html>
<head>
    <title>Statistics</title>
</head>
<body>
    <p>Count: {{count}}</p>
    <p>Sum: {{sum}}</p>
    <p>Average: {{average}}</p> 
</body>
</html>

At execution time, the "count", "sum", and "average" markers are replaced by their corresponding values from the data dictionary, producing the following markup:

<html>
<head>
    <title>Statistics</title>
</head>
<body>
    <p>Count: 3</p>
    <p>Sum: 9.0</p>
    <p>Average: 3.0</p> 
</body>
</html>

TemplateEncoder Class

JTemplate provides the TemplateEncoder class for merging a template document with a data dictionary. Templates are applied using one of the following TemplateEncoder methods:

public void writeValue(Object value, OutputStream outputStream) { ... }
public void writeValue(Object value, OutputStream outputStream, Locale locale) { ... }
public void writeValue(Object value, Writer writer) { ... }
public void writeValue(Object value, Writer writer, Locale locale) { ... }

The first argument represents the value to write (i.e. the data dictionary), and the second the output destination. The optional third argument represents the locale for which the template will be applied. If unspecified, the default locale is used.

For example, the following code snippet applies a template named map.txt to the contents of a data dictionary whose values are specified by a hash map:

HashMap<String, Object> map = new HashMap<>();

map.put("a", "hello");
map.put("b", 123");
map.put("c", true);

TemplateEncoder encoder = new TemplateEncoder(getClass().getResource("map.txt"), "text/plain");

String result;
try (StringWriter writer = new StringWriter()) {
    encoder.writeValue(map, writer);

    result = writer.toString();
}

System.out.println(result);

If map.txt is defined as follows:

a = {{a}}, b = {{b}}, c = {{c}}

this code would produce the following output:

a = hello, b = 123, c = true

Additional Information

This article introduced the JTemplate framework and provided a brief overview of its key features.

The latest JTemplate release can be downloaded here. For more information, see the project README.