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
• Date compares
• Dynamic String likes

6
Defined in ejb-jar.xml

• Person
• Personhema-name
• firstNamename
• lastNameame
• ...
• ...
• ...

7
Defined in ejb-jar.xml

• findByNamee
• java.lang.Stringmethod-param
• java.lang.Stringmethod-param
• SELECT OBJECT(p) FROM Person p
• WHERE p.firstName ?1 AND
  p.lastName ?2

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
• returns empty Collection when no objects 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
• Throws ObjectNotFoundException when

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
• 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 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
• ejbSelectPhoneNumbers/method-name
• SELECT DISTINCT p.phoneNumber FROM
  Person p

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.identity.phoneNumber FROM Borrower b
  //illegal
• SELECT b.checkouts.outSQLDate FROM Borrower b
  //illegal and doesnt make sense

17
FROM IN Operator

• FROM IN() AS
• SELECT OBJECT(co) FROM Borrower b, IN
  (b.checkouts) AS co
• SELECT p.phoneNumber FROM Borrower b, IN
  (b.identity) AS p
• SELECT co.outSQLDateFROM Borrower b,
  IN(b.checkouts) AS co
• Assigns CMR fields to 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

• findByNamee
• java.lang.Stringmethod-param
• java.lang.Stringmethod-param
• SELECT OBJECT(p) FROM Person pWHERE p.firstName
  ?1 AND p.lastName ?2

21
CDATA Sections

• SELECT OBJECT(c) FROM Checkout c
• WHERE c.outSQLDate

22
WHERE Clauses

• Arithmetic Operators
• SELECT OBJECT(s) FROM Seat sWHERE (r.price
  .04) 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 , , NO relative
  comparisons
• numeric , , ,

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(p)WHERE p.firstName
  IN (cat, thing)
• SELECT OBJECT(p) FROM Person(p)WHERE p.firstName
  NOT IN (cat, thing)
• IS NULL
• SELECT OBJECT(b) FROM Borrower(b)WHERE
  b.identity IS NULL

24
WHERE Clauses (cont.)

• IS EMPTY
• SELECT OBJECT(b) FROM Borrower bWHERE
  b.checkouts IS EMPTY
• MEMBER OF
• SELECT Object(b) FROM Borrower b,
  IN(b.checkouts) coWHERE co.id ?1 AND
  co MEMBER OF b.checkouts
• find borrowers who have the identified checkout
• 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, ...