EJB-QL (EJB 2.02.1)
EJB-QL (EJB 2.0/2.1)
Introduced in version 2.0 and enhanced in 2.1 is the query language EJB-QL. EJB-QL CMP stands for EJB Query Language for Container-Managed-Persistence Query Methods. As the name suggests, this is an EJB-specific query language for formulating searches across entity beans with container-managed persistence. The syntax of EJB-QL is based largely on the syntax of the query language SQL92. Earlier sections on finder and select methods have already given a foretaste of EJB-QL.
Search queries formulated with EJB-QL can be implemented unchanged in various EJB containers and diverse persistence systems. For the formulation of finder methods in the case of container-managed persistence, many application servers have developed their own proprietary languages, since there was no independent query language defined in the specification before version 2.0. In porting an entity bean with container-managed persistence the search queries must then be modified to comply with the finder methods on the application server of the EJB container in question.
EJB-QL is not interpreted at run time, but is translated into another query language at the time of deployment by the EJB container. For example, if an entity bean with container-managed persistence is linked to a relational database at deployment, then the EJB container would translate the search query from EJB-QL into SQL. When the search query is called, the EJB container executes the corresponding SQL commands.
EJB-QL search queries are always related to a particular query space. The boundaries of such a query space are set by the deployment descriptor. In formulating a search query, which is always defined within the confines of a particular entity bean type, all entity beans with container-managed persistence defined in the same deployment descriptor can be accessed.
In practice, EJB-QL is implemented for the definition of the following methods:
-
Finder methods
Finder methods are defined in the home or local home interface of an entity bean with container-managed persistence. They are implemented by specifying an EJB-QL query in the deployment descriptor. If a finder method is defined in the home interface of an entity bean, then the type of the return value of the associated EJB-QL query must be of the type of the remote interface of the entity bean. If the finder method was defined in the local home interface of the entity bean, then the type of the return value of the associated search query must be of the type of the local interface of the entity bean.
-
Select methods
Select methods are declared as abstract methods in the bean class, and they provide access to the persistent state of other entity beans. They are implemented (like the finder methods) by specifying an EJB-QL query in the deployment descriptor. The type of the return value of such queries can be of the type of the remote or local interface or of a persistent attribute of an entity bean.
Listing 5-36 shows a segment from the deployment descriptor, in which the entity bean Person and the entity bean Address are defined. Both entity beans are in bidirectional relationship to each other. The Person bean defines the persistent attributes name (for the surname) and firstname, which together with the persistent relationship to the Address bean belong to the abstract persistence schema of the Person bean, to which the name PersonAPS is given via the element abstract-schema-name. The suffix APS stands for abstract persistence schema, and it is used here to avoid confusion between the bean name and the name of the abstract persistence schema (usually, one uses the same name for both elements). The Address bean defines the persistent attributes (foreign key for the relationship to the person bean), street, zip (for the postal code), and city. Together with the persistent relationship to the Person bean they belong to the abstract persistence schema of the Address bean, which is called AddressAPS. In the course of this section we shall always return to this deployment descriptor in our EJB-QL examples.
Listing 5-36: EJB-QL example for the entity bean Person.
 |
... <entity> <ejb-name>PersonBean</ejb-name> <local-home>PersonLocalHome</local-home> <local>PersonLocal</local> <ejb-class>PersonBean</ejb-class> <persistence-type>Container</persistence-type> <prim-key-class>java.lang.String</prim-key-class> <reentrant>False</reentrant> <cmp-version>2.x</cmp-version> <abstract-schema-name>PersonAPS</abstract-schema-name> <cmp-field> <field-name>name</field-name> </cmp-field> <cmp-field> <field-name>firstname</field-name> </cmp-field> <primkey-field>name</primkey-field> </entity> <entity> <ejb-name>AddressBean</ejb-name> <local-home>AddressLocalHome</local-home> <local>AddressLocal</local> <ejb-class>AddressBean</ejb-class> <persistence-type>Container</persistence-type> <prim-key-class>java.lang.String</prim-key-class> <reentrant>False</reentrant> <cmp-version>2.x</cmp-version> <abstract-schema-name>AddressAPS</abstract-schema-name> <cmp-field> <field-name>name</field-name> </cmp-field> <cmp-field> <field-name>street</field-name> </cmp-field> <cmp-field> <field-name>zip</field-name> </cmp-field> <cmp-field> <field-name>city</field-name> </cmp-field> <primkey-field>name</primkey-field> </entity> ... <ejb-relation> <ejb-relation-name>Person-Address</ejb-relation-name> <ejb-relationship-role> <ejb-relationship-role-name> person-has-address </ejb-relationship-role-name> <multiplicity>One</multiplicity> <relationship-role-source> <ejb-name>PersonBean</ejb-name> </relationship-role-source> <cmr-field> <cmr-field-name>address</cmr-field-name> </cmr-field> </ejb-relationship-role> <ejb-relationship-role> <ejb-relationship-role-name> address-belongs-to-person </ejb-relationship-role-name> <multiplicity>One</multiplicity> <relationship-role-source> <ejb-name>AddressBean</ejb-name> </relationship-role-source> <cmr-field> <cmr-field-name>person</cmr-field-name> </cmr-field> </ejb-relationship-role> </ejb-relation> ...
|
Constructing the Search Query
All search queries are divided into four parts:
-
SELECT clause: This determines the type of the return value for the search query. This can be a reference to an entity bean or a persistent attribute of an entity bean.
-
FROM clause: This determines the domain of the instructions in the SELECT and optional WHERE clauses. Queries relate to the CMP 2.0 entity beans defined in the deployment descriptor or to their persistent attributes and relationships.
-
WHERE clause (optional): This serves to limit the result set.
-
ORDER BY clause (optional): This serves to order the result set. This was added in EJB 2.1 and does not exist in version 2.0 of the specification.
Listing 5-37 shows the simplest example of an EJB-QL query. It is assumed that the Person bean defines a finder method in its home interface with the name findAllPersons. The query defined via the element ejb-ql is associated with the method findAllPersons via the element query-method.
Listing 5-37: Simple EJB-QL query for findAllPersons.
|
... <entity> <ejb-name>PersonBean</ejb-name> ... <query> <query-method> <method-name>findAllPersons</method-name> </query-method> ejb-ql> SELECT OBJECT(p) FROM PersonAPS AS p </ejb-ql> </query> </entity> ...
|
The query returns all objects that correspond to the abstract persistence schema PersonAPS, that is, all existing Person entity beans. The client that calls the findAllPersons method receives an object of type java.util.Collection, which contains the local references to the found Person beans.
The following points hold generally for search queries in EJB-QL:
-
Queries always relate to the abstract persistence schema of one or more entity beans with container-managed persistence 2.0.
-
To the abstract persistence schema of an entity bean belong the persistent attributes and the persistent relationships to other entity beans.
-
EJB-QL queries are defined in the deployment descriptor, in fact, in the domain of a particular entity bean.
-
EJB-QL queries are always associated with a particular finder or select method.
-
In the search query the only entity beans that may be involved are those defined in the same deployment descriptor as the entity bean in whose domain the search query is defined.
Attribute Search
The result set of a query can be limited by using attributes of an entity bean (which are a component of the abstract persistence schema). Let us assume that the person bean defines the following finder method in its local home interface:
java.util.Collection findByFirstname(java.lang.String firstname) throws javax.ejb.FinderException;
The purpose of this method is to find persons that have a particular first name. Listing 5-38 shows the corresponding definition in the deployment descriptor.
Listing 5-38: EJB-QL query for findByFirstname.
|
... <entity> <ejb-name>PersonBean</ejb-name> ... <abstract-schema-name>PersonAPS</abstract-schema-name> ... <cmp-field> <field-name>firstname</field-name> </cmp-field> ... <query> <query-method> <method-name>findByFirstname</method-name> <method-params> <method-param>java.lang.String</method-param> </method-params> </query-method> <ejb-ql> SELECT OBJECT(p) FROM PersonAPS AS p WHERE p.firstname=?1 </ejb-ql> </query> </entity> ...
|
With the WHERE clause the search result is restricted to all objects whose first name corresponds to the value of the first parameter (?1) of the method findByFirstname (in this case the parameter firstname). The types of the parameters must be set using the element method-params.
In addition to the restriction of the search results using attributes, the values of persistent attributes can also be returned. However, this is reserved for the select methods, since finder methods are permitted only to return references to entity beans. Finder methods are called by the client, not select methods.
We assume that the person bean defines a select method
public abstract Collection ejbSelectNamesInCity(String city) throws FinderException;
in order to determine which persons live in a particular city. The associated declaration in the deployment descriptor is shown in Listing 5-39.
Listing 5-39: EJB-QL query for ejbSelectNamesInCity.
|
... <entity> <ejb-name>PersonBean</ejb-name> ... <query> <query-method> <method-name>ejbSelectNamesInCity</method-name> <method-params> <method-param>java.lang.String</method-param> </method-params> </query-method> <ejb-ql> SELECT p.name FROM AddressAPS AS p WHERE p.city=?1 </ejb-ql> </query> </entity> <entity> <ejb-name>AddressBean</ejb-name> ... <abstract-schema-name>AddressAPS</abstract-schema-name> <cmp-field> <field-name>name</field-name> </cmp-field> ... <cmp-field> <field-name>city</field-name> </cmp-field> ... </entity> ...
|
The type of the return value is no longer a bean object, but the type of the attribute name of the abstract persistence schema AddressAPS. Since the query can return more than one result, the select method defines the return value to be of type Collection.
Searches over Relationships
In addition to attributes, the persistent relationships also belong to the abstract persistence schema of an entity bean. They can be used like attributes in search queries. On the one hand, the attributes of the participating entity beans can be used for limiting the result set, while on the other hand, the participating entity bean objects and values of their persistent attributes can be returned by the search query.
Let us assume that the person bean has defined the following select methods:
public abstract AddressLocal ejbSelectAddress() throws FinderException; public abstract String ejbSelectCity() throws FinderException; public abstract Collection ejbSelectPersonWithZip(Integer zip) throws FinderException;
The method ejbSelectAddress should return the address that is linked via the persistent one-to-one relationship with the associated person bean. The method ejbSelectCity should return only the persistent attribute city of the Address bean for all existing persons. The method ejbSelectPersonWithZip returns all persons whose address contains a particular zip code. Listing 5-40 shows thequeries belonging to the methods.
Listing 5-40: EJB-QL and persistent relationships.
|
... <entity> <ejb-name>PersonBean</ejb-name> ... <abstract-schema-name>PersonAPS</abstract-schema-name> ... <query> <query-method> <method-name>ejbSelectAddress</method-name> </query-method> <ejb-ql> SELECT p.address FROM PersonAPS AS p </ejb-ql> </query> <query> <query-method> <method-name>ejbSelectCity</method-name> </query-method> <ejb-ql> SELECT p.address.city FROM PersonAPS AS p </ejb-ql> </query> <query> <query-method> <method-name>ejbSelectPersonWithZip</method-name> <method-params> <method-param>java.lang.Integer</method-param> </method-params> </query-method> <ejb-ql> SELECT OBJECT(p) FROM PersonAPS AS p WHERE p.address.zip = ?1 </ejb-ql> </query> </entity> <entity> <ejb-name>AddressBean</ejb-name> ... <abstract-schema-name>AddressAPS</abstract-schema-name> ... <cmp-field> <field-name>zip</field-name> </cmp-field> <cmp-field> <field-name>city</field-name> </cmp-field> ... </entity> ... <ejb-relation> <ejb-relation-name>Person-Address</ejb-relation-name> <ejb-relationship-role> ... <multiplicity>One</multiplicity> <relationship-role-source> <ejb-name>PersonBean</ejb-name> </relationship-role-source> <cmr-field> <cmr-field-name>address</cmr-field-name> </cmr-field> </ejb-relationship-role> <ejb-relationship-role> ... </ejb-relationship-role> </ejb-relation> ...
|
The relationship to the address bean is handled in the case of cardinality one like a persistent attribute. For referencing the participating entity bean the dot operator (SELECT p.address) is used. The navigation can be extended using the dot operator to the persistent attributes of the participating entity bean (SELECT p.address.city).
If the relationship to the address bean were not of cardinality one but of cardinality n (that is, one person can have several domiciles), then the definition of the select methods as well as the queries shown in Listing 5-40 would no longer be valid. The signatures of the select methods would have to be changed as follows:
public abstract Collection ejbSelectAddress() throws FinderException; public abstract Collection ejbSelectCity() throws FinderException; public abstract Collection ejbSelectPersonWithZip(Integer zip) throws FinderException;
Since the relationship is of cardinality one-to-n, the person bean can stand in relationship to several address beans. Therefore, the return value of the methods ejbSelectAddress and ejbSelectCity are of type java.util.Collection (alternatively, java.util.Set is possible). If navigation over a relationship is to take place within a query with cardinality n, then instead of the dot operator, the IN operator is used. Listing 5-41 shows the use of the IN operator.
Listing 5-41: Use of the IN operator.
|
... <entity> <ejb-name>PersonBean</ejb-name> ... <abstract-schema-name>PersonAPS</abstract-schema-name> ... <query> <query-method> <method-name>ejbSelectAddress</method-name> </query-method> <ejb-ql> SELECT OBJECT(a) FROM PersonAPS AS p, IN(p.address) AS a </ejb-ql> </query> <query> <query-method> <method-name>ejbSelectCity</method-name> </query-method> <ejb-ql> SELECT a.city FROM PersonAPS AS p, IN(p.address) AS a </ejb-ql> </query> <query> <query-method> <method-name>ejbSelectPersonWithZip</method-name> <method-params> <method-param>java.lang.Integer</method-param> </method-params> </query-method> <ejb-ql> SELECT OBJECT(p) FROM PersonAPS AS p, IN(p.address) AS a WHERE a.zip = ?1 </ejb-ql> </query> </entity> <entity> <ejb-name>AddressBean</ejb-name> ... </entity> ... <ejb-relation> <ejb-relation-name>Person-Address</ejb-relation-name> <ejb-relationship-role> ... <multiplicity>One</multiplicity> <relationship-role-source> <ejb-name>PersonBean</ejb-name> </relationship-role-source> <cmr-field> <cmr-field-name>address</cmr-field-name> </cmr-field> </ejb-relationship-role> <ejb-relationship-role> ... <multiplicity>Many</multiplicity> <relationship-role-source> <ejb-name>AddressBean</ejb-name> </relationship-role-source> </ejb-relationship-role> </ejb-relation>
|
In the case of a relationship with cardinality n the navigation can no longer take place via the dot operator. The objects that are linked to the entity bean over the relationship must first be bound to a variable using the IN operator. With this variable the persistent attributes of the linked entity beans can be referenced in the further course of the query. The variable is also used when the bound objects themselves are to be returned.
Additional Operators and Expressions
Table 5-1 shows the data types that can be used for the constants in EJB-QL instructions.
| Data Type | Syntax for Constants |
|---|---|
| Strings | Strings are enclosed in single quotes |
| Integers | Whole numbers |
| Floating-point numbers | Numbers with decimal point |
| Boolean values | TRUE or FALSE |
Table 5-2 executes all operators. The order of appearance is that of increasing precedence of the operators.
| Operator | Description |
|---|---|
| NOT | Logical negation |
| AND | Logical and |
| OR | Logical or |
| = | Equal |
| > | Greater than |
| >= | Greater than or equal |
| < | Less than |
| <= | Less than or equal |
| <> | Not equal |
| + (unary) | Increment a number by 1 |
| -(unary) | Decrement a number by 1 |
| * | Multiplication sign |
| + (binary) | Addition sign |
| -(binary) | Subtraction sign |
| . | Navigation operator for attributes and references within the deployment descriptor |
The greater-than and less-than signs, both of which can be used in EJB-QL queries, belong to the XML syntax and therefore may not be used within XML elements. To avoid such symbols from causing problems with the XML parser, the EJB-QL query must be linked to a so-called CDATA section:
<query> <query-method> <method-name>...</method-name> </query-method> <ejb-ql> <![CDATA[ SELECT l.sum FROM calculation AS r WHERE r.sum > 1000 ]]> </ejb-ql> </query>
The XML parser does not interpret data found within a CDATA section. It transmits them uninterpreted to the next level of the application layer.
Table 5-3 shows expression that can be used in the WHERE part.
| Expression | Description |
|---|---|
| BETWEEN | Checks whether a number lies between two given values. Syntax: <value> [NOT] BETWEEN <value> AND <value> Example: ... WHERE p.address.zip BETWEEN 01060 AND 01096 |
| IN | Checks whether a string appears in a collection of strings (not to be confused with the IN operator in the FROM part of a query). Syntax: <String-value> [NOT] IN (<String-value>, <String-value>, ...) Example: ... WHERE p.address.city IN ('New York', 'New Haven', 'Hartford') |
| LIKE | Compares a string with a simple regular expression to detect similar strings. The syntax of the regular expression allows for two special characters: The underscore (_) stands for an arbitrary character, and the percent sign (%) for a sequence of zero or more arbitrary characters. All other characters stand for themselves. To use a percent or underscore character, prefix the sign with a backslash (\). Syntax: <String-value> [NOT] LIKE <regular expression> Example: ... WHERE p.name LIKE ('%M__er%') |
| IS NULL | Checks whether an attribute is set for an instance as a one-to-one or n-to-one relationship. Syntax: <cmp/cmr-Feld> IS [NOT] NULL Example: ... WHERE p.address IS NOT NULL AND ... |
| IS EMPTY | Checks whether a one-to-n or n-to-one relationship is set for an instance. If the relationship does not refer to any instance, then the result is TRUE. Syntax: <cmr-Feld> IS [NOT] EMPTY Example: ... WHERE p.address IS NOT EMPTY (one-to-n case) |
| MEMBER OF | Checks whether an object is a component of a set of objects. Syntax: <cmp/cmr-Feld/parameters> [NOT] MEMBER [OF] Example: ... WHERE p.address MEMBER OF ... |
Table 5-4 shows functions that can be used in a query.
| Expression | Description |
|---|---|
| CONCAT | Concatenates two strings. Syntax: CONCAT( <String1>, <String2> ) Example: CONCAT ('abc', 'defg') returns the string 'abcdefg' |
| SUBSTRING | Extracts a substring from a given string. Syntax: SUBSTRING( <String>, <Startposition>, <Length> ) Example: SUBSTRING ('abcdefg', 2, 3) returns the string 'cde' |
| LOCATE | Searches for a substring in a given string. Syntax: LOCATE( <String>, <Substring>, [ <start> ] ) Example: LOCATE('abcdefg', 'cde') returns the value 2 (integer) |
| LENGTH | Determines the length of a string. Syntax: LENGTH( <String> ) Example: LENGTH('abcdefg') returns the value 7 (integer) |
| ABS | Determines the absolute value for the data types int, float, and double Syntax: ABS ( <int> ) or ABS ( <float> ) or ABS ( <double> ) Example: ABS( - 11.72 ) returns 11.72 |
| SQRT | Determines the square root. Syntax: SQRT( <double> ) Example: SQRT( 16 ) returns the value 4.0 (double) |
| COUNT | (Introduced in EJB 2.1) Counts the size of the result set. Syntax: COUNT( <attribute> ) Example: SELECT COUNT(p.address.city) ... |
| MAX | (Introduced in EJB 2.1) Determines the largest item in a collection. Syntax: SELECT MAX (<attribute>) Example: SELECT MAX (p.address.zip) ... |
| MIN | (Introduced in EJB 2.1) Determines the smallest item in a collection. Syntax: SELECT MIN (<attribute>) Example: SELECT MIN (p.address.zip ) ... |
| AVG | (Introduced in EJB 2.1) Determines the average of the specified attribute in the collection. Syntax: SELECT AVG (<attribute>) Example: SELECT AVG (employee.age ) ... |
| SUM | (Introduced in EJB 2.1) Determines the sum of the specified attribute in the collection. Syntax: SUM (<attribute>) Example: SELECT SUM (sales.value ) ... |
| DISTINCT | (Introduced in EJB 2.1) Determines the distinct values of the specified attribute in the collection. Syntax: DISTINCT (<attribute>) Example: DISTINCT (p.address.state ) ... |
Finally, we note that EJB-QL provides an enormous contribution to the portability of entity beans with container-managed persistence. Unfortunately, EJB-QL does not yet offer the level of functionality to which one has become accustomed with SQL. For example, the ORDER BY operator is lacking, which allows one to sort search results. Moreover, the data type java.util.Date is not supported. It is difficult to imagine an application in which persistent data are not used in some form. The next versions of the EJB specification will certainly provide some assistance in this direction.
EAN: 2147483647
Pages: 103