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:
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".
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.
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.
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.
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.
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,
masterLink
property from the Properties page of the Inspector. In the masterLink
custom property editor, specify the following properties for the detail data set:
masterDataSet
property provides a choice menu of available data sets. Choose the data set that contains the master records for the current detail data set.
Available Master Columns
. This column will now display in the Master Link Columns
property.
Available Detail Columns
. The data type for each column is shown. If you select a detail column whose type does not match the corresponding master column, nothing will happen since the link columns must match by type. When properly selected, this column will display in the Detail Link Columns
property.
Running
, Success
, or Failed
.
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.
To save changes back to the tables, see "Saving changes in 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,
Select the Design tab in the content pane.
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.
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.
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 |
Click the Test Query button to ensure that the query is runnable. When the status area indicates Success, click OK to close the dialog.
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 |
Click the Test Query button to ensure that the query is runnable. When the status area indicates Success, click OK to close the dialog.
Select the masterLink
property for the detail data set (queryDataSet2
) in the Inspector. In the masterLink
property editor, set the properties as follows:
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
.
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.
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.
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".
Click Test Link. The dialog should look like below when you are done. When successful, click OK.
Add a DBDisposeMonitor
to your application from the More dbSwing tab. The DBDisposeMonitor
will close the JDataStore when the window is closed.
Set the DBDisposeMonitor
's dataAwareComponentContainer
property to 'this'.
To create a UI for this application,
contentPane (BorderLayout)
in the component tree. Set its layout
property to null
.
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.
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.
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.
Drop a JdbTable
into the center of tableScrollPane1
in the UI designer. Set its dataSet
property to queryDataSet1
.
Add another TableScrollPane
to the lower part of the panel in the UI designer. This will become tableScrollPane2
.
Drop a JdbTable
into tableScrollPane2
and set its dataSet
property to queryDataSet2
.
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: