More on Creating Database Forms Using AppBuilder

1. More on Creating Database Forms Using AppBuilder

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

3. Master-Detail Relationships DEPTNO 10 20 30 40 DNAME ACCOUNTING RESEARCH SALES OPERATIONS LOC NEW YORK DALLAS CHICAGO BOSTON Master table, DEPT EMPNO 7839 7782 7934 7566 7902 ENAME KING CLARK MILLER JONES FORD DEPTNO 10 10 10 20 20 Details table, EMP

4. Master-Detail Form at Run Time • This is how the Master-Detail form will appear at run time • Master section • Detail section

5. Specifying QueryDataSet Properties • Two or more QueryDataSets are required in a master-detail form Master query Detail query

6. Establishing the Master-Detail Relationship • Specify masterLinkproperty in detail QueryDataSet

7. Connecting the Master Query to the Detail Query This column in themaster query … … is linked to thiscolumn in the detail query

8. Using the Master and Detail QueryDataSets • Data-aware controls can now be added to display the fields • Fields attached to master query • Fields attached to detail query

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

10. ENAME KING CLARK MILLER SAL 2450 5000 1300 ANNUAL_SAL 29400 60000 15600 Calculated Columns • It is also possible to define calculated columns in a QueryDataSet

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

12. Supplying a Value for a Calculated Field • When a calculated field needs evaluation: • QueryDataSet component generates a calcFields event • You must handle this event

13. Guided Practice: Providing calcFields 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); }

14. Calculated Columns at Run Time • The GridControl displays the calculated column automatically • Calculated column

15. Filtering Rows • If required, a filter can be defined for a QueryDataSet • Example: Filteremployees earning less than a certainamount

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

17. 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(); }

18. Forcing a Row Filter to Occur Call refilter() to force rows to be refiltered void btn_actionPerformed(ActionEvent e) { try { queryDetail.refilter(); } catch(DataSetException dse) {} }

19. Sorting Rows • A data set can be dynamically re-sorted at any time • Example: ChoiceControlcan specify thesort column

20. Performing 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) {} }

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

22. database1 queryDataSet1 queryDataSet2 Java applet The Bigger Picture • The programs we have seen so far comprise a single applet • The applet contains the Database and QueryDataSet components

23. database1 database1 database1 queryDataSet1 queryDataSet1 queryDataSet1 queryDataSet2 queryDataSet2 queryDataSet2 Frame 1 Java applet Frame 2 Working with Larger Programs • Complex programs can have many frames • Each frame may need its own Database and QueryDataSet components

24. The Benefit of Data Modules • To avoid duplication, data components can be placed in a shared Data Module database1 queryDataSet1 Applet queryDataSet2 Frame 1 queryDataSet3 Data module Frame 2

25. Creating a Data Modulein AppBuilder • Select File—>New, and double-clickthe Data Moduleicon • Fill in details for the new data module

26. Adding Components to a Data Module • Add shared Database and QueryDataSet components to the data module

27. Linking an Applet to a Data Module • 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 … };

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

29. Adding a Frame to Display Orders Information • Select File—>New, and double-clickthe Frame icon • Fill in details for the new frame

30. 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 public class MyFrame extends DecoratedFrame { MyDataModule dm = MyDataModule.getDataModule(); XYLayout xYLayout1 = new XYLayout(); // Etc … };

31. 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 select ORDID, ORDERDATE, SHIPDATE, TOTAL from ORD where CUSTID = :currCust

32. Defining Parameterized Rows • 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)); }

33. Executing 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) {} }

34. Putting It All Together

35. Summary • 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

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