more on creating database forms using appbuilder
DESCRIPTION
More on Creating Database Forms Using AppBuilder. Objectives. After completing this lesson, you should be able to do the following: Create master-detail forms Use JBCL components programmatically in Java code Filter and sort data records Use data modules, and perform parameterized queries. - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: More on Creating Database Forms Using AppBuilder](https://reader030.vdocuments.us/reader030/viewer/2022032709/568131ca550346895d9830b5/html5/thumbnails/1.jpg)
Copyright Oracle Corporation, 1998. All rights reserved.
55
More on Creating Database Forms Using AppBuilder
More on Creating Database Forms Using AppBuilder
![Page 2: More on Creating Database Forms Using AppBuilder](https://reader030.vdocuments.us/reader030/viewer/2022032709/568131ca550346895d9830b5/html5/thumbnails/2.jpg)
Copyright Oracle Corporation, 1998. All rights reserved.5-5-22
ObjectivesObjectives
After completing this lesson, you should be able to do the following:
• Create master-detail forms
• Use JBCL components programmatically in Java code
• Filter and sort data records
• Use data modules, and perform parameterized queries
After completing this lesson, you should be able to do the following:
• Create master-detail forms
• Use JBCL components programmatically in Java code
• Filter and sort data records
• Use data modules, and perform parameterized queries
![Page 3: More on Creating Database Forms Using AppBuilder](https://reader030.vdocuments.us/reader030/viewer/2022032709/568131ca550346895d9830b5/html5/thumbnails/3.jpg)
Copyright Oracle Corporation, 1998. All rights reserved.5-5-33
Master-Detail RelationshipsMaster-Detail Relationships
LOC
NEW YORK
DALLAS
CHICAGO
BOSTON
DEPTNO
10
20
30
40
DNAME
ACCOUNTING
RESEARCH
SALES
OPERATIONS
Master table, Master table, DEPTDEPT
EMPNO
7839
7782
7934
7566
7902
ENAME
KING
CLARK
MILLER
JONES
FORD
Details table, Details table, EMPEMP
DEPTNO
10
10
10
20
20
![Page 4: More on Creating Database Forms Using AppBuilder](https://reader030.vdocuments.us/reader030/viewer/2022032709/568131ca550346895d9830b5/html5/thumbnails/4.jpg)
Copyright Oracle Corporation, 1998. All rights reserved.5-5-44
Master-Detail Form at Run TimeMaster-Detail Form at Run Time
This is how the Master-Detail form will appear at run time
Master section
Detail section
This is how the Master-Detail form will appear at run time
Master section
Detail section
![Page 5: More on Creating Database Forms Using AppBuilder](https://reader030.vdocuments.us/reader030/viewer/2022032709/568131ca550346895d9830b5/html5/thumbnails/5.jpg)
Copyright Oracle Corporation, 1998. All rights reserved.5-5-55
Two or more QueryDataSets are required in a master-detail formTwo or more QueryDataSets are required in a master-detail form
Specifying QueryDataSet Properties
Specifying QueryDataSet Properties
Detail query Detail query Master query Master query
![Page 6: More on Creating Database Forms Using AppBuilder](https://reader030.vdocuments.us/reader030/viewer/2022032709/568131ca550346895d9830b5/html5/thumbnails/6.jpg)
Copyright Oracle Corporation, 1998. All rights reserved.5-5-66
Establishing the Master-Detail Relationship
Establishing the Master-Detail Relationship
Specify masterLink property in detail QueryDataSet
Specify masterLink property in detail QueryDataSet
![Page 7: More on Creating Database Forms Using AppBuilder](https://reader030.vdocuments.us/reader030/viewer/2022032709/568131ca550346895d9830b5/html5/thumbnails/7.jpg)
Copyright Oracle Corporation, 1998. All rights reserved.5-5-77
Connecting the Master Query to the Detail Query
Connecting the Master Query to the Detail Query
This column in themaster query … This column in themaster query …
… is linked to thiscolumn in the detail query
… is linked to thiscolumn in the detail query
![Page 8: More on Creating Database Forms Using AppBuilder](https://reader030.vdocuments.us/reader030/viewer/2022032709/568131ca550346895d9830b5/html5/thumbnails/8.jpg)
Copyright Oracle Corporation, 1998. All rights reserved.5-5-88
Data-aware controls can now be added to display the fields
Fields attached to master query
Fields attached to detail query
Data-aware controls can now be added to display the fields
Fields attached to master query
Fields attached to detail query
Using the Master and Detail QueryDataSets
Using the Master and Detail QueryDataSets
![Page 9: More on Creating Database Forms Using AppBuilder](https://reader030.vdocuments.us/reader030/viewer/2022032709/568131ca550346895d9830b5/html5/thumbnails/9.jpg)
Copyright Oracle Corporation, 1998. All rights reserved.5-5-99
Delayed FetchingDelayed Fetching
• Delayed fetching can improve performance
• Data in the “details” data set is fetched only when needed
• Select “Delay fetch…” check box in masterLink editor
• A WHERE clause is required in the SQL string of the detail QueryDataSet
• Delayed fetching can improve performance
• Data in the “details” data set is fetched only when needed
• Select “Delay fetch…” check box in masterLink editor
• A WHERE clause is required in the SQL string of the detail QueryDataSet
![Page 10: More on Creating Database Forms Using AppBuilder](https://reader030.vdocuments.us/reader030/viewer/2022032709/568131ca550346895d9830b5/html5/thumbnails/10.jpg)
Copyright Oracle Corporation, 1998. All rights reserved.5-5-1010
Calculated ColumnsCalculated Columns
• It is also possible to define calculated columns in a QueryDataSet
• It is also possible to define calculated columns in a QueryDataSet
ENAME
KING
CLARK
MILLER
ANNUAL_SAL
29400
60000
15600
SAL
2450
5000
1300
![Page 11: More on Creating Database Forms Using AppBuilder](https://reader030.vdocuments.us/reader030/viewer/2022032709/568131ca550346895d9830b5/html5/thumbnails/11.jpg)
Copyright Oracle Corporation, 1998. All rights reserved.5-5-1111
Adding a Calculated Column to a QueryDataSet
Adding a Calculated Column to a QueryDataSet
1. Select <new column> in theQueryDataSet
2. Open Inspector window
3. Set calcType field to calculated
4. Set columnName and dataType
1. Select <new column> in theQueryDataSet
2. Open Inspector window
3. Set calcType field to calculated
4. Set columnName and dataType
![Page 12: More on Creating Database Forms Using AppBuilder](https://reader030.vdocuments.us/reader030/viewer/2022032709/568131ca550346895d9830b5/html5/thumbnails/12.jpg)
Copyright Oracle Corporation, 1998. All rights reserved.5-5-1212
Supplying a Value for a Calculated Field
Supplying a Value for a Calculated Field
When a calculated field needs evaluation:
• QueryDataSet component generates a calcFields event
• You must handle this event
When a calculated field needs evaluation:
• QueryDataSet component generates a calcFields event
• You must handle this event
![Page 13: More on Creating Database Forms Using AppBuilder](https://reader030.vdocuments.us/reader030/viewer/2022032709/568131ca550346895d9830b5/html5/thumbnails/13.jpg)
Copyright Oracle Corporation, 1998. All rights reserved.5-5-1313
Guided Practice: Providing calcFields Handler MethodGuided Practice: Providing calcFields Handler Method
Explain the following handler method: Explain the following handler method:
import java.math.BigDecimal; // Standard class
…
void queryDetail_calcFields(ReadRow readRow,
DataRow dataRow,
boolean boolean1) … {
BigDecimal sal, ann;
sal = readRow.getBigDecimal("SAL");
ann = sal.multiply(new BigDecimal(12));
dataRow.setBigDecimal("ANNUAL_SAL", ann);
}
![Page 14: More on Creating Database Forms Using AppBuilder](https://reader030.vdocuments.us/reader030/viewer/2022032709/568131ca550346895d9830b5/html5/thumbnails/14.jpg)
Copyright Oracle Corporation, 1998. All rights reserved.5-5-1414
Calculated Columns at Run Time
Calculated Columns at Run Time
The GridControl displays the calculated column automatically
Calculated column
The GridControl displays the calculated column automatically
Calculated column
![Page 15: More on Creating Database Forms Using AppBuilder](https://reader030.vdocuments.us/reader030/viewer/2022032709/568131ca550346895d9830b5/html5/thumbnails/15.jpg)
Copyright Oracle Corporation, 1998. All rights reserved.5-5-1515
Filtering RowsFiltering Rows
If required, a filter can be defined for a QueryDataSet
Example: Filteremployees earning less than a certainamount
If required, a filter can be defined for a QueryDataSet
Example: Filteremployees earning less than a certainamount
![Page 16: More on Creating Database Forms Using AppBuilder](https://reader030.vdocuments.us/reader030/viewer/2022032709/568131ca550346895d9830b5/html5/thumbnails/16.jpg)
Copyright Oracle Corporation, 1998. All rights reserved.5-5-1616
QueryDataSets Generate filterRow Events
QueryDataSets Generate filterRow Events
• A filterRow event is generated before each row is added to the data set
• You provide a handler method, to decide whether to keep or reject the row
• A filterRow event is generated before each row is added to the data set
• You provide a handler method, to decide whether to keep or reject the row
![Page 17: More on Creating Database Forms Using AppBuilder](https://reader030.vdocuments.us/reader030/viewer/2022032709/568131ca550346895d9830b5/html5/thumbnails/17.jpg)
Copyright Oracle Corporation, 1998. All rights reserved.5-5-1717
Supplying a Handler Method to Filter Rows
Supplying a Handler Method to Filter Rows
void queryDetail_filterRow(
ReadRow readRow,
RowFilterResponse rowFilterResponse)… {
String txt = txtMinSal.getText();
if (txt.equals(""))
txt = "0.0";
BigDecimal min = new BigDecimal(txt);
BigDecimal sal = readRow.getBigDecimal("SAL");
if (sal.compareTo(min) >= 0)
rowFilterResponse.add();
}
![Page 18: More on Creating Database Forms Using AppBuilder](https://reader030.vdocuments.us/reader030/viewer/2022032709/568131ca550346895d9830b5/html5/thumbnails/18.jpg)
Copyright Oracle Corporation, 1998. All rights reserved.5-5-1818
Forcing a Row Filter to Occur Forcing a Row Filter to Occur
void btn_actionPerformed(ActionEvent e) {
try {
queryDetail.refilter();
}
catch(DataSetException dse) {}
}
Call refilter() to force rows to be refiltered
Call refilter() to force rows to be refiltered
![Page 19: More on Creating Database Forms Using AppBuilder](https://reader030.vdocuments.us/reader030/viewer/2022032709/568131ca550346895d9830b5/html5/thumbnails/19.jpg)
Copyright Oracle Corporation, 1998. All rights reserved.5-5-1919
Sorting RowsSorting Rows
A data set can be dynamically re-sorted at any time
Example: ChoiceControlcan specify thesort column
A data set can be dynamically re-sorted at any time
Example: ChoiceControlcan specify thesort column
![Page 20: More on Creating Database Forms Using AppBuilder](https://reader030.vdocuments.us/reader030/viewer/2022032709/568131ca550346895d9830b5/html5/thumbnails/20.jpg)
Copyright Oracle Corporation, 1998. All rights reserved.5-5-2020
Performing a Sort ProgrammaticallyPerforming a Sort Programmatically
void choiceControl1_itemStateChanged(ItemEvent e){
String[] s = new String[1];
s[0] = choiceControl1.getSelectedItem();
SortDescriptor sd;
sd = new SortDescriptor(s, true, false);
try {
queryDetail.close();
queryDetail.setSort(sd);
queryDetail.open();
} catch (DataSetException dse) {}
}
![Page 21: More on Creating Database Forms Using AppBuilder](https://reader030.vdocuments.us/reader030/viewer/2022032709/568131ca550346895d9830b5/html5/thumbnails/21.jpg)
Copyright Oracle Corporation, 1998. All rights reserved.5-5-2121
Practice 5-1 OverviewPractice 5-1 Overview
• Define a master-detail form
• Add a calculated column to a QueryDataSet
• Add a filter to a QueryDataSet
• Add sort capabilities to a QueryDataSet
• Define a master-detail form
• Add a calculated column to a QueryDataSet
• Add a filter to a QueryDataSet
• Add sort capabilities to a QueryDataSet
![Page 22: More on Creating Database Forms Using AppBuilder](https://reader030.vdocuments.us/reader030/viewer/2022032709/568131ca550346895d9830b5/html5/thumbnails/22.jpg)
Copyright Oracle Corporation, 1998. All rights reserved.5-5-2222
The Bigger PictureThe Bigger Picture
• The programs we have seen so far comprise a single applet
• The applet contains the Database and QueryDataSet components
• The programs we have seen so far comprise a single applet
• The applet contains the Database and QueryDataSet components
database1
queryDataSet1
queryDataSet2Java appletJava applet
![Page 23: More on Creating Database Forms Using AppBuilder](https://reader030.vdocuments.us/reader030/viewer/2022032709/568131ca550346895d9830b5/html5/thumbnails/23.jpg)
Copyright Oracle Corporation, 1998. All rights reserved.5-5-2323
Working with Larger ProgramsWorking with Larger Programs
• Complex programs can have many frames
• Each frame may need its own Database and QueryDataSet components
• Complex programs can have many frames
• Each frame may need its own Database and QueryDataSet components
database1
queryDataSet1
queryDataSet2
Java appletJava applet
database1
queryDataSet1
queryDataSet2
Frame 1Frame 1
database1
queryDataSet1
queryDataSet2
Frame 2Frame 2
![Page 24: More on Creating Database Forms Using AppBuilder](https://reader030.vdocuments.us/reader030/viewer/2022032709/568131ca550346895d9830b5/html5/thumbnails/24.jpg)
Copyright Oracle Corporation, 1998. All rights reserved.5-5-2424
The Benefit of Data ModulesThe Benefit of Data Modules
To avoid duplication, data components can be placed in a shared Data ModuleTo avoid duplication, data components can be placed in a shared Data Module
database1
queryDataSet1
queryDataSet2
Data moduleData module
AppletApplet
Frame 1Frame 1
Frame 2Frame 2
queryDataSet3
![Page 25: More on Creating Database Forms Using AppBuilder](https://reader030.vdocuments.us/reader030/viewer/2022032709/568131ca550346895d9830b5/html5/thumbnails/25.jpg)
Copyright Oracle Corporation, 1998. All rights reserved.5-5-2525
Creating a Data Modulein AppBuilder
Creating a Data Modulein AppBuilder
• Select File—>New, and double-clickthe Data Moduleicon
• Select File—>New, and double-clickthe Data Moduleicon
• Fill in details for the new data module
• Fill in details for the new data module
![Page 26: More on Creating Database Forms Using AppBuilder](https://reader030.vdocuments.us/reader030/viewer/2022032709/568131ca550346895d9830b5/html5/thumbnails/26.jpg)
Copyright Oracle Corporation, 1998. All rights reserved.5-5-2626
Adding Components to a Data Module
Adding Components to a Data Module
Add shared Database and QueryDataSet components to the data moduleAdd shared Database and QueryDataSet components to the data module
![Page 27: More on Creating Database Forms Using AppBuilder](https://reader030.vdocuments.us/reader030/viewer/2022032709/568131ca550346895d9830b5/html5/thumbnails/27.jpg)
Copyright Oracle Corporation, 1998. All rights reserved.5-5-2727
Linking an Applet to a Data Module
Linking an Applet to a Data Module
Applets can be linked to a data module, as follows:
• Data module automatically added to Structure Pane
Applets can be linked to a data module, as follows:
• Data module automatically added to Structure Pane
public class MyApplet extends Applet {
MyDataModule dm = MyDataModule.getDataModule();
XYLayout xYLayout1 = new XYLayout(); // Etc
…
};
![Page 28: More on Creating Database Forms Using AppBuilder](https://reader030.vdocuments.us/reader030/viewer/2022032709/568131ca550346895d9830b5/html5/thumbnails/28.jpg)
Copyright Oracle Corporation, 1998. All rights reserved.5-5-2828
Designing the Applet User Interface
Designing the Applet User Interface
• Data-aware controls can be added to the
applet, as usual
• The QueryDataSet is now located in the data module
• Data-aware controls can be added to the
applet, as usual
• The QueryDataSet is now located in the data module
![Page 29: More on Creating Database Forms Using AppBuilder](https://reader030.vdocuments.us/reader030/viewer/2022032709/568131ca550346895d9830b5/html5/thumbnails/29.jpg)
Copyright Oracle Corporation, 1998. All rights reserved.5-5-2929
Adding a Frame to Display Orders Information
Adding a Frame to Display Orders Information
• Select File—>New, and double-clickthe Frame icon
• Select File—>New, and double-clickthe Frame icon
• Fill in details for the new frame
• Fill in details for the new frame
![Page 30: More on Creating Database Forms Using AppBuilder](https://reader030.vdocuments.us/reader030/viewer/2022032709/568131ca550346895d9830b5/html5/thumbnails/30.jpg)
Copyright Oracle Corporation, 1998. All rights reserved.5-5-3030
Linking a Frame to a Data Module
Linking a Frame to a Data Module
• Frames are linked to a data module in exactly the same way as for applets:
• Data module automaticallyadded to the Structure Pane
• Frames are linked to a data module in exactly the same way as for applets:
• Data module automaticallyadded to the Structure Pane
public class MyFrame extends DecoratedFrame {
MyDataModule dm = MyDataModule.getDataModule();
XYLayout xYLayout1 = new XYLayout(); // Etc
…
};
![Page 31: More on Creating Database Forms Using AppBuilder](https://reader030.vdocuments.us/reader030/viewer/2022032709/568131ca550346895d9830b5/html5/thumbnails/31.jpg)
Copyright Oracle Corporation, 1998. All rights reserved.5-5-3131
Specifying a SQL String to Populate the Frame
Specifying a SQL String to Populate the Frame
• Our frame will display all the orders made by a selected customer
• Define a separate QueryDataSet to perform this query
• Place this query in the data module
• Our frame will display all the orders made by a selected customer
• Define a separate QueryDataSet to perform this query
• Place this query in the data module
select ORDID, ORDERDATE, SHIPDATE, TOTAL
from ORD
where CUSTID = :currCust
![Page 32: More on Creating Database Forms Using AppBuilder](https://reader030.vdocuments.us/reader030/viewer/2022032709/568131ca550346895d9830b5/html5/thumbnails/32.jpg)
Copyright Oracle Corporation, 1998. All rights reserved.5-5-3232
Defining Parameterized RowsDefining Parameterized Rows
• The Orders query takes a parameter to specify the current customer
• This parameter must be represented by a ParameterRow object:
• The Orders query takes a parameter to specify the current customer
• This parameter must be represented by a ParameterRow object:
void jbInit() … { // Code in MyDataModule
ParameterRow pr = new ParameterRow();
pr.addColumn("currCust", Variant.BIGDECIMAL);
pr.setBigDecimal("currCust", new BigDecimal(0));
queryOrders.setQuery(
new QueryDescriptor(…, pr, true, false));
}
![Page 33: More on Creating Database Forms Using AppBuilder](https://reader030.vdocuments.us/reader030/viewer/2022032709/568131ca550346895d9830b5/html5/thumbnails/33.jpg)
Copyright Oracle Corporation, 1998. All rights reserved.5-5-3333
Executing the Orders QueryExecuting the Orders Query
void btnControl1_actionPerformed(ActionEvent e) {
try {
QueryDataSet qc = dm.getQueryCustomers();
BigDecimal custid = qc.getBigDecimal("CUSTID");
QueryDataSet qo = dm.getQueryOrders();
ReadWriteRow pr = qo.getParameterRow();
pr.setBigDecimal("currCust", custid);
qo.executeQuery();
new MyFrame().show();
} catch(DataSetException dse) {}
}
![Page 34: More on Creating Database Forms Using AppBuilder](https://reader030.vdocuments.us/reader030/viewer/2022032709/568131ca550346895d9830b5/html5/thumbnails/34.jpg)
Copyright Oracle Corporation, 1998. All rights reserved.5-5-3434
Putting It All Together Putting It All Together
![Page 35: More on Creating Database Forms Using AppBuilder](https://reader030.vdocuments.us/reader030/viewer/2022032709/568131ca550346895d9830b5/html5/thumbnails/35.jpg)
Copyright Oracle Corporation, 1998. All rights reserved.5-5-3535
SummarySummary
• Master-detail forms can be defined, using the masterLink property
• Calculated columns can be added to a data set
• Rows can be filtered and sorted
• In large programs, a data module can be used for shared components
• Parameterized queries can be used to specify query values at run time
• Master-detail forms can be defined, using the masterLink property
• Calculated columns can be added to a data set
• Rows can be filtered and sorted
• In large programs, a data module can be used for shared components
• Parameterized queries can be used to specify query values at run time
![Page 36: More on Creating Database Forms Using AppBuilder](https://reader030.vdocuments.us/reader030/viewer/2022032709/568131ca550346895d9830b5/html5/thumbnails/36.jpg)
Copyright Oracle Corporation, 1998. All rights reserved.5-5-3636
Practice 5-2 OverviewPractice 5-2 Overview
• Define a data module
• Attach a panel to the data module, and add the panel to an applet
• Attach a frame window to the data module
• Launch the frame window from the applet
• Define a data module
• Attach a panel to the data module, and add the panel to an applet
• Attach a frame window to the data module
• Launch the frame window from the applet