Development of nested beans for SQL queries that contain table joins

You use pureQuery annotations in a set of nested beans to create sets of beans that return data from SQL queries that contain table joins. pureQuery Runtime generates the implementation for the set of nested beans. The implementation is based on the table joins in the SQL statements and the pureQuery annotations added to the beans.

You create the set of nested beans that defines the hierarchical data structure of the data you want returned. When you specify an @JoinPoint annotation to a bean property that matches a column of a ResultSet object, pureQuery Runtime generates the appropriate implementation. pureQuery Runtime analyzes the hierarchical structure of the top-level bean, which is the bean that is returned after processing the ResultSet object and the nested beans. Only those beans or list of beans that are updated by a column of a ResultSet object and have an @JoinPoint annotation are generated and populated. Any beans or list of beans that are not populated by a column of a ResultSet object or that does not have an @JoinPoint annotation are either null or the bean constructor initialization value.

An example of a set of nested beans

Here is an example showing two beans, the top-level bean is a Department bean and the child beans are a list of Employee beans.

The example uses the following SQL query with a join between the DEPARTMENT and EMPLOYEE tables. The query takes one parameter, the department number and returns all the employees who belong to the department:
Select D.DEPTNO, D.DEPTNAME, D.MGRNO, D.ADMRDEPT, E.EMPNO, E.FIRSTNME, E.MIDINIT, 
     E.LASTNAME, E.JOB, E.SEX, E.BIRTHDATE, E.SALARY 
   from (DEPARTMENT AS D LEFT JOIN EMPLOYEE AS E ON D.DEPTNO=E.WORKDEPT) 
   WHERE D.DEPTNO=? ORDER BY D.DEPTNO, E.LASTNAME
The following DAO interface method specifies the SQL query:
  @Select(sql = "Select D.DEPTNO, D.DEPTNAME, D.MGRNO, D.ADMRDEPT, E.EMPNO, 
     E.FIRSTNME, E.MIDINIT, E.LASTNAME, E.JOB, E.SEX, E.BIRTHDATE, E.SALARY 
    from (DEPARTMENT AS D LEFT JOIN EMPLOYEE AS E ON D.DEPTNO=E.WORKDEPT) 
    WHERE D.DEPTNO=? ORDER BY D.DEPTNO, E.LASTNAME")
  public Department joinTest (String deptNo);

The following two beans have been developed for the SQL query. The Department bean is top-level bean that is returned after processing the result set. The Department bean is the parent bean of the list of deptEmployees beans. The list of deptEmployees beans is a set of child beans. The identity column, which is the property in a bean with the @Id annotation, is deptNo.
@Table(name = "DEPARTMENT")
public class Department
{
  @Id
  @GeneratedKey
  public String deptNo; // this is the ID of the bean and its value is generated by the database
  public String deptName;
  public String mgrNo;
  public String admrDept;
  @JoinPoint(@JoinColumn(name = "EMPNO", table = "EMPLOYEE", propertyName = "empNo"))
  public List<Employee> deptEmployees;
} 
The data returned by the list of deptEmployees beans is defined by the Employee class.
@Table(name = "EMPLOYEE")
public class Employee
{
  @Id
  @GeneratedKey
  public String empNo; // this is the ID of the bean and its value is generated by the database
  public String firstNme;
  public String midInit;
  public String lastName;
  public String workDept;
  public String job;
  public String sex;
  public java.sql.Date birthdate;
  public String salary;
}

Based on the SQL query and the beans, pureQuery Runtime generates implementation code for the beans.

The Java objects returned are one Department bean, that contains a java.util.list of 11 deptEmployees beans.

The resulting formatted output from calling the method joinTest("D11"). The first four columns are the data from the Department bean, the other columns of employee data are from the deptEmployees beans:
dept                             admr                    mid
No  deptName              mgrNo  Dept empNo    firstNme  Init lastName  job      sex birthdate  salary
D11 MANUFACTURING SYSTEMS 000060 D01  000150   BRUCE          ADAMSON   DESIGNER M   1977-05-17 55280.00
                                      000200   DAVID          BROWN     DESIGNER M   1971-05-29 57740.00
                                      200220   REBA      K    JOHN      DESIGNER F   1978-03-19 69840.00
                                      000210   WILLIAM   T    JONES     DESIGNER M   2003-02-23 68270.00
                                      000220   JENNIFER  K    LUTZ      DESIGNER F   1978-03-19 49840.00
                                      000160   ELIZABETH R    PIANKA    DESIGNER F   1980-04-12 62250.00
                                      000180   MARILYN   S    SCOUTTEN  DESIGNER F   1979-02-21 51340.00
                                      000060   IRVING    F    STERN     MANAGER  M   1975-07-07 72250.00
                                      000190   JAMES     H    WALKER    DESIGNER M   1982-06-25 50450.00
                                      200170   KIYOSHI        YAMAMOTO  DESIGNER M   1981-01-05 64680.00
                                      000170   MASATOSHI J    YOSHIMURA DESIGNER M   1981-01-05 44680.00 

Types of beans in a set of nested beans

When you create a set of nested beans for SQL queries that contain table joins, you create these types of beans:
top-level bean
The top-level bean is the bean that is the return value of the method or inline API. The top-level bean must have at least one @Id annotation when it is used with pureQuery annotations in a set of nested beans. As a parent bean, it must also contain at least one @JoinPoint annotation. The bean can be a parent or a child bean. In some cases, a bean can be both a parent and a child bean in the same nesting structure.
parent bean
A parent bean must contain at least one @JoinPoint annotation that references a bean or list of beans, the child beans. An @Id annotation is not required in a parent bean unless it is a top-level bean. If a parent bean, for example BeanB, does not contain @Id annotations, it must also be a child bean. The parent bean of BeanB, BeanA and it must contain @JoinColumn annotations within an @JoinPoint annotation. The @JoinColumn annotations in BeanA specify identity columns for its child bean, BeanB.
child bean
A child bean is referenced by its parent bean with an @JoinPoint annotation. A child bean does not need to contain @Id annotation unless it is also a top-level bean. If the child bean does not contain an @Id annotation, then its parent bean must specify this bean's identity columns with one or more @JoinColumn annotations within an @JoinPoint annotation.

Identity columns in a set of nested beans

When generating and populating a set of nested beans, pureQuery Runtime uses the @JoinPoint annotation in the reference to the bean in the bean's parent, or @Id annotation in the bean itself. The annotations define the identity columns that link the child beans to the parent bean. For example, pureQuery Runtime uses identity columns to determine when to generate a list of beans for a parent bean.

For example, an EMPLOYEE table might contain multiple columns that could be identity columns. Columns that could be defined as an identity column are the employee number or the employee social security number. The identity column can be the primary key of the table but is not required to be a table key. Multiple columns can be combined into a composite key just as a composite key can be created on a database table. A poor choice for an EMPLOYEE table identity column would be the name, since employees might have the same.

pureQuery Runtime uses the identity column information and other bean information when processing the ResultSet object. For an SQL query that contains table joins, the data in the ResultSet object might make it difficult to map that data into a hierarchical structure of beans. These are some characteristics that might cause problems during processing:
  • Data might not be in sorted order.
  • The order of the data in the database tables might need to be preserved.
  • Data in the ResultSet is duplicated. This happens when the data contains one-to-many relationships in the database. For example, one department has many employees. A ResultSet can contain data from a join between a department table and employee table. The department information is duplicated for each employee in the department.

pureQuery Runtime ensures that the beans are populated with the correct set of data in the correct order. For example, the ResultSet data might not be sorted to map exactly to the structure of the nested beans. pureQuery Runtime can process ResultSet data that belongs to beans that were created earlier in the processing of the ResultSet and populate beans with the appropriate data in the correct order.

Rules for creating sets of nested beans

You can create set of nested beans that returns results from SQL statements with complex join predicates by creating a more complex nesting structure with the proper annotations. To create a set of beans that return the correct results, the following rules must be followed. Changes to the SQL query or the beans might be necessary to generate a useful set of beans.

  1. The column name of each column in the ResultSet object must map to a single property in the parent bean or any child beans.
    Note: If more than one column maps to a given property and the columns always return the same data, this mapping is not a problem. Problems can arise if one or more of the columns can be null. For example, a column can return a null from an SQL query that contains a table join. The query can contain a WHERE or ON clause that does not find a matching key between two tables.

    This problem can be solved by specifying aliases in the SQL query. @Column, @Table, or @JoinColumn annotations can also be specified with Table Name on the properties in the beans that have more than one column.

  2. If the structure of the nested beans contains the same bean multiple times, all of the references to that bean after the first instance are ignored. Using only the first instance prevents the possibility of infinite recursion when pureQuery Runtime processes the set of nested beans. Use the @JoinPoint optional element columnPrefix, or a subclass of the original bean and column aliases in the SQL query, or @JoinPoint or @JoinColumn annotations to create multiple, similar beans.
  3. The @JoinColumn required element name must match the base property name in the child bean. If it does not, then the optional element propertyName must be specified with the name of the matching base property in the child bean.

    The base property name is the name of a bean property without any existing annotations that modify its mapping to the ResultSet object. For a field, the base property name is the Java variable name. For a get, set or is method, it is the method name without the get, set, or is prefix and the first character set to lowercase. In the following examples, the ResultSet column label WORKDEPT is not case-sensitive.

    In the following example get method, the base property name workDepartment is case sensitive.
    @Column (name = "WORKDEPT")
    public String getWorkDepartment (); 

    An @JoinColumn annotation can be specified inside an @JoinPoint annotation on a bean or list of beans. When it is, the specified name and table elements override the column label and optional table name that is defined in the child bean @Id annotations, including any associated @Column annotation that is specified.

    A child bean can have more than one @Id annotation. When it does, the @JoinColumn annotation required element name should match the base property name in the child bean. If the names do not match, then the optional element propertyName must be specified with the name of the matching base property in the child bean.

    If you modify the previous example so that an alias for EMPNO in the SQL query is EMPNUM, the propertyName element is not required in an @JoinColumn annotation. The child bean has only one @Id annotation and pureQuery Runtime determines which property to map to. You would modify the following the Department bean @JoinPoint annotation:
    @JoinPoint(@JoinColumn(name = "EMPNO", table = "EMPLOYEE", propertyName = "empNo"))
      public List<Employee> deptEmployees;
    The @JoinPoint annotation could be specified as follows:
      @JoinPoint(@JoinColumn(name = "EMPNUM", table = "EMPLOYEE", propertyName = "empNo"))
       public List<Employee> deptEmployees;
    If you want to change the identity column of the Employee child bean from empNo to workDept, you specify the PropertyName element as follows:
      @JoinPoint(@JoinColumn(name = "WORKDEPT", table = "EMPLOYEE", propertyName = "workDept"))
      public List<Employee> deptEmployees;
  4. The @Table annotation is used at the class level. The annotation specifies a default table name to be added to all properties that do not specify a table name. The table name can be overridden to specify a property that matches any table name.
  5. Child beans do not require an @Id annotation to be considered part of the set of nested beans. The @JoinPoint annotation can specify the identity columns for the child bean. This specification of identity columns with the @Id annotation is only for that parent bean in that call.

    A child bean must be single bean or a list of beans and the child bean must have the @JoinPoint annotation. The @JoinPoint annotation must be on the parent bean property that defines the child bean or list of beans.

  6. Each column in the ResultSet object must map only to properties in the parent bean and child beans that are correct.

    An example of this mapping is a DEPARTMENT table that has a column DEPTNO that is a primary key and a PROJECT table that also has a column DEPTNO. The PROJECT table DEPTNO column is a foreign key for the DEPARTMENT table. The default match between columns in the ResultSet object and properties is the column label. In this example, a join of the DEPARTMENT and PROJECT tables could result in two columns with a column label of DEPTNO. If the parent and child bean structure require these columns to have different information you can add annotations to the beans. The annotations can use table name to make the columns unique. You can also modify the SQL statement by adding an alias to create a unique column label. However, it is common in SQL queries with a join to return only one column when multiple columns would contain identical data. In this case, it would be valid for one column to update multiple properties.

    With data access object (DAO) style methods, you get warnings of multiple properties being updated by the same column from the ResultSet object in the following ways:
    • A warning is generated describing the generated methods that use the same column. This warning is displayed after generation of the implementation if you are using IBM® Data Studio. The warning is also placed in the generator log file if it exists and the trace level is set to java.util.logging.Level.WARNING or finer.
    • The pureQuery Generator creates a comment block above the generated nested bean ResultHandler. The comment shows which generated handlers use the ResultSet column. It also shows which ResultSet columns are never used.

    With the inline method style, you get warnings of multiple properties being updated by the same ResultSet column. The warning are logged in the pureQuery Runtime log file if it exists and the trace level is set to java.util.logging.Level.WARNING or finer.

  7. An @JoinPoint annotation is required whenever a child bean is joined to a parent bean. One @JoinColumn annotation must be specified within the @JoinPoint annotation except when any of the following statements is true:
    • The child bean or list of beans contains one or more @Id annotations that fully define the identity columns of the child bean.
    • The child bean identity column properties (with any optional column label and table name) correctly specify the columns for the SQL statement that is run to create the ResultSet object. In that case, just the @JoinPoint annotation without any elements can be specified.

Nested bean runtime processing

These steps describe how pureQuery Runtime processes a set of nested beans when generating the objects with the data from a ResultSet object.
  1. Parsing starts with the bean that is being returned, the top-level parent bean. Each property of the bean is examined to determine if that property is updated by the ResultSet object. If a property is updated and has an @JoinPoint annotation, then the property is examined to determine if it references a bean or list of beans. If the property does reference a bean or list of beans, the bean is added to a list of child beans for the parent bean. All properties in the parent bean are processed before pureQuery Runtime processes any child beans that are found. The order that properties are examined within a bean is not deterministic.
  2. After all the properties in the parent bean have been examined, the list of child beans is checked. If the list contains one or more child beans, then the first bean in the list is processed as if it where a parent bean.
  3. After the first child bean is found in the original parent bean and a list of its child beans is created, pureQuery Runtime processes the next child bean of the original parent bean. If another bean exists, it is processed as in the previous step.

    All the child beans of the original parent bean are processed before any of the child beans are processed to determine if they have child beans.

pureQuery Runtime can determine if a child bean class is being processed for the first time or if there are recursive references in the beans. If the pureQuery Runtime parsing of the nested bean structure detects a bean it has already been parsed, it stops the parsing of that child bean.

Note: Having a parent bean that contains two or more of the same child beans is not supported. In this situation, pureQuery Runtime places the information in only one of the child beans. You can specify a subclass with unique property annotations of the child bean. You can also use the columnPrefix element in the parent bean's @JoinPoint annotation for each of the duplicate classes.

Recursive references to a bean also affect how pureQuery runtime populates the properties of child beans. Only the first instance of the bean is populated. Later references to that bean are ignored unless the reference has an @JoinColumn annotation with a different columnPrefix element defined. Because the order of processing of properties within a single bean is nondeterministic, the order that child beans are processed is also nondeterministic. However, the child beans are processed in order of the level of nesting. For example, a bean that is nested directly in the parent bean is processed before a bean at the next level of nesting.

For information about the columnPrefix element, see the @JoinPoint annotation documentation.


Feedback