EJB Query Language

1
EJB Query Language

• Source
• Enterprise JavaBeans, 3rd Edition, Richard
  Monson-Haefel

2
Finder Methods

• Container can implement findByPrimaryKey
• public interface PersonLocalHome extends
  EJBLocalHome
• PersonLocal create(String id) throws
  CreateException
• PersonLocal create(String id,
• String firstName, String lastName, String
  address, String phoneNumber)
• throws CreateException
• PersonLocal findByPrimaryKey(PersonPK pkey)
  throws FinderException
• public interface PersonRemoteHome extends EJBHome
  
• PersonRemote create(String id) throws
  CreateException, RemoteException
• PersonRemote create(Person values) throws
  CreateException, RemoteException
• PersonRemote findByPrimaryKey(PersonPK pkey)
• throws FinderException, RemoteException

3
Finder Methods

• Container cannot implement custom finders
• public interface PersonLocalHome extends
  EJBLocalHome
• ...
• PersonLocal findByName(String firstName,
  String lastName)
• throws FinderException
• Collection findAll() throws
  FinderException
• Collection findByAddress(String address)
  throws FinderException
• public interface PersonRemoteHome extends EJBHome
  
• ...
• PersonRemote findByName(String firstName,
  String lastName)
• throws FinderException, RemoteException
• Collection findAll() throws FinderException,
  RemoteException
• Collection findByAddress(String address)
  throws FinderException, RemoteException

4
EJB Query Language

• There was no portable way to express query
  methods prior to EJB2.0
• Used to express the behavior of query methods
• custom finders
• declared in EJBHome/EJBLocalHome
• Select methods (added in EJB2.0)
• as functional as custom finders, but adds more
  power
• can return objects, object attributes, and
  objects not of the scoping class
• usable only by the EJB class
• declared in EJB class as an abstract method

5
EJB Query Language (cont.)

• Expressed in terms of the Java Abstract Schema
  and not the database schema
• Robust, while remaining portable across databases
  of different technologies
• However, not without limitations
• Ordering (Addressed in EJB 2.1)
• Date compares
• Dynamic String likes

6
Defined in ejb-jar.xml

• ltejb-jargt
• ltenterprise-beansgt
• ltentitygt
• ltejb-namegtPersonlt/ejb-namegt
• ltabstract-schema-namegtPersonlt/abstract-sc
  hema-namegt
• ltcmp-fieldgt ltfield-namegtfirstNamelt/field-
  namegt lt/cmp-fieldgt
• ltcmp-fieldgt ltfield-namegtlastNamelt/field-n
  amegt lt/cmp-fieldgt
• ...
• ltresource-refgt...lt/resource-refgt
• ltquerygt
• ...
• lt/querygt
• lt/entitygt
• lt/enterprise-beansgt
• lt/ejb-jargt

7
Defined in ejb-jar.xml

• ltquerygt
• ltquery-methodgt
• ltmethod-namegtfindByNamelt/method-nam
  egt
• ltmethod-paramsgt
• ltmethod-paramgtjava.lang.Stringlt/
  method-paramgt lt!-- fname --gt
• ltmethod-paramgtjava.lang.Stringlt/
  method-paramgt lt!-- lname --gt
• lt/method-paramsgt
• lt/query-methodgt
• ltejb-qlgt
• SELECT OBJECT(p) FROM Person p
• WHERE p.firstName ?1 AND
  p.lastName ?2
• lt/ejb-qlgt
• lt/querygt

8
Find Methods

• Invoked by EJB Clients to obtain local or remote
  object references to specific objects
• Return object reference for single object finds
• public interface PersonLocalHome extends
  EJBLocalHome
• PersonLocal findByName(String firstName, String
  lastName)
• throws FinderException
• public interface PersonRemoteHome extends EJBHome
  
• PersonRemote findByName(String firstName,
  String lastName)
• throws FinderException, RemoteException
• Throws FinderException on application error
• Throws ObjectNotFoundException when object not
  found

9
Find Methods (cont.)

• Return Collection type for multi-object finds
• public interface PersonLocalHome extends
  EJBLocalHome
• Collection findByAddress(String address)
  throws FinderException
• public interface PersonRemoteHome extends EJBHome
  
• Collection findByAddress(String address)
  throws FinderException, RemoteException
• Collections may return duplicates (use DISTINCT
  in query expression to eliminate)
• Throws FinderException on application error
• Returns empty Collection when no objects found

10
Select Methods

• Private query methods of the EJB class
• not directly exposed in any of the beans
  interfaces
• Operate within the transaction that calls it
• Finder methods execute within the transaction
  context individually defined for them
• Can return CMP fields as well as CMR objects
• ltTypegt for single-object selects
• java.util.Collection for multi-object selects
• java.util.Set for distinct multi-object selects
• List and Map are being considered for future
  releases

11
Select Methods (cont.)

• Returns either remote or local object
• local by default
• remote if ltresult-type-mappinggt is selected

12
Select Method Example

• Declare abstract Select method in the EJB Class
• public abstract class PersonEJB implements
  PersonLBI, PersonBI, EntityBean
• public abstract Set ejbSelectPhoneNumbers()
  throws FinderException
• Define the Select Method in ejb-jar.xml
• ltquerygt
• ltquery-methodgt
• ltmethod-namegtejbSelectPhoneNumberslt
  /method-namegt
• ltmethod-params/gt
• lt/query-methodgt
• ltejb-qlgt
• SELECT DISTINCT p.phoneNumber FROM
  Person p
• lt/ejb-qlgt
• lt/querygt

13
Select Method Example

• Declare a home method that accesses the Select
  method
• public interface PersonRemoteHome extends EJBHome
  
• Set getPhoneNumbers() throws FinderException,
  RemoteException
• public interface PersonLocalHome extends
  EJBLocalHome
• Set getPhoneNumbers() throws FinderException
• Define the method in the EJB Class
• public Set ejbHomeGetPhoneNumbers() throws
  FinderException
• return ejbSelectPhoneNumbers()

14
EJB QL Basics

• SELECT OBJECT(p) from Person AS p
• selects all person objects from the database
• returns collection of either local/remote object
  references
• OBJECT() required for non-Path expressions
• Person comes from abstract-schema-name
• AS is optional
• p must not appear in abstract schema of class
• p is not case-sensitive
• SELECT OBJECT(person) from Person person //illegal

15
EJB QL with Paths

• SELECT p.phoneNumber FROM Person p
• selects phone number attributes for all persons
  in the database
• returns a collection of java.lang.Strings (based
  on abstract schema mapping for phoneNumber)
• SELECT b.identity FROM Borrower b
• selects person objects for all borrowers in the
  database
• returns a collection of PersonLocal (or
  PersonRemote) (based on context of call or
  return-type-mapping attribute)
• OBJECT() not used here

16
FROM IN Operator

• Illegal to navigate relationship from SELECT
  clause
• SELECT b.checkouts FROM Borrower b //illegal
• SELECT b.checkouts.outSQLDate FROM Borrower b
  //illegal and doesnt make sense

17
FROM IN Operator

• FROM IN() AS ltnamegt
• SELECT OBJECT(co) FROM Borrower b, IN
  (b.checkouts) AS co
• SELECT co.outSQLDateFROM Borrower b,
  IN(b.checkouts) AS co
• Assigns CMR fields to ltnamegt identifier

18
DISTINCT

• SELECT DISTINCT p.phoneNumber FROM Person p
• DISTICT keyword prevents the query from returning
  duplicate elements
• redundant for Set return types

19
Literal WHERE Clauses

• SELECT OBJECT(p)FROM Person pWHERE p.firstName
  cat
• escape with to implement searching for Ocat
• WHERE p.firstName Ocat

20
Parameterized Where Clauses

• ltquery-methodgt
• ltmethod-namegtfindByNamelt/method-nam
  egt
• ltmethod-paramsgt
• ltmethod-paramgtjava.lang.Stringlt/
  method-paramgt
• ltmethod-paramgtjava.lang.Stringlt/
  method-paramgt
• lt/method-paramsgt
• lt/query-methodgt
• SELECT OBJECT(p) FROM Person pWHERE p.firstName
  ?1 AND p.lastName ?2

21
CDATA Sections

• ltejb-qlgt
• lt!CDATA
• SELECT OBJECT(c) FROM Checkout c
• WHERE c.outSQLDate lt ?1
• gt
• lt/ejb-qlgt

22
WHERE Clauses

• Arithmetic Operators
• SELECT OBJECT(s) FROM Seat sWHERE (s.price
  .04) gt 1.00
• Logical Operators
• SELECT OBJECT(p) FROM Person pWHERE p.firstName
  ?1 AND p.lastName ?2
• Comparisons (must be of same type)
• boolean and String , ltgt, NO relative
  comparisons
• numeric , gt, gt, lt, lt, and ltgt

23
WHERE Clauses (cont.)

• BETWEEN
• SELECT OBJECT(s) FROM Seat sWHERE s.price
  BETWEEN 1.00 AND 10.00
• IN
• SELECT OBJECT(p) FROM Person pWHERE p.firstName
  IN (cat, thing)
• SELECT OBJECT(p) FROM Person pWHERE p.firstName
  NOT IN (cat, thing)
• IS NULL
• SELECT OBJECT(b) FROM Borrower bWHERE b.identity
  IS NULL

24
WHERE Clauses (cont.)

• IS EMPTY
• SELECT OBJECT(b) FROM Borrower bWHERE
  b.checkouts IS EMPTY
• MEMBER OF (p. 248 Monson-Haefel)
• SELECT OBJECT(crs) FROM Cruise crs,
  IN(crs.reservations) res,
• 
  Customer cust
• WHERE cust?1 AND cust MEMBER OF
  res.customers
• Finds whether a Customer (input parameter to
  finder) is a member of any reservation-customer
  relationships
• LIKE
• SELECT Object(p) FROM Person pWHERE p.firstName
  like c //finds cat
• WHERE p.firstName like c_t //finds cat
• WHERE p.firstName like \c //finds c...

25
EJB QL Shortfalls

• No ORDER By
• disconnect between Java and database string
  compares
• No Date compare
• must implement as a long compare storing
  Date.getTime()
• No dynamic LIKE
• identified solution LIKE (CONCAT('foo', ?1))
• Limited Functions
• no MAX, MIN, SUM, UPPER, DOW, MONTH, ...