Specifying Queries

Use the Queries tab to specify TopLink Expressions, EJB QL queries, and SQL queries to access the database. EJB QL is a declarative language that presents queries from an object-model perspective. Refer to the EJB specification for detailed information.

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 the TopLink Mapping editor, or define them on the Queries tab and write them to the file.

To create queries:

  1. In the Structure window, select a descriptor.
  2. Click the Named Queries tab in the Mapping editor.
    The Queries tab contains the following additional tabs:
  3. Select the appropriate tab:

Custom SQL Queries

Use this procedure to create custom SQL queries for the TopLink descriptor.

To create custom SQL queries:

  1. In the Application Navigator, select a descriptor.
  2. Click the Queries tab in the Editor pane.
  3. Click the Custom SQL tab.

    Figure 4-2 Queries Custom SQL Tab

    This figure shows the Custom SQL tab on the Queries tab. This tab also includes the following tabs: - Insert - Update - Delete - Read Object - Read All

  4. Click appropriate SQL function tab on the Custom SQL Queries tab to type your own query object or SQL string to control these actions for a descriptor.
    Note: The TopLink Mapping editor does not validate the SQL code that you enter. The code is defined by the specific database type.

Example 4-1 Custom Queries

To control the five custom query tabs, you can include your own query object or SQL string for a particular descriptor. The SQL string for each database is defined by the type of database.

For example, the stored procedure to read an object may use the following string:

Read_Employee(EMP_ID=>4653)

The query manager requires the following string to read the object:

Read_Employee(EMP_ID=>#EMP_ID)

Named Queries

Use named queries to specify SQL, EJB QL, or TopLink Expression queries to access the database.

To create a named query:

  1. In the Application Navigator, select a descriptor.
  2. Click the Queries tab in the Editor pane.
  3. Click the Named Queries tab in the Queries tab.

    Figure 4-3 Queries Tab

    This figure shows the Named Queries - General tab on the Queries tab.

  4. Click Add to create a new query. The Add Named Query dialog appears.
    To delete an existing query, select the query and click Remove. To rename an existing query, select the query and click Rename.
  5. Select the query type (for EJB descriptors), enter the query name, and click OK.
  6. The Named Queries tab contains the following additional tabs:
  7. Click on the Named Queries - General tab to specify the query type and parameters.

    Figure 4-4 Queries General Tab

    This figure shows the General tab on the Named Queries tab.

  8. Select the query type and Add or Remove parameters for the query.
  9. Click the Named Queries - Format tab to specify the named query and its format.

    Figure 4-5 Queries Format Tab

    This figure shows the Named Queries - Format tab on the Queries tab. For Expressions, click Add/Modify to edit the expression with the TopLink Expression Builder.

  10. Click on the Named Queries - Options tab to specify additional options for the named query.
    Note: These options are not available for findOneByQuery and findManyByQuery.

    Figure 4-6 Queries Options Tab

    This figure shows the Named Queries - Options tab on the Queries tab.

  11. Click Advanced to specify additional named query options.

    Figure 4-7 Advanced Query Options

    This figure shows the Advanced Query Options dialog box.

  12. Use the Advanced Query Options dialog to specify additional query options.
  13. Click OK.

Related Topics

About TopLink Descriptors

Building Expressions
Working with Descriptors
Working with Descriptor Properties
Mapping Descriptors

 

Copyright © 1997, 2004, Oracle. All rights reserved.