Configuring Named Queries at the Descriptor Level

A named query is a TopLink query that you create and store, by name, in a descriptor's DescriptorQueryManager for later retrieval and execution. Named queries improve application performance because they are prepared once and they (and all their associated supporting objects) can be efficiently reused thereafter making them well suited for frequently executed operations.

If a named query is global to a Class, configure it at the descriptor level. Alternatively, you can configure a named query at the session level (see "Configuring Named Queries at the Session Level").

Use named queries to specify SQL, EJB QL, or TopLink Expression queries to access your data source.

For entity bean descriptors, you must define a named query for every finder defined for the corresponding entity bean.

Using TopLink Workbench, you can configure named queries for a subset of query types and store them in a descriptor's DescriptorQueryManager (see "Using TopLink Workbench").

Using Java, you can create named queries for all query types and store them in a descriptor's DescriptorQueryManager (see "Using Java").

Table 28-4 summarizes which descriptors support named query configuration.

Table 28-8 Descriptor Support for Named Queries

Descriptor	Using TopLink Workbench	Using Java
Relational DescriptorsFoot 1
Object-Relational Descriptors
EIS DescriptorFoot 2
XML Descriptors

^Footnote 1 Relational class descriptors only (see "Relational Class Descriptors").

^Footnote 2 EIS root descriptors only (see "EIS Root Descriptors").

For 2.0 CMP projects, the ejb-jar.xml file stores query lists. You can define the queries in the file, and then read them into TopLink Workbench, or define them on the Queries tab and write them to the file. See "Working With the ejb-jar.xml File" for more information.

After you create a named query, you can execute it by name and class on the TopLink session (see "Using Named Queries").

For more information about named queries, see "Named Queries".

Using TopLink Workbench

To create a named query, use this procedure

1. In the Navigator, select a descriptor. Its properties appear in the Editor.

2. Click the Queries tab in the Editor. The Queries tab appear with three additional tabs.

3. Click the Named Queries tab in the Queries tab. The Named Queries tab appears.

   Figure 28-6 Queries – Named Queries Tab

   
   Description of "Figure 28-6 Queries – Named Queries Tab"
   
   

Use the following information to complete each field on this tab:

Field	Description
Queries	Lists the existing queries for this descriptor. To create a new query, click Add (see "Adding Named Queries"). To delete an existing query, select the query and click Delete. TopLink Workbench prompts for confirmation. To rename an existing query, select the query and click Rename. The Rename dialog box appears. Type a new name for the query and click OK.
Query Variety	Displays the variety of the currently selected query (see "Adding Named Queries").
Quick View	Lists the parameters and joined attributes defined for the query. Clicking on a heading in the Quick View area selects the corresponding subtab. You can also remove parameters or attributes from the Quick View area by selecting the item and clicking Remove.

The Named Queries tab includes the following subtabs:

• General–See "Configuring Named Query Type and Parameters".

• Selection Criteria–See "Configuring Named Query Selection Criteria".

• Order–This tab appears for ReadAllQuery queries only. See "Configuring Read All Query Order".

• Optimization–See "Configuring Named Query Optimization".

• Attributes–This tab appears for ReportQuery queries only. See "Configuring Named Query Attributes".

• Group/Order–This tab appears for ReportQuery queries only. "Configuring Named Query Group/Order Options".

• Calls–This tab appears for EIS root descriptors only (for ReadAllQuery and ReadObjectQuery queries). See "Creating an EIS Interaction for a Named Query".

• Options–See "Configuring Named Query Options".

Adding Named Queries

Use this dialog box to create a new named query.

Figure 28-7 Add Named Query Dialog Box

Description of "Figure 28-7 Add Named Query Dialog Box"

Use the following information to complete the dialog box and create the named query:

Field	Description
Variety	For EJB descriptors only, select the variety of query:
TopLink Named Query	Select to create a general purpose TopLink query of the type given by the Type area. You can execute this query by name on the TopLink session passing in the class and arguments (see "Using Named Queries").
EJB Finder	Select to create a TopLink query of the type given by the Type area for use as the implementation of the EJB finder method of the name you specify. The TopLink runtime executes this query when you call the EJB finder method of the given name.
TopLink Reserved Finder	Select to create a TopLink query of the type given by the Type area for use as the implementation of the TopLink reserved finder method name you select. The TopLink runtime executes this query when you call the EJB finder method of the given name. For more information, see "Predefined Finders".
EJB Select	Select to create a TopLink query of the type given by the Type area for use as the implementation of the EJB life cycle method ejbSelect. The TopLink runtime executes this query whenever the ejbSelect method is called.
Type	Select the type of query: ReadObject (ReadObjectQuery) ReadAll (ReadAllQuery) ReportFoot 1  (ReportQuery) Note: If Variety is set to TopLink Reserved Finder, you cannot select a query Type .
Name	The name of this query. If Variety is set to TopLink Named Query, you can specify any name. If Variety is set to EJB Finder, the name must be prefixed by find. If Variety is set to TopLink Reserved Finder, select from the list of available names that TopLink reserves. For more information, see "Predefined Finders". If Variety is set to EJB Select, the name must be ejbSelect.

^Footnote 1 Relational descriptors only.

Enter the necessary information and click OK. TopLink Workbench adds the query to the list of queries in the Named Query tab.

Configuring Named Query Type and Parameters

Use this tab to select the query type and add or remove parameters.

Figure 28-8 Named Queries, General Tab

Description of "Figure 28-8 Named Queries, General Tab"

Use the following information to complete each field on this tab:

Field	Description
Type	Select the type of query from the list. You can create any of the following query types: ReadAllQuery ReadObjectQuery ReportQueryFoot 1  To create other types of query, you must use Java (see "Using Java"). When you change the type of an existing query, TopLink Workbench preserves any configuration that is common between the old and new type and warns you if changing the type will result in the loss of configuration that is not shared by the new type.
Parameters	For queries that take parameters, specify the parameters: To add a new parameter, click Add. The Add Query Parameter dialog box appears. Click Browse to select the type, specify a name, and click OK. To delete an existing parameter, select the parameter and click Remove. TopLink Workbench prompts for confirmation. To modify an existing parameter, select the parameter and click Edit. The Edit Query Parameter dialog box appears. Modify the name and type of the parameter and click OK. To change the order of the parameters, select an existing parameter and click Up or Down.
Type	Select the class of the parameter's type.
Name	Enter the name of the parameter.

^Footnote 1 Relational descriptors only.

Configuring Named Query Selection Criteria

Use this tab to specify the format of the named query and enter the query string.

Figure 28-9 Named Queries, Selection Criteria Tab

Description of "Figure 28-9 Named Queries, Selection Criteria Tab"

Use the following information to complete each field on this tab:

Field	Description
Type	Specify if query uses a TopLink Expression, SQL, or EJB QL.
Expression or Query String	If the Type is SQL or EJB QL, enter the query string (either SQL or EJB QL). TopLink Workbench does not validate the query string. See a note that follows this table for information on query syntax.

Note: Use a combination of an escape character and a double-quote ( \" ) instead of just a double-quote ( " ) when defining your query using SQL or EJB QL. For example: SELECT OBJECT(employee) FROM Employee employee WHERE employee.name = \"Bob\" If you fail to do so, the generated Java code would look as follows: query.setEJBQLString("SELECT OBJECT(employee) FROM Employee employee WHERE employee.name = "Bob""); The preceding code produces an error at compile time. If you define your query using the correct syntax, the generated Java code will contain no errors and be similar to the following: query.setEJBQLString("SELECT OBJECT(employee) FROM Employee employee WHERE employee.name = \"Bob\"");

Configuring Read All Query Order

Use this tab to specify how the results of a read all query should be ordered by attribute name.

Figure 28-10 Order Tab

Description of "Figure 28-10 Order Tab"

Select one of the following actions:

• To add a new attribute by which to order query results, click Add. The Add Ordering Attribute dialog box appears. Select the mapped attribute to order by, specify ascending or descending order, and then click OK.

• To change the order of the order attributes, select an existing attribute and click Up or Down.

• To modify an existing order attribute's ordering options, select an existing attribute and click Edit.

• To remove an order attribute, select an existing attribute and click Remove.

Configuring Named Query Optimization

You can optimize a named query by configuring batch (ReadAllQuery only) or joining (ReadAllQuery and ReadObjectyQuery) attributes.

For more information on using batch reading, see "Query Optimization", "Reading Case 2: Batch Reading Objects", and "Using Batch Reading".

For more information on joining, see "Join Reading and Object-Level Read Queries" and "Using Join Reading".

Use this tab to specify batch reading and joining attributes.

Figure 28-11 Optimization Tab

Description of "Figure 28-11 Optimization Tab"

Select one of the following actions for Batch Read Attributes (ReadAllQuery only):

• To add a new batch read attribute, click Add. The Add Batch Read Attribute dialog box appears. Select the mapped attribute and click OK.

• To change the order of the batch read attributes, select an existing attribute and click Up or Down.

• To modify an existing batch read attribute's options, select an existing attribute and click Edit.

• To remove a batch read attribute, select an existing attribute and click Remove.

Select one of the following actions for Joined Attributes (ReadAllQuery and ReadObjectQuery):

• To add a new joined attribute, click Add. The Add Joined Attribute dialog box appears.

  Figure 28-12 Add Joined Attribute Dialog Box

  
  Description of "Figure 28-12 Add Joined Attribute Dialog Box"
  
  

  Select the mapped attribute. Optionally, enable or disable Allows Null or, for a Collection attribute, Allows None. Click OK.

• To change the order of the joined attributes, select an existing attribute and click Up or Down.

• To modify an existing joined attribute's options, select an existing attribute and click Edit.

• To remove a joined attribute, select an existing attribute and click Remove.

Configuring Named Query Attributes

For ReportQuery queries only, you can configure report query functions to apply to one or more attributes.

For more information about report queries, see "Report Query".

Use this tab to configure report query attributes.

Figure 28-13 Queries – Named Queries – Attributes Tab

Description of "Figure 28-13 Queries – Named Queries – Attributes Tab"

Select one of the following actions for Attributes (ReportQuery only):

• To add a new report query attribute, click Add. The Add Joined Attribute dialog box appears. Continue with "Adding Report Query Attributes".

• To change the order of the report query attribute attributes, select an existing attribute and click Up or Down.

• To modify an existing report query attribute's options, select an existing attribute and click Edit.

• To remove a report query attribute, select an existing attribute and click Remove.

Note: You can only choose attributes that are configured with a direct mapping (converters included) or a user-defined query key.

Adding Report Query Attributes

Use this dialog box to add a report query attribute.

Figure 28-14 Add Report Query Attribute Dialog Box

Description of "Figure 28-14 Add Report Query Attribute Dialog Box"

Select the attribute you want in this report query and use the following table to complete the dialog box and add the report query attribute:

Option	'Description
Allows None or Allows Null	Use the Allows Null and Allows None options to define an expression with an outer join. Check the Allows Null option to use the ExpressionBuilder method getAllowingNull. Check the Allows None option for Collection attributes to use the ExpressionBuilder method anyOfAllowingNone. For more information, see "Using TopLink Expression API For Joins".
Function	Select from the list of report query functions that TopLink provides. This function will be applied to the specified attribute. You must select an attribute for all functions, except Count. Alternatively, you can enter the name of a custom function that you implement in your database. For more information, see Expression method getFunction in the API reference.
Name	The name associated with the calculated value. By default, the name is <AttributeName><FunctionName>.

Enter the necessary information and click OK. TopLink Workbench adds the report query attribute to the list of attributes in the Attribute tab.

Configuring Named Query Group/Order Options

For ReportQuery queries only, you can configure grouping and ordering attributes.

For more information about report queries, see "Report Query".

Use this tab to specify grouping and ordering attributes.

Figure 28-15 Group/Order Tab

Description of "Figure 28-15 Group/Order Tab"

Select one of the following actions for Grouping Attributes (ReportQuery only):

• To add a new grouping attribute, click Add. The Add Grouping Attribute dialog appears. Select the desired mapped attribute and click OK.

• To change the order of the grouping attributes, select an existing attribute and click Up or Down.

• To modify an existing grouping attribute's options, select an existing attribute and click Edit.

• To remove a grouping attribute, select an existing attribute and click Remove.

Select one of the four following actions for Ordering Attributes (ReportQuery only):

• To add a new ordering attribute, click Add. The Add Ordering Attribute dialog box appears. Continue with "Adding Ordering Attributes".

• To change the order of the ordering attributes, select an existing attribute and click Up or Down.

• To modify an existing ordering attribute's options, select an existing attribute and click Edit.

• To remove an ordering attribute, select an existing attribute and click Remove.

Adding Ordering Attributes

Use this dialog box to add a report query ordering attribute.

Figure 28-16 Add Report Query Grouping Attribute Dialog Box

Description of "Figure 28-16 Add Report Query Grouping Attribute Dialog Box"

Use the following information to complete the fields on the dialog box and add an ordering attribute:

Option	'Description
Selected Attribute	Select this option to view a list of the report query attributes you added (see "Configuring Named Query Attributes"). Select an attribute and choose its ordering option in the Order field.
New Attribute	Select this option to view a list of all class attributes. Select an attribute and choose its ordering option in the Order field.
Order	Select ascending or descending.

Enter the necessary information and click OK. TopLink Workbench adds the report query attribute to the list of attributes in the Attribute tab.

Creating an EIS Interaction for a Named Query

For an EIS root descriptor, you can define EIS interactions to invoke methods on an EIS.

You can use TopLink to define an interaction as a named query for read object and read all object queries, as described here. These queries are not called for basic persistence operations ("Configuring Custom EIS Interactions for Basic Persistence Operations"); you can call these additional queries by name in your application for special purposes.

Call Tab

Use this tab to define an interaction as a named query for read object and read all object queries.

Figure 28-17 Call Tab

Description of "Figure 28-17 Call Tab"

Use the following information to complete each field on the tab:

Field	Description
Interaction Type	Using TopLink Workbench, you can only use XML Interactions. You cannot change this field.
Function Name	Specify the name of the EIS function that this call type (Read Object or Read All) invokes on the EIS.
Input Record Name	Specify the name passed to the J2C adapter when creating the input record.
Input Root Element	Specify the root element name to use for the input DOM.
Input Arguments	Specify the query argument name to map to the interaction field or XPath nodes in the argument record. For example, if you are using XML records, use this option to map input argument name to the XPath name/first-name.
Output Arguments	Specify the result record field or XPath nodes to map the correct nodes in the record used by the descriptor's mappings. For example, if you are using XML records, use this option to map the output fname to name/first-name. Output arguments are not required if the interaction returns an XML result that matches the descriptor's mappings.
Input Result Path	Use this option if the EIS interaction expects the interaction arguments to be nested in the XML record. For example, specify arguments, if the arguments were to be nested under the root element exec-find-order, then under an arguments element.
Output Result Path	Use this option if the EIS interaction result record contains the XML data that maps to the objects in a nested structure. For example, specify order, if the results were return under a root element results, then under an order element.
Properties	Specify any properties required by your EIS platform. For example, property name operation (from AQPlatform.QUEUE_OPERATION) and property value enqueue (from AQPlatform.ENQUEUE).

Configuring Named Query Options

Use this tab to configure additional options for the query.

Figure 28-18 Named Queries, Options Tab

Description of "Figure 28-18 Named Queries, Options Tab"

Use the following information to complete each field on the tab:

Field	Description
Refresh Identity Map ResultsFootref 2	Refreshes the attributes of the object(s) resulting from the query. If cascading is used, the private parts of the objects will also be refreshed.
Cache StatementFoot 1	Caches the prepared statements. This requires full parameter binding as well (see Bind Parameters).
Bind ParametersFootref 1	Binds all of the query's parameters.
Cache UsageFoot 2	Selects how TopLink should use the session cache when a query is executed: Use descriptor settings Do not check cache Check cache by exact primary key Check cache by primary key Check cache then database Check cache only Conform results in unit of work For more information, see the following: "Configuring Cache Usage for In-Memory Queries" "Configuring Unit of Work Conforming at the Descriptor Level"
In Memory Query IndirectionFootref 2	Selects how TopLink should handle indirection when an in-memory or conforming query is executed: Throw indirection exception–if this object uses indirection and indirection has not been triggered, TopLink will throw an exception. Trigger indirection–if this object uses indirection and indirection has not been triggered, TopLink will trigger indirection. Ignore exception return conformed–returns conforming if an untriggered value holder is encountered. That is, you expect results from the database to conform, and an untriggered value holder is taken to mean that the underlying attribute has not changed. Ignore exception return not conformed–returns not conforming if an untriggered value holder is encountered. For more information, see the following: "Handling Exceptions Resulting From In-Memory Queries" "Indirection".
Return ChoiceFoot 3	Selects how TopLink should handle ReportQuery results: Result collection–return ReportQuery results as a Collection of ReportQueryResult objects. Single result–return only the first ReportQueryResult object (not wrapped in a Collection or Map). Use this option if you know that the ReportQuery returns only one row. Single value–return only a single value. Use this option if you know that the ReportQuery returns only one row that contains only one attribute. Single attribute–return only a single Collection of values. If the query returns multiple rows, but each row only has a single attribute, this option will return a Collection of values, instead of a Collection of ReportQueryResults. For more information, see "Report Query Results".
Retrieve Primary KeysFootref 3	Selects whether or not TopLink retrieves the primary key values within each result. You can use the primary keys to retrieve the real objects. None–do not retrieve primary keys All–retrieve primary keys for each object read; First–return only the first primary key value (in the case of a composite primary key). This can be used if you just want to know if something exists or not, but do not really care about the value.

^Footnote 1 For more information, see "Parameterized SQL (Binding) and Prepared Statement Caching".

^Footnote 2 For ReadObjectQuery and ReadAllQuery queries only.

^Footnote 3 For ReportQuery queries only.

Click Advanced to configure additional options. See "Configuring Named Query Advanced Options".

Configuring Named Query Advanced Options

To configure additional advanced query options, use this procedure.

1. From the Named Queries – Options tab, click Advanced. The Advanced Query Options dialog box appears.

   Figure 28-19 Advanced Query Options Dialog Box

   
   Description of "Figure 28-19 Advanced Query Options Dialog Box"
   
   

Use the following information to enter data in each field and click OK:

Field	Description
Maintain Cache	Specify whether to use the cache for the query or to build objects directly from the database result. You should only use this option if you are executing a partial object query (see "Partial Object Queries"), whose results cannot be cached. For more information, see "Disabling the Identity Map Cache Update During a Read Query".
Use Wrapper Policy	Specify whether or not the named query will use the wrapper policy configured for this descriptor. For more information, see "Configuring Wrapper Policy".
Prepare SQL Once	Specify the setShouldPrepare() for the named query. By default, TopLink optimizes queries to generate their SQL only once. You may need to disable this option for certain types of queries that require dynamic SQL based on their arguments, such as: Expressions that use equal where the argument value could be null. This may cause problems on databases that require IS NULL, instead of = NULL. Expressions that use in and use parameter binding. This will cause problems as the in values must be bound individually.
Cache Query Results	Specify the cacheQueryResults method for the query. The query will only access the database the first time it is executed. Subsequent execution will return exactly the original result. For more information, see "Caching Results in a ReadQuery".
Refresh Remote Identity Map Results	Specify the refreshRemoteIdentityMapResult method for the query. TopLink can refresh the attributes of the object(s) resulting from the query. With cascading, TopLink will also refresh the private parts of the object(s).
Exclusive Connection	Specify whether or not the named query will use an exclusive connection. You can also configure exclusive connection acquisition at the session level (see "Configuring Connection Policy").
Pessimistic Locking	Specify the specific pessimistic locking policy for the query or use the locking policy from the descriptor.
Distinct State	Specify if TopLink prints the DISTINCT clause, if a distinct has been set.
Query Timeout	Specify if the query will time out (or abort) after a specified number of seconds.
Maximum Rows	Specify if the query will limit the results to a specified number of rows. Use this to option for queries that could return an excessive number of objects.

Using Java

To configure named queries in Java, use a descriptor amendment method (see "Configuring Amendment Methods"). Example 28-1 illustrates an amendment method that creates a named query and adds it to the DescriptorQueryManager.

Example 28-1 Creating a Named Query with an Amendment Method

public class EmployeeAmmendmentMethodClass 
{
....
    // Create named query
    public static void createEmployeeQuery(ClassDescriptor descriptor)
    {
        ExpressionBuilder emp = new ExpressionBuilder();
        Expression firstNameExpression =             emp.get("firstName").equal(emp.getParameter("firstName"));
        ReadObjectQuery query = new ReadObjectQuery();
        query.setReferenceClass(Employee.class);
        query.setSelectionCriteria(firstNameExpression);
        query.addArgument("firstName");

        descriptor.getQueryManager().addQuery(
            "employeeReadByFirstName",
            query
        );
    }
}