Query Methods and EJB QL

Workshop for WebLogic supports annotations through which you can more easily add find and select methods to your entity beans. You may already have used these annotations through the EJBGen tool provided with WebLogic Server. This topic provides an introduction to find and select methods and EJB QL. For more complete documentation on the annotations, see the EJBGen Reference.

You define find and select methods for CMP (2.0) entity beans using EJB QL. This query language, similar to SQL used in relational databases, is used to select one or more entity EJBs or entity bean fields. The WebLogic platform fully supports EJB QL 2.0 and offers a number of additional methods that can be used in conjunction with EJB QL.

Without the EJBGen annotations, you would typically specify these query methods through a combination of method declarations and the bean's deployment descriptor. Through values you specify in the annotations, Workshop for WebLogic updates the descriptor for you and generates the needed method declarations in bean interfaces.

Note. The EJB QL is used for all query methods, with the exception of findByPrimaryKey, which is automatically generated by the EJB container.

The topics in this section are:

Specifying Find Methods
Specifying Select Methods
Standard EJB QL Operators

Specifying Find Methods

A find method is invoked by other EJBs or client applications on a CMP entity bean's local or remote home interface, and returns local or remote references to one or more instances of this entity bean that match the query. A find method can return a reference to a single entity instance, such as findByPrimaryKey, or to multiple entity instances returned as a java.util.Collection. Find methods must start with the prefix find.

In Workshop for WebLogic, you specify a find method by using the @Finder annotation on the class declaration. You collect multiple find methods within an @Finders annotation. Through the @Finder annotation's attributes you indicate query method features. Basic features include the query language to use (EJB QL or WebLogic QL), which interfaces the method should be declared in (local or remote home), and the method's Java signature.

The following example illustrates two find methods declared within an ItemsBean_F entity bean. Both use EJB QL and both will appear within the bean's local home interface.

@Finders( {
        @Finder(ejbQl = "SELECT OBJECT(o) from ItemsBean_F as o " +
                "WHERE o.itemname = ?1", 
                generateOn = Finder.GenerateOn.LOCAL, 
                signature = "Collection findByItemName(java.lang.String itemname)"),
        @Finder(ejbQl = "SELECT OBJECT(i) from ManufacturerBean_F as o, " +
                "IN(o.items) AS i WHERE o.usManufacturer = 1", 
                generateOn = Finder.GenerateOn.LOCAL, 
                signature = "Collection findByUSManufacturer()")
})

Note: For more information on the @Finder annotation, see weblogic.ejbgen.Finder in the EJBGen Reference.

In the IDE, you can view and set annotation values using the Annotations view. To do this, follow these basic steps:

With your bean source code visible, place your cursor in the bean class declaration.
`
In the Annotations view, scroll to where the Finders annotation is listed, right-click it, then click Add Annotation.

Note that the @Finders annotation is written to your source code.
With your cursor in the class declaration or the new @Finders annotation, in Annotations view right-click Finders, then click Add Member weblogic.ejbgen.Finder.

The @Finder annotation is written to your source code. Note in Annotations view that all Finder attribute values are set to their default UNSPECIFIED values.
In Annotations view, set Finder annotation values in the value column.

Find Method Examples

The following list shows common uses of EJB QL queries with find methods:

Selecting all. The method Collection findAll(), defined in the home interface of the ItemsBean_F, returns all records in the database table:
```
@Finder(generateOn = Finder.GenerateOn.LOCAL, 
    ejbQl = "SELECT OBJECT(o) from ItemsBean_F as o", 
    signature = "Collection findAll()")
```
Notice that an EJB QL query always uses the EJB's abstract schema name ItemsBean_F to reference it. This name is typically the same as the EJB's descriptive name. For more details on these names, see the @Entity Annotation. When the @Entity annotation's abstractSchemaName attribute isn't specified, the its ejbName attribute value will be used.
Usiing input parameters. The method Collection findByItemName(java.lang.String itemname), defined in the home interface of the ItemsBean_F, returns all references to bean instances matching the item name:
```
@Finder(ejbQl = "SELECT OBJECT(o) from ItemsBean_F as o " +
    "WHERE o.itemname = ?1", 
    generateOn = Finder.GenerateOn.LOCAL, 
    signature = "Collection findByItemName(java.lang.String itemname)")
```
Notice that the method argument itemname is matched to input parameter ?1.
Specifying literal values. The method Collection findUSManufacturers(), defined in the home interface of the ManufacturerBean_F, returns all references to ManufacturerBean_F instances who are US manufacturers:
```
@Finder(ejbQl = "SELECT OBJECT(o) from ManufacturerBean_F as o WHERE o.usManufacturer = 1", 
    generateOn = Finder.GenerateOn.LOCAL, 
    signature = "Collection findUSManufacturer()")
```
Making relationship queries. A ManufacturerBean_F and an ItemsBean_F are defined to have an entity relation, such that each item has a manufacturer, and a manufacturer can produce multiple items. For each item, the ItemsBean_F's CMR field manufacturer stores a unique index to a manufacturer. The method Collection findAllManufacturers(), defined in the home interface of the ManufacturerBean_F, queries the ItemsBean_F to return the different manufacturers for all the items. It is possible that multiple items are created by the same manufacturer, yielding multiple references to the same manufacturer in the returned results:
```
@Finder(ejbQl = "SELECT OBJECT(m) from ItemsBean_F as o, IN(o.manufacturer) AS m", 
    generateOn = Finder.GenerateOn.LOCAL, 
    signature = "Collection findAllManufacturers()")
```
Notice that the keyword IN is used to return object references via a CMR field.
Getting unique records. A ManufacturerBean_F and an ItemsBean_F are defined to have an entity relation, such that each item has a manufacturer, and a manufacturer can produce multiple items. For each item, the ItemsBean_F's CMR field manufacturer stores a unique index to a manufacturer. The method Collection findDistinctManufacturer(), defined in the home interface of the ManufacturerBean_F, queries the ItemsBean_F to return the different manufacturers for all the items. It is possible that multiple items are created by the same manufacturer, yielding multiple references to the same manufacturer, but the keyword DISTINCT is used to not return duplicates:
```
@Finder(ejbQl = "SELECT DISTINCT OBJECT(m) from ItemsBean_F as o, IN(o.manufacturer) AS m", 
    generateOn = Finder.GenerateOn.LOCAL, 
    signature = "Collection findDistinctManufacturer()")
```

Select Methods

A select method is defined using EJB QL and it can either return (local or remote) references to entity beans or values of an individual CMP field. A select method is not defined in the EJB's interfaces. In other words, it is a private method that can only be used internally by a CMP entity bean class. When returning object references, a select method can return a reference to a single entity instance, or to multiple entity instances which are returned as a java.util.Collection or java.util.Set. Select methods must start with the prefix ejbSelect.

In Workshop for WebLogic, you specify a select method by adding the method's declaration to your bean class, then annotating method with the @Select annotation. As with the @Finder annotation, you use the @Select annotation's attributes to indicate query method features, such as the language to use.

The following example shows a select method declared within an ItemsBean_S entity bean. It uses EJB QL.

@Select(ejbQl = "SELECT OBJECT(o) from ItemsBean_S as o")
public abstract java.util.Collection ejbSelectAll() throws FinderException;

Note: For more information on the @Select annotation, see weblogic.ejbgen.Select in the EJBGen Reference.

In the IDE, you can view and set @Select annotation values using the Annotations view. To do this, do as described above for adding a find method, except that your cursor should be in a method declaration.

Select Method Examples

The following list shows common uses of EJB QL queries with select methods:

Object References. A select method can use the same queries as find methods to return object references. The method java.util.Collection ejbSelectAll(), defined in the ItemsBean_S class (but not its interfaces), returns all records in the database table:
```
@Select(ejbQl = "SELECT OBJECT(o) from ItemsBean_S as o")
public abstract java.util.Collection ejbSelectAll() throws FinderException;
```
CMP Fields. The method java.util.Collection ejbSelectItemNames(), defined in the ItemsBean_S class, returns only the item names of all items:
```
@Select(ejbQl = "SELECT o.itemname from ItemsBean_S as o")
public abstract java.util.Collection ejbSelectItemNames()
    throws FinderException;
```
Just as with a query that returns object references, a query that returns CMP fields can have input parameters, literal values, relationship querying with the IN keyword, and the DISTINCT keyword to avoid duplicate records. The method java.util.Collection ejbSelectByItemName(java.lang.String itemname), defined in the ItemsBean_S class, returns only the item names of the items that match the argument itemname:
```
@Select(ejbQl = "SELECT o.itemname from ItemsBean_S as o WHERE o.itemname = ?1")
public abstract java.util.Collection ejbSelectNameByItemName(String itemname)
    throws FinderException;
```

Standard EJB QL Operators

EJB QL 2.0 defines a number of standard operators. Some of these, like IN, DISTINCT, and the use of '.' as a navigational operator (for instance, to access a EJB's CMP field) have been described above. Other operators include:

The comparison operators <, >, <=, >=, =, and <>.
The logical operators NOT, AND, and OR.
The arithmetic operators +, - (unary); *, /, +, and -.
The arithmetic functions ABS(number), returning the absolute value of a (int, double, or float) number, and SQRT(double) returning the square root.
LIKE is used for pattern matching with String fields. Use % to match with any number of characters and _ to match with exactly one character (use \ if these characters actually occur in the pattern). For instance, the following query will select all items whose price ends in .95:
```
SELECT OBJECT(o) from ItemsBean_S as o WHERE o.price LIKE '%.95'
```
The String functions CONCAT(String1, String2), LENGTH(String), LOCATE(StringToFind, ContainingString [, starting position]) - equivalent to java.lang.String.indexOf -, and SUBSTRING(String, startposition, endposition).
BETWEEN specifies a range of values (inclusive). For instance, the following query returns all items between $20 and $40:
```
SELECT OBJECT(o) from ItemsBean as o WHERE o.price BETWEEN 20.00 AND 40.00
```
IS NULL is used to test for null fields. CMP fields that hold an object (such as a String) and CMR fields that hold a single object can be null, while CMP fields holding primitive values and CMR fields holding a collection of objects cannot. For instance, the following query returns all items whose manufacturer is not known (assuming the same entity relationship as mentioned above):
```
SELECT OBJECT(o) from ItemsBean as o WHERE o.manufacturer IS NULL
```
IS EMPTYis used to test for empty sets, in particular CMR fields that holds a collection of objects. For example, the following query returns all manufacturers without known items in the database:
```
SELECT OBJECT(o) from ManufacturerBean as o WHERE o.items IS EMPTY
```
MEMBER OF is used to evaluate whether an object is part of a collection-based relationship. For example, the following query returns the manufacturer of a given item:
```
SELECT OBJECT(o) from ManufacturerBean as o, IN (o.items) AS allItems, ItemsBean oneItem 
    WHERE oneItem.itemname = ?1 AND oneItem MEMBER OF allItems
```

For more detailed information on EJB QL queries and the operators defined in this language, see the Finder and Select Methods Samples, or your favorite J2EE documentation.