PreparedStatementBinder - JDBC Named Host Variables

JDBC makes it easy for Java applications to access and manipulate data in databases. However there are parts of the API that are somewhat primitive. Many host languages provide support for named host variables in SQL statements and stored procedures. Java, via JDBC does not provide this capability. Two statement binders have been created that allow the use of named host variables. This reduces the propensity for making an error and makes it more convenient to work with JDBC. These binders are fully JDBC compliant and are simply a bridge between an application and JDBC. Two statement binders have been created; PreparedStatementBinder and its subclass CallableStatementBinder. CallableStatementBinder obviously inherits all of the capability of its superclass, but provides additional functionality relating to CallableStatements (more commonly known as stored procedures).

Named host variables

To truly understand the value of using named host variables, a brief review of the standard JDBC support for generic parameter markers is in order. When using SQL via JDBC, host variables are specified inside an SQL statement using a generic parameter marker (?). The following SQL SELECT statement illustrates a typical SQL statement that can be executed via JDBC.

SELECT * FROM sometable WHERE CUST_NUM = ?

This statement allows a value to be set to replace the parameter marker. The following Java code might be used to issue an SQL statement that retrieves a customer whose CUST_NUM value is 25.

java.sql.Connection connection = // Code to establish database connection.
String sqlString = "SELECT * FROM sometable WHERE CUST_NUM = ?";
java.sql.PreparedStatement statement = connection.prepareStatement(sqlString);
statement.setInt(1, 25);  // Set the value of the first parameter to 25

This example illustrates that it isn't too difficult to use (anonymous) host variables using JDBC. Consider how difficult it suddenly becomes when you have a statement such as the following:

INSERT INTO sometable ( CUST_NUM, 
  CUST_NAM, 
  CUST_ADDR1,
  CUST_ADDR2,
  CUST_STATE,
  CUST_ZIP,
  CUST_PHONE ) VALUES
  (?, ?, ?, ?, ?, ?, ?)

It becomes quite error prone if you start adding, removing and/or changing the sequence of the variables. Having to keep your code consistent with these changes isn't impossible, just not very convenient. Enter the statement binders. Statement binders allow the use of named host variables instead of having to use generic parameter markers and they are completely compatible with JDBC. Rewriting the first example from above using named host variables, the SQL would look like:

SELECT * FROM sometable WHERE CUST_NUM = :CUST_NUM

This statement specifies a host variable (with a : prefix) named CUST_NUM. It happens to be named the same as the column, but it doesn't have to. The host variable name could have been called customerNumber, custNum or anything you desire. The binder parses the statement, builds a mapping between the variable names and their ordinal positions within the statement and returns a JDBC compliant SQL string to you. The following Java code could be used in a similar manner to accomplish the same task as above.

java.sql.Connection connection = // Code to establish database connection
String sqlString = "SELECT * FROM sometable WHERE CUST_NUM = :CUST_NUM";
PreparedStatementBinder binder = new PreparedStatementBinder();
sqlString = binder.parseHostVariables(sqlString);
java.sql.PreparedStatement statement = connection.prepareStatement(sqlString);
binder.setInt(statement, "CUST_NUM", 25);

Statement binders allow you to ignore the ordinal positions of the parameters and use meaningful names instead of having to map values to ordinal positions. They also have the benefit of allowing variables to be resequenced without any changes to the code that sets the parameters.

xWire also provides support to automatically bind values into the named hostvariables. You may either manually call the set* methods on the binder.However, you may also pass a JDOM XML structure and the XMLelement/attribute values will be bound into the host variables.Finally, you may also bind the values of a Java bean into the hostvariables.

You can view the Javadocfor the JDBC Named Host Variables components. The JDBC Named HostVariables feature is yet another available to you in the xWire toolkit.