Example of pureQuery nested beans

pureQuery nested beans work with SQL statements containing join predicates. The hierarchy of the beans is defined by pureQuery annotations that are defined on bean properties.

In this example, the SQL query returns department and employee information from the DB2® database GSDB. The example creates pureQuery nested beans to organize employee information by department. The top-level bean is the Dept bean that contains a list of Emp beans.

The information is from the EMPLOYEE, EMPLOYEE_HISTORY, and ORGANIZATION tables from the GOSALESHR schema. The query returns the following information from the tables:
  • The employee ID, given name, and family name from the EMPLOYEE table.
  • The current department ID for the employee from EMPLOYEE_HISTORY table.
  • The department ID and the name of the department ORGANIZATION table.

The Dept1 class that is used with the example interface class contains data from the ORGANIZATION table.

The Dept2 class that is used with the example inline SQL query contains data from the ORGANIZATION table.

The Emp class retrieves data from the EMPLOYEE and EMPLOYEE_HISTORY tables. The Emp class defines the Emp bean that is the child bean for the Dept1 bean and Dept2 bean.

The example SQL query

The SQL query returns employee information and the department information for each employee.
SELECT E.EMPLOYEE_CODE, E.FIRST_NAME, E.LAST_NAME, E.DATE_HIRED,
     ORG.ORGANIZATION_CODE, ORG.ORGANIZATION_NAME_EN
  FROM EMPLOYEE AS E, EMPLOYEE_HISTORY AS EH, ORGANIZATION AS ORG
  WHERE EH.EMPLOYEE_CODE = E.EMPLOYEE_CODE 
      AND EH.ORGANIZATION_CODE = ORG.ORGANIZATION_CODE
      AND EH.RECORD_END_DATE IS NULL
  ORDER BY ORGANIZATION_CODE, E.LAST_NAME;
The EMPLOYEE_HISTORY table can contain multiple records for an employee. The current employee information does not have an end date. The predicate AND EH.RECORD_END_DATE IS NULL restricts the query to return only the current employee history.

For information about the GSDB database and the tables that are used in this example, see Tables accessed by the pureQuery nested beans example.


Feedback