EJB QL

When the EJB 1.1 was introduced, it took a good beating for its CMP. For one, the specification did not define any standard SQL-like language to interact with the database and to implement persistence. The findByXXX() methods contained application code proprietary to the vendor supplying the CMP tools. This resulted in each application server vendor either defining a proprietary query language, or adopting a query language of its choice. The very reason that the emergence of EJB's WORA (write once, run anywhere) was defeated was that the application components developed using CMP (as defined in the EJB 1.1 specification) became tied down to the application server on which it was developed.

To overcome problems of portability associated with CMP, developers resorted to developing entity beans using BMP. The pooling of beans and persistence was then managed by the bean developer. That is when the shortfalls of developing entity beans using BMP-like code came into play. The code was complex and unwieldy and required increased development times.

In the EJB 1.1 specification, there was no ideal query language to navigate between a bean class and its dependent classes or any of the member variables of the dependent classes. This resulted in proprietary select or query methods to find information internal to dependent classes.

To overcome the cited problems, the EJB 2.0 specification defines a new ANSI SQL92 compliant language, called the EJB Query Language (EJB QL). EJB QL is a simple SQL-based language aimed at only executing queries for in-memory objects in the application server. It is not a full-fledged database SQL language. The application server compiles the EJB QL query that is based on the abstract persistence schema of entity beans and then converts the query into the application server's target language, making it portable.

The EJB QL language works with the definitions of entity beans, rather than with the relational database tables themselves. EJB QL can be used by entity beans using either local or remote interfaces as well as dependent objects.

The EJB QL language has two types of syntaxes: tag syntax and query syntax. In the following section, you will explore them in more detail.

Tag Syntax

The tag syntax component of the EJB QL language defines how the EJB QL query should be embedded in the ejb-jar.xml deployment descriptor file. The different tags that will be used for embedding the EJB QL query have a well-defined syntax.

To enable the EJB QL queries to be used across any EJB 2.0 compliant container, the entries have to be primarily made in the common ejb-jar.xml. Here is how the contents of the ejb-jar.xml file look with the tags:

 <ejb-jar>      <enterprise-beans>         <session>             ...         </session>         <entity>             ...             <abstract-schema-name>             </abstract-schema-name>             <cmp-field>             </cmp-field>             <query>                 <query-method>                     <method-name>                     </method-name>                     <method-params>                         <method-param>                         </method-param>                         </method-params>                 </query-method>                 <result-type-mapping>                 </result-type-mapping>                 <ejb-ql>                     <![CDATA[SELECT OBJECT(a) FROM objectname AS a                             WHERE a.cmp-field= ?1]]>                 </ejb-ql>             </query>         </entity>     </enterprise-beans> </ejb-jar> 

Take a look at some of the tags in the ejb-jar.xml file in detail:

• <abstract-schema-name> This is the abstract name for the entity bean. The value embedded in this tag will be used in the FROM clause of the EJB QL query and is analogous to a table name. Because an entity bean can be mapped to more than one table, this abstract-schema-name maps to multiple tables on the database end, and to a single object at the application side.

• <method-name> The finder or select method, to which the EJB QL query is mapped, is listed in the <method-name> tag. The finder method listed here also needs to be declared in the home interface. The <method-name> tag encloses the information about the input parameters of each finder method.

• <method-params> All the input parameters of a finder or select method are listed within the <method-params> tag. The <method-params> tag encloses the details of each input parameter defined using the <method-param> tag (note that this tag does not have an 's' in param). One <method-params> tag can contain multiple <method-param> tags.

• <method-param> The metadata of each of the input parameters is listed using the <method-param> tag. That is the reason this tag is nested inside the tag <method-params>. While referring to more than one parameter in the EJB QL query, these parameters are read by the compiler in the order they are entered.

One thing to keep in mind here is that the value of this tag should be of the object type. For example, if your input parameter is of the type string, then the entry in this parameter would be java.lang.String, and not string alone. However, in case of primitive Java types, just the variable type has to be entered. For example, if your parameter is of the type int, the value corresponding to the <method-param> tag will be int.

• <result-type-mapping> The return type of the finder or select method is defined in the <result-type-mapping> tag. The return type of the finder or select method can be either a remote interface, a local interface, a persistent field, or a collection of objects implementing the local or remote interface.

• <ejb-ql> This tag contains the actual EJB QL query. The <ejb-ql> has been enclosed in the XML CDATA attribute, which indicates that the EJB QL query written in this tag will be treated as character data. This ensures that if any special characters such as ' or & were to appear in the EJB QL, the finder methods would not throw any errors on compilation.

Query Syntax

Apart from the tag syntax, EJB QL has a well-defined query syntax and grammar like any other SQL language.

The basic structure of EJB QL consists of the following three clauses:

• SELECT (OBJECT name/ Any type of value)

• FROM (abstract name of entity bean) AS(optional) name

• WHERE (where clause)

Now you will take a look at each of these clauses in detail.

The SELECT Clause

The SELECT clause defines the elements to be extracted from the Cartesian join of the schema names defined in the FROM clause. The return type of the SELECT clause is either a remote interface, a local interface, a persistent field, or a collection of objects implementing the local or remote interface. A code snippet follows:

 SELECT OBJECT(a) FROM Item AS a WHERE a.itemDesc = ?1  

The preceding EJB QL query will return objects of Item where the description field matches the input parameter.

Tip

To obtain distinct results, you can use either a DISTINCT clause, or specify the return type in the finder method as java.util.Set. The java.util.Set class does not allow duplicate values and it performs an internal DISTINCT sorting, so that it returns the same result set as that obtained by a DISTINCT keyword in the SELECT clause.

Additionally, the EJB 2.0 specification defines a list of functions that can be used by developers in the SELECT clause of the EJB QL for strings as well as other primitive data types:

 CONCAT(String, String)  

returns a concatenated string.

 DISTINCT (Object)  

returns either a distinct object or a set of objects with distinct combinations.

 SUBSTRING(String, start, length)  

returns a string.

 LOCATE(String, String, [startPosition])  

returns an int.

 LENGTH(String)  

returns an int.

 ABS(number)  

returns a converted integer.

 SQRT(double)  

returns a double.

The FROM Clause

The FROM clause defines the abstract schema on which the Cartesian join has to be performed to obtain the results requested in the SELECT query and filtered by the WHERE clause.

The abstract schema name is used here, and not the database table name. The abstract schema name is the abstract name for an entity bean. A code snippet follows:

 SELECT OBJECT(a) FROM Item AS a WHERE a.itemDesc = ?1  

In the preceding query, Item is the abstract schema name defined in the deployment descriptor file, ejb-jar.xml, under the tag <abstract-schema-name>. Alternatively, this can also be a part of a collection of objects existing in a one-to-many relationship. a is used as a pseudo name and is defined here in the FROM clause after the AS keyword. This pseudo name is used in the SELECT and WHERE clauses too.

The WHERE Clause

In this clause, you get to define the criteria based on which one you wish to perform your select. Almost all comparison operators and Boolean expressions allowed in an SQL language are allowed here. To specify the values to be compared, a number with a question mark sign (?) is used. The number is replaced by that occurrence of the <method-param> ejb-jar.xml tag.

The comparison operators which can be used are:

• <> (Not equal to)

• = (Equal to)

• > (Greater than)

• < (Less than)

• >= (Greater than or equal to)

• <= (Less than or equal to)

• AND

• OR

• BETWEEN(values)

• NOT BETWEEN(values)

• IS NULL

• IS NOT NULL

• IN(values)

• NOT IN(values)

• LIKE(value)

• NOT LIKE(value)

The operators specific to EJB are

• NOT EMPTY: This can be used to determine whether the object returned because of an expression has any elements.

• (value 1) MEMBER OF(value 2): This can be used to determine whether the value 1 of a single path expression is a member of a result of a collection or a member of a set of objects returned by another expression.

Limitations of EJB-QL

As with any language, along with all its advantages come some limitations. Here are some of the limitations of EJB QL:

• Because this is not a full-fledged query language yet, many of the SQL functions such as ORDER BY are not provided.

• The only Boolean and string comparisons allowed are the <> (not equal to) and the = (equal to).

• In arithmetic expressions, comparisons in decimal format are not allowed.

• Primary class inheritance is still a problem in the EJB 2.0 version.

• Date and time comparisons are in the primitive Java Long data type represented in milliseconds. This is not the easiest way to perform data and time manipulations.

• A code-commenting facility is not available. There is no syntax-defined way to tell the compiler that the line you are writing is a comment.