6

The Data Model

Introduction  Accessing a Database  Specifying Tables and Fields at Each Level

Setting the Commit Policy  The Result Set  Virtual Columns  Handling Data Integrity Violations 

---

6.1 Introduction

Creating an application with JClass DataSource normally involves these steps:

1. Establishing a database connection.
2. Creating the root meta data table.
3. Defining the meta data for sub-tables.
4. Setting properties, such as the commit policy.
5. Optionally adding generated fields in what are called "virtual columns".
6. Connecting display objects, like JClass HiGrid.

This chapter illustrates some of the methods that accomplish the above mentioned steps programmatically.

JClass DataSource is structured around two TreeModels, a meta data tree and a data table tree. The classes that make up the meta data model cooperate to define methods for describing the way that you want your data structured. You define the abstract relationship between data tables as a tree. This is the meta data structure, and after it has been designed, you query the database to fill data tables with result sets. The abstract model defines the structure for the specific data items that are to be retrieved and indexed using a dynamic bookmark mechanism. At the base level of the class hierarchy, class MetaData describes a node in the MetaTree structure and class DataTable holds the actual data for that node. There are different implementations of MetaData for differing data access technologies, therefore there will be a different MetaData defined for the JDBC and for various IDEs. Similarly, there will be different DataTable classes depending on the basic data access methodology.

MetaData and DataTable are concrete subclasses of the classes BaseMetaData and BaseDataTable. The latter is an implementation of the methods and properties common to various implementations of the DataTable model. This class must be extended to concretely implement those methods that it does not, which are all of the methods in the data table abstraction layer. Both of these classes are derived from TreeNode, which contains a collection of methods for managing tree-structured objects.

Interface MetaDataModel defines the methods that BaseMetaData and its derived classes must implement. This is the interface for the objects that hold the meta data for DataTables. There is one MetaDataModel for the root data table, and there can be zero, one, or more DataTable objects associated with one meta data object for all subsequent nodes in the meta data model. Thus it is more efficient to store this meta data only once, rather than repeating it as a part of every data table. In JClass DataSource, meta data objects are the nodes of the meta tree. The meta tree, through instances of the MetaData classes, describes the hierarchical relations between the meta data nodes. DataTableModel is the interface for data storage for the JClass HiGrid data model. It requests data from instances of this table and manipulates that data through this interface. That is, rows can be added, deleted or updated through this DataTable. To allow sorting of rows and columns, all operations access cell data using unique identifiers for rows and columns, rather than their indexes.

The DataModel has one "global" cursor. Commit policies rely on the position of this cursor. This cursor, which is closely related to the bookmark structure, can point anywhere in the "opened" data.

Additionally, each DataTableModel has its own "current bookmark." This cursor is used by the getTable method to point to a definite row in the named table. If another table is referenced, a new, likely different, bookmark is used as the current row cursor.

---

6.2 Accessing a Database

Because this product is desiged primarily to populate its data tables from SQL queries, it provides various ways to make the necessary connection to the database or databases that source the data.

6.2.1 Specifying the Database Connection

If you are working on a Windows platform, and wish to test your application using ODBC, register your database as shown in the section on "Setting Up the Data Source" in the JClass DesktopViews Installation Guide. Other platforms have similar mechanisms for registering the database with an appropriate driver - consult their documentation for details.

JClass DataSource provides a programmatic mechanism for making a database connection and one based on customizers for those using IDEs. As long as you are using the JDBC API, you may use the JARs that accompany this product in your development environment. If you are using a supported IDE, you may optionally use the IDE-specific data binding solution in the customizer.

6.2.2 Accessing a Database Via JDBC Type 1 Driver

The JDBC-ODBC bridge is part of the JDK. ODBC drivers are commonly available for many databases. Some ODBC binary code is required on each client machine, which means that the bridge and the driver are written in native code. For security reasons, Web browsers may not use the ODBC driver, and therefore Applets must use another approach.

The JDBC-ODBC bridge lets you use the wide range of existing ODBC drivers. Unfortunately, this is not a pure Java solution, and as such may impose an unacceptable limitation. Use of a Type 4 driver, described next, is highly recommended.

6.2.3 Accessing a Database Via JDBC Type 4 Driver

The JavaSoft Web page http://java.sun.com/products/jdbc/driverdesc.html has this to say about Type 4 drivers: "A native-protocol fully Java technology-enabled driver converts JDBC calls into the network protocol used by DBMSs directly. This allows a direct call from the client machine to the DBMS server and is a practical solution for Intranet access. Since many of these protocols are proprietary the database vendors themselves will be the primary source for this style of driver. Several database vendors have these in progress." As these become available, Web browsers can use this approach to allow applets access to databases.

// A sybase jConnect connection
  DataTableConnection connection=
  new DataTableConnection(
    "com.sybase.jdbc.SybDriver",           // driver
    "jdbc:sybase:Tds:localhost:1498",      // url
    "dba",                                 // user
    "sql",                                 // password
    "HiGridDemoSQLAnywhere");              // database
} catch (Exception e) {
    System.out.println(
    "Data connection attempt failed to initialize " + e.toString());
  }

Note: The connection object handles all four types of JDBC drivers, only the parameters names are different as one changes from one driver to another.

6.2.4 The JDBC-ODBC Bridge and Middleware products

You have seen that you can establish a database connection through code similar to this snippet.

try {
  // create the connection object which will be shared
  DataTableConnection connection= new DataTableConnection(
    "sun.jdbc.odbc.JdbcOdbcDriver",  // driver
    "jdbc:odbc:HiGridDemo",          // url
    "Admin",                         // user
    "",                              // password
    null);                           // database
} catch (Exception e) {
  System.out.println(
  "Data connection attempt failed to initialize " + e.toString());
  }

There are many JDBC-ODBC bridge products, including one from JavaSoft. The JDBC-ODBC Bridge driver (package sun.jdbc.odbc) is included in JDK 1.2.

6.2.5 Instantiating the Data Model

The root level of the model is created by code similar to the following. Here, all the fields from a table named "Orders" have been chosen.

// Create the Orders MetaData for the root table
MetaData Orders = new MetaData(this, connection, " select * from Orders order by OrderID asc");
Orders.setDescription("Orders");

The root-level MetaData constructor is passed parameters naming the TreeData object, the JDBC connection, and the SQL query. The meta data for sub-level tables is instantiated through a command similar to the one shown directly below.

// Create the Customer MetaData
MetaData Customers = new MetaData(this, Orders, connection);

This constructor takes as parameters a TreeData object, the name of the parent meta data object, and the connection object.

If you wish to present fields from more than one table at the same level in the hierarchy, you use the same constructor and the same syntax. The difference only appears when you build the query statement. The next section creates an OrderDetails level that is joined to the Orders table but obtains data from two database tables, here having the anonymous names a and b.

// Create the Products MetaData
MetaData Products = new MetaData(this, OrderDetails, connection);
String query = "select a.ProductID, a.ProductDescription, a.ProductName,";
query += " a.CategoryID, a.UnitPrice, a.Picture, ";
query += " b.CategoryName";
query += " from Products a, Categories b";
query += " where a.ProductID = ?";
query += " and a.CategoryID = b.CategoryID";

In the previous code snippet, the two tables are joined to the parent table using the ProductID key. That a join with the parent is taking place is recognizable by the use of the ? parameter that substitutes a particular single ProductID value in the parent table to match against ProductID values in table a. The two sub-tables themselves are joined on the CategoryID key. The next section discusses SQL queries in more detail.

6.2.6 Specifying the SQL Query

JClass HiGrid's customizer permits the point-and-click construction of SELECT statements as one of the essential operations along with naming a database and its tables, and constructing the grid's meta data. Similarly, JClass DataSource's Beans have custom editors that facilitate building a query. These customizers and custom editors have text panels that permit you to edit the query.

If you do edit the SQL query statement, your more elaborate statement is passed on to the database with only the most rudimentary validation having been done, Therefore, please realize that you must take extra care when testing your code, especially with commands that potentially modify the host database.

---

6.3 Specifying Tables and Fields at Each Level

Specifying the tables and fields that comprise each level of the hierarchical structure of the grid is really more of a design issue that depends on your particular application rather than any requirement imposed by the data model. Once you have created your design, specify the top level's tables and fields with the command:

MetaData Orders = new MetaData(this, connection, " select * from Orders order by OrderID asc");

This is the constructor for the root level, and is distinguished by the fact that the constructor actually passes a query to the database. For dependent tables, use this form of the constructor:

MetaData Territory = new MetaData(this, Customers, connection);

As before this is a DataModel (or TreeData) object and connection is a Connection object, while Customers is the name of the parent level. The query is set up using the method setStatement:

String select = "SELECT TerritoryID, TerritoryName from Territories
    WHERE TerritoryID = ?";
Territory.setStatement(t);

Further setup is done with the commands:

Territory.joinOnParentColumn("TerritoryID","TerritoryID");
Territory.open();

Methods joinOnParentColumn and open cooperate to return the meta data for the query. The data itself is retrieved when some operation that opens sub-levels is performed.

There is a recurring pattern used to describe and construct the data binding at each level. The commands are:

• Create the level. A root level requires one form of the MetaData constructor; all others make use of a second form.
• Define the SQL query for the level as a Java String.
• Provide a descriptive word for the level and pass it to the MetaData object via setDescription. It's a good idea to ensure that you don't duplicate any of these descriptive words. If you do, you can't be sure which instance the getDescription method will return.
• Use joinOnParentColumn to name the join fields. This will be checked at run time (or in a custom editor if you are using an IDE or a customizer) against the WHERE clause of the query to confirm that they match.
• Use MetaData's open method to load the ResultSetMetaData for the level. The retrieval of actual data is deferred until there is a need to display it.

---

6.4 Setting the Commit Policy

There are three commit policies defined in MetaDataModel:

Commit Policy	Description
COMMIT_LEAVING_RECORD	Modifications to a row will be written to the originating data source when the cursor moves to any other row.
COMMIT_LEAVING_ANCESTOR	Changes will be written to the originating data source when the cursor moves to a row which does not have the same parent as the current row.
COMMIT_MANUALLY	Any row changes will simply change the status of those rows (see DataTableModel.getRowStatus()). You must then call DataTableModel.commitRow(bookmark) or DataModel.updateAll() to make the changes permanent, or call DataTableModel.cancelRowChanges(bookmark) or DataModel.cancellAll() to undo the changes. If you are using JClass HiGrid, the end-user can click on the Edit Status column icon to commit edits, or use the popup menu to commit or cancel edits.

By default, edits to a row are committed upon leaving it (the record).

Note that you can find the commit policy currently in effect by calling getCommitPolicy, and you can cause all pending updates to be written to the database using updateAll. These methods are in classes MetaDataModel and DataModel respectively.

Also note that commitAll should not be used to update the database even though it is declared public. Use updateAll instead.

// override the default commit policy COMMIT_LEAVING_ANCESTOR
Orders.setCommitPolicy(MetaDataModel.COMMIT_LEAVING_RECORD);
OrderDetails.setCommitPolicy(MetaDataModel.COMMIT_LEAVING_ANCESTOR);
Customers.setCommitPolicy(MetaDataModel.COMMIT_LEAVING_ANCESTOR);
Products.setCommitPolicy(MetaDataModel.COMMIT_MANUALLY);
Territory.setCommitPolicy(MetaDataModel.COMMIT_LEAVING_ANCESTOR);

---

6.5 Methods for Traversing the Data

Interface TreeNodeModel specifies the methods that the nodes of a TreeModel must implement. TreeModel itself is an interface for the whole tree, including the root, while TreeNodeModel refers only to the nodes of a generic tree structure. Both these interfaces are used for meta data objects and for actual data tables. TreeModel includes many of the methods of TreeNodeModel merely as a convenience.

Method	Description
append	Adds a TreeNodeModel to the node upon which the method is invoked. The argument node is added as a child of this node.
getChildren	Returns the Vector that contains the child nodes of the node upon which the method is invoked.
getData	Returns the Object associated with a TreeNodeModel.
getFirstChild	The TreeNode of the first child node for the current data model.
getIterator	Given a starting node, a tree iterator is used to follow the links to the node's descendents.
getLastChild	Follows the link to the last child table for the current TreeNodeModel; that is, the last table of the group of tables at the meta data level directly beneath the object upon which the method is invoked.
getNextChild	Follows the link to the next child table for the current TreeNodeModel.
getNextSibling	Follows the link to the next sibling table for the current TreeNodeModel; that is, the next table of the group of tables at the same meta data level as the object upon which the method is invoked.
getParent	Returns the parent, as a TreeNodeModel, of the object upon which the method is invoked.
getPreviousChild	Follows the link to the last child table for the current TreeNodeModel; that is, the last table of the group of tables at the meta data level directly beneath the object upon which the method is invoked.
getPreviousSibling	Follows the link to the last child table for the current TreeNodeModel; that is, the last table of the group of tables at the meta data level directly beneath the object upon which the method is invoked.
hasChildren	Use this Boolean method to find out if the object upon which the method is invoked has children.
insert	Inserts a TreeNodeModel as a child of the object upon which this method is invoked.
isChildOf(TreeNode),	Use this boolean method to determine if the object upon which the method is invoked is a child of the TreeNodeModel parameter.
remove	Removes the specified TreeNodeModel from this node's array of children.
removeChildren	Removes the children of the object upon which the method is invoked.

TreeNodeModel defines:

Method	Description
append	Adds a TreeNode to this node.
getChildren	Returns the Vector that contains the child nodes of this node.
getFirstChild	Returns the first child of this node.
getIterator	Returns an iterator to traverse this node's children.
getLastChild	Returns the last child of this node.
getNextChild	Returns the child of this node which follows the node parameter.
getParent	Returns the parent node of this node.
getPreviousChild	Returns the child of this node which precedes the node parameter.
hasChildren	Returns a Boolean: true if this node has children.
insert	Inserts a TreeNode as a child node of this node.
remove	Removes a child node from the Tree.
removeChildren	Removes all children of this node.

TreeIteratorModel defines:

Method	Description
advance	Moves to the next element in this iterator's list.
advance	Moves ahead a specified number of elements in this iterator's list.
atBegin	Returns Boolean: true if iterator is positioned at the beginning of list, false otherwise.
atEnd	Returns Boolean: true if iterator is positioned at the end of list, false otherwise.
clone	Returns a copy of the current node.
get	Returns the current node.
hasMoreElements	Returns Boolean: true if this node has more children, false otherwise.
nextElement	Returns the next child of this node.

---

6.6 The Result Set

6.6.1 Performing Updates and Adding Rows Programmatically

Performing Updates

JClass DataSource implements all the standard Requery, Insert, Update, and Delete operations. The requery methods are DataModel requeryAll, DataTableModel requeryRow, and DataTableModel (and SummaryMetaData) requeryRowAndDetails.

After a user has modified a cell, call updateCell(rowNumber, columnName, value) to inform the data source of the change. This method will then fire a DataTableEvent to inform listeners about this change. getRowStatus will report this row as UPDATED.

Cancelling pending updates to the database is accomplished via the cancel methods called cancelAll (DataModel) and cancelAllRowChanges (BaseDataTable). See the API for cancelCellEditing in com.klg.jclass.cell.JCCellEditor and its overridden methods in com.klg.jclass.cell.editors for methods which cancel edits to cells.

Requerying the Database

requeryAll requeries the root-level of the database - all rows. Not only do the bookmarks get reset, the sub-tables need to be set up from scratch after a requeryAll.

Adding a Row

The addRow method adds a row and returns a bookmark to the row.

6.6.2 Accessing Rows and Columns

Rows and columns may be accessed in various ways, depending on what information is currently available.

Method	Description
BaseMetaData.getColumnCount	The number of columns in the result set.
MetaDataModel.getColumnIdentifier	Returns a String that uniquely identifies the column. Used to access data rather than a column index which can change when the columns are sorted.
DataModelEvent.getColumn	Returns a String indicating which column changed, or " " in the case where the column is not applicable.
MetaDataModel.getCurrentBookmark DataTableModel.getCurrentBookmark	Moves global cursor to a row, say by first, and return the bookmark.
DataTableModel.getRowIdentifier(i)	The index i is the row order within the result set. The method returns the bookmark for that row.

6.6.3 Column Properties

Most of these properties are derived from the JDBC class ResultSetMetaData in java.sql. They are declared in the ColumnModel interface.

Property	Description
getCatalogName	Returns the catalog name for the table containing this field.
getDisplayWidth	Returns the width in pixels of the column.
getColumnName	The column's name.
getPrecision	The number of decimal digits.
getSchemaName	The name of the schema for the table containing this column.
getTableName	The name of the table containing this column.
getColumnType	The Java type of the column.
isAutoIncrement	When a new row containing this column is created, its contents are assigned a sequence number. Some databases permit it to be overridden.
isCaseSensitive	Is upper case to be distinguished from lowercase?
isCurrency	Is the data a currency value?
isDefinitelyWritable	Is the field writable?
isNullable	Is null an allowable value?
isReadOnly	Is the column write protected?
isSearchable	Can this column's contents be used in a WHERE clause?
isSigned	Is the object signed?
isWritable	Is the field writable?

---

6.7 Virtual Columns

You can add columns whose contents are not retrieved from the data source. The class BaseVirtualColumn allows you to add columns which are derived from other columns on the row, including other virtual columns, by performing defined operations on one or more other columns in the row to arrive at a computed value.

Virtual columns are based on VirtualColumnModel, an interface with one method: Object getResultData(DataTableModel, bookmark). This allows access to all the other cells in the row.

A base implementation of VirtualColumnModel called BaseVirtualColumn is provided. It handles the obvious operations you might want to perform on one or more cells in a row: SUM, AVERAGE, MIN, MAX, PRODUCT, QUOTIENT1. Whether a column is real or virtual, it is transparent to listeners (like HiGrid). They simply call getResultData(bookmark) as before. The DataTable will check the column type. If it is real the normal method is used. If virtual, the VirtualColumnModel.getResultData(DataTableModel, bookmark) method will be called. There can be zero, one, or more virtual columns for a row. Virtual columns will be added by calling a method on the MetaDataModel. For example,

String name = "LineTotal";
int type = MetaDataModel.TYPE_BIG_DECIMAL;
int operation = VirtualColumnModel.PRODUCT;
Orders.addVirtualColumn(new BaseVirtualColumn(name, type, operation, new String[] = {"col1","col2"});

UserDefinedVirtualColumn v = new UserDefinedVirtualColumn(....);
v.setSomeProperty( .. );
Orders.addVirtualColumn(v);

Columns are added to the end of the list of existing columns. VirtualColumns cannot be removed.

Computation Order when using Virtual Columns

The implementation of virtual columns requires that the columns referenced by the virtual column must lie to the left of the summary column containing the result. This is usually not a problem because totals and other such summary data are normally placed to the right of the source columns. However, the rule admits of some flexibility because it is the order in which items are added to the meta data structure that determines the left-right relationship referred to above, but the visual layout may be different.

The following code snippet demonstrates the procedure.

// Create the OrderDetails MetaData
// Three virtual columns are used:
//
//      TotalLessTax      (Quantity * UnitPrice),
//      SalesTax           (TotalLessTax * TaxRate) and
//      LineTotal          (TotalLessTax + SalesTax).
//
// Thus, when Quantity and/or UnitPrice is changed, these derived
// values reflect the changes immediately.
// Note 1: TaxRate is not a real column either,
// it is a constant returned by the sql statement.
// Note 2: Virtual columns can themselves be used to derive other
// virtual columns. They are evaluated from left to right.
MetaData OrderDetails = new MetaData(this, Orders, c);
OrderDetails.setDescription("OrderDetails");
String detail_query = "select OrderDetailID, OrderID, ProductID, ";
detail_query += " DateSold, Quantity, UnitPrice, ";
detail_query += " '0.15' AS TaxRate ";
detail_query += " from OrderDetails where OrderID = ?";
OrderDetails.setStatement(detail_query);
OrderDetails.joinOnParentColumn("OrderID","OrderID");
OrderDetails.open();
BaseVirtualColumn TotalLessTax = new BaseVirtualColumn(
  "TotalLessTax",
  java.sql.Types.FLOAT,
  BaseVirtualColumn.PRODUCT,
  new String[] {"Quantity",
    "UnitPrice"});
BaseVirtualColumn SalesTax = new BaseVirtualColumn(
  "SalesTax",
  java.sql.Types.FLOAT,
  BaseVirtualColumn.PRODUCT,
  new String[] {"TotalLessTax",
    "TaxRate"});
BaseVirtualColumn LineTotal = new BaseVirtualColumn(
  "LineTotal",
  java.sql.Types.FLOAT,
  BaseVirtualColumn.SUM,
  new String[] {"TotalLessTax",
    "SalesTax"});
OrderDetails.addColumn(TotalLessTax);
OrderDetails.addColumn(SalesTax);
OrderDetails.addColumn(LineTotal);

The BaseVirtualColumn constructor is given a column label, the column's data type, the arithmetic operation (one of the supported types), and an array of column names on which the operation is to be applied.

6.7.1 Excluding Columns from Update Operations

Because the setColumnTableRelations method explicitly sets the relationships between tables and columns, it can be used to exclude a column from update operations. This is useful in the case of a column containing bitmapped graphics. The database may think that some pixels have changed in the displayed data and the cell should be updated even though the picture has not been edited at all. In cases like this, you can list only those columns that really should be updated, and save the cost of updating a read-only column.

// override the table-column associations for the Products table
// to exclude the Picture column so it is not included as part of
// the update. Precision problems cause the server to think it's
// changed.
Products.setColumnTableRelations("Products",
  new String[] {"ProductID","ProductDescription",
  "ProductName","CategoryID","UnitPrice"});

Now the column containing the pictures will not be updated.

---

6.8 Handling Data Integrity Violations

6.8.1 Exceptions

Many files, including the JCData, NodeProperties, JCTreeData, DataTableModel, TreeData, and VirtualColumnModel throw exceptions to alert the environment that actions need to be taken as a result of changes, both planned and unplanned, that have happened as data is retrieved, manipulated and stored to the underlying database.

Since many of these exceptions are specific to the way that data is handled internally, and because extra information is often needed about the details of the exception, a special class extending java.lang.Exception called DataModelException is available to supply the extra necessary information.

DataModelException adds information about the context of the exception. From it you can determine the bookmark, the column identifier (columnID), the action that caused the exception, the DataTableModel related to the exception, and the exception itself. There are overridden toString and getMessage methods that allow you access to the exceptions in readable form.

The following code snippet is just one example of the numerous situations where you might wish to catch a DataModelException object. Here, a new MetaData object is being created. If the table names are incorrect, or there is a problem accessing the database, the catch block will inform you of the problem.

try
{
  String query = new String("");
  query = query + "SELECT * FROM OrderDetail ";
  query = query + "ORDER BY order_id,store_id,prod_id,qty_ordered ASC";
  MetaData node = new MetaData(link, connection, query);

  node.setColumnTableRelations("OrderDetail", new String[] {"*"});
  return node;
}
catch (DataModelException e)
{
  ExceptionProcess(); //Print diagnostic and exit
  System.exit(0);
}

¹You can define your own operation by defining a new constant and subclassing BaseVirtualColumn's getResultData method.