Table Joins

RowSet Controls and SQL Join Queries

Suppose you have two database tables, CUSTOMERS and ORDERS. CUSTOMERS is a unique list of customers. ORDERS records customers' orders. ORDERS contains a foreign key field that associates each order with a record on the CUSTOMERS table. The foreign key field identifies the customer by their customer ID number, the primary key of the CUSTOMERS table.

The schemas for the these table are shown below. (The syntax shown is Oracle syntax.)

        CREATE TABLE CUSTOMERZ 
           (
           customerz_id NUMBER(10) primary key,
           firstname VARCHAR2(100),
           lastname VARCHAR2(100)
           )

        CREATE TABLE PARTORDER
          (
          partorder_id NUMBER(12) NOT NULL
              CONSTRAINT partorder_pk PRIMARY KEY,
          customerz_id NUMBER(12) NOT NULL,
          sku NUMBER(8),
              CONSTRAINT partorder_customerz_fk
              FOREIGN KEY (customerz_id)
              REFERENCES customerz(customerz_id) 
          )

If you generate a RowSet control from the ORDERS table, the queries that are generated refer to the customer ID number, but not the customer name or any other data from the CUSTOMERS table.

    /**
     * @jc:sql command-type="grid"
     *   rowset-name="PARTORDERRowSet"
     *   max-rows="1000" 
     *   statement::
     *   SELECT PARTORDER_ID,CUSTOMERZ_ID,SKU FROM DEMO.PARTORDER {sql: filter.getWhereClause ()} {sql: filter.getOrderByClause ()}
     *   ::
     */
    public RowSet getAllPartorder(DatabaseFilter filter)
        throws SQLException;

How can you edit the RowSet control to return the customer name and other customer data? Below are general rules for editting an auto generated RowSet control to accomodate join queries.

Step 1: Add additional methods for the join queries.

For example, suppose you want the following join query included in your RowSet control.

    SELECT O.PARTORDER_ID,O.CUSTOMERZ_ID,C.FIRSTNAME,C.LASTNAME,O.SKU FROM customerz C, partorder O WHERE C.customerz_id=O.customerz_id

You could add the query by adding the following method to the RowSet control.

    /**
     * @jc:sql command-type="grid"
     *   rowset-name="PARTORDERRowSet"
     *   max-rows="1000" 
     *   statement::
     *   SELECT O.PARTORDER_ID,O.CUSTOMERZ_ID,C.FIRSTNAME,C.LASTNAME,O.SKU FROM customerz C, partorder O WHERE C.customerz_id=O.customerz_id
     *   ::
     */
    public RowSet getAllCustomerorders(DatabaseFilter filter)
        throws SQLException;

Note: If you plan to generate a page flow from your RowSet control, you should keep the column list in the @jc:sql annotation on one line, which ensures that the page flow wizard can read your control methods correctly.

Step 2: Edit the RowSet-schema annotation to add columns mentioned in the join queries

Each RowSet control contains an RowSet-schema annotation that controls the data returned from the database. For example, the following RowSet-schema is generated when you autogenerate a RowSet control for the ORDERS table.

/**
 * @jc:connection data-source-jndi-name="oradev_source"
 * @common:schema file="#rowset-schemas" inline="true"
 * @common:define name="rowset-schemas" value::
 *    <xsd:schema targetNamespace="java:///database.oracle.relationalDB.rowset" xmlns="java:///database.oracle.relationalDB.rowset" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:wld="http://www.bea.com/2002/10/weblogicdata" elementFormDefault="qualified" attributeFormDefault="unqualified">
 *     <xsd:element name="PARTORDERRowSet" wld:DefaultNamespace="java:///database.oracle.relationalDB.rowset" wld:RowSet="true" wld:WriteTable="DEMO.PARTORDER">
 *      <xsd:complexType>
 *       <xsd:choice maxOccurs="unbounded">
 *        <xsd:element name="PARTORDERRow" wld:DatabaseProductName="Oracle">
 *         <xsd:complexType>
 *          <xsd:sequence>
 *           <xsd:element name="PARTORDER_ID" type="xsd:integer" wld:JDBCType="NUMERIC" minOccurs="0" wld:PrimaryKey="true" wld:ReadOnly="true" wld:TableName="DEMO.PARTORDER">
 *           </xsd:element>
 *           <xsd:element name="CUSTOMERZ_ID" type="xsd:integer" wld:JDBCType="NUMERIC" minOccurs="0" wld:TableName="DEMO.PARTORDER">
 *           </xsd:element>
 *           <xsd:element name="SKU" type="xsd:integer" wld:JDBCType="NUMERIC" minOccurs="0" wld:TableName="DEMO.PARTORDER" nillable="true">
 *           </xsd:element>
 *          </xsd:sequence>
 *          <xsd:anyAttribute namespace="http://www.bea.com/2002/10/weblogicdata" processContents="skip">
 *          </xsd:anyAttribute>
 *         </xsd:complexType>
 *        </xsd:element>
 *       </xsd:choice>
 *      </xsd:complexType>
 *     </xsd:element>
 *    </xsd:schema>::
 * 
 */
public interface OrdersRowSet extends ControlExtension, DatabaseControl

When you add columns to a RowSet-schema, keep the following points in mind:

You must set the attribute wld:ReadOnly to true, because only one table is updatable through a given RowSet control.

For the sample join query above, add the columns shown in red below.

/**
 * @jc:connection data-source-jndi-name="oradev_source2"
 * @common:schema file="#rowset-schemas" inline="true"
 * @common:define name="rowset-schemas" value::
 *    <xsd:schema targetNamespace="java:///database.oracle.relationalDB.rowset" xmlns="java:///database.oracle.relationalDB.rowset" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:wld="http://www.bea.com/2002/10/weblogicdata" elementFormDefault="qualified" attributeFormDefault="unqualified">
 *     <xsd:element name="PARTORDERRowSet" wld:DefaultNamespace="java:///database.oracle.relationalDB.rowset" wld:RowSet="true" wld:WriteTable="DEMO.PARTORDER">
 *      <xsd:complexType>
 *       <xsd:choice maxOccurs="unbounded">
 *        <xsd:element name="PARTORDERRow" wld:DatabaseProductName="Oracle">
 *         <xsd:complexType>
 *          <xsd:sequence>
 *           <xsd:element name="PARTORDER_ID" type="xsd:integer" wld:JDBCType="NUMERIC" minOccurs="0" wld:PrimaryKey="true" wld:ReadOnly="true" wld:TableName="DEMO.PARTORDER">
 *           </xsd:element>
 *           <xsd:element name="CUSTOMERZ_ID" type="xsd:integer" wld:JDBCType="NUMERIC" minOccurs="0" wld:TableName="DEMO.PARTORDER">
 *           </xsd:element>
             <xsd:element name="FIRSTNAME" type="xsd:string" wld:readOnly="true" wld:JDBCType="VARCHAR" minOccurs="0" wld:TableName="DEMO.CUSTOMERZ">
             </xsd:element> 
             <xsd:element name="LASTNAME" type="xsd:string" wld:readOnly="true" wld:JDBCType="VARCHAR" minOccurs="0" wld:TableName="DEMO.CUSTOMERZ">
             </xsd:element> 
 *           <xsd:element name="SKU" type="xsd:integer" wld:JDBCType="NUMERIC" minOccurs="0" wld:TableName="DEMO.PARTORDER" nillable="true">
 *           </xsd:element>
 *          </xsd:sequence>
 *          <xsd:anyAttribute namespace="http://www.bea.com/2002/10/weblogicdata" processContents="skip">
 *          </xsd:anyAttribute>
 *         </xsd:complexType>
 *        </xsd:element>
 *       </xsd:choice>
 *      </xsd:complexType>
 *     </xsd:element>
 *    </xsd:schema>::
 * 
 */
public interface OrdersRowSet extends ControlExtension, DatabaseControl

RowSet Controls and SQL Join Queries

Step 1: Add additional methods for the join queries.

Step 2: Edit the RowSet-schema annotation to add columns mentioned in the join queries

Related Topics