Annotations can also contain metadata that controls the options that the pureQuery StaticBinder uses when performing a bind operation on the interface.
@ColumnOverride(propertyName="extension", column="PHONE") public class EmployeeNewCo extends Employee {...
To understand the conventions that are used in the syntax diagrams, see How to read syntax diagrams.
>>-@ColumnOverride--(--propertyName--=--"--property--",--column--=--"--name--")-><
>>-@ColumnOverrides--(------------------------------------------> .-,----------------------------------------------------------------------------. V | >--{----@ColumnOverride--(--propertyName--=--"--property--",--column--=--"--name--")-+--}--> >--)-----------------------------------------------------------><
The array of @ColumnOverride annotations appears within curly braces within the parentheses for the @ColumnOverrides annotation.
>>-@Table--(--name--=--name_of_table----------------------------> >--+---------------------------------+--)---------------------->< | (1) | '-------schema--=--name_of_schema-'
An optional annotation specified at the class level that specifies a default table name for all of the properties in the beans. The annotation is not required when you create a set of nested beans for SQL statements that contain joins. However, the annotation simplifies defining beans.
The default table name can be overridden by the table name specified in an @Column annotation.
The @Table annotation does not specify a default table name for @JoinColumn annotations.
Unlike @ColumnOverride and @ColumnOverrides in inline style, the @Table annotation is inherited by subclasses, both in data access object (DAO) style and inline style.
pureQuery recognizes property-level annotations only on public fields and public get() or set() methods. It does not recognize these annotations on private or protected fields.
To understand the conventions that are used in the syntax diagram, see How to read syntax diagrams.
This annotation specifies the SQL column in a database object that a property maps to. You can use this annotation only on public fields and public methods. If you use it anywhere else, pureQuery ignores it.>>-@Column--(--name--=--name_of_column--+-------------------------+->< '-table--=--name_of_table-'
You can use the @Column annotation for either of two reasons:
@Column(name="DEPTNO") public String deptNum;
@Column(name="EMPNO") public String getEmpNum() { return empNum; }
Example
select a.col1, b.col1 from a, b where a.id=b.id;The methods setA_col1 and setB_col1 for the corresponding properties in the beans that hold the query results need @Column annotations that give the name of the table in which the two col1 columns appear:
public class JoinExample{ private int a_col1; private int b_col1; @Column (name="col1", table="a") public void setA_col1 (int a_col1) { this.a_col1 = a_col1; } public int getA_col1 () { return a_col1; } @Column (name="col1", table="b") public void setB_col1 (int b_col1) { this.b_col1 = b_col1; } public int getB_col1 () { return b_col1; } }
>>-@Format--(--fomatterClass--=--formatter_class----------------> >--+-----------------------------------------+--)-------------->< '-, -formattingOptions--=--pattern-string-'
This annotation specifies the class that contains the formatter that is invoked to perform any necessary conversions, and the formatting options. You can use this annotation on a field in a bean object that is specified as the input or output on a pureQuery method. This applies to both DAO style, and inline style.
This formatter class converts String fields to the proper JDBC DateTime types for input variables, and converts and formats the returned JDBC types to String variables for output.
The following example formats the startDate field:
class MyBean { … @Format(formatter=com.ibm.pdq.Dateformatter.class, formattingOtions=”style=LONG; timezone=UTC”) String startDate; … }
The annotation does not apply to Java primitive fields because Java primitives cannot represent a null value.
If the @Required annotation is not specified, the default behavior is to recognize Java nulls on input parameters and to set the corresponding host variable value to SQL NULL.
If the @Required annotation is specified on a field that is used as input to an SQL parameter, and that field contains a Java null value when the field is accessed, an exception is thrown by pureQuery, and the SQL operation is not attempted.
The following example requires that the customerAddress value must contain a non-null value.
@Required String customerAddress;
For an SQL query that contains table joins, you can create a set of nested beans that return data from the query. The hierarchy of the beans replicates the relational structure of the data in the database. You specify pureQuery annotations with nested beans to specify the table and column relationships. pureQuery Runtime populates the beans with the results of the query. For information about nested beans used with table joins, see Nested beans and SQL queries that contain table joins.
Property-level annotations
>>-@Id---------------------------------------------------------><
This annotation indicates that the annotated property represents an identity column that is used by pureQuery Runtime to determine equality between beans of the same class. This annotation is used on properties of a bean. The identity columns are columns that link the child beans to the parent bean. At least one @Id annotation must be specified in the top-level bean to enable generation of nested beans by pureQuery Runtime. The matching column in the database does not need to be declared as a primary or foreign key column.
The @Column annotation can be used with the @Id annotation to specify the column name and table name that maps the ResultSet object to the key column.
In child beans, either the @Id annotation or an @JoinPoint annotation can be used to define identity columns that link a parent bean to a child bean. If both are specified, the @JoinPoint annotation information is used and the @Id annotation is ignored. The @JoinPoint annotation is specified in a reference to the bean in its parent bean and the @Id annotation is specified in the bean itself.
If the bean has a composite key, multiple @Id annotations must be specified in the child bean or the keys must be specified with an @JoinPoint annotation of the parent bean. In the @JoinPoint annotation, a key must be specified by an @JoinColumn annotation with the propertyName element.
To determine if beans are equal when processing nested beans, pureQuery Runtime checks the bean properties with @Id annotations. If all the properties with @Id annotations are equal, then the beans are considered equal. Bean properties with @Id annotation do not need to be primary or foreign keys in the database, however, they must be sufficient to determine equality between beans of the same class.
>>-@JoinColumn--(--name--=--name_of_column----------------------> >--+--------------------------+---------------------------------> '-table--=--name_of_column-' >--+-----------------------------------+--)-------------------->< '-propertyName--=--name_of_property-'
This annotation specifies a column of a ResultSet object as an identity column of a child bean when the parent bean property name does not match the column label of a ResultSet object or table name. This annotation is specified in the parent bean as an annotation on the child beans. The child beans are either a single bean or a list of beans.
A @JoinColumn annotation can be specified only in an @JoinPoint annotation.
If you specify an empty string ("") as a value for the name element, pureQuery Runtime disables support for generating nested beans on this property without generating a warning that the property does not match. You specify an empty string in a subclass if you do not want a particular child bean or list of beans to be populated.
>>-@JoinPoint---------------------------------------------------> >--+-----------------------------------------------------------------------------------------+->< '-(--+--------------------------------------------------+--+-------------------------+--)-' '-+------------+--array_of_@JoinColumn_annotations-' '-columnPrefix--=--prefix-' +-joinCol--=-+ '-value--=---'
At least one @JoinPoint annotation must be specified in the top-level bean, that is, the bean that is the return value of the method or in-line API. The annotation enables generation of a set of nested beans by pureQuery Runtime.
The annotation can be defined only on a property that contains a bean or a list of beans. The optional @JoinColumn annotations define the columns of the ResultSet object that are mapped to the identity columns of a child bean. The optional columnPrefix element specifies a column prefix to be added to each property in the child bean. When the child bean has a composite key, more that one column of ResultSet object must be specified as a key column.
The annotation can be defined only on a property that contains a bean or a list of beans. If none of the optional elements (an array of @JoinColumn annotations or ancolumnPrefix element) are specified, then all ResultSet mappings and identity column definitions are taken from the child bean.
An array of @JoinColumn annotations is optional and applies to the child bean properties. Arrays of more than one @JoinColumn annotations must be enclosed within braces, ({ }).
If no @JoinColumn annotations are specified, then pureQuery Runtime uses the referenced child bean @Id annotations to determine which ResultSet columns map to the child bean identity columns. If a child bean does not contain at least one @Id annotation, a warning is generated and the child bean is ignored.
The element joinCol is mutually exclusive with the element value. One or the other can be specified but not both. The element is required when the columnPrefixelement is specified.
The value of the columnPrefix element specifies a string that is prefixed to each property in the child bean. With the prefix, pureQuery Runtime generates unique classes and properties. The prefix allows the same bean class to be used at different levels or the same level in the nested bean structure without recursion. Make sure that these prefixed properties are unique. Once a prefix is added to a class, that prefix applies to all child beans and the properties of the beans of the prefixed class. Prefixes are additive and nesting can continue as deep as required. The value of the columnPrefix element is applied only to the child bean not the column labels defined in the parent bean @JoinColumn annotations.
Column prefixes are applied only if the query returns columns that populate the child bean.
@JoinPoint({ @JoinColumn (name = "DEPTNO", table = "DEPARTMENT", propertyName="empDepartment"), @JoinColumn (name = "EMPNO", table = "EMPLOYEE", propertyName="employeeId") }) public List<Employee> getListofDeptEmployees ();
The first @JoinColumn annotation maps the DEPTNO column from the DEPARTMENT table to the property empDepartment in the Employee bean.
If you chose to use the Column Label WORKDEPT from the ResultSet object you would not have to specify the element propertyName = "empDepartment" if the column label WORKDEPT existed in the ResultSet object. The Property WORKDEPT was created by the annotation @Column (name = "WORKDEPT") in the Employee bean.
@JoinPoint(value = { @JoinColumn (name = "DEPTNO", table = "DEPARTMENT", propertyName="empDepartment"), @JoinColumn (name = "EMPNO", table = "EMPLOYEE", propertyName="employeeId") }) public List<Employee> getListofDeptEmployees ();
@JoinPoint(columnPrefix = "DE", joinCol = { @JoinColumn (name = "DEPTNO", table = "DEPARTMENT", propertyName="empDepartment"), @JoinColumn (name = "EMPNO", table = "EMPLOYEE", propertyName="employeeId") }) public List<Employee> getListofDeptEmployees ();
A columnPrefix of DE to represent department employees is prefixed to all of properties of the Employee bean and the string DE would be added to all employee columns in the ResultSet (by AS clause) that are meant for the Department List of Employee beans.
The prefix is also applied to all child beans in that Employee bean.
For example if the Employee bean contains a child Department bean, all properties of the Department bean within that Employee bean would have DE prefixed to them.
The prefixing continues for as many levels as are in the ResultSet object. The second level of the List of Employee beans within the child Department bean would be prefixed with DEDE.
The prefix is also applied to all child beans in that Employee bean, not all Employee beans in the nested bean structure. For example, the prefix is not used on List of Employee beans in the Project bean.
@JoinPoint public List<Emp> emps; @JoinPoint(columnPrefix="m_") public Emp manager;
By specifying the element columnPrefix="m_", the columns from EMPLOYEE table that are prefixed with m_ are mapped to the Emp manager field.
CREATE TABLE HRDEPT.EMPLOYEE( EMPNO CHAR(6) NOT NULL, FIRSTNME VARCHAR(12) NOT NULL, MIDINIT CHAR(1), LASTNAME VARCHAR(15), WORKDEPT CHAR(2), PHONENO CHAR(4), HIREDATE DATE, PRIMARY KEY(EMPNO))Some of the column names are abbreviated or do not follow Java naming conventions.
The following definition for an Employee bean uses @Column annotations to provide more meaningful names to the users of objects of the com.company.Employee class.
public class Employee { private String employeeId; private String firstName; private String middleInitial; private String lastName; private String departmentId; private String extension; private Date hireDate; @Column(name = "EMPNO") public String getEmployeeId () { return employeeId; } public void setEmployeeId (String employeeId) { this.employeeId = employeeId; } @Column(name = "FIRSTNME") public String getFirstName () { return firstName; } public void setFirstName (String firstName) { this.firstName = firstName; } @Column(name = "MIDINIT") public String getMiddleInitial () { return middleInitial; } public void setMiddleInitial (String middleInitial) { this.middleInitial = middleInitial; } public String getLastName () { return lastName; } public void setLastName (String lastName) { this.lastName = lastName; } @Column(name = "WORKDEPT") public String getDepartmentId () { return departmentId; } public void setDepartmentId (String departmentId) { this.departmentId = departmentId; } @Column(name = "PHONENO") public String getExtension () { return extension; } public void setExtension (String extension) { this.extension = extension; } public Date getHireDate () { return hireDate; } public void setHireDate (Date hireDate) { this.hireDate = hireDate; } }
If an application runs against a database with a slightly different schema. The table is defined as follows:
CREATE TABLE HRDEPTNEWCO.EMPLOYEE( EMPNO CHAR(6) NOT NULL, FIRSTNME VARCHAR(12) NOT NULL, MIDINIT CHAR(1), LASTNAME VARCHAR(15), WORKDEPT CHAR(2), PHONE CHAR(4), HIREDATE DATE, PRIMARY KEY(EMPNO))
You can create a subclass of the Employee bean to override the previous annotation so that you can work with the new table definition. The beginning of the subclass might look like this example:
@ColumnOverride(propertyName="extension", column="PHONE") public class EmployeeNewCo extends Employee {...
@Id @GeneratedKey @Column(name = "EMPNO") public String getEmployeeId () { return employeeId; } public void setEmployeeId (String employeeId) { this.employeeId = employeeId; } @Id @Column(name = "WORKDEPT") public String getDepartmentId () { return departmentId; } public void setDepartmentId (String departmentId) { this.departmentId = departmentId; }
@JoinPoint({ @JoinColumn (name = "DEPTNO", table = "DEPARTMENT", propertyName="workDept"), @JoinColumn (name = "EMPNO", table = "EMPLOYEE", propertyName="employeeId") }) public List<Employee> getListofDeptEmployees ();
In the first @JoinColumn annotation, the DEPTNO column from the DEPARTMENT table maps to the property workDept in the Employee bean. If the column label WORKDEPT from the ResultSet had been used, the propertyName="workDept" would not be needed if the column label WORKDEPT exists in the ResultSet. The property WORKDEPT is created by the annotation @Column (name = "WORKDEPT") in the Employee bean.