Designing Queries using the Query Manager in Studio

Queries can be defined and stored for the finder methods of Enterprise JavaBeans (EJB) in Studio. Types of queries defined are:

Enterprise JavaBeans Query Language (EJB QL): Defines queries for CMP entity beans version 2.0

Structured Query Language (SQL): Define queries for CMP entity beans version 1.1. They can be generated using the Query Wizard in Studio

SQL queries are designed specific to a data resource and driver. Multiple queries can be defined for a single data resource and driver. Using any of the following methods opens Input Query dialog or the Query Wizard, where the queries can be designed:

Bean Wizard: Select Methods and Finder Methods panels can be used to define EJB QL Queries in EJB 2.0 modules. Use Finder Methods panel to define SQL Queries in EJB 1.1 and Java modules

Bean Properties: Click Finder or Select method node in Bean Properties panel and select the specific Query file

Deploy Tool: If the Queries are not defined for finder or select methods, select the EJB module or JAR in Deploy Tool and choose View > Next Task

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

Connecting to a database

Table 1:
Attribute	Description
JNDI Name	Displays all the data resources defined on the Server. Select a data resource from the dropdown
Driver	Displays the driver on which the query for the selected data resource has been defined. This field is non-editable
URL	Displays the URL on selecting the data resource. This field is non-editable
User Name	Enter the user name. This field is optional
Password	Enter the password for the username

Based on the information provided here, the wizard attempts to connect to the database. When connection is established, the next panel appears which allows you to specify the tables to be queried. If connect attempt fails, the Status box displays a message. In such case, verify the data resource name and driver settings.

Specifying tables to be queried

The Query name is used to map finder methods when defining or deploying entity beans. To add the tables to be queried from Available tables list, click Right arrow button. To delete an existing table, select it in Selected Tables list and click Left arrow button. Click Next.

Specifying Search Criteria

The third panel in Query Wizard, Specify Search Criteria is used to create a search criterion for a Query.

Table 2:
Attribute	Sub option	Description
Field 1		Select database field name from the dropdown
Field 2		Choose from available options as the second argument under Field 2
	Field	Select a database field from the dropdown
	Literal	Specify a literal expression in the Query
	Parameter	Specify a user-defined value in the Query
Operator		Choose an operator from dropdown lists under Operator. Sub options are described below
	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

This panel is the first to come up when Query Wizard is brought up using Bean Wizard, as the table and resource to be used is already defined. Click Prev button to modify. Add the search criterion defined to the WHERE Clause table by clicking Add. Use and/or toggle text in the WHERE Clause table to join all clauses. To remove a search criterion that has been added, click Delete. If the Query contains a parameter to be given at runtime, Query results tab will not display the results. Specifying output fields and sorting Query results

Table 3:
Attribute	Sub options	Description
Select Mode		Select from the two modes under this field that are described below
	All	If database rows are returned several times by the search criteria then all occurrences should be included in the output
	Distinct	Only one database row is included in the output
Sort Result By		Select a field name from the dropdown, and click Forward arrow button to add it to Field Name table. The ascending/descending toggle sets sort order

Viewing the Generated Query

This panel displays the generated SQL Query. Click Finish to generate the query.

The Query is stored in the Bean Properties file. Deploying the module or JAR containing the bean, generates a file called queries.props, and stores it in the specific Module or JAR.

Using Query Viewer

Query Viewer can be brought up from inside Deploy Tool. Open the required archive in Deploy Tool and select a bean node. In the right panel, select Finder Queries tab, and click Import. The Query Viewer dialog allows to:

View Queries

Import Queries from a remote queries.props to local props file. This facility is used when transferring Queries prepared during development time to a deployment site or while relocating EARs

Delete Queries

Pick the Query name from Designed Queries table in Query Viewer dialog. The query is displayed in Generated SQL table. To remove a Query, select it and click Delete. Names of Queries defined are listed in Select Query dropdown in the Define Finder Methods dialog that appears while defining an EJB using Bean Wizard.

Importing Queries

Browse for the props file that contains Queries you want to import. Select the props file to view the Queries. Select queries to import and click OK. An imported Query is highlighted in blue in Import Query dialog along with the generated SQL statement. Duplicate Queries cannot be imported into Studio.

Designing EJB QL Queries

EJB QL queries can be defined only for CMP 2.0 beans, and Input Query panel comes up only when the selected module is an EJB 2.0 module.