WAS v8.5 > 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.

WAS v8.5 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.


Example: Queries with EJB

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

EJB schema and example queries. EJB schema and example queries

EJB schema Example query
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

EJB schema Example query
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:

The following query returns departments whose name begins with the letters "Web". Sort the result by 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:

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

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:

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:

Find employees whose bonus is more than 40% of their 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 your datastore is DB2. Find all employees who have worked at least 20 years as of January 1st, 2000:

If the datastore is not DB2 or if you 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:

Find departments with no employees:

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:

Return department 42 manager's name:

Return the names of employees in department 42:

Another way to write the same query is:

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.

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 containing bean or value object methods: 

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


Subtopics


Reference:

EJB specification and WebSphere query language comparison


+

Search Tips   |   Advanced Search