A join is represented in the design file as a special jdbcTable element. There are two separate ways to represent joins in the Domain schema file:
| • | basic join syntax – Supports joins based on equality comparisons only. Available in all versions of JasperReports Server that use Domains. |
| • | advanced join syntax – In addition to joins based on equality comparisons, advanced joins support joins based on inequalities, joins in a set or range of fields or values, and joins between a foreign key and a constant. Available in JasperReports Server 6.0 and higher. |
| Advanced joins can only be created using XML in the design file. They are not supported in the Domain Designer. If you have a Domain that uses advanced joins, and you open it in the Domain Designer and then save it, your advanced joins will be lost. |
You cannot combine both types of syntax in a single data island. Each data island must use either all basic syntax or all advanced syntax for their joins. We recommend using the same join syntax for your entire Domain.
The Domain Designer automatically exposes all columns of all tables in a join, but in the design file you only need to specify those you want to reference elsewhere in the Domain.
Top-level Syntax for Joins
Both types of joins use the same tags to specify the tables and fields used in the join and the output table for the join results.
| • | jdbcTable – Represents the results of one or more joins between tables. If not all tables are joined together, there is one jdbcTable representing each join tree and containing only the join expressions for that tree. To define the join, the attributes and elements have a different meaning than for a regular table. |
| • | datasourceId – Alias that identifies the data source for the Domain. The alias designates the data source where the join is performed. In the design file, this alias must be identical to that for all tables and derived tables. |
| • | id – ID that is used to reference the join results in the Domain design. In the Domain Designer, each join tree is automatically given the ID JoinTree_n, where n is a sequential number. In the design file, you can give the join any name, as long as it is unique among all other tables and derived tables. |
| • | tableName – Literal name of the first table in the join. This table name is combined with those in the join, joinInfo and joinedDataSetList to define the join expressions. |
| • | For PostgreSQL and Oracle-based data sources, the table name includes the database schema name in the form schema.table, if you opted to include schema names when exporting the design. |
| • | For PostgreSQL and Oracle-based data sources within a virtual data source, the table name includes the data source prefix defined when the virtual data source was created and the database schema name in the form dataSourcePrefix_schema.table, if you opted to include schema names when exporting the design. The table name includes the data source prefix in the form dataSourcePrefix.table if you opted not to include schema names when exporting the design. |
| • | For MySQL and similar data sources within a virtual domain, the table name includes the data source prefix and the database name in the form dataSourcePrefix_database.table. |
| • | fieldList – A required container for the field elements in the join tree. |
| • | field – Represents a column in the join tree. The table of each column is identified by a prefix on the id attribute. When created in the Domain Designer, the design file includes every column in every table of the join. When you create your own design file, only the columns you want to reference are needed. Both attributes of field are required: |
| • | id – Field ID composed of the ID of the table in the design and the literal name of the column in the data source. The syntax is table_ID.field_name. |
| • | type – The Java type of the column, identical to the type in its table definition. |
| • | joinInfo – Gives the table ID and alias for the table given by the tablename attribute. The table ID and alias are used as the first table in the join definition. This element and its two attributes are required even if they are identical. |
| • | alias – Alternative name within the join expression for the table identified in referenceId. By default, the alias is the same as the referenceID. If you use a distinct alias, you must be careful to use the alias throughout the joinString element that defines the join. |
| • | referenceId – Table id from the <jdbcTable> element of the table within the design whose data source name is given in tableName. |
Basic Join Syntax
The basic join syntax supports joins based on equality comparisons only. This is the format produced by the Domain Designer. Uses the joinInfo and joinedDataSetList elements to define the actual join; also contains a list of columns that are exposed through the Domain, each with a prefix on the field id attribute to identify its originating table. The basic join syntax is available in all versions of JasperReports Server that use Domains.
Example of Basic Joins in XML
<jdbcTable datasourceId="SugarCRMDataSource" id="JoinTree_1" tableName="accounts"> <fieldList> <field id="accounts_opportunities.account_id" type="java.lang.String"/> ... </fieldList> <joinInfo alias="accounts" referenceId="accounts"/> <joinedDataSetList> <joinedDataSetRef> <joinString>join accounts_opportunities accounts_opportunities on (accounts.id == accounts_opportunities.account_id)</joinString> </joinedDataSetRef> <joinedDataSetRef><joinString>join opportunities opportunities on (accounts_opportunities.opportunity_id == opportunities.id)</joinString> </joinedDataSetRef>[/code]
<joinedDataSetRef><joinString>join users1 users1 on (opportunities.assigned_user_id == users1.id)</joinString> </joinedDataSetRef> <joinedDataSetRef><joinString>left outer join p1cases p1cases on (accounts.id == p1cases.account_id)</joinString> </joinedDataSetRef> <joinedDataSetRef><joinString>right outer join users2 users2 on (users2.id == p1cases.assigned_user_id)</joinString> </joinedDataSetRef> </joinedDataSetList></jdbcTable>[/code]
| • | joinedDataSetList – Container for the list of join statements. |
| • | joinedDataSetRef – Container for the join statement. |
| • | joinString – A string expressing a DomEL join statement in the following format: |
join_type join table_IDtable_alias on expression
Where:
| • | join_type – One of right outer, left outer, or full outer. Inner join is the default if no join type is specified. |
| • | table_ID – The ID of a table within the design. |
| • | table_alias – Alternative name to use for the table_ID within the join expression. By default, the alias is the same as the table_ID, but you may supply a true alias in handwritten design files. |
| • | expression – Expression that compares the columns on which the join is made, in the form |
left_table_alias.field_name == right_table_alias.field_name
The order of joinedDataSetRef elements is important. The first one must contain a join expression between the table_alias it defines and the alias in the joinInfo element. The subsequent ones may only reference the table_alias they define and ones that appear in joinString elements before them.
Advanced Joins in XML
In JasperReports Server 6.0, a new join syntax was added to the XML in the design file. This syntax uses a joinList element instead of joinedDataSetList. It supports all joins supported by the basic join syntax along with the following additional types of joins:
| • | comparison operators – in addition to equal to (==), the advanced syntax supports less than (<), less than or equal to (<=), greater than (>), greater than or equal to (gt;=), and not equal to (!=). |
| • | Boolean operators – AND and NOT. |
| • | IN operator with support for sets and ranges. |
| • | joins between a table foreign key and a constant value. |
Examples of each syntax type are given below.
| Advanced joins can only be created using XML in the design file. They are not supported in the Domain Designer. If you have a Domain that uses advanced joins, and you open it in the Domain Designer and then save it, your advanced joins will be lost. |
| Within each jdbcTable tag, the number of joins should be less than the number of tables. That is, if you have N tables, use at most N-1 joins. Using more than N-1 joins may result in unpredictable behavior. |
The following table shows how to express the basic joins in the advanced join syntax. This syntax gives the same join structure as in Example of Basic Joins in XML
<jdbcTable datasourceId="SugarCRMDataSource" id="JoinTree_1" tableName="accounts"> <tableRefList> <tableRef tableId="accounts_opportunities" alias="accounts_opportunities"/> <tableRef tableId="opportunities" alias="opportunities"/> <tableRef tableId="p1cases" alias="p1cases"/> <tableRef tableId="users1" alias="users1"/> <tableRef tableId="users2" alias="users2"/> </tableRefList> <fieldList> <field id="accounts_opportunities.account_id" type="java.lang.String"/> ... </fieldList> <joinInfo alias="accounts" referenceId="accounts"/> <joinList> <join left="accounts" right="accounts_opportunities" type="inner" expr="(accounts.id == accounts_opportunities.account_id)"/> <join left="accounts_opportunities" right="opportunities" type="inner" expr="(accounts_opportunities.opportunity_id == opportunities.id)"/>[/code] <join left="opportunities" right="users1" type="inner" expr="(opportunities.assigned_user_id == users1.id)"/> <join left="accounts" right="p1cases" type="leftOuter" expr="(accounts.id == p1cases.account_id)"/> <join left="p1cases" right="users2" type="rightOuter" expr="(users2.id == p1cases.assigned_user_id)"/> </joinList> </joinInfo></jdbcTable>[/code] |
| • | <tableRefList> – Container for a list of tables and aliases used in the join. Must include a tableRef tag for every table used, except the table in the joinInfo tag. |
| • | <tableRef> – A table and its alias. |
| • | table – The ID of a table within the design. |
| • | tableAlias – Alternative name for the table used within the join expression. |
| • | <joinList> – Container for the join statement |
| • | <join> – A string expressing an SQL join statement in the following format: |
left="left_table_ID" right=right_table_ID" type="join_type" expr="expression"
Where:
| • | left_table_ID – The first table in the join definition. This table must have been declared in the jdbcTable element, either in the <joinInfo> element or in a another join element in the joinList. |
| • | right_table_ID – The second table in the join definition. |
| • | join_type – One of inner, rightOuter, leftOuter, or fullOuter. |
| • | expression – Expression that compares the columns on which the join is made. The following expressions are supported: |
| • | Boolean operators – AND, OR, and NOT. See the other expressions for examples of how these are used. |
| • | comparison operators –Supports equal to (==), less than (<), less than or equal to (<=), greater than (>), greater than or equal to (gt;=), and not equal to (!=). Operators other than == must be used in conjunction with ==. For example: |
expr="(public_store.store_id == public_employee.store_id) AND
(public_store.coffee_bar != public_store.salad_bar)"
expr="(public_employee.employee_id == public_salary.employee_id) AND
(public_salary.overtime_paid > 2) AND (public_salary.overtime_paid <= 2.1)"
| • | IN operator – Must be used in conjunction with ==. Supports the following: |
| • | a set of strings or values, separated by commas. Strings are enclosed in single quotes. For example: |
expr="(public_store.region_id == public_region.region_id) AND (public_store.store_city IN ('San Francisco','Portland', 'Seattle'))"
expr="(public_store.store_id == public_employee.store_id) AND (NOT public_store.coffee_bar IN ('true'))"
| • | a range of values, separated by a colon. For example: |
expr="(public_employee.employee_id == public_salary.employee_id) AND (NOT public_employee.salary IN (7000 : 8000))"
| • | joins between a table foreign key and a constant value. Must be used in conjunction with ==. For example: |
expr="(public_employee.employee_id == public_salary.employee_id) AND (public_employee.salary == 5000)"
| If you are using a field of type Date with an Oracle database, use the JasperReports Server Date() function in your join expression. For example: <join left="FOODMART_STORE" right="FOODMART_EMPLOYEE" type="inner" expr="(FOODMART_STORE.STORE_ID == FOODMART_EMPLOYEE.STORE_ID) AND (FOODMART_EMPLOYEE.BIRTH_DATE > Date('1914-02-02'))"/> This applies to Date literals only; it is not necessary for fields with date formats but other field types, for example, Timestamp. |
Working With Advanced Joins
Keep the following in mind when working with advanced join syntax:
| • | Advanced joins are not supported in the Domain Designer. If you have a Domain that uses advanced joins, and you open it in the Domain Designer and then save it, your advanced joins will be lost. |
| • | You cannot combine advanced joins and basic joins in the same data island (jdbcTabl> tag). We recommend that you do not combine advanced joins and basic joins in the same Domain. |
| • | Within eachjdbcTable tag, the number of joins should be less than the number of tables. That is, if you have N tables, use at most N-1 joins. Using more than N-1 joins may result in unpredictable behavior. |
| • | When you use a join tag, the left table must have been defined previously. You can define the table either in the joinInfo tag or as the right table in a previous join in the same joinList. Follow these guidelines for your joins: |
| • | When you have a join that depends on the right table from another join in the joinList, make sure that the dependent join appears after the join it depends on. |
| • | When you have a join that refers to a table that has not yet been defined, make sure that the new table is referenced as the right table in the join. |
| • | In some cases, you can start your Domain creation using the Domain Designer. To do this: |
| a. | Set up the rest of your Domain, such as derived tables and calculated fields, in the Domain Designer. |
| b. | Create a join that uses the same tables that you want to join in your Domain. This will set up the jdbcTable and fieldList tags for you. |
| c. | Export the schema from the Domain Designer. |
| d. | Manually edit the tableRefList and joinInfo sections of the exported design file to create the join you want. |
Recommended Comments
There are no comments to display.