The example programs in Exercise 8.2 delve deeper into the
complexities of EJB QL. You will learn about arithmetic and
logic operators in
WHERE
clauses as well as other, more complex
WHERE
-clause constructs. The test programs of this
section demonstrate most of the example queries provided in Chapter 8
of the EJB book.
Build the examples for this exercise in the
ex08_2
directory, following the same procedure
as for earlier exercises.
This exercise introduces no new features in JBoss-specific files. If you think you need to, review Exercise 6.1 of this workbook to understand the JBoss-specific files in this example.
The database tables for this exercise will automatically be created
in JBoss’s default database, HypersonicSQL, when the
EJB JAR is deployed, but to initialize all database tables in this
example, you must perform the Ant task
run.initialize
:
C:workbookex08_2>ant run.initialize Buildfile: build.xml prepare: compile: run.initialize: [java] added Bill Burke [java] added Sacha Labourey [java] added Marc Fleury [java] added Jane Swift [java] added Nomar Garciaparra [java] added Richard Monson-Haefel
As in the preceding exercise, all example business logic is
implemented within a stateless session bean—in this case,
com.titan.test.Test82Bean
—and the database
initialization code is in that bean’s
initialize( )
method, which creates all the entity
beans for this exercise.
Each example method of Test82Bean
implements the
example code fragments shown in the EJB book. Each
Test82Bean
method is invoked by a small, simple
client application.
The Client_82a
program implements the queries
illustrated in the EJB section of this book, in the section of Chapter 8 called Section 8.3.4.
The business logic for this example is implemented in
com.titan.test.Test82Bean
, in the
test82a( )
method.
The code demonstrates a Customer EJB finder query that returns
duplicate responses, then invokes a finder query that uses the
DISTINCT
keyword to filter out duplicates.
finder method: |
|
query: |
|
| |
finder method: |
|
query: |
|
|
Client_82a
invokes these queries and displays
their output. To run it, invoke the Ant task
run.client_82a
. Remember to set your
JBOSS_HOME
and PATH
environment
variables. The output should look something like this:
C:workbookex08_2>ant run.client_82a Buildfile: build.xml prepare: compile: run.client_82a: [java] USING DISTINCT [java] -------------------------------- [java] Non-distinct: [java] SELECT OBJECT( cust) [java] FROM Reservation res, IN (res.customers) cust [java] Bill has a reservation. [java] Sacha has a reservation. [java] Nomar has a reservation. [java] Bill has a reservation. [java] Marc has a reservation. [java] Jane has a reservation. [java] [java] Distinct: [java] SELECT DISTINCT OBJECT( cust) [java] FROM Reservation res, IN (res.customers) cust [java] Bill has a reservation. [java] Sacha has a reservation. [java] Marc has a reservation. [java] Jane has a reservation. [java] Nomar has a reservation.
The Client_82b
program implements the queries
illustrated in the EJB book, in the section of Chapter 8 called Section 8.3.5.
The business logic for this example is implemented in
com.titan.test.Test82Bean
, in the
test82b( )
method.
Various Customer and Ship EJB finder queries show how to use string, numeric, and Boolean literals in EJB QL queries.
EJB: |
|
finder method: |
|
query: |
|
| |
EJB: |
|
finder method: |
|
query: |
|
| |
EJB: |
|
finder method: |
|
query: |
|
|
Client_82b
invokes these queries and displays
their output. To run it, invoke the Ant task
run.client_82b
. The output should look something
like this:
C:workbookex08_2>ant run.client_82b Buildfile: build.xml prepare: compile: run.client_82b: [java] THE WHERE CLAUSE AND LITERALS [java] -------------------------------- [java] SELECT OBJECT( c ) FROM Customer AS c [java] WHERE c.creditCard.organization = 'American Express' [java] Jane has an American Express card. [java] Nomar has an American Express card. [java] [java] SELECT OBJECT( s ) FROM Ship AS s [java] WHERE s.tonnage = 100000.0 [java] Ship Queen Mary as tonnage 100000.0 [java] [java] SELECT OBJECT( c ) FROM Customer AS c [java] WHERE c.hasGoodCredit = TRUE [java] Bill has good credit. [java] Marc has good credit. [java] Jane has good credit. [java] Nomar has good credit. [java] Richard has good credit.
The Client_82c
program implements the queries
illustrated in the EJB book, in the section of Chapter 8 called Section 8.3.6.
The business logic for this example is implemented in
com.titan.test.Test82Bean
, in the
test82c( )
method.
The code demonstrates a Customer EJB ejbSelect
query that uses strings as input parameters to the query and a Cruise
EJB finder method that uses a Ship EJB as an input parameter. As in
previous sections, the ejbSelect
query is wrapped
in a custom home method.
EJB: |
|
|
|
custom home method: |
|
|
|
query: |
|
| |
| |
EJB: |
|
finder method: |
|
query: |
|
|
Client_82c
invokes these queries and displays
their output. To run it, invoke the Ant task
run.client_82c
. Remember to set your
JBOSS_HOME
and PATH
environment
variables. The output should look something like this:
C:workbookex08_2>ant run.client_82c Buildfile: build.xml prepare: compile: run.client_82c: [java] THE WHERE CLAUSE AND INPUT PARAMETERS [java] -------------------------------- [java] SELECT OBJECT( c ) FROM Customer AS c [java] WHERE c.homeAddress.state = ?2 [java] AND c.homeAddress.city = ?1 [java] Get customers from Billerica, MA [java] Bill is from Billerica. [java] [java] SELECT OBJECT( crs ) FROM Cruise AS crs [java] WHERE crs.ship = ?1 [java] Get cruises on the Titanic [java] Atlantic Cruise is a Titanic cruise.
The Client_82d
example program implements the
queries illustrated in the EJB book, in the section of Chapter 8 called Section 8.3.8
The business logic for this example is implemented in
com.titan.test.Test82Bean
, in the
test82d( )
method. The code demonstrates a
Reservation EJB finder method that must be enclosed in an XML CDATA
section because it uses the > symbol in the query.
EJB: |
|
finder method: |
|
query: |
< |
| |
| |
|
Client_82d
invokes this query and displays its
output. To run it, invoke the Ant task
run.client_82d
. The output should look something
like this:
C:workbookex08_2>ant run.client_82d Buildfile: build.xml prepare: compile: run.client_82d: [java] THE WHERE CLAUSE AND CDATA Sections [java] -------------------------------- [java] ![CDATA[ [java] SELECT OBJECT( r ) FROM Rservation r [java] WHERE r.amountPaid > ?1 [java] ]]> [java] found reservation with amount paid > 20000.0: 40000.0
The Client_82e
program implements the queries
illustrated in the EJB book, in the section of Chapter 8 called Section 8.3.13.
The business logic for this example is implemented in
com.titan.test.Test82Bean
, in the
test82e( )
method. Two Ship EJB finder methods
demonstrate how to use the BETWEEN
keyword in a
WHERE
clause.
EJB: |
|
finder method: |
|
query: |
|
| |
EJB: |
|
finder method: |
|
query: |
|
|
Client_82e
invokes these queries and displays
their output. To run it, invoke the Ant task
run.client_82e
. The output should look something
like this:
C:workbookex08_2>ant run.client_82e Buildfile: build.xml prepare: compile: run.client_82e: [java] THE WHERE CLAUSE AND BETWEEN [java] -------------------------------- [java] SELECT OBJECT( s ) FROM Ship s [java] WHERE s.tonnage BETWEEN 80000.00 and 130000.00 [java] Queen Mary has tonnage 100000.0 [java] [java] SELECT OBJECT( s ) FROM Ship s [java] WHERE s.tonnage NOT BETWEEN 80000.00 and 130000.00 [java] Titanic has tonnage 200000.0
The Client_82f
program implements the queries
illustrated in the EJB book, in the section of Chapter 8 called Section 8.3.14.
The business logic for this example is implemented in
com.titan.test.Test82Bean
, in the
test82f( )
method.
The code uses two Customer EJB finder methods. One queries for all customers living in Georgia or Massachusetts. The other queries for all customers that do not live in these two states.
EJB: |
|
finder method: |
|
query: |
|
| |
EJB: |
|
finder method: |
|
query: |
|
|
Client_82f
invokes these queries and displays
their output. To run it, invoke the Ant task
run.client_82f
. The output should look something
like this:
C:workbookex08_2>ant run.client_82f Buildfile: build.xml prepare: compile: run.client_82f: [java] THE WHERE CLAUSE AND IN [java] -------------------------------- [java] SELECT OBJECT( c ) FROM Customer c [java] WHERE c.homeAddress.state IN ('GA', 'MA') [java] Bill [java] Marc [java] Jane [java] Nomar [java] [java] SELECT OBJECT( c ) FROM Customer c [java] WHERE c.homeAddress.state NOT IN ('GA', 'MA') [java] Sacha
The Client_82g
program implements the queries
illustrated in the EJB book, in the section of Chapter 8 called Section 8.3.15.
The business logic for this example is implemented in
com.titan.test.Test82Bean
, in the
test82g( )
method.
There are two Customer EJB finder methods. One selects all customers
that have a null
home address. The other selects
all customers that do not have a null
address.
EJB: |
|
finder method: |
|
query: |
|
| |
EJB: |
|
finder method: |
|
query: |
|
|
Client_82g
invokes these queries and displays
their output. To run it, invoke the Ant task
run.client_82g
. The output should look something
like this:
C:workbookex08_2>ant run.client_82g Buildfile: build.xml prepare: compile: run.client_82g: [java] THE WHERE CLAUSE AND IS NULL [java] -------------------------------- [java] SELECT OBJECT( c ) FROM Customer c [java] WHERE c.homeAddress IS NULL [java] Richard [java] [java] SELECT OBJECT( c ) FROM Customer c [java] WHERE c.homeAddress IS NOT NULL [java] Bill [java] Sacha [java] Marc [java] Jane [java] Nomar
The Client_82h
program implements the queries
illustrated in the EJB book, in the section of Chapter 8 called Section 8.3.16.
The business logic for this example is implemented in
com.titan.test.Test82Bean
, in the
test82h( )
method.
The code uses two Cruise EJB finder methods to illustrate the use of
IS
EMPTY
. One returns all the
Cruises that do not have Reservations. The other method returns all
Cruises that have Reservations.
EJB: |
|
finder method: |
|
query: |
|
| |
EJB: |
|
finder method: |
|
query: |
|
|
Client_82h
invokes these queries and displays
their output. To run it, invoke the Ant task
run.client_82h
. The output should look something
like this:
C:workbookex08_2>ant run.client_82h Buildfile: build.xml prepare: compile: run.client_82h: [java] THE WHERE CLAUSE AND IS EMPTY [java] -------------------------------- [java] SELECT OBJECT( crs ) FROM Cruise crs [java] WHERE crs.reservations IS EMPTY [java] [java] SELECT OBJECT( crs ) FROM Cruise crs [java] WHERE crs.reservations IS NOT EMPTY [java] Alaskan Cruise is not empty. [java] Atlantic Cruise is not empty.
The Client_82i
program implements the queries
illustrated in the EJB book, in the section of Chapter 8 called Section 8.3.17.
The business logic for this example is implemented in
com.titan.test.Test82Bean
, in the
test82i( )
method.
Two Cruise EJB finder methods demonstrate how to use EJB QL to find whether or not an entity is a member of a relationship.
EJB: |
|
finder method: |
|
query: |
|
| |
| |
EJB: |
|
finder method: |
|
query: |
|
| |
|
Client_82i
invokes these queries and displays
their output. To run it, invoke the Ant task
run.client_82i
. The output should look something
like this:
C:workbookex08_2>ant run.client_82i Buildfile: build.xml prepare: compile: run.client_82i: [java] THE WHERE CLAUSE AND MEMBER OF [java] -------------------------------- [java] SELECT OBJECT( crs ) FROM Cruise crs, [java] IN (crs.reservations) res, Customer cust [java] WHERE cust = ?1 ANT cust MEMBER OF res.customers [java] Use Bill Burke [java] Bill is member of Alaskan Cruise [java] Bill is member of Atlantic Cruise [java] [java] SELECT OBJECT( crs ) FROM Cruise crs, [java] IN (crs.reservations) res, Customer cust [java] WHERE cust = ?1 ANT cust NOT MEMBER OF res.customers [java] Use Nomar Garciaparra [java] Nomar is not member of Atlantic Cruise
The Client_82j
program implements the queries
illustrated in the EJB book, in the section of Chapter 8 entitled Section 8.3.18.
The business logic for this example is implemented in
com.titan.test.Test82Bean
, in the
test82j( )
method.
One Customer EJB finder method is used to query all Customers with a hyphenated name.
EJB: |
|
finder method: |
|
query: |
|
|
Client_82j
invokes this query and displays its
output. To run it, invoke the Ant task
run.client_82j
. The output should look something
like this:
C:workbookex08_2>ant run.client_82j Buildfile: build.xml prepare: compile: run.client_82j: [java] THE WHERE CLAUSE AND LIKE [java] -------------------------------- [java] SELECT OBJECT( c ) FROM Customer c [java] WHERE c.lastName LIKE '%-%' [java] Monson-Haefel
The Client_82k
program implements the queries
illustrated in the EJB book, in the section of Chapter 8 called Section 8.3.19.
The business logic for this example is implemented in
com.titan.test.Test82Bean
, in the
test82k( )
method.
One Customer EJB finder method demonstrates the use of a couple of functional expressions.
EJB: |
|
finder method: |
|
query: |
|
| |
|
Client_82k
invokes this query and displays its
output. To run it, invoke the Ant task
run.client_82k
. The output should look something
like this:
C:workbookex08_2>ant run.client_82k Buildfile: build.xml prepare: compile: run.client_82k: [java] THE WHERE CLAUSE AND FUNCTIONAL EXPRESSIONS [java] -------------------------------- [java] SELECT OBJECT( c ) FROM Customer c [java] WHERE LENGTH(c.lastName) > 6 AND [java] LOCATE(c.lastName, 'Monson') > -1 [java] Labourey [java] Garciaparra [java] Monson-Haefel
The Client_82m
program implements the queries
illustrated in the EJB book, in the section of Chapter 8 called Section 8.3.19.2. The business logic for this
example is implemented in
com.titan.test.Test82Bean
, in the
test82m( )
method.
The first query finds the count of all customer Zip codes that are in
the Boston, MA area. Most Zip codes in the Boston area start with
0211, so a LIKE
statement is used. A custom home
method from the Customer EJB’s home interface is
used to wrap the private ejbSelect
queries defined
in the same bean.
query: SELECT DISTINCT COUNT(c.homeAddress.zip) FROM Customer AS c WHERE c.homeAddress.zip LIKE '0211%' ejbSelect method: ejbSelectCountOfBostonZips( ) custom home method: countOfBostonZips( ) ejbHome method: ejbHomeCountOfBostonZips( )
The second gets the maximum value of the
amountPaid
field of all Reservation EJBs. A custom
home method from Reservation EJB is used to wrap the private
ejbSelect
queries that are declared within the
bean class.
query: SELECT SELECT MAX(r.amountPaid) FROM Reservation As r ejbSelect method: ejbSelectMaxAmountPaid( ) custom home method: maxAmountPaid( ) ejbHome method: ejbHomeMaxAmountPaid( )
The third query gets the sum of all reservations paid based on a
cruise passed in as a parameter. A custom home method from Cruise EJB
is used to wrap the private ejbSelect
queries that
are declared within the bean class.
query: SELECT SUM( r.amountPaid) FROM Cruise c, IN( c.reservations) AS r WHERE c = ?1 ejbSelect method: ejbSelectSumReservation(CruiseLocal cruise) custom home method: sumReservation(CruiseLocal cruise) ejbHome method: ejbHomeSumReservation(CruiseLocal cruise)
The final query takes the average of all reservations paid based on a
cruise passed in as a parameter. A custom home method from Cruise EJB
is used to wrap the private ejbSelect
queries that
are declared within the bean class.
query: SELECT AVG( r.amountPaid) FROM Cruise c, IN( c.reservations) AS r WHERE c = ?1 ejbSelect method: ejbSelectAveragePaidReservation(CruiseLocal cruise) custom home method: averagePaidReservation(CruiseLocal cruise) ejbHome method: ejbHomeAveragePaidReservation(CruiseLocal cruise)
Client_82m invokes these queries and displays
their output. To run it, invoke the Ant task
run.client_82m
. The output should look something
like this:
C:workbook...x08_2>ant run.client_82m Buildfile: build.xml prepare: compile: run.client_82m: [java] EJB 2.1 Aggregate Functions [java] -------------------------------- [java] SELECT DISTINCT COUNT(c.homeAddress.zip) [java] FROM Customer AS c [java] WHERE c.homeAddress.zip LIKE '0211%' [java] count of Boston zip codes: 2 [java] -------------------------------- [java] SELECT MAX(r.amountPaid) [java] FROM Reservation As r [java] max amount paid for a reservation: $40000.0 [java] -------------------------------- [java] SELECT SUM( r.amountPaid) [java] FROM Cruise c, IN( c.reservations) AS r [java] WHERE c = ?1 [java] Sum of Alaskan Cruise reservations: $40000.0 [java] -------------------------------- [java] SELECT AVG( r.amountPaid) [java] FROM Cruise c, IN( c.reservations) AS r [java] WHERE c = ?1 [java] Average of Atlantic Cruise reservations: $10000.0
The Client_82n
program implements the queries
illustrated in the EJB book, in the section of Chapter 8 entitled Section 8.3.20. The business logic for this
example is implemented in
com.titan.test.Test82Bean
, in the
test82n( )
method.
One Customer EJB finder method demonstrates the use of
ORDER
BY
with the
DESC
keyword.
EJB: Customer finder method: findByOrderedLastName( ) query: SELECT OBJECT( c ) FROM Customer AS c ORDER BY c.lastName DESC
Client_82n invokes this query and displays its
output. To run it, invoke the Ant task
run.client_82n
. The output should look something
like this:
C:workbook...x08_2>ant run.client_82n Buildfile: build.xml prepare: compile: run.client_82n: [java] EJB 2.1 ORDER BY Clause [java] -------------------------------- [java] SELECT OBJECT( c ) FROM Customer AS c [java] ORDER BY c.lastName DESC [java] Swift [java] Monson-Haefel [java] Labourey [java] Garciaparra [java] Fleury [java] Burke
One of the features seriously lacking in EJB QL is the ability to do dynamic queries at run time. This example shows how you can do dynamic queries on Customer EJBs with JBoss CMP 2.0.
First, you must declare an ejbSelectGeneric( )
method that will invoke your dynamic queries and an
ejbHome
wrapper method so that the test program
can invoke it.
public abstract class CustomerBean implements javax.ejb.EntityBean { public abstract SetejbSelectGeneric
(String jbossQl, Object[] arguments) throws FinderException; public SetejbHomeDynamicQuery
(String jbossQL, Object[] arguments) throws FinderException { return ejbSelectGeneric(jbossQL, arguments); }
Next, declare your ejbHome
wrapper method in
CustomerHomeLocal.java
:
public interface CustomerHomeLocal extends javax.ejb.EJBLocalHome
{
...
public Set dynamicQuery
(String jbossQl, Object[] arguments)
throws FinderException;
}
The ejbSelectGeneric( )
method must be defined in
the ejb-jar.xml
deployment descriptor. Notice
that the <ejb-ql>
value is empty.
<ejb-jar> <enterprise-beans> <entity> <ejb-name>CustomerEJB
</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> <ejb-ql></ejb-ql> </query>
Finally, in jbosscmp-jdbc.xml
, tell JBoss that
the ejbSelectGeneric( )
method is dynamic:
<jbosscmp-jdbc>
<enterprise-beans>
<entity>
<ejb-name>CustomerEJB</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>
The business logic for this example is implemented in
com.titan.test.Test82Bean
, in the
test82Dynamic( )
method.
public String test82Dynamic( ) throws RemoteException
{
...
// obtain Home interfaces
InitialContext jndiContext = getInitialContext( );
Object obj = jndiContext.lookup("CustomerHomeLocal");
CustomerHomeLocal customerHome = (CustomerHomeLocal)obj;
...
Object[] params = {};
Set customers =
customerHome.dynamicQuery
("SELECT OBJECT( c ) FROM Customer c " +
"WHERE c.lastName LIKE 'B%'", params);
...
}
The test82Dynamic( )
method generates a dynamic
query string and invokes the dynamicQuery( )
method
defined in the CustomerHomeLocal
interface.
Client_82Dynamic
invokes
test82Dynamic( )
and displays its output. To run
it, invoke the Ant task run.client_82dynamic
. The
output should look something like this:
C:workbookex08_2>ant run.client_82dynamic Buildfile: build.xml prepare: compile: run.client_82dynamic: [java] JBoss Dynamic Queries [java] -------------------------------- [java] SELECT OBJECT( c ) FROM Customer c [java] WHERE c.lastName LIKE 'B%' [java] Burke
In Section 8.4
of Chapter 8, Richard Monson-Haefel talks about
some of the limitations of EJB QL. In the JBoss CMP 2.0
implementation, EJB QL is just a subset of a larger JBoss query
language. JBoss QL does a great job of filling in some of the gaps in
the EJB QL spec. Features such as the ability to use parameters
within IN
and LIKE
clauses are
just a few of the enhancements JBoss has implemented. Please review
the advanced CMP 2.0 documentation available at the JBoss web site,
http://www.jboss.org, for more
information on these features.