Configuring Named Query Parameterized SQL and Statement Caching at the Project Level

You can configure TopLink to use parameterized SQL and prepared statement caching for all named queries and finders.

These setting apply only to named queries (see "Named Queries")–not to all queries in general or write operations.

Generally, to use parameterized SQL, you should configure it on the session's login (see "Configuring JDBC Options"). Configuring at the session level ensures that all queries will use parameterized SQL.

Note: For applications using a J2EE data source or external connection pool, you must configure statement caching in the J2EE server's data source–not in TopLink.

Table 23-2 summarizes which projects support query statement caching and binding configuration.

Table 23-2 Project Support for Default Named Query Caching and Binding

Descriptor	Using TopLink Workbench	Using Java
Relational Projects
EIS Projects
XML Projects

This section describes configuring statement caching and binding options at the project level. This configuration applies to all named queries you create on the descriptors in this project.

You can also configure named query statement caching and binding options at the query level to override this project-level configuration on a query-by-query basis (see "Configuring Named Query Options").

Using parameterized SQL, you can create and store queries that are complete except for one or more bound parameters. The TopLink runtime binds the current parameter values when executing the query. This approach avoids the preparation of SQL execution and, thus, improves the performance of frequently executed SQL statements.

By default, Oracle TopLink writes data directly into its generated SQL, and does not use parameterized SQL. This is due to the fact that many JDBC drivers do not fully support parameter binding and impose restrictive size or type limits. For more information, see "Parameterized SQL (Binding) and Prepared Statement Caching".

Using TopLink Workbench

To specify the named query options, use this procedure:

1. Select the project object in the Navigator.

2. Select the Defaults tab in the Editor. The Defaults tab appears.

   Figure 23-5 Defaults Tab, Named Query Options

   
   Description of "Figure 23-5 Defaults Tab, Named Query Options"
   
   

Use this table to enter data in following fields on the Defaults tab to specify the named query options for newly created descriptors.:

Field	Description
Cache All Statements	Caches the query's prepared statement in the TopLink statement cache.
Bind All Parameters	Binds all of the query's parameters.