Monday, June 30, 2008

The Value of Named Parameters in JPA QL

In my article Basic Java Persistence API Best Practices, I wrote that because JPA is the result of good ideas from many previous technologies (Hibernate, JDBC, JDO, etc.), it follows that many of the best practices associated with these inspiring technologies can and should be applied to JPA. I used named and positional parameters as an example of this.

The interesting thing about JDBC's PreparedStatement is the fact that, out of the box, the JDBC standard PreparedStatement does not support named parameters. Rather, the PreparedStatement supports placeholders in the SQL syntax that are "populated" via set methods that provide the index of the ? being replaced (counting from left to right).

In order to make code more readable and easier to maintain, many people have tried to workaround the PreparedStatement's positional parameter binding approach. For example, I have seen cases where developers have made the numeric instances Java constants with names that are more readable than the numeral itself. More elegant solutions for using a named parameter type approach have been created to more easily enable "named" parameters. For example, Adam Crume introduces a class called NamedParameterStatement in the JavaWorld article Named Parameters for PreparedStatement. Perhaps even better known is the Spring Framework's support for the NamedParameterJdbcStatement (part of Spring's extensive JDBC support). Finally, Oracle has provided an Oracle-specific JDBC extension that allows named parameters to be specified and used with the OraclePreparedStatement when using an Oracle JDBC driver.

The rising popularity of being able to used named parameters in addition to ordinal parameters is also showcased by Oracle's support for named parameters in JDBC calls to PL/SQL and in IBM's Informix Dynamic Server (IDS).

The JPA contributors recognized the usefulness and desirability of named parameters and JPA Query Language has supported them since JPA's inception. JPA Query Language supports input parameters that are specified with a colon and the parameter name. Then, as demonstrated in the article Standardizing Java Persistence with the EJB3 Java Persistence API, this input parameter can be set by calling the Query.setParameter(---) method.

The JPA 1.0 specification covers named parameters in section 3.6.3. This section indicates how to use named parameters and points out that named parameters are only portable across JPA providers for JPA Query Language statements (not for native queries).

In the case of JPA, hindsight is 20/20. JPA was able to leverage named parameter binding in JPA Query Language statements from the beginning. This allows JPA developers to take advantage of a feature that has been proven to be beneficial many times over and has led to custom implementations when named parameter support is not built into the language or framework.

No comments: