6

Sample Programs

The Sample Database  The DemoData Program  Custom Data Binding

---

6.1 The Sample Database

The database included with JClass DataSource has the following structure:

Figure 41 :  Entity-Relationship diagram for the sample database.

The entity-relationship diagram shows the table names, column (field) names, and data types. Many-one relationships are shown by terminating the dotted lines connecting two tables with a black circle at the "many" end of the relationship. The key fields are shown at the top of each table and the foreign keys are designated by placing the tag "(FK)" after the data type.

---

6.2 The DemoData Program

We'll begin with an example of using a class called DemoData, used to retrieve data from a database, and then show how a HiGrid is used to display selected columns from the database. What follows is a line-by-line breakdown of the code. Lines 1-20 are the standard copyright notice that accompanies all JClass examples. They should be assumed as the beginning lines of every other example given in this chapter. Lines 22-29 list the package name and the libraries that DemoData imports. This package identifies itself as part of the examples that accompany the product. The datasource package forms the data source part of JClass HiGrid's code. As this example shows, it is responsible for setting up the connection to the chosen database and then passing the appropriate SQL query to the database. Actually, the jclass.datasource.jdbc package is where the code to connect via JDBC resides (or to an ODBC, through a JDBC-ODBC bridge).

Lines 54-57 define the constants that are used to specify which is the desired database connection. Line 59 states that the Microsoft Access database is currently selected.

Line 62 is the beginning of the code for the constructor. It sets up a String for the JDBC-ODBC driver, then embeds the database connection attempt in a try block.

Line 74 sets up a new DataTableConnection. The JDBC URL structure is defined generally as follows:

jdbc:<subprotocol>:<subname>

In this line, jdbc is the standard base, subprotocol is the particular data source type, and subname is an additional specification that the subprotocol uses. In our example, the subprotocol is odbc. The Driver Manager uses the subprotocol to match the proper driver to a specific subprotocol. The subname identifies the name of the data source.

Line 74 begins the process of instantiating a new connection. Line 75 declares the driver. In fact, lines 74-79 are a concrete instance of a constructor call whose general form is datasource.jdbc.DataTableConnection(String driver, String url, String user, String password, String database). Parameter driver is a String indicating which driver to load, url is the URL String described above, user is the String for the user's name, password is a String for the user's password, if required, and database is the String for the database name, which may be null. This class defines various ways of connecting to databases, such as using a host name and port, or an odbc style connection, in addition to the one used in our example. Once the connection is established, a query sets up the structure for the data that will be retrieved.

In line 108 of our example, the top-level table of our grid is declared in a query specifying that the database table, Orders, is to be used. We wish to include, as sub-tables, information contained in tables Customers, Territories, OrderDetails and Products-Categories. The last-mentioned is a detail level consisting of a join of two tables.

Line 108 shows that the MetaData class holds the structure of the query. Two constructors are used. First, the "root" constructor is called to set up and execute the query to bootstrap root levels of the DataModel and the MetaDataModel. This constructor executes the query and sets the resulting DataTable as the root of the DataTableTree. Call this constructor first, then call the MetaData(DataModel dataModel, DataTableConnection ds_connection, MetaData parent) constructor to build the meta data tree hierarchy. Next, the second form of the constructor is called to add master-detail relationships. All of this is accomplished in lines 113-125.

Note that the class' constructor does all the work, and a try block encloses all of the code. If the class can't be instantiated, the exception will print an error message on the monitor.

Once an instance of this class is successfully created, we have established a connection to the named database and the query will return a result set.

Joins are accomplished programmatically by code such as is seen in lines 116 and 124. They may be specified by using Bean customizers if you are using an IDE.

Lines 127 and following show how to attach virtual columns to a grid. These use the BaseVirtualColumn class as illustrated in line 151, 156, and 160. The type of aggregation to be done is specified using BaseVirtualColumn constants, as shown in lines 154, 158, and 162.

Finally, commit policies for each level are set, beginning at line 188. All three commit policies are illustrated.

1 /*
2 * Copyright (c) 2002, QUEST SOFTWARE. All Rights Reserved.
3 * http://www.quest.com
4 *
5 * This file is provided for demonstration and educational uses only.
6 * Permission to use, copy, modify and distribute this file for
7 * any purpose and without fee is hereby granted, provided that the
8 * above copyright notice and this permission notice appear in all
9 * copies, and that the name of Quest Software not be used in
10 * advertising or publicity pertaining to this material without,
11 * the specific prior written permission of an authorized
12 * representative of Quest Software.
13 *
14 * QUEST SOFTWARE MAKES NO REPRESENTATIONS OR WARRANTIES ABOUT THE
15 * SUITABILITY OF THE SOFTWARE, EITHER EXPRESS OR IMPLIED, INCLUDING
16 * BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS
17 * FOR A PARTICULAR PURPOSE, OR NON-INFRINGEMENT. QUEST SOFTWARE SHALL
18 * NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY USERS AS A RESULT OF USING,
19 * MODIFYING OR DISTRIBUTING THIS SOFTWARE OR ITS DERIVATIVES.
20 */
21
22 package jclass.datasource.examples.jdbc;
23
24 import java.util.*;
25 import java.sql.*;
26
27 import jclass.datasource.treemodel.*;
28 import jclass.datasource.jdbc.*;
29 import jclass.datasource.*;
30
31 /**
32 * This is an implementation of the JClass DataSource DataModel which
33 * relies on the our own JDBC wrappers (rather than IDE-specific data
34 * binding).
35 *
36 * It models a database for a fictitious bicycle company. The same
37 * schema has been implemented using an MS Access database
38 * and a SQLAnywhere database (demo.mdb and demo.db respectively).
39 * They contain the same table structures and data.
40 *
41 * The default is to use the jdbc-odbc bridge to connect to the Access
42 * implementation of the data base. You can change which data base is
43 * accessed by changing the dataBase variable to either SA or SYB below.
44 *
45 * This is the tree hierarchy for the data:
46 *   Orders
47 *     Customers
48 *       Territory
49 *       OrderDetails
50 *         Products-Categories
51 *
52 */
53 public class DemoData extends TreeData {
54
55 public static final int MS = 1;
56 public static final int SA = 2;
57 public static final int SYB = 3;
58 //Change the definition of database to any of the above constants.
59 int dataBase = MS;
60 DataTableConnection c;
61
62 public DemoData() {
63   String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
64   if (System.getProperty("java.vendor").indexOf("Microsoft") != -1) {
65     // use the driver that Microsoft Internet Explorer wants
66     driver = "com.ms.jdbc.odbc.JdbcOdbcDriver";
67   }
68   try {
69     switch (dataBase) {
70     case MS:
71     // This connection uses the jdbc-odbc bridge to
72     // connect to the Access implementation of the
73     // data base.
74     c = new DataTableConnection(
75       driver,                         // driver
76       "jdbc:odbc:JClassDemo",         // url
77       "Admin",                        // user
78       "",                             // password
79       null);                          // database
80     break;
81
82     // This connection uses the jdbc-odbc bridge to connect
83     // to the SQLAnywhere implementation of the data base.
84     case SA:
85     c = new DataTableConnection(
86       "sun.jdbc.odbc.JdbcOdbcDriver", // driver
87       "jdbc:odbc:JClassDemoSQLAnywhere",// url
88       "dba", // user
89       "sql", // password
90       null); // database
91     break;
92
93     // This connection uses Sybase's jConnect type 4
94     // driver to connect to the SQLAnywhere implementation
95     // of the data base.
96     case SYB:
97     c = new DataTableConnection(
98       "com.sybase.jdbc.SybDriver", // driver
99       "jdbc:sybase:Tds:localhost:1498", // url
100       "dba", // user
101       "sql", // password
102       "HiGridDemoSQLAnywhere"); // database
103     break;
104     default:
105       System.out.println("No database chosen");
106     }
107
108     // Create the Orders MetaData
109     MetaData Orders = new MetaData(this, c, " select * from Orders order by OrderID asc");
110     Orders.setDescription("Orders");
111
112     // Create the Customer MetaData
113     MetaData Customers = new MetaData(this, Orders, c);
114     Customers.setDescription("Customers");
115     Customers.setStatement("select * from Customers where CustomerID = ?");
116     Customers.joinOnParentColumn("CustomerID","CustomerID");
117     Customers.open();
118
119     // Create the Territory MetaData
120     MetaData Territory = new MetaData(this, Customers, c);
121     Territory.setDescription("Territory");
122     String t = "select TerritoryID, TerritoryName from Territories where TerritoryID = ?";
123     Territory.setStatement(t);
124     Territory.joinOnParentColumn("TerritoryID","TerritoryID");
125     Territory.open();
126
127     // Create the OrderDetails MetaData
128     // Three virtual columns are used:
129     //
130     // TotalLessTax    (Quantity * UnitPrice),
131     // SalesTax        (TotalLessTax * TaxRate) and
132     // LineTotal       (TotalLessTax + SalesTax).
133     //
134     // Thus, when Quantity and/or UnitPrice is changed, these derived
135     // values reflect the changes immediately.
136     // Note 1: TaxRate is not a real column either, it is a
137     // constant returned by the sql statement.
138     // Note 2: Virtual columns can themselves be used to derive other
139     // virtual columns. They are evaluated from left to right.
140     MetaData OrderDetails = new MetaData(this, Orders, c);
141     OrderDetails.setDescription("OrderDetails");
142     String detail_query = "select OrderDetailID, OrderID, ProductID, ";
143     detail_query += " DateSold, Quantity, UnitPrice, ";
144     detail_query += " '0.15' AS TaxRate ";
145     detail_query += " from OrderDetails where OrderID = ?";
146     OrderDetails.setStatement(detail_query);
147     OrderDetails.joinOnParentColumn("OrderID","OrderID");
148     OrderDetails.open();
149
150     //Extend the row with some calculated values.
151     BaseVirtualColumn TotalLessTax = new BaseVirtualColumn(
152       "TotalLessTax",
153       java.sql.Types.FLOAT,
154       BaseVirtualColumn.PRODUCT,
155       new String[] {"Quantity", "UnitPrice"});
156     BaseVirtualColumn SalesTax = new BaseVirtualColumn(
157       "SalesTax",java.sql.Types.FLOAT,
158       BaseVirtualColumn.PRODUCT,
159       new String[] {"TotalLessTax", "TaxRate"});
160     BaseVirtualColumn LineTotal = new BaseVirtualColumn(
161       "LineTotal",java.sql.Types.FLOAT,
162       BaseVirtualColumn.SUM,
163       new String[] {"TotalLessTax", "SalesTax"});
164
165     OrderDetails.addColumn(TotalLessTax);
166     OrderDetails.addColumn(SalesTax);
167     OrderDetails.addColumn(LineTotal);
168
169     // Create the Products MetaData
170     MetaData Products = new MetaData(this, OrderDetails, c);
171     Products.setDescription("Products");
172     String query = "select a.ProductID, a.ProductDescription,a.ProductName,";
173       query += " a.CategoryID, a.UnitPrice, a.Picture, ";
174       query += " b.CategoryName";
175       query += " from Products a, Categories b";
176       query += " where a.ProductID = ?";
177       query += " and a.CategoryID = b.CategoryID";
178     Products.setStatement(query);
179     Products.joinOnParentColumn("ProductID","ProductID");
180     Products.open();
181
182     // Override the table-column associations for the Products table
183     // to exclude the Picture column so it is not included as part of
184     // the update. Precision problems cause the server to think it's
185     // changed.
186     Products.setColumnTableRelations("Products", new String[] {"ProductID", "ProductDescription", "ProductName", "CategoryID", "UnitPrice"});
187
188     // Override the default commit policy COMMIT_LEAVING_ANCESTOR
189     Orders.setCommitPolicy(MetaDataModel.COMMIT_LEAVING_RECORD);
190     OrderDetails.setCommitPolicy(MetaDataModel.COMMIT_LEAVING_ANCESTOR);
191     Customers.setCommitPolicy(MetaDataModel.COMMIT_LEAVING_ANCESTOR);
192     Products.setCommitPolicy(MetaDataModel.COMMIT_MANUALLY);
193     Territory.setCommitPolicy(MetaDataModel.COMMIT_LEAVING_ANCESTOR);
194
195   } catch (Exception e) {
196     System.out.println("DemoData failed to initialize " + e.toString());
197   }
198}
199
200}

---

6.3 Custom Data Binding

Binding Stock AWT and Swing Components to a Data Source

Binding ordinary AWT or Swing components to a data source involves subclassing the component and having it implement the DataModelListener interface. An example is given in jclass.datasource.examples.components.textfield. The signature for a data-aware TextField is:

public class DBTextField extends TextField
implements DataModelListener, FocusListener, KeyListener

Call its constructor to create a new DBTextField component and bind it to a particular column in a MetaDataModel:

public DBTextField(DataModel data_model,
    MetaDataModel meta_data_model,
    String column_name) {
  this();
  setDataBinding(data_model, meta_data_model, column_name);
}

Note that the three parameters are the DataModel, the MetaDataModel for the master-detail level, and the name of the database field. This information is passed to the setDataBinding method, which completes the name association and registers the component as a listener for DataModelEvents.

See jclass.datasource.examples.components.textfield.DBTextField for a full code example.

Binding Your Own Components to a Data Source

A simpler data binding solution exists. This involves extending an AWT or Swing component, then subclassing FieldDataBinding from jclass.datasource.components.FieldDataBinding in an inner class, and implementing refreshCell in that inner class.

class FieldDataBinding extends
  jclass.datasource.components.FieldDataBinding

The constructor for this class passes the instance of the component defined in the containing class so that if an error is generated an error popup can be presented. After changes have been made to the component, save them by calling either convertAndSaveItem or saveItem. Use convertAndSaveItem to ensure that the data type has been converted to a database type acceptable to JClass DataSource and use saveItem for a component that returns a Boolean and therefore does not need extra conversion into a database-acceptable type. These methods should be called from within the implementation of a listener method. For instance, call convertAndSaveItem on a text-type data bound component from the focusLost method as part of your FocusListener implementation.