Declaring Queries

Entity beans allow for two types of queries: finders and selects. A finder provides queries on an entity bean to clients of the bean. A select method is designed to provide private query statements to an entity implementation. Unlike finders, which are restricted to only return entities of the same type as the home interface on which they are defined, select methods can return any entity type or just one field of the entity. EJB-QL is the query language that is used to specify finders and select methods in a platform-independent way.

Declaring Finders and Selects

How you declare finders has not changed in CMP 2.0. You still declare finders in the home interface (local or remote) of the entity. Finders defined on the local home interface do not throw a RemoteException. The following code declares the findBadDudes_ejbql finder on the GangsterHome interface. The ejbql suffix here is not required. It is simply a naming convention used here to differentiate the different types of query specifications:

 public interface GangsterHome     extends EJBLocalHome {     Collection findBadDudes_ejbql(int badness) throws FinderException; } 

You declare select methods in the entity implementation class, and they must be public and abstract, just like CMP and CMR field abstract accessors, and they must throw a FinderException. The following code declares a select method:

 public abstract class GangsterBean     implements EntityBean {     public abstract Set ejbSelectBoss_ejbql(String name)         throws FinderException; } 

Declaring EJB-QL Queries

Every select or finder method (except findByPrimaryKey) must have an EJB-QL query defined in the ejb-jar.xml file. The EJB-QL query is declared in a query element, which is contained in the entity element. The following are declarations for the findBadDudes_ejbql and ejbSelectBoss_ejbql queries:

 <enterprise-beans>         <entity>             <ejb-name>GangsterEJB</ejb-name>             <!-- ... -->             <query>                 <query-method>                     <method-name>findBadDudes_ejbql</method-name>                     <method-params>                         <method-param>int</method-param>                     </method-params>                 </query-method>                 <ejb-ql><![CDATA[                  SELECT OBJECT(g)                  FROM gangster g                  WHERE g.badness > ?1                  ]]></ejb-ql>             </query>             <query>                 <query-method>                     <method-name>ejbSelectBoss_ejbql</method-name>                     <method-params>                         <method-param>java.lang.String</method-param>                     </method-params>                 </query-method>                 <ejb-ql><![CDATA[                  SELECT DISTINCT underling.organization.theBoss                  FROM gangster underling                  WHERE underling.name = ?1 OR underling.nickName = ?1                  ]]></ejb-ql>             </query>         </entity>     </enterprise-beans> </ejb-jar> 

EJB-QL is similar to SQL, but it has some surprising differences. The following are some important things to note about EJB-QL:

• EJB-QL is a typed language, meaning that it only allows comparison of like types (that is, strings can only be compared with strings).

• In an equals comparison, a variable (that is, a single-valued path) must be on the left side. These are some examples:

   g.hangout.state = 'CA' Legal 'CA' = g.shippingAddress.state NOT Legal 'CA' = 'CA' NOT Legal (r.amountPaid * .01) > 300 NOT Legal r.amountPaid > (300 / .01) Legal 

  
  

• Parameters use a base 1 index, similarly to java.sql.PreparedStatement.

• Parameters are allowed only on the right side of a comparison. Here's an example:

   gangster.hangout.state = ?1 Legal ?1 = gangster.hangout.state NOT Legal 

  
  

Overriding the Mapping of EJB-QL to SQL

You can override an EJB-QL query in the jbosscmp-jdbc.xml file. The finder or select is still required to have an EJB-QL declaration, but the ejb-ql element can be left empty. Currently, you can override the SQL with JBossQL, DynamicQL, DeclaredSQL, or a BMP-style custom ejbFind method. All EJB-QL overrides are nonstandard extensions to the EJB specification, so use of these extensions limits portability of an application. You declare all the EJB-QL overrides, except for BMP custom finders, by using a query element in the jbosscmp-jdbc.xml file. The content model is shown in Figure 11.11.

Figure 11.11. The jbosscmp-jdbc query element content model.

Detailed descriptions of the query element follow:

• description This is an optional description for the query.

• query-method This required element specifies the query method that is being configured. This must match a query-method declared for this entity in the ejb-jar.xml file.

• jboss-ql This is a JBossQL query to use in place of the EJB-QL query. JBossQL is discussed in the following section of this chapter.

• dynamic-ql This indicates that the method is a dynamic query method and not an EJB-QL query. Dynamic queries are discussed later in this chapter.

• declared-sql This query uses declared SQL in place of the EJB-QL query. Declared SQL is discussed later in this chapter.

• read-ahead This optional element allows you to optimize the loading of additional fields for use with the entities referenced by the query. This is discussed in detail later in this chapter, in the section "Optimized Loading."

JBossQL

JBossQL is a superset of EJB-QL that is designed to address some of the inadequacies of EJB-QL. In addition to having a more flexible syntax, JBossQL now has new functions, keywords, and clauses. At the time of this writing, JBossQL includes support for ORDER BY, OFFSET, and LIMIT clauses; parameters in the IN and LIKE operators; and COUNT, MAX, MIN, AVG, SUM, UCASE, and LCASE functions. Queries can also include functions in the SELECT clause for select methods.

JBossQL is declared in the jbosscmp-jdbc.xml file with a jboss-ql element that contains the JBossQL query. The following example provides an example of a JBossQL declaration:

 <jbosscmp-jdbc>     <enterprise-beans>         <entity>             <ejb-name>GangsterEJB</ejb-name>             <query>                 <query-method>                     <method-name>findBadDudes_jbossql</method-name>                     <method-params>                         <method-param>int</method-param>                     </method-params>                 </query-method>                 <jboss-ql><![CDATA[                  SELECT OBJECT(g)                  FROM gangster g                  WHERE g.badness > ?1                  ORDER BY g.badness DESC                  ]]></jboss-ql>             </query>         </entity>     </enterprise-beans> </jbosscmp-jdbc> 

The corresponding generated SQL is straightforward:

 SELECT t0_g.id     FROM gangster t0_g     WHERE t0_g.badness > ?     ORDER BY t0_g.badness DESC 

JBossQL also has the capability to retrieve finder results in blocks by using the LIMIT and OFFSET functions. For example, to iterate through the large number of jobs performed, you can define the following findManyJobs_jbossql finder:

 <jbosscmp-jdbc>     <enterprise-beans>         <entity>             <ejb-name>GangsterEJB</ejb-name>             <query>                 <query-method>                     <method-name>findManyJobs_jbossql</method-name>                     <method-params>                         <method-param>int</method-param>                     </method-params>                     <method-params>                         <method-param>int</method-param>                     </method-params>                 </query-method>                 <jboss-ql><![CDATA[                  SELECT OBJECT(j)                  FROM jobs j                  OFFSET ?1 LIMIT ?2                  ]]></jboss-ql>             </query>         </entity>     </enterprise-beans> </jbosscmp-jdbc> 

DynamicQL

DynamicQL allows the runtime generation and execution of JBossQL queries. A DynamicQL query method is an abstract method that takes a JBossQL query and the query arguments as parameters. JBoss compiles the JBossQL and executes the generated SQL. The following generates a JBossQL query that selects all the gangsters who have a hangout in any state in the states set:

 public abstract class GangsterBean     implements EntityBean {     public Set ejbHomeSelectInStates(Set states)         throws FinderException     {         // generate JBossQL query         StringBuffer jbossQl = new StringBuffer();         jbossQl.append("SELECT OBJECT(g) ");         jbossQl.append("FROM gangster g ");         jbossQl.append("WHERE g.hangout.state IN (");         for (int i = 0; i < states.size(); i++) {             if (i > 0) {                 jbossQl.append(", ");             }             jbossQl.append("?").append(i+1);         }             jbossQl.append(") ORDER BY g.name");         // pack arguments into an Object[]         Object[] args = states.toArray(new Object[states.size()]);         // call dynamic-ql query         return ejbSelectGeneric(jbossQl.toString(), args);     } } 

The DynamicQL select method may have any valid select method name, but the method must always take a string and an object array as parameters. DynamicQL is declared in the jbosscmp-jdbc.xml file with an empty dynamicql element. The following is the declaration for ejbSelectGeneric:

 <jbosscmp-jdbc>     <enterprise-beans>         <entity>             <ejb-name>GangsterEJB</ejb-name>             <query>                 <query-method>                        <method-name>ejbSelectGeneric</method-name>                        <method-params>                              <method-param>java.lang.String</method-param>                              <method-param>java.lang.Object[]</method-param>                        </method-params>                 </query-method>                 <dynamic-ql/>             </query>         </entity>     </enterprise-beans> </jbosscmp-jdbc> 

DeclaredSQL

DeclaredSQL is based on the legacy JAWS CMP 1.1 engine finder declaration, but it has been updated for CMP 2.0. Commonly this declaration is used to limit a query with a WHERE clause that cannot be represented in EJB-QL or JBossQL. The content model for the declared-sql element is shown in Figure 11.12.

Figure 11.12. The jbosscmp-jdbc declared-sql element content model.

Detailed descriptions of the declared-sql element follow:

• select The select element specifies what is to be selected and consists of the following elements:

  • distinct If this empty element is present, JBoss adds the DISTINCT keyword to the generated SELECT clause. The default is to use DISTINCT if the method returns a java.util.Set.

  • ejb-name This is the ejb-name of the entity that will be selected. This is required only if the query is for a select method.

  • field-name This is the name of the CMP field that will be selected from the specified entity. The default is to select the entire entity.

  • alias This specifies the alias that will be used for the main select table. The default is to use the ejb-name.

  • additional-columns This declares other columns to be selected to satisfy ordering by arbitrary columns with finders or to facilitate aggregate functions in selects.

• from The from element declares additional SQL to append to the generated FROM clause.

• where The where element declares the WHERE clause for the query.

• order The order element declares the ORDER clause for the query.

• other The other element declares additional SQL that is appended to the end of the query.

The following is an example of a DeclaredSQL declaration:

 <jbosscmp-jdbc>     <enterprise-beans>         <entity>             <ejb-name>GangsterEJB</ejb-name>             <query>               <query-method>                   <method-name>findBadDudes_declaredsql</method-name>                     <method-params>                         <method-param>int</method-param>                     </method-params>                 </query-method>                 <declared-sql>                     <where><![CDATA[ badness > {0} ]]></where>                     <order><![CDATA[ badness DESC ]]></order>                 </declared-sql>             </query>         </entity>     </enterprise-beans> </jbosscmp-jdbc> 

The generated SQL would look like this:

 SELECT id FROM gangster WHERE badness > ? ORDER BY badness DESC 

As you can see, JBoss generates the SELECT and FROM clauses that are necessary to select the primary key for this entity. If desired, you can specify an additional FROM clause that is appended to the end of the automatically generated FROM clause. The following is an example of a DeclaredSQL declaration with an additional FROM clause:

 <jbosscmp-jdbc>     <enterprise-beans>         <entity>             <ejb-name>GangsterEJB</ejb-name>             <query>                 <query-method>                     <method-name>ejbSelectBoss_declaredsql</method-name>                     <method-params>                         <method-param>java.lang.String</method-param>                     </method-params>                 </query-method>                 <declared-sql>                     <select>                         <distinct/>                         <ejb-name>GangsterEJB</ejb-name>                         <alias>boss</alias>                     </select>                     <from><![CDATA[, gangster g, organization o]]></from>                     <where><![CDATA[                      (LCASE(g.name) = {0} OR LCASE(g.nick_name) = {0}) AND                      g.organization = o.name AND o.the_boss = boss.id                      ]]></where>                 </declared-sql>             </query>         </entity>     </enterprise-beans> </jbosscmp-jdbc> 

The generated SQL would look like this:

 SELECT DISTINCT boss.id     FROM gangster boss, gangster g, organization o     WHERE (LCASE(g.name) = ? OR LCASE(g.nick_name) = ?) AND           g.organization = o.name AND o.the_boss = boss.id 

Notice that the FROM clause starts with a comma. This is because the container appends the declared FROM clause to the end of the generated FROM clause. It is also possible for the FROM clause to start with a SQL JOIN statement. Because this is a select method, it must have a select element to declare the entity that will be selected. Note that an alias is also declared for the query. If an alias is not declared, the table-name is used as the alias, resulting in a SELECT clause with the table_name.field_name-style column declarations. Not all database vendors support that syntax, so the declaration of an alias is preferred. The optional empty distinct element causes the SELECT clause to use the SELECT DISTINCT declaration. The DeclaredSQL declaration can also be used in select methods to select a CMP field.

The following is an example that overrides a select to return all the zip codes an organization operates in:

 <jbosscmp-jdbc>     <enterprise-beans>         <entity>             <ejb-name>OrganizationEJB</ejb-name>             <query>                 <query-method>                     <method-name>ejbSelectOperatingZipCodes_declaredsql </method-name>                     <method-params>                         <method-param>java.lang.String</method-param>                     </method-params>                 </query-method>                 <declared-sql>                     <select>                         <distinct/>                         <ejb-name>LocationEJB</ejb-name>                         <field-name>zipCode</field-name>                         <alias>hangout</alias>                     </select>                     <from><![CDATA[ , organization o, gangster g ]]></from>                     <where><![CDATA[                     LCASE(o.name) = {0} AND o.name = g.organization AND                     g.hangout = hangout.id                     ]]></where>                     <order><![CDATA[ hangout.zip ]]></order>                 </declared-sql>             </query>         </entity>     </enterprise-beans> </jbosscmp-jdbc> 

The corresponding SQL would look like this:

 SELECT DISTINCT hangout.zip     FROM location hangout, organization o, gangster g     WHERE LCASE(o.name) = ? AND o.name = g.organization AND g.hangout = hangout.id                 ORDER BY hangout.zip 

Parameters

DeclaredSQL uses a completely new parameter-handling system, which supports entity and DVC parameters. Parameters are enclosed in curly brackets and use a zero-based index, which is different from the one-based EJB-QL parameters. There are three categories of parameters:

• Simple A simple parameter can be of any type except for a known (mapped) DVC or an entity. A simple parameter contains only the argument number, such as {0}. When a simple parameter is set, the JDBC type used to set the parameter is determined by the datasource-mapping for the entity. An unknown DVC is serialized and then set as a parameter. Note that most databases do not support the use of a BLOB value in a WHERE clause.

• DVC A DVC parameter can be any known (mapped) DVC. A DVC parameter must be dereferenced down to a simple property (that is, one that is not another DVC). For example, if you had a DVC property of type ContactInfo, valid parameter declarations would be {0.email} and {0.cell.areaCode} but not {0.cell}. The JDBC type used to set a parameter is based on the class type of the property and the datasourcemapping of the entity. The JDBC type used to set the parameter is the JDBC type that is declared for that property in the dependent-value-class element.

• Entity An entity parameter can be any entity in the application. An entity parameter must be dereferenced down to a simple primary key field or simple property of a DVC primary key field. For example, if you had a parameter of type gangster, a valid parameter declaration would be {0.gangsterId}. If you had some entity with a primary key field named info of type ContactInfo, a valid parameter declaration would be {0.info.cell.areaCode}. Only fields that are members of the primary key of the entity can be dereferenced. (This restriction may be removed in later versions.) The JDBC type used to set the parameter is the JDBC type that is declared for that field in the entity declaration.

EJB-QL 2.1 and SQL92 Queries

The default query compiler doesn't fully support EJB-QL 2.1 or the SQL92 standard. If you need either of these functions, you can replace the query compiler. The default compiler is specified in standard jbosscmp-jdbc.xml:

 <defaults>     ...     <ql-compiler>org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLCompiler</ql-compiler>     ... </defaults> 

To use the SQL92 compiler, you simply specify the SQL92 compiler in the ql-compiler element:

 <defaults>     ...     <ql-compiler>org.jboss.ejb.plugins.cmp.jdbc.EJBQLToSQL92Compiler</ql-compiler>     ... </defaults> 

This changes the query compiler for all beans in the entire system. You can also specify the ql-compiler for each element in jbosscmp-jdbc.xml. Here is an example that uses one of the earlier queries:

 <query>     <query-method>         <method-name>findBadDudes_ejbql</method-name>         <method-params>             lt;method-param>int</method-param>         </method-params>     </query-method>     <ejb-ql><![CDATA[         SELECT OBJECT(g)         FROM gangster g         WHERE g.badness > ?1]]>     </ejb-ql>     <ql-compiler>org.jboss.ejb.plugins.cmp.jdbc.EJBQLToSQL92Compiler</ql-compiler> </query> 

One important limitation of SQL92 query compiler is that it always selects all the fields of an entity, regardless of the read-ahead strategy in use. For example, if a query is configured with the on-load read-ahead strategy, the first query includes all the fieldsnot just primary key fieldsbut only the primary key fields will be read from the result set. Then, on load, other fields will actually be loaded into the read-ahead cache. The on-find read-ahead with the default load group * works as expected.

BMP Custom Finders

JBoss supports bean-managed persistence (BMP) custom finders. If a custom finder method matches a finder declared in the home or local home interface, JBoss will always call the custom finder over any other implementation declared in the ejb-jar.xml or jbosscmp-jdbc.xml files. The following simple example finds the entities by a collection of primary keys:

 public abstract class GangsterBean     implements EntityBean {     public Collection ejbFindByPrimaryKeys(Collection keys)     {           return keys;     } } 

This is a very useful finder because it quickly converts primary keys into real Entity objects without contacting the database. One drawback is that it can create an Entity object with a primary key that does not exist in the database. If any method is invoked on the bad Entity, a NoSuchEntityException is thrown. Another drawback is that the resulting entity bean violates the EJB specification in that it implements a finder, and the JBoss EJB verifier fails the deployment of such an entity unless the StrictVerifier attribute is set to false.