Here is an example EJB schema, followed by a set of 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) |
|
Relationships |
|
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) |
|
Relationships |
|
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 ( ) { ... } ; }
SELECT OBJECT(d) FROM DeptBean d
SELECT OBJECT(d) FROM DeptBean d WHERE d.name LIKE 'Web%' ORDER BY d.name
SELECT OBJECT(d) FROM DeptBean d WHERE d."select" > 5
SELECT OBJECT (e) FROM EmpBean e WHERE e.dept.mgr.name='Bob'
SELECT OBJECT (e) FROM EmpBean e WHERE e.dept.mgr.name= ?1
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.
SELECT DISTINCT OBJECT(d) from DeptBean d, IN (d.emps) AS e WHERE e.salary > 50000
SELECT OBJECT(e) FROM EmpBean e where e.bonus > 0.40 * e.salary
SELECT OBJECT(d) FROM DeptBean d where d.budget < ( SELECT SUM(e.salary+e.bonus) FROM IN(d.emps) AS e )
SELECT OBJECT(e) FROM EmpBean e where year( '2000-01-01' - e.hireDate ) >= 20
SELECT OBJECT(e) FROM EmpBean e WHERE e.birthDate < 631180800232
SELECT OBJECT(d) from DeptBean d where d.emps IS EMPTY
SELECT OBJECT(e) FROM EmpBean e, EmpBean b WHERE b.name = 'Bob' AND e.salary + e.bonus > b.salary + b.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.
SELECT d.mgr FROM DeptBean d
SELECT d.mgr.name FROM DeptBean d WHERE d.deptno = 42
SELECT e.name FROM EmpBean e WHERE e.dept.deptno=42
SELECT e.name from DeptBean d, IN (d.emps) AS e WHERE d.deptno=42
SELECT max(e.salary) FROM EmpBean e WHERE e.dept.deptno=42
SELECT e.name, e.salary+e.bonus as total_pay , object(e), e.dept.mgr FROM EmpBean e ORDER BY 2
SELECT e.dept.deptno as department_number , count(*) as employee_count FROM EmpBean e GROUP BY by e.dept.deptno ORDER BY 1
SELECT object(e), e.address.format( ) FROM EmpBean e EmpBean e
Related concepts
EJB query language
Related tasks
Using EJB query
Using the dynamic query service