Designing Queries

Queries can be defined and stored for the finder methods of the Enterprise JavaBeans in Studio. The two types of queries that can be defined are:

• EJB QL The Enterprise JavaBeans query language, EJB QL, is used to define queries for CMP entity beans (version 2.0). These queries can be defined by entering the query in the Input Query dialog.
• SQL These queries are used to define queries for CMP entity beans (version 1.1). These queries can be generated using the Query Wizard.

Queries are designed specific to a data resource and driver. Multiple queries can be defined for a single data resource and driver.

Queries can be designed in three different ways:

• Bean Wizard The Select Methods panel, and the Finder Methods panel can be used to define EJB QL queries (in the EJB 2.0 Modules). The Finder Methods panel can be used to define SQL queries (in the EJB 1.1 and Java Modules)
• Bean Properties Click on the Finder/Select Methods node in the Bean Properties panel, and click on the specific Query file.
• Deploy Tool Click on the specific Bean node in the Deploy Tool panel, select the Finder Queries tab, and click on the New button.

Using any of the above methods brings up the Input Query dialog, or the Query Wizard, where the queries can be designed.

Designing SQL Queries

SQL queries can be defined only for CMP 1.1 beans, and the Query Wizard comes up only when the Module selected is either an EJB 1.1 Module or Java Module.

Connecting to a Database

Data Resource Name The field displays all the data resources connected to the Server. Choose one.

Driver Selecting the resource automatically displays the driver on which the query has been defined. This field is non-editable.

URL Selecting the resource also displays the URL. This field is also non-editable.

User Name Enter the user name if necessary.

Password Enter the password if necessary.

Based on the information provided in this panel, the Wizard attempts to connect to the database. On connecting, a Specify tables to be queried panel appears. In case the connect attempt fails, the Status box on the left displays the message connection failed. In such a case, verify the data resource name and driver settings.

Specifying Tables to be Queried

The query name is used to map the finder methods when defining or deploying entity beans. To add the tables to be queried from the Available tables list, click on the right button arrow. To delete the tables that have been added to the Selected tables list, click on the left arrow button.

Specifying Search Criteria

The third panel that comes up in the Query Wizard is Specify search criteria, which is used to create a search criterion for the query.

Note: This is the first panel that comes up when the Query Wizard is brought up using the Bean Wizard, as the table and resource to be used is already selected. Click on the Prev button to make any modifications.

Field 1 Select the database field name from the Field 1 dropdown list.

Field 2 Choose from one of the following options as the second argument under Field 2.

• Field Select a database field from the dropdown list
• Literal Specify a literal expression in the query
• Parameter Specify an user-defined value in the query

Operator Choose the operator from one of the following dropdown lists under Operator:

• Comparison includes all the comparative operators
• Range includes BETWEEN and NOT BETWEEN
• Pattern includes LIKE and NOT LIKE
• List includes IN and NOT IN
• Value includes IS NULL and IS NOT NULL

Add the search criterion defined to the WHERE Clause table using the Add button. Use and/or toggle text in the WHERE Clause table to join all the clauses. To delete a search criterion that has been added, use the Delete button.

If the query contains a parameter to be given at runtime, the query results tab will not display the results.

Specifying Output Fields

and Sort Query Results

Select Mode Select form the two modes under this field:

• All If the database rows are returned several times by various search criteria, then as many occurrences should be included in the output.
• Distinct Only one database row is included in the output.

Sort Result By Click on a field name from the list box, and click on the forward arrow icon to add it to the Field Name table. The ascending/descending toggle sets the sort order.

Viewing the Generated Query

This panel displays the generated SQL Query. Click on Finish to generate the query. The generated query is stored in the bean's Bean Properties file. Deploying the Module or the JAR containing the bean, generates a file called queries.props, and stores it in the specific Module or JAR.

Modifying SQL Query

Re-trace by clicking the Prev (previous) button, and make necessary modifications in the appropriate panels to modify the query.

Using Query Viewer

The Query Viewer can be brought up from inside Deploy Tool. To do this, open the required archive in the Deploy Tool and click on the specific bean node. In the panel that is displayed on the right side, select the Finder Queries tab, and click on Import.

The Query Viewer dialog box that comes up allows the following operations to be performed:

• View queries
• Import queries from a remote queries.props into the local props file. This facility is used when transferring queries, prepared during development time to a deployment site or while relocating the EARs.
• Delete queries

Pick the query name from the Designed Queries table in the Query Viewer dialog box. The query is displayed in the Generated SQL table. The queries are deleted using Delete button.

Names of queries defined are listed in the Select Query dropdown list in the Define Finder Methods dialog that appears while defining an EJB (Bean Wizard). It is a good practice to first define a set of queries likely to be used in the finder methods.

Importing Queries

Clicking on Import in the query Viewer panel brings up the Import Queries dialog box.

Browse for the props file that contains the query(ies) you want to import. Selecting the props file displays the queries. Select the query(ies) that you want to import and click OK.

An imported query is shown in blue for the first time in the Import Query dialog box, along with the generated SQL statement. Duplicate queries cannot be imported into the Studio.

Designing EJB QL Queries

EJB QL queries can be defined only for CMP 2.0 beans, and the Input Query panel comes up only when the Module selected is an EJB 2.0 Module. Enter the EJB QL query in the Input Query panel and click OK.

Pramati Technologies  © Copyright