Representing Joins in XML

This documentation is an older version of JasperReports Server User Guide. View the latest documentation.

Representing Joins in XML

A join is represented in the design file as a special jdbcTable element. There are several 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. This is the only syntax that is fully supported in Domain Designer.

•

advanced join syntax – In addition to joins based on equality comparisons, advanced join syntax supports 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 later.

•

advanced joins syntax in schema version 1.2 – In JasperReports Server 6.1.1, elements and attributes were added to give you influence over which join paths are chosen.

Advanced joins can be created only with XML. They are not supported in the Domain Designer. However, the Domain Designer will not prevent you from opening Domains with advanced joins. 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 basic and advanced 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.

If you use the jdbcQuery element to define derived tables, you may have additional data islands in your Domain.

The Domain Designer automatically exposes all columns of all tables in a join, but in the design file you need to specify only 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 different meanings 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 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 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. This format uses the joinInfo and joinedDataSetList elements to define the actual join. It also contains a list of the columns 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 reference only 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 (!=).

You have to use the character entities for less than (<) and greater than (>)

•

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.

When you add or modify joins in the Domain design file, make sure that all tables in a join element are actually connected. If you include a table that is not actually joined to any other tables, the unjoined table will be included in any Ad Hoc view that uses that data island. In this case, the fields in the unjoined table will show up in the Ad Hoc view. You will receive errors in your Ad Hoc view when you add unconnected fields from a poorly-configured join.

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></jdbcTable>[/code]

•

joinOptions element – Child element of jdbcTable. Allows you to influence the joins that are pushed down to SQL in an Ad Hoc view. A jdbcTable element can contain only one joinOptions element. joinOptions has the following attributes:

•

suppressCircularJoins (Boolean; default = false) – When this attribute is set to true for a join set/data island, an Ad Hoc view created from the data island uses a minimum join. When suppressCircularJoins is set to true, you can optionally assign weights to the joins in the join set.

For most situations, the best practice is to set this option to true to avoid circular joins. Set it to false only if you need backwards compatibility with schema_1_0.xsd.

•

includeAllDataIslandJoins (Boolean; default = false) – Available for backwards compatibility. When this attribute is set to true for a join set/data island, an Ad Hoc view created from the data island includes all joins specified for that data island in the Domain. For most situations, the best practice is to set this option to false (default).

•

<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.

•

alwaysIncludeTable (version 1.2 of the schema only) – Include this table in all Ad Hoc views created from this data island.

•

<joinList> – Container for the join statement. The left join in the first join in the joinList must be the table given by the tableName attribute of jdbcTable.

•

<join> – A string expressing an SQL join statement. The following attributes are required; an additional optional weight attribute was added in version 1.2 of the schema:

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 another join element that precedes it 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. Each column used in the expression must come from the two tables in the current join element. When two columns are compared directly – for example, public_store.store_id == public_employee.store_id – the first column must come from the left table and the second column must come from the right table.

•

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)"

•

weight (optional; schema_1_2 only) – Boolean that specifies a weight to use when calculating the minimum join path.

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:

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.

•

If you are using advanced joins with version 1.0 of the schema, the number of joins must be less than the number of tables within each jdbcTable tag. That is, if you have N tables, use at most N-1 joins. In JasperReports Server 6.1.1, additional elements were added to help you influence join paths when you have more than N-1 joins. See Minimum Paths and Circular Joins in Version 2.0 of the Schema for more information.

•

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:

Set up the rest of your Domain, such as derived tables and calculated fields, in the Domain Designer.

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.

Export the schema from the Domain Designer.

Manually edit the tableRefList and joinInfo sections of the exported design file to create the join you want.

Minimum Paths and Circular Joins in Version 2.0 of the Schema

A new schema, schema_1_2.xsd, added in JasperReports Server 6.1.1 includes syntax that gives you influence over which join paths are chosen when multiple options are possible. These attributes are primarily used to avoid circular joins. To use these options, your Domain design file must reference the updated schema, schema_1_2.xsd, and you must use the advanced join syntax.

schema_1_2.xsd supports the original basic and advanced join syntax. In addition, the following new elements and properties have been added to the advanced syntax:

•

For most situations, the best practice is to set this option to true to avoid circular joins. Set it to false only if you need backwards compatibility with schema_1_0.xsd.

•

weight attribute (positive integer; default = 1) – New attribute for the join element; only available when suppressCircularJoins is set to true. You can assign a weight to one, several, or all of the join elements in a join set that has suppressCircularJoins enabled. Higher weights indicate costlier, less-desirable joins.

•

alwaysIncludeTable attribute (Boolean; default=false) – Available for backwards compatibility. Attribute of tableRef. When this attribute is set to true, the table is included in all Ad Hoc views that use this data island. For most situations, the best practice is to set this option to false (default).

Understanding Circular Joins

The Domain Designer and Domain Design file do not impose any limits on the number of joins you can specify for a given number of tables. However, when you define joins among N tables, it is good design to use N-1 or fewer joins, in order to avoid circular joins.

Circular joins occur, for example, when table A is joined to table B and table B is joined to table C which is in turn joined to table A.

Circular Joins in a Domain

Suppose you create an Ad Hoc view using table A and table B from such a Domain. JasperReports Server attempts to avoid circular joins in the SQL by choosing a path between the tables. However, if you are using schema_1_0.xsd, you cannot be sure which join path the Ad Hoc view will use: the direct join from A–B or the indirect join A–C–B.

In this situation, you can use joinOptions with the suppressCircularJoins set to true. What this does is assign a default weight of 1 to each join, as shown in the following simplified diagram.

The best practice is to avoid circular joins in your Domain by creating aliases for one or more tables in the join. For an example of creating a table alias, see step 12in Example of Creating a Domain.

Circular Join with Default Weights

Now, when you create an Ad Hoc view using table A and table B, the Ad Hoc Designer calculates the weight of the possible join paths from A to B by summing the weights of their subpaths. The direct path A–B has total weight=1, while the indirect path A–C–B has weight 1+1=2. The path with the smallest total weight is chosen, in this case, the direct path from A to B.

Note, however, that this does not cover all cases. For example, suppose you have four joins as shown in the following table, where table A is joined to tables C and D, and table B is also joined to tables C and D. If you create an Ad Hoc view with tables A and B, both possible paths A–C–B and A–D–B have weight 1+1=2, so again, you do not know which join is being used.

Circular Joins with Equal Weights Between A and B

To exert even more control over the joins used by your Ad Hoc view, you can add an optional weight to one or more of the join elements when you are using suppressCircularJoins set to true. Adding a weight increases the cost of a particular join, and reduces its desirability. The higher you set a weight the more you dislike the join. For example, you can add a weight of 2 to the join between table B and table D. Then you can ensure that an Ad Hoc view with tables A and B will use the A–C–B join path, which only has a total weight of 2, and not the A–D–B join path, which now has a total weight of 3. Note that, in this situation, an Ad Hoc view with tables B and D will still use the direct path with weight 2, instead of the path B–C–A–D, which has a total weight of 3.

Circular Joins with Custom Weight

suppressCircularJoins Syntax

The following example shows how you might use suppressCircularJoins in a Domain file, giving the second join in the list a weight of 10:

<schema xmlns="http://www.jaspersoft.com/2007/SL/XMLSchema" version="1.2">[/code]

alwaysIncludeTable

The alwaysIncludeTable property of the tableRef element lets you specify that any Ad Hoc view created from your Domain must include the indicated table. You can use this property whether suppressCircularJoins is true or false.

For example, suppose you have four joins for four tables, where table A is joined to tables C and D, and table B is also joined to tables C and D and you create an Ad Hoc view using tables A and B. As mentioned above, there are two possible join paths, A–C–B and A–D–B. If you set the alwaysIncludeTable property of table D to true and suppressCircularJoins is false or unspecified, the Ad Hoc view will use the A–D–B path.

Including a Table

When suppressCircularJoins is true, you can use both the alwaysIncludeTable property and the weight property to further control the join path. For example, suppose instead of the default weights, you set the weight on the join between A and D to 100. In this case, the join path with the minimum weight is actually the join that goes from A–C–B–D, avoiding the join from A to D.

Using alwaysIncludeTable and Weighted Joins

The following example shows how you might set alwaysIncludeTable as well as a join weight:

<schema xmlns="http://www.jaspersoft.com/2007/SL/XMLSchema" version="1.2">[/code]

<jdbcTable id="JoinTree_1" datasourceId="FoodmartDataSourceJNDI" tableName="public.customer">  <fieldList>    <field id="public_customer.account_num" fieldDBName="account_num" type="java.lang.Long" />    ...  <fieldList>       <joinInfo alias="public_store" referenceId="public_store" />	<joinOptions suppressCircularJoins="true"/>     	 <tableRefList>	   <tableRef tableId="public_store" tableAlias="public_store"/>	   <tableRef tableId="public_customer" tableAlias="public_customer"/>	   <tableRef alwaysIncludeTable="true" tableId="public_region" tableAlias="public_region"/>	 </tableRefList>	 	<joinList>	  <join left="public_store" right="public_customer" type="inner"                          expr="public_store.region_id == public_customer.customer_region_id"/>	  <join left="public_store" right="public_region" type="inner"                          expr="public_store.region_id == public_region.region_id"/>	  <join weight="10" left="public_region" right="public_customer" type="inner"                          expr="public_region.region_id == public_customer.customer_region_id"/>       </joinList>      </jdbcTable>[/code]

includeAllDataIslandJoins

The includeAllDataIslandJoins property of the joinOptions element lets you specify that any Ad Hoc view created from your Domain must include all joins in your data island. This option can only be set to true when suppressCircularJoins is false.

Joins in a Data Island

In the figure above, if you set includeAllDataIslandJoins to true for Data Island 1, then an Ad Hoc view containing tables A and B will include all joins between tables A, B, C and D. However, the join between X and Y is in a different data island, and is not included.

The following example shows how to use includeAllDataIslandJoins in a Domain file:

<joinOptions suppressCircularJoins="false" includeAllDataIslandJoins="true"/>[/code]

You can include at most one joinOptions element in a specific jdbcTable element.

Tips for Using Version 2.0 Joins

•

To use the features in version 2.0, set your domain schema version to 1.2 and use the <joinsOptions> syntax and the <tableRef> element.

•

To avoid join cycles in queries set <joinOptions suppressCircularJoins=true/>. This will find the lowest weight join set.

•

To configure your Domain to use the fewest number of joins required to reach a given set of chosen fields, use <joinOptions suppressCircularJoins=true/> AND set all <join weight="W"> values to be the same value. In this case, the lowest weight join set will be a set that has the minimum possible number of joins. You do not have to set the join weight explicitly. If the weight is not set, it defaults to 1.

•

To give preferential treatment to joins that involve indexed columns for better join performance, assign lower join weights to the preferred joins and higher join weights to less desirable joins.

•

To make sure that one or more specific table(s) always get included in your results, set alwaysIncludeTable=true. For example, to make sure tableA is always included, use

design files: joins

Followers 0

Go to documents

User Feedback

0 Comments

Recommended Comments

There are no comments to display.

This is now closed for further comments

Sign In