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.
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.
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
@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);
@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;
}
@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.
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
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 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.
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.
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.
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.
@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.
@JoinPoint(@JoinColumn(name = "EMPNO", table = "EMPLOYEE", propertyName = "empNo"))
public List<Employee> deptEmployees;
@JoinPoint(@JoinColumn(name = "EMPNUM", table = "EMPLOYEE", propertyName = "empNo"))
public List<Employee> deptEmployees;
@JoinPoint(@JoinColumn(name = "WORKDEPT", table = "EMPLOYEE", propertyName = "workDept"))
public List<Employee> deptEmployees;
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.
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 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.
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.
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.