Example: EJB queries

Here is an example EJB schema, followed by a set of example queries:

Table 1. DeptBean schema

Entity bean name (EJB name)	DeptEJB (not used in query)
Abstract schema name	DeptBean
Implementation class	com.acme.hr.deptBean (not used in query)
Persistent attributes (cmp fields)	deptno - Integer (key) name - String budget - BigDecimal
Relationships	emps - 1:Many with EmpEJB mgr - Many:1 with EmpEJB

Table 2. EmpBean schema

Entity bean name (EJB name)	EmpEJB (not used in query)
Abstract schema name	EmpBean
Implementation class	com.acme.hr.empBean (not used in query)
Persistent attributes (cmp fields)	empid - Integer (key) name - String salary - BigDecimal bonus - BigDecimal hireDate - java.sql.Date birthDate - java.util.Calendar address - com.acme.hr.Address
Relationships	dept - Many:1 with DeptEJB manages - 1:Many with DeptEJB

Address is a serializable object used as cmp field in EmpBean. The definition of address is as follows

    public class com.acme.hr.Address  extends Object implements Serializable { 
  public String street;
  public String state;
  public String city;
  public Integer zip;
      public double distance (String start_location) { ... } ;
      public  String format ( ) { ... } ;
 }

The following query returns all departments

SELECT OBJECT(d) FROM DeptBean d

The following query returns departments whose name begins with the letters "Web". Sort the result by name

SELECT OBJECT(d) FROM DeptBean d WHERE  d.name LIKE  'Web%' ORDER BY d.name

The keywords SELECT and FROM are shown in uppercase in the examples but are case insensitive. If a name used in a query is a reserved word, the name must be enclosed in double quotes to be used in the query. We can find a list of reserved words in EJB query: Reserved words. Identifiers enclosed in double quotes are case sensitive. This example shows how to use a cmp field that is a reserved word

SELECT OBJECT(d) FROM DeptBean d  WHERE  d."select" > 5

The following query returns all employees who are managed by Bob. This example shows how to navigate relationships using a path expression

SELECT OBJECT (e) FROM EmpBean e WHERE e.dept.mgr.name='Bob'

A query can contain a parameter which refers to the corresponding value of the finder or select method. Query parameters are numbered starting with 1

SELECT OBJECT (e) FROM EmpBean e WHERE e.dept.mgr.name= ?1

This query shows navigation of a multivalued relationship and returns all departments that have an employee that earns at least 50000 but not more than 90000

SELECT OBJECT(d) FROM DeptBean d,  IN (d.emps) AS e
WHERE e.salary BETWEEN 50000 and 90000

There is a join operation implied in this query between each department object and its related collection of employees. If a department has no employees, the department does not appear in the result. If a department has more than one employee that earns more than 50000, that department appears multiple times in the result.

The following query eliminates the duplicate departments

SELECT DISTINCT OBJECT(d) from DeptBean d,  IN (d.emps) AS e  WHERE e.salary > 50000

Find employees whose bonus is more than 40% of their salary

SELECT OBJECT(e) FROM EmpBean e where e.bonus > 0.40 * e.salary

Find departments where the sum of salary and bonus of employees in the department exceeds the department budget

SELECT OBJECT(d) FROM DeptBean d where d.budget < 
( SELECT SUM(e.salary+e.bonus) FROM IN(d.emps) AS e )

A query can contain DB2 style date-time arithmetic expressions if you use java.sql.* datatypes as CMP fields and your datastore is DB2. Find all employees who have worked at least 20 years as of January 1st, 2000

SELECT OBJECT(e) FROM EmpBean e where year(  '2000-01-01' - e.hireDate ) >= 20

If the datastore is not DB2 or if you prefer to use java.util.Calendar as the CMP field, then use the java millsecond value in queries. The following query finds all employees born before Jan 1, 1990

SELECT OBJECT(e) FROM EmpBean e WHERE e.birthDate <  631180800232

Find departments with no employees

SELECT OBJECT(d) from DeptBean d where d.emps IS EMPTY

Find all employees whose earn more than Bob

SELECT OBJECT(e) FROM EmpBean e, EmpBean b
WHERE b.name = 'Bob' AND e.salary + e.bonus > b.salary + b.bonus

Find the employee with the largest bonus

SELECT OBJECT(e) from EmpBean e  WHERE e.bonus =
(SELECT MAX(e1.bonus) from EmpBean e1)

The above queries all return EJB objects. A finder method query must always return an EJB Object for the home. A select method query can in addition return CMP fields or other EJB Objects not belonging to the home.

The following would be valid select method queries for EmpBean. Return the manager for each department

SELECT  d.mgr FROM DeptBean d

Return department 42 manager's name

SELECT  d.mgr.name FROM DeptBean d WHERE  d.deptno = 42

Return the names of employees in department 42

SELECT e.name FROM EmpBean e WHERE  e.dept.deptno=42

Another way to write the same query is

SELECT e.name from DeptBean d, IN (d.emps) AS e WHERE d.deptno=42

Finder and select queries allow only a single CMP field or EJBObject in the SELECT clause. A select query can return aggregate values in Enterprise JavaBeans 2.1 using SUM, MIN, MAX, AVG and COUNT

SELECT max(e.salary) FROM EmpBean e WHERE e.dept.deptno=42

The dynamic query api allows multiple expressions in the SELECT clause. The following query would be a valid dynamic query, but not a valid select or finder query

SELECT  e.name, e.salary+e.bonus as total_pay , object(e), e.dept.mgr
FROM  EmpBean e
ORDER BY 2

The following dynamic query returns the number of employees in each department

SELECT e.dept.deptno as department_number , count(*) as employee_count
FROM  EmpBean e
GROUP BY  by e.dept.deptno
ORDER BY 1

The dynamic query api allows queries that contain bean or value object methods

SELECT object(e), e.address.format( )
FROM EmpBean e EmpBean e

---

 

See Also

EJB query language

 

Related Tasks

Using EJB query
Using the dynamic query service

 

---