number of ratings	1
average rating	3.00

22 Data management

Data is central to most applications and many applications spend much of their code on manipulating data. Carousel includes a powerful data modelling capability. The data model holds all data in a hierarchical structure much like a filesystem or an XML document.

Each piece of data in the model must provide a minimum interface so that it can be incorporated into the data model. The requirements of this interface allow applications to access various parts of data model using a simple and consistent set of methods.

The abstract data model also allows applications to be oblivious to the actual data type and the implementation of the data type. For the application developer this greatly helps to decouple the user interface from the underlying data model.

In many instances the application need only know how to map data into the user interface. This mapping of data to the user interface or data binding is a very powerful feature of Carousel applications. In earlier chapters ( "Data binding" on page 131 ) we saw how data binding could be used to connect data to the user interface and in this chapter we will show how to get that data into the application in the first place.

Types of data

Carousel provides assistance in loading and managing an application's data and this data may fall into several different categories. The main distinction between different types of data is in how the framework processes the data.

For the simplest data access the framework does nothing other than assist in loading the files. A wide variety of data can fall into this category including things that you may not otherwise regard as 'data', for example images, text files, HTML and so on.

The next kind of data is loading in much the same way but gets processed during the loading process. Typical of this is the static model data that we have already encountered. Other files of this mature include the data bindings, validation rules, localization files and the various configuration files used by an application. In loading these files the framework transforms the data into more usable structures.

The third type of data and the type that this chapter is most concerned with is data that originates in databases of one form or another. Carousel includes some special features to make working with and integrating database data as simple as possible.

Carousel can also handle a wide variety of other data sources via the routes and services mechanism but that is covered in a dedicated chapter ( "Introduction to Routes and Services" on page 227).

Resource access

The Carousel project provides a means of loading files from the classpath and from Jar files. The project also hides some of the details of building input and output streams. Typically a number of predefined subdirectories including pages, resources, lib and lang are used to load files. The API provides access to these resources without the need to specify these directories in the file path. The project can also use additional ClassLoader s if so desired or where special circumstances dictate. The project API also provides access to startup parameters.

Many of the components within Carousel reference the project API while accessing files and other resources (The API includes special image loading methods). The API helps simplify access as the components do not need to know how to search the file path(s) or classpaths(s).

The data model

The popular Model-View-Controller is used heavily in Carousel. The MVC architecture has already been discussed in connection with data binding ( "Data binding" on page 131 ), so we will not reiterate how it is implemented in Carousel. However the data model can be used outside of the context of data bindings and we will focus on this briefly.

The data model in Carousel is a hierarchical structure. Each node in the model is an instance of the XModel interface. The interface provides access to a set of attributes and a set of child elements and this makes it possible to address the nodes with the XPath like syntax that we have already seen.

There can be many different implementations of the XModel interface and therefore not all data within the model needs to be structured in this way. For instance a node can internally implement a completely different arrangement of data as it is only the connection of the node to the model that is governed by the XModel interface.

The DataSources

As the data model is a loosely coupled arrangement of what might be disparate types the mechanism for loading data also needs to be flexible. As we have seen in earlier chapters the data for the model can come from numerous sources each listed in the files pointed to by the startup property ModelData .

The basic DataSource class ( net.xoetrope.XDataSource ) loads the static data and can process some simple table structures. The nodes loaded by this data source are of the default type for the model, the XBaseModel class and each can have an arbitrary number of attributes and children.

Static data

Typically an application with include some static data for things like populating lists and perhaps for provision of initial or default values.

The aforementioned startup file points to an XML files that lists the data sources via the 'ModelData' entry, which in turn the model data file will appear something like this:

Code Sample 22-1 - A model data configuration file

An example of this static data is:

Code Sample 22-2 - A sample dataset

...

Each node should have an id attribute as it is this ID that is used to identify the node with the model. If an ID attribute is not provide Carousel with synthesize one and the data will still be accessible (although it may be more difficult to identify the nodes at first glance had an ID been provided).

Tables

The static data can also include HTML like tables and lists. These tables are often used to configure content for dropdown lists and the like in the user interface. You will numerous examples of such tables in the on-line examples and tutorials.

Finding data

Once the model is loaded we need a way to interact with it. The model provides a number of functions to query, update, add and remove nodes and attributes. To query the model the following can be used.

Code Sample 22-3 - Retrieve the root model for the project

XBaseModel rootModel = XProjectManager.getModel();

This call retrieves a reference to the current project's root model node. This model will contain any information which might have been loaded from the dataset files specified by the ModelData startup parameter. In XPage classes or derivatives the rootModel member variable is predefined for convenience.

Code Sample 22-4 - Retrieving a model node

XModel model = ( XModel )rootModel.get( "customer/firstname" ); String firstName = ( String )model.get();

Here a model node at a specified path is being looked-up. Since the model can hold a variety of data types most of the lookup methods return the abstract Object type and some type casting is required to make use of the returned data objects. The get method in this way returns an object and must therefore be cast to an XModel or an XBaseModel object to be usable. Once retrieved the value attribute of the model can be retrieved by calling the get method without a parameter.

Code Sample 22-5 - Iterating the children of a model

XModel model = ( XModel )rootModel.get( "customer" ); for ( int i = 0; i < model.getNumChildren(); i++ ) { XModel child = ( XModel )model.get( i ); String name = child.getId(); String value = ( String )child.get(); }

This code fragment shows how to retrieve and iterate a model node and its children in order to get their names and values. The model is nearly always based or configured by XML files and following from this the id and value attributes are of special use as we have already seen (with the id attribute being used to help identify individual nodes).

Code Sample 22-6 - Checking attributes

XModel model = ( XModel )rootModel.get( "application" ); int idx = model.getAttribute( "date" ); String appDate = model.getAttribValue( idx );

A model node can contain any amount of attributes above and beyond the default id and value attributes. In order to obtain the value of a named attribute it is necessary to first get the index of the attribute and then to get the value of the attribute at that index.

Code Sample 22-7 - Setting values

XModel model = ( XModel )rootModel.get( "customer/firstname" ); model.set( "Joe" ); model = ( XModel )rootModel.get( "customer" ); for ( int i = 0; i < model.getNumChildren(); i++ ) { XModel child = ( XModel )model.get( i ); int idx = child.getAttribute( "somedata" ); child.setAttribValue( idx, "somevalue" ); }

Values can be set for the model itself or for any of the attributes. Within the XBaseModel node structure if the attribute does not yet exist it will be created automatically. This automatic creation of storage is very helpful when binding to a user interface as it means that you do not need to create and initialize a data structure for the associated model, instead you can just bind to the 'imaginary' data structure in the knowledge that it will be created on demand. However it is important to remember that this functionality is derived from the XBaseModel node and that it is not 'injected' into other node types and that there is no 'magic' at work here. For more information you can refer to the API documentation.

If necessary you can also create your own nodes, for example:

Code Sample 22-8 - Appending one model to another

XModel model = ( XModel )rootModel.get( "customer" ); new XBaseModel( model, "firstname", "Joe" ); new XBaseModel( model, "surname", "Bloggs" );

This code shows two new XBaseModel nodes being appended to a parent node. The constructor takes care of assigning the id and value and also of added the model to the parent. Similarly you can remove nodes from the model:

Code Sample 22-9 - Removing a child model node from its parent

XModel customerModel = ( XModel )rootModel.get( "customer" ); XModel firstNameMdl = ( XModel )customerModel.get( "firstname" ); model.remove( firstNameMdl );

The above code shows how child nodes can be first referenced relative to the parent. you need not reference directly from the root node and instead you can reference with the relative path from some other node, lower in the node hierarchy. Once you have a node reference you can then manipulate the node, add a new node or as above remove a node from its parent.

Loading and saving files

While it is often useful and sufficient to load data at startup it may be necessary at times to open or save data files explicitly from Java classes after the application has first started. This can be done by using the XDataSource class.

Code Sample 22-10 - Persisting a model node to file

import net.xoetrope.data.XDataSource; ... public void save() { String contents = getModelText(); saveFile( "C:\XUIapps\test.xml", contents ); }

private String getModelText() { XBaseModel customerModel = ( XBaseModel )rootMdl.get( "customer" ); StringWriter sw = new StringWriter(); XDataSource.outputModel( sw, customerModel ); return "" + sw.toString() + ""; }

public void saveFile( String filename, String contents ) { try { FileOutputStream fos = new FileOutputStream( filename ); OutputStreamWriter osw = new OutputStreamWriter( fos, "UTF8" ); BufferedWriter bw = new BufferedWriter( osw ); bw.write( contents ); bw.flush(); bw.close(); } catch ( IOException ex ) { ex.printStackTrace(); } }

The getModelText function uses the XDataSource class to output the contents of the customerModel node to a StringWriter in XML format. The text is wrapped in a Datasets node so that the saved file can easily be reopened by the application. The saveFile function outputs the text to a FileOutputStream . The saved data file will be saving in the following format.

Code Sample 22-11 - The saved data file

In order to reopen the datafile the XDataSource class can once more be employed.

Code Sample 22-12 - Restoring a datasets file programatically

import net.xoetrope.data.XDataSource; import net.xoetrope.xml.XmlElement; import net.xoetrope.xml.XmlSource;

... public void openFile() { String contents = getFileContents( "c:\XUIapps\test.xml" ); restoreState( contents ); }

private String getFileContents( String filename ) { StringBuffer contents = new StringBuffer(); try { FileInputStream fos = new FileInputStream( filename ); InputStreamReader osw = new InputStreamReader( fos, "UTF8" ); BufferedReader bw = new BufferedReader( osw ); String temp = bw.readLine(); while ( temp != null ) { if ( temp != null ) contents.append( temp ); temp = bw.readLine(); } bw.close(); } catch ( IOException ex ) { ex.printStackTrace(); } return contents.toString(); }

private void restoreState( String contents ) { ( ( XBaseModel )rootModel.get( "xui_state" ) ).clear(); ( ( XBaseModel )rootModel.get( "customer" ) ).clear(); StringReader sr = new StringReader( contents ); XmlElement ele = XmlSource.read( sr ); if ( ele != null ) { XDataSource ds = new XDataSource(); ds.loadTable( ele, rootModel ); } }

The getFileContents function simply reads the contents of the named file into a StringBuffer and returns its contents. The restoreState function clears the xui_state model node. This needs to be done if any of the databindings make use of the data being restored. The customer node is also initialized as this is the root of the node being restored. The file contents are then used to construct an XmlElement . The XDataSource class is then used to load the XmlElement into the specified model which in this case is the project's root model.

This mechanism provides a quick and simple way of managing projects and their data. For a fully working example of this you can refer to the introductory and advanced tutorials.

Other data types

While the static data by its nature is restricted in what it can hold the same restriction does not apply to the model itself. Each node in the model is a Java object and therefore each node can contain just about any type of data. Carousel will wrap new nodes in an instance of XBaseModel so that necessary query semantics can be supported by the actual value is flexible.

Not only can the model hold any type of data but the node types can also be varied. The nodes need only implement the XModel interface to be included in the model.

The ModelData file provides the opportunity to list additional data sources. Such a data source should extend the XDataSource class.

Databases

Many applications rely on relational databases for their data, some are even little more than editors for database resident data.

A common style of application is one that presents forms based on a result set and allows interactive iteration through the result set. Visual Basic popularized this type of application and now Carousel enables similar functionality for rich Java applications.

Support for database tables and result sets can be added to XUI through use of the optional database packages ( net.xoetrope.optional ). These packages allow JDBC result sets to be included in the Carousel data model. The database model nodes can then be bound to UI components in the normal manner.

Addings the database DataSource

The first task in using database tables in XUI is to add an additional datasource so that your application can locate and load the database data and configuration.

The datasource is configured through the startup file with an entry as follows:

Code Sample 22-13 - The startup.properties entry for the database datasource

XDataSourceClass=net.xoetrope.optional.data.XOptionalDataSource

This entry causes the XOptionalDataSource to be loaded. The data source in turn reads another startup file entry, the ' ModelData ' entry to locate its configuration data, just as we have seen with the static only models presented above:

Code Sample 22-14 - The data model configuration file entry for a database datasource

ModelData=test_datasets.xml

The modeldata file in turn points to a set of data sets:

Code Sample 22-15 - The datasources configuration

These data sets are then processed according to the type attribute. In the above example the ListValues data set is the same static data source that we have seen earlier. The Tables data set is of the database type and is therefore processed by the XOptionalDataSource class.

Code Sample 22-16 - Configuration of an individual datasource

url="jdbc:hsqldb:testdb" user="sa" pwd=""/> sql="SELECT DISTINCT FREQUENCY,PHASES FROM CS_VOLTAGES"/>

The entries in the file pointed to by the database entry (in the test_tables.xml file) configure a database connection and some tables. The tables are specified as a simple shortcut to accessing the database. The same table is referenced in two ways with the first retrieving all fields while the second specifies particular fields.

The tables listed are added to the model under the tables node and thus the Voltages table is located at the tables/Voltages node in the database.

Tables need not be preconfigured in this way, but it is the easiest way to access tables so that no extra code is required.

Binding to database tables

The simplest way to use tables is to bind UI components such as the Table, Table2 or Combo components ( XTable2 is a derivate of the Swing JTable class whereas XTable is a built-in component that provides common functionality for AWT and Swing widget sets. XTable2 provides greater functionality at the expense of requiring the Swing library ). In the case of the Voltages table above a binding can be specified as follows:

Code Sample 22-17 - Binding to a database table

In this example we can see the same table being bound to various UI components. Note how the output field is used to dictate where the state information about each binding is saved. Note also the display attribute that controls the field that is displayed.

Accessing database tables

Within Java it is also possible to access the tables via the data model. The DatabaseTableModel class provides additional access methods and in the code below the class is used to implement a sort method (actually it relies on the SQL ORDER BY clause).

Code Sample 22-18 - Retrieving a table and updating a UI component

public class MyTable extends XPage { XTable myTable;

public void pageCreated() { myTable = (XTable)findComponent( "myTable" ); myTable.setUpdateModelSelection( true ); myTable2 = (XTable2)findComponent( "yourTable" ); myTable2.setUpdateModelSelection( true ); myCombo = (XComboBox)findComponent( "myList" ); myCombo.doLayout(); }

public void sort() { DatabaseTableModel completeVoltageTable = DatabaseTableModel.getTable( "Voltages"); completeVoltageTable.setDistinct( true ); completeVoltageTable.setOrderField( "ID" ); completeVoltageTable.retrieve(); myTable.setModel( completeVoltageTable ); updateBoundComponentValues(); }

Distinct and other clauses

Tables often contain duplicates and the DISTINCT directive is used in SQL to eliminate such duplicates from a result set. The methods of the DatabaseTableModel class and other classes provide access to the particular clauses within a SQL statement and if you use such an access mechanism (rather than formulating the entire SQL statement yourself) you may need to explicitly set the DISTINCT directive as in the example above using the setDistinct method.

Code Sample 22-19 - Setting the fields to retrieve

public void filter() { DatabaseTableModel voltageTable = new DatabaseTableModel(); // Set the query elements // FROM clause, FIELDS, WHERE clause voltageTable.setupTable( "CS_VOLTAGES", "VOLTAGE_DESCRIPTION, VOLTAGE_MIN, " + "VOLTAGE_MAX", "FREQUENCY=50" ); voltageTable.retrieve(); myTable.setModel( voltageTable ); updateBoundComponentValues(); }

Again using this feature we can set individual clauses:

Code Sample 22-20 - Setting individual SQL clauses

String fromClause = "LEFT_TABLE INNER JOIN RIGHT_TABLE ON LEFT_TABLE.FIELD_A = RIGHT_TABLE.FIELD_1"; String fields = "FIELD_A, FIELD_B, FIELD_2, FIELD_G; String where = "(FIELD_3 LIKE ('"+someValue+"'))"; String order = "FIELD_A"; DatabaseTableModel valveTable = new DatabaseTableModel(); valveTable.setupTable( fromClause, fields, where ); valveTable.setOrderField( order ); valveTable.setDistinct( true ); valveTable.retrieve();

Finally when this isn't enough you can set the entire SQL statement using the setSqlStatement :

Table 22-1 - Set the entire SQL statement

String sql = "SELECT DISTINCT foo, bar FROM snafu"; String connName = null; // Use the "default" connection DatabaseTableModel valveTable = new DatabaseTableModel(); valveTable.setSqlStatement( sql, connName, false ); valveTable.retrieve();

Filtering and finding rows

A table can be filtered or refined in a sort of drill-down mode using the findRows method. The method builds a query by augmenting the parent query with a WHERE clause:

Code Sample 22-21 - Drill down using findRows

String where = "(A='foo') AND (B='" +someValue+"')"; DatabaseTableModel myTable = new DatabaseTableModel(); myTable.setupTable( "TABLE_A", "A, B, C", null ); myTable.setOrderField( "A, B, C" ); DatabaseTableModel selectedTypes = myTable.findRows( where );

Prepared statements

Not all database access can be setup via simple predefined and explicit SQL queries, in some cases it is necessary to parameterize the queries and PreparedStatements are used for this purpose. PreparedStatements are also used when the data or parameters being passed as part of the query cannot conform to the normal encoding rules for SQL statements. Normally SQL allows only ASCII characters and even then there are limits to what can be used for parameters or conditions, for example you delimiter characters cannot easily be used in simple SQL queries and PreparedStatements statements overcome this problem by separating the parameter data from the query.

PreparedStatements are setup in a very similar way to the simple SQL statements seen above..

Code Sample 22-22 - The database.xml file

url="jdbc:mysql://localhost/mortgage" user="mortuser" pwd=""/> sql="INSERT INTO application ( added_by, create_date, finance_amt, property_value ) VALUES ( ?, ?, ?, ? )"/> sql="SELECT application.application_id, added_by, finance_amt, property_value, applicant_id, title, firstname, surname, dob, bankname, bankaccount_no, bank_nsc FROM application, applicant WHERE application.application_id = ? AND applicant.application_id = application.application_id"/>

The PreparedStatements are declared in the standard way with question marks indicating the data which will be expected for the query. Within you Java code the .

Code Sample 22-23 - Calling a PreparedStatement

DatabaseTableModel dtmRes = null; String params[] = { "testuser", "22/11/2005", "80000", "140000" }; DatabaseTableModel dtm = (DatabaseTableModel)XProjectManager.getModel().get( "InsertApplication" ); dtm.setParams( params ); dtmRes = (DatabaseTableModel)dtm.get(); dtmRes.setDistinct( true ); dtmRes.setDirty( true ); dtmRes.retrieve(); dtmRes.first();

By calling the setParams function in the DatabaseTableModel it is aware that it needs to execute a PreparedStatement which needs to be populated using the params array.

Adding data

The DatabaseTable class and its derivative ( CachedDatabaseTable ) and the DatabaseTableModel classes include limited functionality to write to a database. The classes include setValue methods that write individual field values to a database. In some circumstances where you need to perform limited database writes this may be sufficient but in other cases you would be better served by creating PreparedStatements where you can write multiple field values in one statement.

Furthermore as has already been mentioned there are certain restrictions on the types of values that can be written to a database as part of the query and using a PreparedStatement can also help work around these issues.

The API also distinguishes between a writable database and one that is read-only as in the case of the setSqlStatement used above. Please refer to the API documentation for further details.

Named connections

Some applications rely on access to more than one database and the configuration files shown above have only listed a single database, the default database. However the configuration file may refer to additional databases by other names and specifying other parameters:

Code Sample 22-24 - Referencing multiple databases

url="jdbc:hsqldb:testdb" user="sa" pwd=""/> url="jdbc:hsqldb:crmdb" user="sa" pwd=""/> sql="SELECT FirstName, SecondName, Telephone FROM CUSTOMERS"/>

Configuring connections

In certain circumstances you may not wish to include the connection details in the configuration file or you may wish to specify them dynamically. Carousel includes a NamedConnectionManager facility to do this.

Code Sample 22-25 - Resetting the database connection

NamedConnectionManager connMgr = ( NamedConnectionManager )NamedConnectionManager.getInstance(); connMgr = (NamedConnectionManager)connMgr.reset( connName, dbDriverStr, dbUrlStr, userName, passwordStr );

The default connection names is " default " and resetting this connection will affect all subsequent use of this connection. Other connections can be added with the addConnection method which takes the same familiar JDBC connection parameters. Many of the database access functions include a reference for the connection name and in this way you should be able to control database access.

Debugging

When working with database it is often useful to use the debug versions of the XUI libraries. These libraries output the SQL statements as they are passed to the JDBC database driver. Once you have the SQL you can work with the database's tools directly to tweak and adjust the SQL till you get the expected results.

Many databases also have advanced tools for profiling and optimization and you can use these to build the queries and some will also allow you to log the queries being made against the database.

If you do not have such tools there are a number of commercial and open source tools for such purposes for example:

Table 22-2 - Some free or open source database tools

Squirrel SQL	hhttp://squirrel-sql.sourceforge.net/
DB Wrench	http://www.dbwrench.com/
Aqua Data Studio	http://www.aquafold.com/

Databases

Although we do not intend recommending any particular database there is a certain category of database that is of particular use when working with Carousel applications, that is pure Java databases. Carousel applications can often run off-line or in standalone mode.

Standalone application frequently need off-line storage and databases of their own. The pure Java databases make this particularly easy and remove much of the need for special installation. The HSQLDB is one such database and is of particular interest being of small footprint and offers good performance for smaller databases. The database even includes an in-memory mode that makes it easy to include in an application distribute.

The HSQLDB has also been chosen as the database for OpenOffice 2.0. The office suite includes some nice database tools and the combination of the two makes for some interesting design, analysis and reporting opportunities.

One of the great strengths of Java is the vast array of databases that can be accessed via JDBC. Sometimes however you may not have a pure Java JDBC database driver available and in such cases you can fall back on the ODBC driver via the JDBC-ODBC bridge. The drawback of this approach is that you have to configure a DSN and that requires manual intervention on native code.

Another class of database that is of interest when programming Carousel applications is the lightweight database. Many of the major database vendors offer such databases and they include features like replication and synchronization that is of great interest for mobile applications. Carousel includes some features along these lines but this manual does not yet document such features (please contact Xoetrope directly for further information).

comments

If you were logged in you could rate this article or add a comment.