Network Deployment (Distributed operating systems), v8.0 > End-to-end paths > Dynamic query


Use EJB query

The EJB query language is used to specify a query over container-managed entity beans. The language is like structured query language (SQL). An EJB query is independent of the bean mapping to a persistent store.

An EJB query can be used in three situations:

Finder and select queries are specified in the bean deployment descriptor using the <ejb-ql> tag; they are compiled into SQL during deployment. Dynamic queries are included within the application code itself.

The product EJB query language is compliant with the EJB QL defined in the Sun EJB 2.1, EJB 3.0, and EJB 3.1 specifications and has additional capabilities as listed in the topic Comparison of EJB specification and WebSphere Query Language.


Procedure


Example

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

EJB schema and example queries. EJB schema and example queries

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

EJB schema and example queries. EJB schema and example queries

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 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. We 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

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 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 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 EJB 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


EJB query language
Use the dynamic query service


Related


http://wilson.boulder.ibm.com/infocenter/radhelp/v7r5mbeta/index.jsp?topic=/com.ibm.jee5.doc/topics/cejb3.html

+

Search Tips   |   Advanced Search