Enterprise JavaBeans Query Language in Sun Java System Application Server 8 and J2EE 1.4 SDK

Print-friendly Version

Most business applications today rely on a bedrock of databases. Outside the Java 2 Platform, Enterprise Edition (J2EE) environment, you as developers must possess expertise in both application components and databases. The functionality of J2EE servers' Container Managed Persistence (CMP) shields you from the database aspect so that you can concentrate on business logic and become more productive. In CMP, persistent data are represented by Enterprise JavaBeans (EJB) objects, which you can query through finder methods characterized by the EJB Query Language (EJB QL). In other words, the application server generates the corresponding Structured Query Language (SQL) to retrieve data from a database.

Sun Java System Application Server 8 (called Application Server 8 for short) and J2EE 1.4 SDK support the latest EJB QL standard in the EJB 2.1 Specification.

This paper introduces the history and benefits of EJB QL along with the concepts of finders and selectors. It also cites the key syntaxes supported by Application Server 8 and illustrates them with examples.

• A Word of History
• Benefits of EJB QL
• Case Study
• Finders and Selectors
• EJB QL Syntax
• Basic Constructs
• Arithmetic and String Constructs in WHERE Clause
• Collection Constructs
• Ordering Constructs
• Aggregate Functions in the SELECT Clause
• Conclusion
• References

Before EJB QL was first introduced in EJB 2.0 as part of J2EE 1.3 in 2001, no standard ways were available for defining finder methods. For solutions, we looked to proprietary software from various application vendors.

EJB 2.0 presents a standard mechanism, whereby you can specify finder methods through a string with EJB QL, an SQL-like syntax. Those methods reside in the form of <ejb-ql> elements in the ejb-jar.xml file and are used by Application Server 8 during deployment. In addition, you can perform these tasks:

• Define Container-Managed Relationships (CMRs) among EJB objects.
  
  
• Navigate from one EJB object to another related object by means of a path expression with the standard dot notation.

Furthermore, EJB QL defines selector methods with which you can pinpoint more specific data, such as CMP fields, CMRs, or aggregate functions. Introduced in EJB 2.1, aggregate functions enable you to compute sums, averages of CMP fields, and such.

EJB QL lowers the total cost of ownership, commonly called TCO, in the following ways:

• Enhancement of productivity -- EJB QL is a declarative language. That means you need to specify only the language in the ejb-jar.xml file with Application Deployment Tool (deploytool) or a text editor. You need not write any Java code at all.
  
  
• Reduction of deployment cycle -- In case of changes to EJB QL, you just need to update the ejb-jar.xml, repackage, and redeploy the enterprise archive (EAR) files. You need not recompile any Java code or restart Application Server 8.
  
  
• Ease of use -- The syntax of EJB QL is very similar to that of SQL. You can quickly come up to speed.
  
  
• Portability -- Compared to EJB 1.0, finders and selectors defined by EJB QL are more portable. Applications with EJB QL can deploy on J2EE 1.4 platform-compliant application servers that contain different databases.
  
  
• Standardized construction -- As a standard query language for the EJB architecture, EJB QL is implemented by all J2EE 1.4 application servers (also J2EE 1.3 application servers for a subset of features). Thanks to the standardization, you can recycle your knowledge in different projects, thus accelerating the development cycle.

In effect, EJB QL can save both development time and cost--a critical factor for the success of a project.

FIGURE 1 illustrates an example of a simple department-employee-project set of EJB objects, all local. The subsections that follow describe several important EJB QL concepts.

FIGURE 1: Example of Department-Employee-Project Set of EJB Objects

In CMP, you can use two methods--finders and selectors--to query an EJB object or its related data or collection. Each finder or selector, except findByPrimaryKey, is described by an <ejb-ql> element in ejb-jar.xml. Here are the definitions:

• Finders -- These are query methods, defined on the home or local interface, that locate an entity object or a collection of objects. For finders, you only need to specify the signature of the methods in the interface and the EJB QL code in the corresponding <ejb-ql> element.
  
  
• Selectors -- These are query methods within an entity bean instance that locate entity objects, CMP fields, CMRs, or results of aggregate functions. For selectors, you need only specify the signature of methods in the entity bean class and the EJB QL code in the corresponding <ejb-ql> element.

In EJB QL, each EJB object is referenced by the name of an abstract schema specified in the <abstract-schema-name> element of ejb-jar.xml.

TABLE 1 describes the elements of finders and selectors from the user standpoint.

Element	Finder	Selector
Method name	find<METHOD>	ejbSelect<METHOD>
Scope	Definition in the home or local interface Exposure to clients	An abstract method in the entity bean's class No exposure to clients: internal to the bean class
Return value	Entity bean of this type Collection of entity beans of this type	Entity bean of any type defined in the same ejb-jar file Collection or set of entity beans of any type defined in the same ejb-jar file CMP field, collection, or set of CMP fields Result of aggregate functions
Corresponding <ejb-ql> element	A requirement, except for findByPrimaryKey	A requirement
EJB QL	Line must start with either of the following: SELECT OBJECT(...) SELECT DISTINCT OBJECT(...)	No restrictions as long as valid

For details, see section 10.5.7 of the Enterprise JavaBeans Specification 2.1.

Based on the example in FIGURE 1, you can define a finder to retrieve the names of the employees for a given salary range, like this:

public Collection findBySalaryRange(double low, double high)
           throws FinderException; 
 

The corresponding XML elements in the ejb-jar.xml file are shown below:

<abstract-schema-name>Employee</abstract-schema-name>
...
<query>
  <description>Find employee in a given salary range</description>
  <query-method>
    <method-name>findBySalaryRange</method-name>
    <method-params>
      <method-param>double</method-param>
      <method-param>double</method-param>
    </method-params>
  </query-method>
  <ejb-ql>SELECT OBJECT(e) FROM Employee e WHERE e.salary BETWEEN ?1 AND ?2</
ejb-ql>
</query>
 

As in SQL, EJB QL consists of four clauses: SELECT, FROM, WHERE, and ORDER BY. The EJB QL Backus Naur Form is in the EJB Specification.

This subsection describes the key EJB QL constructs.

TABLE 2 defines the data types used in our case study.

Data Type	Definitions
Numeric	byte, short, char, int, long, float, double
Boxed numeric	java.lang.Byte, java.lang.Short, java.lang.Character, java.lang.Integer, java.lang.Long, java.lang.Float, java.lang.Double
integerType	int or java.lang.Integer
doubleType	double or java.lang.Double

Recall that EJB QL is similar to SQL. They share the same basic functionality, such as field comparisons and conditional statements, as follows:

• SELECT [DISTINCT] [OBJECT] path expression
  
  EJB QL's SELECT clause must match the return type of the finder and selector. Use the keyword OBJECT(...) to indicate that the return result is an entity bean or its collection. The path expression must be single-valued.
  
  See the following example:
  
  

  Selector	public Department ejbSelectTest() throws FinderException;
  EJB QL	SELECT OBJECT(e.department) FROM Employee e     WHERE e.department.deptId = 1

  
  

  The syntax in the following EBJ QL queries for the preceding selector is incorrect because of a mismatched return type:

  SELECT OBJECT(e) FROM Employee e WHERE e.empId = 1 SELECT e.lastName FROM Employee e WHERE e.empId = 1

  
  

  Below is another example of incorrect EBJ QL syntax, invalid because the path expression is not single-valued:

  SELECT d.employees FROM Department d WHERE d.name = ?1

  
  

  You must use the navigation IN(...) (see "Collection Constructs.").

  Also, use the DISTINCT keyword for deleting duplicates in the return result.

  For example, you can find out the distinct campus names for the employees in a given department as follows:

  Selector	public Set ejbSelectCampusByDepartmentId(int deptId)     throws FinderException;
  EJB QL	SELECT DISTINCT e.campus FROM Employee e WHERE e.department.deptId = ?1

  
  
• Comparison: <, <=, >=, =, <>, IS [NOT] NULL
  
  

  Like SQL, EJB QL has a syntax other than <> to check whether the column is NULL. The syntax is IS [NOT] NULL.

  For example, you can look for the names of the employees who are not assigned to a campus, as follows:

  Finder	public Collection findEmployeeWithoutCampus() throws FinderException;
  EJB QL	SELECT OBJECT(e) FROM Employee e WHERE e.campus IS NULL

  
  
• Comparison of numeric types, their boxed equivalents, BigDecimal, BigInteger, and Date-related classes
  
  

  In EJB QL, you can compare primitive types, their boxed equivalents, BigDecimal, and BigInteger, for example, an int with a Double or BigInteger. Here is an example of a comparison of numeric types:

  Finder	public Collection findBySalaryRange2(Double low, BigDecimal high)     throws FinderException;
  EJB QL	SELECT OBJECT(e) FROM Employee e     WHERE e.salary >= ?1 AND e.salary <= ?2

  
  

  In comparing Date classes, remember that in EJB QL, you can regard the instances of two different Date classes as the same--if they represent the same time. For example, the following finders with different signatures and the same EJB QL return the same results for input that represents the same time, assuming that the database supports the corresponding comparison.

  Finder	public Collection findByHireDate(java.util.Date hdate)     throws FinderException;
  	public Collection findByHireDate(java.sql.Date hdate)     throws FinderException;
  	public Collection findByHireDate(java.sql.Time hdate)     throws FinderException;
  	public Collection findByHireDate(java.sql.Timestamp hdate)     throws FinderException;
  EJB QL	SELECT OBJECT(e) FROM Employee e WHERE e.hireDate < ?1

  
  
• Conditional expressions: AND, OR, NOT
  
  

  Conditional statements in EJB QL mean the same in SQL. For example, the following two EJB QL statements are equivalent:

  SELECT OBJECT(e) FROM Employee e WHERE e.firstName = 'John' AND e.deptId <> ?1

  
  

  SELECT OBJECT(e) FROM Employee e WHERE e.firstName = 'John' AND NOT e.deptId = ?1

  
  
• [NOT] BETWEEN ... AND
  
  

  In EJB QL, you can specify a range for comparison with BETWEEN ... AND. Note that this comparison includes the endpoints. For example, the following two EJB QL statements are equivalent:

  SELECT OBJECT(e) FROM Employee e WHERE e.salary BETWEEN ?1 AND ?2

  
  

  SELECT OBJECT(e) FROM Employee e WHERE e.salary >= ?1 AND e.salary <= ?2

  
  

  That means findBySalaryRange in "Finders and Selectors" and findBySalaryRange2 in the bullet on the comparisons of numeric types are equivalent.

As these similarities between SQL and EJB QL demonstrate, if you are familiar with SQL, you can easily master EJB QL.

In most database applications, numeric and string fields are common. EJB QL provides several convenient functions for manipulating numeric and string types with a WHERE clause, as follows:

• Arithmetic operations and functions: +, -, *, /, ABS, SQRT, MOD
  
  Besides addition, subtraction, multiplication, and division, the following arithmetic functions are available in EJB QL:
  
  
  • numeric ABS (numeric), where numeric is of type numeric, boxed numeric, java.math.BigDecimal, or java.math.BigInteger
    
    
  • double SQRT(doubleType)
    
    
  • n int MOD(integerType, integerType)
    
    
  Important: Use these functions in WHERE clauses only. For example:
  
  

  SELECT OBJECT(e) FROM Employee e WHERE MOD(e.empId, 10) = ?1

  
  

  SELECT OBJECT(e) FROM Employee e WHERE SQRT(ABS(e.salary - ?1)) > 50000

  
  

  As an incorrect example, the following EJB QL is invalid because this is not a WHERE clause:

  SELECT SQRT(ABS(e.salary - ?1)) FROM Employee e

  
  
• String functions: CONCAT, SUBSTRING, LENGTH, LOCATE
  
  

  Similarly to SQL, EJB QL provides functions for concatenating strings, getting a substring, computing length, and locating the starting position of a substring. Here is the syntax:

  String CONCAT(String first, String second) String SUBSTRING(String aString, integerType start, integerType length) int LENGTH(String aString) int LOCATE(String pattern, String string) int LOCATE(String pattern, String string, integerType start)

  
  

  Note: You can use these string functions only in WHERE clauses.

  The start index starts with 1 and LOCATE(pattern, string, 1) = LOCATE(pattern, string). However, some databases contain no functionalities that correspond to LOCATE(pattern, string, start). In that case, do not define LOCATE(pattern, string, n) when n >= 2. Otherwise, the Application Server 8 runtime throws an exception error.

  Here are examples of how to define string functions:

  SELECT e.firstName FROM Employee e WHERE SUBSTRING(e.firstName, ?1, ?2) = ?3

  
  

  SELECT e.firstName FROM Employee e WHERE LOCATE(?1, e.firstName) > ?2

  
  

  SELECT OBJECT(e) FROM Employee e WHERE LENGTH(e.firstName) > ?1

  
  

  As an incorrect example, the following EJB QL is invalid because this is not a WHERE clause:

  SELECT LENGTH(e.firstName) FROM Employee e WHERE LOCATE(?1, e.firstName) > ?2

  
  
• Matching: [NOT] IN, [NOT] LIKE ... [ESCAPE ...]
  
  

  Use the IN operator to exactly match a list of String, numeric literal, or input parameters.

  For example, you can find out the names of the departments with deptid that equals 123 or the input parameter, like this:

  Finder	public Collection findByIdList(int deptId) throws FinderException;
  EJB QL	SELECT OBJECT(d) FROM Department d WHERE d.deptId IN (123, ?1)

  
  

  Another example: You can find out the names of the departments with deptName that equals to HR, Marketing, or the input parameter, like this:

  Finder	public Collection findByNameList(String deptName)     throws FinderException;
  EJB QL	SELECT OBJECT(d) FROM Department d     WHERE d.name IN ('HR', 'Marketing', ?1)

  
  

  The LIKE operator matches patterns. You specify a pattern as a matching criterion for a given CMP field. Remember:

  • _ denotes one arbitrary character.
  • % denotes zero or more arbitrary characters.
    
    

  TABLE 3 shows the string patterns and examples of the strings that are matched.

  TABLE 3 String Patterns and Examples of Matched Strings

   
  

  String Pattern	Examples of Matched Strings
  a_	ac, an, as
  Peter	Peter
  Peter%	Peter, Peterson
  %son	Peterson, Johnson

  
  

  Following are examples of the LIKE operator:

  Finder	public Collection findByLikeName(String name) throws FinderException;
  EJB QL	SELECT OBJECT(d) FROM Department d WHERE d.name LIKE ?1

  
  
  

  Finder	public Collection findByLikeName(String name) throws FinderException;
  EJB QL	SELECT OBJECT(d) FROM Department d WHERE d.name LIKE ?1

  
  

  EJB QL statements are equivalent:

  SELECT OBJECT(e) FROM Employee e WHERE e.firstName LIKE 'Peter%'

  
  

  SELECT OBJECT(e) FROM Employee e WHERE LOCATE('Peter', e.firstName) = 1

  
  

  In most databases, the semantics for the arithmetic and string function constructs are similar to those in SQL. However, the syntax for those functions varies from database to database. The EJB QL syntax works across all J2EE technologycompliant platforms.

You can define the relationships among local EJB objects, such as the relationship between an EJB object and a collection of such objects. With EJB QL, you can navigate a relationship to determine whether a collection is empty or verify whether an object is in a collection.

Here are the constructs:

• Navigation: IN(...)
  
  

  You can reference an element in a collection by means of the IN(...) construct in a FROM clause.

  For example, the following EJB QL retrieves the names of the employees whose salaries are higher than a given value in a given department. This is a selector in Department because a finder in Department cannot return the output of Employee or a collection of Employee only.

  Selector	public Collection ejbSelectByDepartmentAndSalary(String deptName,     double salary) throws FinderException;
  EJB QL	SELECT OBJECT(e) FROM Department d, IN(d.employees) e     WHERE d.name = ?1 AND e.salary > ?2

  
  

  The following EJB QL retrieves the names of the departments--in a given campus--whose employees work on projects with budgets that exceed a given amount:

  Finder	public Collection findByCampusAndBudget(String aCampus, Double aBudget)     throws FinderException;
  EJB QL	SELECT OBJECT(d) FROM Department d, IN(d.employees) e, IN(e.projects) p     WHERE e.campus = ?1 AND p.budget > ?2

  
  

  Interestingly, the IN operator works in both FROM and WHERE clauses. The following EJB QL retrieves employee names from a given department, where firstname is on the specified list, as shown below:

  Selector	public Collection ejbSelectSpecialEmployee (String deptName)     throws FinderException;
  EJB QL	SELECT OBJECT(e) FROM Department d, IN(d.employees) e     WHERE d.name = ?1 AND e.firstName IN ('Peter', 'John', 'Paul')

  
  
• IS [NOT] EMPTY, [NOT] MEMBER [OF]
  
  

  You can check whether a collection is empty. For example, the following EJB QL finds the departments with no projects:

  Finder	public Collection findWithoutProject() throws FinderException;
  EJB QL	SELECT OBJECT(d) FROM Department d WHERE d.projects IS EMPTY

  
  

  To check whether an element is in a given collection, use MEMBER OF. For example, the following EJB QL retrieves the names of the projects with budgets that exceed a given amount and which a given employee has worked on:

  Selector	public Collection ejbSelectProjectByEmployeeAndBudget(     Employee aEmployee, double aBudget) throws FinderException;
  EJB QL	SELECT OBJECT(p) FROM Project p     WHERE ?1 MEMBER OF p.employees AND p.budget > ?2

  
  

  The constructs in EJB QL check and navigate relationships among EJB objects. They serve an important purpose for applications with EJB objects that are interrelated.

You can arrange the order of returned items in queries with ordering constructs, as follows:

• ORDER BY ... ASC OR DESC
  
  

  The ORDER BY construct is exactly the same as that in SQL. The fields in the ORDER BY clause must be orderable CMP path expressions of values that are returned by the SELECT clause. The orderable types are listed below:

  • Numeric
  • Boxed numeric
  • BigDecimal
  • BigInteger
  • Any of the date or time types
  • String
    
    

  If you do not specify ASC or DESC, ORDER BY assumes ASC. Following are three examples of the correct use of the ordering constructs:

  SELECT OBJECT(p) FROM Project p ORDER BY p.budget DESC

  
  

  SELECT OBJECT(e) FROM Employee e ORDER BY e.lastName, e.firstName

  
  

  SELECT OBJECT(e) FROM Department d ORDER BY d.Name ASC, d.deptId DESC

  
  

  The following two EJB QL examples are incorrect because they do not select the orderable fields in the ORDER BY clause:

  SELECT OBJECT(e) FROM Department d, IN(d.employees) e ORDER BY d.name

  
  

  SELECT e.department FROM Employee e ORDER BY e.salary

  
  

  The following EJB QL is also incorrect because e.fullTime is boolean, therefore not orderable:

  SELECT OBJECT(e) FROM Employee e ORDER BY e.fullTime

  
  

As in SQL, you can compute the results of five aggregate functions in EJB QL: SUM, AVG, MIN, MAX, and COUNT.

You can use the aggregate functions and their arguments in SELECT clauses, except that the COUNT arguments must be a CMP path expression. Also, COUNT arguments can be any single-valued path expression or identification variable.

Since aggregate functions are valid only in SELECT clauses, only EJB QL selectors can use these functions. Also, you can specify DISTINCT in an argument.

For example, you can count the number of campuses for a given department, as follows:

EJB QL eliminates null values before applying aggregate functions.

TABLE 4 lists the return types of aggregate functions for the arguments.

Argument Type	SUM	AVG	MIN	MAX	COUNT
byte/Byte	Long	Double	Long	Long	Long
short/Short	Long	Double	Long	Long	Long
int/Integer	Long	Double	Long	Long	Long
long/Long	Long	Double	Long	Long	Long
float/Float	Double	Double	Double	Double	Long
double/Double	Double	Double	Double	Double	Long
BigInteger	BigInteger	BigInteger	BigInteger	BigInteger	Long
BigDecimal	BigDecimal	BigDecimal	BigDecimal	BigDecimal	Long
java.util.Date	error	error	java.util.Date	java.util.Date	Long
java.sql.Date	error	error	java.sql.Date	java.sql.Date	Long
java.sql.Time	error	error	java.sql.Time	java.sql.Time	Long
java.sql.Timestamp	error	error	java.sql.Timestamp	java.sql.Timestamp	Long
char/Character	error	error	Character	Character	Long

Note: You can choose a different return type for a selector, as long as the two are compatible. In that case, EJB QL performs the conversion according to the standard rules for the Java programming language (see section 5.1.3 under References in The Java Language Specification).

For example, the following selectors share the same EJB QL:

Selector1 and Selector2 return the average salary as Double and long, respectively. The salary field for Employee is a double. Hence, according to TABLE 4, AVG returns a Double value, as in Selector1. In Selector2, if you choose long as a return type, then EJB QL converts the AVG Double value to long, truncated, as is standard practice in the Java language.

Suppose the average salary for deptId = 1 is $8,000.75, then Selector1 returns 8000.75 and Selector2 returns 8000.

The following is an incorrect use of AVG because AVG is valid only in SELECT clauses:

SELECT OBJECT(d) FROM Department d WHERE AVG(d.salary) >= ?1 

With EJB QL, you can simply and intuitively retrieve EJB objects, CMP fields, CMRs, or results of aggregate functions and then order the results. For queries, you can use arithmetic and String functions in WHERE clauses. All these capabilities promise to reduce development and deployment cycles and, correspondingly, the related costs.

By virtue of its similarity to SQL, EJB QL as a declarative language is a breeze for SQL-ers to learn, and only slightly heavier weather for newcomers to SQL. Take EJB QL for a sail.

• Enterprise JavaBeans Query Language
  
  
• The Java Language Specification, Second Edition
  
  
• Sun Java System Application Server
  • Main page
  • Developer home
    
    
• Java 2 Platform, Enterprise Edition (J2EE) 1.4
  • Software Development Kit (SDK)
  • Documentation

Shing Wai Chan, who joined Sun six years ago, is a software engineer for Sun Java System Application Server and the J2EE SDK. He has been focusing on development projects that relate to CMP, security, B2B, and B2C.

Marina Sum is a staff writer for Sun Developer Network. She has been writing for Sun for 15 years, mostly in the technical arena.