Developing applications that use EJB query

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 not case-sensitive. If a name used in a query is a reserved word, the name must be enclosed in double quotation marks to be used in the query. You can find a list of reserved words in EJB query: Reserved words. Identifiers enclosed in double quotation marks 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

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 you can 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 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