Example: Queries with EJB

Here is an example EJBs 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 we use java.sql.* datatypes as CMP fields and the 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 we prefer to use java.util.Calendar as the CMP field, then we 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 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 Java Beans 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

---

Related concepts

EJB query language

 

Related tasks

Use EJB query
Use the dynamic query service