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.
This topic contains
the following sections:
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.
- 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.
- 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.
- 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;
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.