Skip Headers

# Parameterized SQL (Binding) and Prepared Statement Caching

Previous

Next

Using parameterized SQL, you can keep the overall length of an SQL query from exceeding the statement length limit that your JDBC driver or database server imposes.

Using parameterized SQL and prepared statement caching, you can improve performance by reducing the number of times the database SQL engine parses and prepares SQL for a frequently called query.

By default, TopLink does not enable parameterized SQL and prepared statement caching, because not all databases and JDBC drivers support it. Oracle recommends that you enable parameterized SQL and prepared statement caching for selected databases and JDBC drivers that support these options.

Not all JDBC drivers support all JDBC binding options (see "Configuring JDBC Options"). Selecting a combination of options may result in different behavior from one driver to another. Before selecting JDBC options, consult your JDBC driver documentation. When choosing binding options, consider the following approach:

1. Try binding all parameters with all other binding options disabled.

2. If this fails to bind some large parameters, consider enabling one of the following options, depending on the parameter's data type and the binding options that your JDBC driver supports:

   1. To bind large `String` parameters, try enabling string binding.

      If large `String` parameters still fail to bind, consider adjusting the maximum `String` size. TopLink sets the maximum `String` size to 32000 characters by default.

   2. To bind large `Byte` array parameters, try enabling byte array binding.

3. If this fails to bind some large parameters, try enabling streams for binding.

   Typically, configuring string or byte array binding will invoke streams for binding. If not, explicitly configuring streams for binding may help.

For J2EE applications that use TopLink external connection pools, you must configure parameterized SQL in TopLink but you cannot configure prepared statement caching in TopLink. In this case, you must configure prepared statement caching in the application server connection pool. For example, in OC4J, if you configure your `data-source.xml` file with a managed `data-source` (where `connection-driver` is `oracle.jdbc.OracleDriver` and `class` is `oracle.j2ee.sql.DriverManagerDataSource`), you can configure a non-zero `num-cached-statements` that enables JDBC statement caching and defines the maximum number of statements cached.

For applications that use TopLink internal connection pools, you can configure parameterized SQL and prepared statement caching.

You can configure parameterized SQL and prepared statement caching at the following levels:

- project level–applies to all named queries (see "Configuring Named Query Parameterized SQL and Statement Caching at the Project Level");

- descriptor level–applies on a per-named-query basis (see "Configuring Named Query Options");

- database login level–applies to all queries (see "Configuring JDBC Options") and provides additional parameter binding API to alleviate the limit imposed by some drivers on SQL statement size;

- query level–applies on a per-query basis (see "Using Parameterized SQL and Statement Caching in a DatabaseQuery").

See Also

  Data Access Optimization
  Optimization