Establishing a master-detail relationship

Database application development is a feature of JBuilder Professional and Enterprise. Distributed application development is a feature of JBuilder Enterprise.

Databases that are efficiently designed include multiple tables. The goal of table design is to store all the information you need in an accessible, efficient manner. Therefore, you want to break down a database into tables that identify the separate entities (such as persons, places, and things) and activities (such as events, transactions, and other occurrences) important to your application. To better define your tables, you need to identify and understand how they relate to each other. Creating several small tables and linking them together reduces the amount of redundant data, which in turn reduces potential errors and makes updating information easier.

In JBuilder, you can join, or link, two or more data sets that have at least one common field using a MasterLinkDescriptor. A master-detail relationship is usually a one-to-many type relationship among data sets. For example, say you have a data set of customers and a data set of orders placed by these customers, where customer number is a common field in each. You can create a master-detail relationship that will enable you to navigate through the customer data set and have the detail data set display only the records for orders placed by the customer who is exposed in the current record.

You can link one master data set to several detail data sets, linking on the same field or on different fields. You can also create a master-detail relationship that cascades to a one-to-many-to-many type relationship. Many-to-one or one-to-one relationships can be handled within a master-detail context, but these kinds of relationships would be better handled through the use of lookup fields, in order to view all of the data as part of one data set. For information on saving changes to data from multiple data sets, see the "Resolving data from multiple tables" topic later in the chapter.

The master and detail data sets do not have to be of the same data set type. For example, you could use a QueryDataSet as the master data set and a TableDataSet as the detail data set. QueryDataSet, TableDataSet, and DataSetView can all be used as either master or detail data sets.

These are the topics covered:

Defining a master-detail relationship

When defining a master-detail relationship, you must link columns of the same data type. For example, if the data in the master data set is of type INT, the data in the detail data set must be of type INT as well. If the data in the detail data set were of type LONG, either no matches or incorrect matches would be found. The names of the columns may be different. You are not restricted to linking on columns that have indexes on the server.

You can sort information in the master data set with no restrictions. Linking between a master and a detail data set uses the same mechanism as maintaining sorted views, a maintained index. This means that a detail data set will always sort with the detail linking columns as the left-most sort columns. Additional sorting criteria must be compatible with the detail linking columns. To be compatible, the sort descriptor cannot include any detail linking columns or, if it does include detail linking columns, they must be specified in the same order in both the detail linking columns and the sort descriptor. If any detail linking columns are included in the sort descriptor, all of them should be specified.

You can filter the data in the master data set, the detail data set, or in both. A master-detail relationship alone is very much like a filter on the detail data set; however, a filter can be used in addition to the master-detail relationship on either data set.

Instead of using a MasterLinkDescriptor, you may use a SQL JOIN statement to create a master-detail relationship. A SQL JOIN is a relational operator that produces a single table from two tables, based on a comparison of particular column values (join columns) in each of the data sets. The result is a single data set containing rows formed by the concatenation of the rows in the two data sets wherever the values of the join columns compare. To update JOIN queries with JBuilder, see "Resolving data from multiple tables".

Fetching details

In a master-detail relationship, the values in the master fields determine which detail records will display. The records for the detail data set can be fetched all at once or can be fetched for a particular master when needed (when the master record is visited).

Be careful when using the cascadeUpdates and cascadeDelete options for master-detail relationships. When using these options, one row of a detail data set may be updated or deleted, but the others may not be. For example, an event handler for the editListener's deleting() event may allow deletion of some detail rows and block deletion of others. In the case of cascaded updates, you may end up with orphan details if some rows in a detail set can be updated and others cannot. For more information on the cascadeUpdates and cascadeDelete options, see the MasterLinkDescriptor topic in the DataExpress Library Reference.

Fetching all details at once

When the fetchAsNeeded parameter is false (or Delay Fetch Of Detail Records Until Needed is unchecked in the masterLinkDescriptor dialog box), all of the detail data is fetched at once. Use this setting when your detail data set is fairly small. You are viewing a snapshot of your data when you use this setting, which will give you the most consistent view of your data. When the refresh() method is called, all of the detail sets are refreshed at once.

For example, initially the data set is populated with all of the detail data set data. When the fetchAsNeeded option is set to false, you could instantiate a DataSetView component, view the detail data set through it, and see that all of the records for detail data set are present, but are being filtered from view based on the linking information being provided from the master data set.

Fetching selected detail records on demand

When the fetchAsNeeded parameter is true (or Delay Fetch Of Detail Records Until Needed is checked in the masterLinkDescriptor dialog box), the detail records are fetched on demand and stored in the detail data set. This type of master-detail relationship is really a parameterized query where the values in the master fields determine which detail records will display. You are most likely to use this option if your remote database table is very large, in order to improve performance (not all of the data set will reside in memory - it will be loaded as needed). You would also use this option if you are not interested in most of the detail data. The data that you view will be fresher and more current, but not be as consistent a snapshot of your data as when the fetchAsNeeded parameter is false=. You will fetch one set of detail records at one point in time, it will be cached in memory, then you will fetch another set of detail records and it will be cached in memory. In the meantime, the first set of detail records may have changed in the remote database table, but you will not see the change until you refresh the details. When the refresh() method is called, only the current detail sets are refreshed.

For example, initially, the detail data set is empty. When you access a master record, for example Jones, all of the detail records for Jones are fetched. When you access another master record, say Cohen, all of the detail records for Cohen are fetched and appended to the detail data set. If you instantiate a DataSetView component to view the detail data set, all records for both Jones and Cohen are in the detail data set, but not any records for any other name.

When the fetchAsNeeded property is true, there should be a WHERE clause that defines the relationship of the detail columns in the current QueryDataSet to a parameter that represents the value of a column in the master data set. If the parameterized query has named parameter markers, the name must match a name in the master data set. If "?" JDBC parameter markers are used, the detail link columns are bound to the parameter markers from left to right as defined in the masterLink property. The binding of the parameter values is implicit when the master navigates to a row for the first time. The query will be re-executed to fetch each new detail group. If there is no WHERE clause, JBuilder throws DataSetException.NO_WHERE_CLAUSE. When fetching is handled this way, if no explicit transactions are active, the detail groups will be fetched in separate transactions. For more information on master-detail relationships within parameterized queries, see "Parameterized queries in master-detail relationships".

When the the master data set has two or more detail data sets associated with it, and the fetchAsNeeded property of each is true, the details remember what detail groups they have attempted to fetch via a query or stored procedure that is parameterized on the active master row linking columns. This memory can be cleared by calling the StorageDataSet.empty() method. There is no memory for masterLink properties that do not set fetchAsNeeded to true.

When the detail data set is a TableDataSet, the fetchAsNeeded parameter is ignored and all data is fetched at once.

Editing data in master-detail data sets

You cannot delete or change a value in a master link column (a column that is linked to a detail data set) if the master record has detail records associated with it.

By default, detail link columns will not be displayed in a JdbTable UI component, because these columns duplicate the values in the master link columns, which are displayed. When a new row is inserted into the detail data set, JBuilder will insert the matching values in the non-displayed fields.

Steps to creating a master-detail relationship

To create a master-detail link between two data set components, one which represents the master data set and another which represents the detail data set,

  1. Create or open an application with at least two data set components, one of which represents the master data set and another which represents the detail data set, or go to a tutorial that uses the sample database files shipped with JBuilder, "Tutorial: Creating a master-detail relationship".

  2. Select the Frame file in the content pane. Select the Design tab to activate the UI designer.
  3. Select the detail data set in the component tree, and select its masterLink property from the Properties page of the Inspector. In the masterLink custom property editor, specify the following properties for the detail data set:

  4. Add visual components (such as JdbTables) to enable you to view and modify data. Set the dataSet property of one to the master data set, and set the dataSet property of the other to the detail data set.

  5. Compile and run the application. The master data set will display all records. The detail data set will display the records that match the values in the linked columns of the current row of the master data set, but will not display the linked columns.

To save changes back to the tables, see "Saving changes in a master-detail relationship".

Tutorial: Creating a master-detail relationship

This tutorial shows how to create a master-detail relationship, using the sample files shipped with JBuilder. The basic scenario for the sample application involves constructing two queries, one that selects all of the unique countries from the COUNTRY table in the employee sample file, and one that selects all of the employees. This tutorial is available as a finished project in the /samples/DataExpress/MasterDetail directory of your JBuilder installation.

The COUNTRY data set is the master data set, with the column COUNTRY being the field that we will link to EMPLOYEE, the detail data set. Both data sets are bound to JdbTables, and as you navigate through the COUNTRY table, the EMPLOYEE table displays all of the employees who live in the country indicated as the current record.

To create this application,

  1. Select File|Close. Select File|New, and double-click the Application icon. Accept all defaults.
  2. Select the Design tab in the content pane.

  3. Select a Database component from the Data Express tab of the component palette, click in the component tree or the UI designer to add the component to your application.

  4. Open the connection property for the Database component in the Inspector, and set properties as follows, assuming your system is set up to use the JDataStore sample as described in "Installing and setting up JBuilder for database applications" (which includes adding the JDataStore library to the project):

    Property name Value
    Driver com.borland.datastore.jdbc.DataStoreDriver
    URL Browse to your local copy of /jbuilder/samples/JDataStore/datastores/employee.jds
    Username Enter your name
    Password not required

    The connection dialog includes a Test Connection button. Click this button to check that the connection properties have been correctly set. Results of the connection attempt are displayed beside the button. When the connection is successful, click OK.

    The code generated by the designer for this step can be viewed by selecting the Source tab and looking for the ConnectionDescriptor code. Click the Design tab to continue.

  5. Select a QueryDataSet component from the Data Express tab, and click in the component tree to add the component to your application. This component sets up the query for the master data set. Select the query property of the QueryDataSet component from the Inspector, and set as follows:
    Property name Value
    Database database1
    SQL Statement select * from COUNTRY

  6. Click the Test Query button to ensure that the query is runnable. When the status area indicates Success, click OK to close the dialog.

  7. Add another QueryDataSet component to your application. Select its query property in the Inspector. This will set up the query for the detail data set. In the query custom property editor, set the following properties:
    Property name Value
    Database database1
    SQL Statement select * from EMPLOYEE

  8. Click the Test Query button to ensure that the query is runnable. When the status area indicates Success, click OK to close the dialog.

  9. Select the masterLink property for the detail data set (queryDataSet2) in the Inspector. In the masterLink property editor, set the properties as follows:

    1. The Master DataSet property provides a choice menu of available data sets. Choose the data set that contains the master records for the current detail data set, in this case select queryDataSet1.
    2. The link fields describe which fields to use when determining matching data between the master and detail data set components. To select a column from the master data set to link with a column in the detail data set, select the column name, in this case COUNTRY (a string field), from the list of Available Master Columns then click the Add to Master Links button. This column displays in the Master Link Columns box.

    3. To select the column from the detail data set to link with a column in the master data set, select the column name, in this case JOB_COUNTRY (a string field), from the list of Available Detail Columns, then click the Add to Detail Links button. This column displays in the Detail Link Columns box.

    4. The Delay Fetch Of Detail Records Until Needed option determines whether the records for the detail data set can be fetched all at once or can be fetched for a particular master when needed (when the master record is visited). Uncheck this box to set fetchAsNeeded to false. For more information on fetching, see "Fetching details".

    5. Click Test Link. The dialog should look like below when you are done. When successful, click OK.

  10. Add a DBDisposeMonitor to your application from the More dbSwing tab. The DBDisposeMonitor will close the JDataStore when the window is closed.

  11. Set the DBDisposeMonitor's dataAwareComponentContainer property to 'this'.

To create a UI for this application,

  1. Select contentPane (BorderLayout) in the component tree. Set its layout property to null.
  2. Add a JdbNavToolBar component from the dbSwing tab. Drop the component in the area at the top of the panel in the UI Designer. JdbNavToolBar automatically attaches itself to whichever DataSet has focus.

  3. Add a JdbStatusLabel and drop it in the area at the bottom of the panel in the UI designer. JdbStatusLabel automatically attaches itself to whichever DataSet has focus.

  4. Select a TableScrollPane component from the dbSwing tab, click and drag the outline for the pane in the upper portion of the UI designer to add it to the application just under jdbNavToolBar1.

    Scrolling behavior is not available by default in any Swing component or dbSwing extension, so, to get scrolling behavior, we add the scrollable Swing or dbSwing components to a JScrollPane or a TableScrollPane. TableScrollPane provides special capabilities to JdbTable over JScrollPane. See the dbSwing documentation for more information.

  5. Drop a JdbTableinto the center of tableScrollPane1 in the UI designer. Set its dataSet property to queryDataSet1.

  6. Add another TableScrollPane to the lower part of the panel in the UI designer. This will become tableScrollPane2.

  7. Drop a JdbTable into tableScrollPane2 and set its dataSet property to queryDataSet2.

  8. Compile and run the application by selecting Run|Run Project.

Now you can move through the master (COUNTRY) records and watch the detail (EMPLOYEE) records change to reflect only those employees in the current country.

The running application looks like this: