EJB QL Sample Application
Table of Contents
Overview of the Sample
Application
|
EJB QL (Enterprise JavaBeans
Query Language) is a Query Language provided for navigation
across a network of enterprise beans and dependent objects defined by
means of container managed persistence. EJB QL was introduced in the EJB
2.0 specification. The EJB QL defines finder methods for entity beans
with container managed persistence and is portable across containers and
persistence managers. EJB QL is used with two types of finder methods:
- Finder methods that are defined in the home interface of an entity
bean and which return entity objects
- Select methods, which are not exposed to the client, but which are
used by the Bean Provider to select persistent values that are maintained
by the Persistence Manager or to select entity objects that are related
to the entity bean on which the query is defined.
According to EJB 2.0 spec; EJB QL, is used to define
finder queries for entity beans with container managed persistence. EJB
QL lets the Bean Provider specify finder methods in a portable way. It
is a specification language that can be compiled to a target language,
such as SQL, of a persistent store used by a persistence manager. This
allows the responsibility for the execution of finder queries to be shifted
to the native language facilities provided for the persistent store (e.g.,
RDBMS), instead of requiring finder queries to be executed directly on
the persistent manager’s representation of the entity beans’
state. As a result, finder methods are both portable and optimizable.
Oracle supports the EJB QL specification in its OC4J
container. From OC4J 9.0.4 onwards, Oracle has also added support for
java.util.Date, java.sql.Date, java.sql.Time and java.sql.TimeStamp objects
within the EJB QL. These are allowed in an EJB QL binary expression, such
as equality expressions.
This sample application demonstrates the features and
usage of EJB QL with OC4J. The sample application demonstrates a wide
range of EJB QL queries starting from a basic query to a more advanced
query using the Date object. It also demonstrates the usage of ejbSelect
methods with EJB QL.
The sample application is based on the scenario of
a Credit Card Account. Through this application, a user can create an
account and specify the Credit Limit for the account. The screenshot for
this is as shown below
This transaction occurs with the help on a CMP Entity
Bean which is mapped to the corresponding database record.
After the accounts are created, the user can query the created accounts
in various ways using different parameters. The screenshot for this page
is as follows
The different searching mechanisms are as described
below :
- Display all accounts. This uses a very basic level of EJB QL.
It queries all the Entity beans and gets the account information which
is then displayed to the user.
|
The EJB-QL query specified in ejb-jar.xml
to achieve this is as follows :
<query>
<description>This query retrieves
all the accounts</description>
<query-method>
<method-name>findByAllAccounts</method-name>
</query-method>
<ejb-ql>select distinct object(ua)
from UserAccount ua</ejb-ql>
</query>
|
|
- Search Accounts by Credit Limit. This uses an EJB QL with a
BETWEEN clause. Here, the user passes the lower and the higher value
of the Credit Limit. The EJB QL then retrieves all the accounts whose
Credit Limit falls between these values.
|
The EJB-QL query specified in ejb-jar.xml
to achieve this is as follows :
<query>
<description>This query retrieves
all the accounts that are present within the entered Credit
Limit</description>
<query-method>
<method-name>findByMinMaxCreditLimit</method-name>
<method-params>
<method-param>java.lang.Long</method-param>
<method-param>java.lang.Long</method-param>
</method-params>
</query-method>
<ejb-ql>select distinct object(ua)
from UserAccount ua where ua.creditlimit between ?1 and ?2</ejb-ql>
</query>
|
|
- Search Accounts by Date. This demonstrates the usage of Date
object in the EJB QL. The user enters any Date in the format DD-MON-YYYY
and the EJB QL retrieves all the accounts that were created on this
Date.
|
The EJB-QL query specified in ejb-jar.xml
to achieve this is as follows :
<query>
<description>This finds the
Account by its Created Date</description>
<query-method>
<method-name>findByCreatedDate</method-name>
<method-params>
<method-param>java.util.Date</method-param>
</method-params>
</query-method>
<ejb-ql>select distinct object(ua)
from UserAccount ua where ua.createddate = ?1</ejb-ql>
</query>
|
|
- Search for a Gold Customer. Here, the user can search for all
the customers that have the highest credit limit. This demonstrates
the usage of ejbSelect method for post processing. The ejbSelect method
first retrieves all the accounts and then processes them to select just
the top accounts. The number of accounts to be returned is specified
by the user.
|
The EJB-QL query specified in ejb-jar.xml
to achieve this is as follows :
<query>
<description>This query selects
all the accounts. Post Processing is done on here before displaying
to the user</description>
<query-method>
<method-name>ejbSelectByTopAccounts</method-name>
</query-method>
<ejb-ql>select distinct object(ua)
from UserAccount ua</ejb-ql>
</query>
|
|
- Get the Credit Limit for a customer. Through this option, the
user can retrieve the Credit Limit of any customer. The user enters
the Account Number of which he/she wants to see the Credit Limit. This
also uses the ejbSelect method, but here, instead of retrieving the
Collection of the accounts (as happens normally); the ejbSelect retrieves
directly the Credit Limit Field of the specified Account. This is not
possible with normal ejbFind (Finder methods).
|
The EJB-QL query specified in ejb-jar.xml
to achieve this is as follows :
<query>
<description>This query retrieves
the Credit Limit for the specified Account</description>
<query-method>
<method-name>ejbSelectCreditLimit</method-name>
<method-params>
<method-param>java.lang.Long</method-param>
</method-params>
</query-method>
<ejb-ql>select ua.creditlimit
from UserAccount ua where ua.accountnumber = ?1</ejb-ql>
</query>
|
|
Please refer to Install.html
for step-by-step instructions on extracting the files, deploying
and running the application successfully.
The directory structure of the deliverable ejbqlsample.jar
is as shown below.
| Directory |
Files |
Description |
| Ejbql/docs |
Readme.html |
This file. |
| Install.html |
This file has instructions required
to install and deploy the sample application. |
| Ejbql/docs/images |
CreateAccounts.gif
SearcAccounts.gif |
The Screenshot image files
used in this file. |
| Ejbql/public_html |
All JSP Files
|
These JSP files create
the view for the sample application. |
| Ejbql/public_html/WEB-INF |
web.xml |
This XML file is defined
according to the J2EE standards. The XML defines the index JSP file
and the EJB references in this application. |
Ejbql/sql
|
Install.sql |
This SQL file creates the table (UserAccount)
and the sequence required by the sample application. |
Ejbql/src/META-INF
|
ejb-jar.xml
|
This XML file is defined according
to the J2EE standards. This XML file defines all the EJB QLs used
within the application. |
orion-ejb-jar.xml
|
This XML file contains
OC4J specific Entity Bean Mapping information. |
| data-sources.xml |
This XML file contains the database
connection information. |
| application.xml |
This XML file drives the application
by defining the EJB jar file and Web war file modules used in this
application. |
| orion-application.xml |
This XML file is required to specify
the location of data-sources.xml to OC4J. |
Ejbql/src/oracle/otnsamples/ejbql
|
All Java Files |
These java files constitutes the
EJB (CMP Entity Bean) on the database table UserAccount.
|
Ejbql/src/oracle/otnsamples/ejbql/classes
|
InvokeEJB.java
|
This Java class has methods to access
the EJB. Every request from the client goes through this class which
in turn queries the EJB and returns the values to be displayed to
the user.
|
|
