Data Management

Data management

Data is central to most applications and many applications spend much of their code on manipulating data. Aria 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 incororated 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 Aria applications. In earlier chapters Data binding.) 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

Aria 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. Aria includes some special features to make working with and integrating database data as simple as possible.

Aria 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.).

Resource access

The Aria 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 Aria 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 Aria. The MVC architecture has already been discussed in connection with data binding (see Data binding.), so we will not reiterate how it is implemented in Aria. 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 Aria is a hierarchical structure. Each node in the model is an instance of the DataModel 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 DataModel 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 DataModel 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 ( org.formaria.DataSource ) 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 BaseModel 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:

A model data configuration file

 <?xml version="1.0" encoding="UTF-8"?>
 <DataSources>
   <DataSource name="ListValues" filename="datasources.xml"/>
 </DataSources>

An example of this static data is:

A sample dataset

 <Datasets>
   <dataset id="settings" value="admin">
      <list id="server1" value="set.Formaria.com">
         <server value="wizards" id="wizards">
            <item value="Add service" id="1" ui="AddService"/>
            <item value="Add mail user" id="2" ui="AddUser"/>
         </server>
         <services value="services" id="services">
           <item value="SMTP server, port 25" id="SMTP"/>
           <item value="POP server, port 110" id="POP"/>
         </services>
       </list>
 	...

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

Retrieve the root model for the project

 BaseModel rootModel = ProjectManager.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 Page classes or derivatives the rootModel member variable is predefined for convenience.

Retrieving a model node

 DataModel model = ( DataModel )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 DataModel or an BaseModel object to be usable. Once retrieved the value attribute of the model can be retrieved by calling the get method without a parameter.

Iterating the children of a model

 DataModel model = ( DataModel )rootModel.get( "customer" );
 for ( int i = 0; i < model.getNumChildren(); i++ ) {
   DataModel child = ( DataModel )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).

Checking attributes

 DataModel model = ( DataModel )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. Setting values

 DataModel model = ( DataModel )rootModel.get( "customer/firstname" );
 model.set( "Joe" );
 
 model = ( DataModel )rootModel.get( "customer" );
 for ( int i = 0; i < model.getNumChildren(); i++ ) {
   DataModel child = ( DataModel )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 BaseModel 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 BaseModel 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:

Appending one model to another

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

This code shows two new BaseModel 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:

Removing a child model node from its parent

 DataModel customerModel = ( DataModel )rootModel.get( "customer" );
 DataModel firstNameMdl = ( DataModel )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 DataSource class.

Persisting a model node to file

 import org.formaria.data.DataSource;
    ...
   public void save()
   {
     String contents = getModelText();
     saveFile( "C:\AriaApps\test.xml", contents );
   }
 
   private String getModelText()
   {
     BaseModel customerModel = ( BaseModel )rootMdl.get( "customer" );
     StringWriter sw = new StringWriter();
     XDataSource.outputModel( sw, customerModel );
     return "<Datasets>" + sw.toString() + "</Datasets>";
   }
 
   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.

The saved data file

 <Datasets>
   <data id="customer">
     <data value="Joe" id="firstname">
     <data value="Bloggs" id="surname">
   </data>
 </Datasets>

In order to reopen the datafile the DataSource class can once more be employed. Restoring a datasets file programatically

 import org.formaria.data.DataSource;
 import org.formaria.xml.XmlElement;
 import org.formaria.xml.XmlSource;
 
    ...
   public void openFile()
   {
     String contents = getFileContents( "c:\AriaApps\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 )
   {
     ( ( BaseModel )rootModel.get( "aria_state" ) ).clear();
     ( ( BaseModel )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 aria_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. Aria will wrap new nodes in an instance of BaseModel 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 DataModel 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 Aria enables similar functionality for rich Java applications.

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

Setting up a database data source

In this section we will look at how an instance of the popular HSQLDB database can be setup within NetBeans.

First we must tell NetBeans about the JDBC database driver, so on the Runtime view expand the Databases | Drivers tree node and right click. Choose the Add Driver...

The driver configuration dialog appears and you can use the Add... button to browse for the hsqldb.jar file. NetBeans then recognizes the driver and you can click OK . Once the driver has been added you can again right click on the database node and choose New Connection... to popup the new connection dialog shown below:

HSQLDB has a number of conection modes and these vary the form of the Database URL. In the example above a connection to a database called my_db , located in the C:\CVS\myproject folder is added. In this standalone mode HSQLDB will create a new databasse instance if none already exists. The setup for other modes and other database is similar, but please consult the appropriate documentation for more details.

Standalone HSQLDB usage

The standalone mode of HSQLDB is of particular interest as using this mode it is possible to load the database from the classpath. The example above gave a full example of the database URL but by changing the URL to jdbc:hsqldb:my_db it would possible to place the C:\CVS\myproject folder on the classpath. In this mode HSQLDB will search the classpath for the named database giving you somewhat greater control over the location of the database. Indeed a read-only database could be located within a Jar file for delivery over the web, however in most cases even if the database is delivered as a Jar it would be necessary to extract the database to a local folder so that changes can be written to the database.

For development purposes it is probably best to add the database (be it HSQLDB or something else) in a server mode. A server mode will make the database less dependant on the application process and this can be an issue while debuging where stopping and starting of the application may occur abruptly (databases normally prefer an orderly shutdown so that they can write changes back to the store). Using a server mode may also leave more memory available for your application.

Whatever the mode, NetBeans provides some valuable tools once you have setup a connection. You can inspect the database fields, view the table data and execute commands.

Eclipse Plug-in and data management

Eclipse does not have the same sort of database facilities as NetBeans out-of-the-box, so we have used a third party module, the open source Eclipse SQL Explorer to provide similar functionality

The Eclipse SQL Explorer plugin is a thin SQL client that allows to query and browse any JDBC compliant database. The plugin (version 3.5) is supported by the AriaEditor Eclipse plugin - this allows to register and setup database tables in a Aria project's data model using drag and drop mechanism.

Installing the Eclipse SQL Explorer plugin.

1 Download the plugin from SourceForge (http://eclipsesql.sourceforge.net -version 3.5) and extract the zip file in the Eclipse installation directory.

2 Restart the Eclipse and open the SQL Explorer perspective .

3 Using the Connections View configure a new connection to the database containing tables which you want to register and setup in your Aria application.

Registering and binding database tables.

1 Open the Connections view and establish a connection to the database containing a table which you want to bind. The available database schemes and tables will appear in the Database Structure view.

2 Drag and drop the selected table from the Data Structure view to the Data node displayed in the Visualizer . The table will appear in the data model hierarchy and will be configured and registered in the current Aria application's data model.

3 Once the table is registered its data can be viewed under the Visualizer tab.

4 Drag and drop the table node from the Visualizer to a component that you want to bind it to.

5 As the table node is dropped you will be asked to configure some properties of the binding in the popup dialog (in most cases the default values will suffice).

6 Once the dialog is closed (by pressing OK button) the binding will be set and configured. The Data property of the component will get the specified value.

Addings the database DataSource

The first task in using database tables in Aria 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:

The startup.properties entry for the database datasource

 DataSourceClass=org.formaria.data.OptionalDataSource

This entry causes the OptionalDataSource 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:

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:

The datasources configuration

 <?xml version="1.0" encoding="UTF-8"?>
 <DataSources>
   <DataSource name="Tables" type="database" filename="test_tables.xml"/>
   <DataSource name="ListValues" filename="test_datasources.xml"/>
 </DataSources>

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.

Configuration of an individual datasource

 <?xml version="1.0" encoding="UTF-8"?>
 <Datasets>
    <Connection id="default" driver="org.hsqldb.jdbcDriver" 
                url="jdbc:hsqldb:testdb" user="sa" pwd=""/>
     <ResultSet id="Voltages" conn="default" from="CS_VOLTAGES"/>
     <ResultSet id="Voltages2" conn="default" 
                sql="SELECT DISTINCT FREQUENCY,PHASES FROM CS_VOLTAGES"/>
 </Datasets>

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.

Adding tables with Drag and Drop

If you added you registered your database with NetBeans as described above you can add a table to your Aria application with drag and drop.

• First open the Visualizer from the Aria menu within NetBeans.
• Select the table of interest in the Runtime view
• Drag and drop the table from the Runtime view to the Visualizer .

When the table is dropped it is registered and the configuration files are updated.

Binding to database tables

The simplest way to use tables is to bind UI components such as the Table, Table2 or Combo components (Table2 is a derivate of the Swing JTable class whereas Table 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:

Binding to a database table

 <Data>
   <Bind target="myList" source="Voltages" output="tables/mylist" display="1" />
   <Bind target="yourTable" source="Voltages" output="tables/Voltages/yourtable"/>
   <Bind target="ourTable" source="Voltages" output="tables/ourtable"/>
   <Bind target="voltageEdit" source="Voltages" output="tables/voltageedit"/>
   <Bind target="voltageList" source="Voltages" output="tables/voltagelist" display="1" />
 </Data>

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.

Binding tables with Drag and Drop

Using the data visualizer as described above you can select a table or some other type of data node from the data model hierarchy displayed in the visualizer and simply drag and drop the node to a component on a page. As the node is dropped on the component you may be asked to select some configuration properties depending on the component type and the data type, but mostly the default values will suffice.

If you do no wish to use drag and drop you can still use the hierarchy displayed in the visualizer to locate the dta node of interest and to find its path within the model. The path can then be copied to the clipboarrd and pasted into the Data property field of the component's property sheet.

Note that the data model may change as you run your application and load or unload datasources, pojos or even dynamically create data. The visualizer will operate in a debug mode and you can stop execution at a breakpoint and refresh the data model to get a snapshot of the model state at that time. This sort of design/debugging can be useful for complex models where it may not be easy or obvious to say where certain data resides, particularly for modular applications that have many distinct developers or where modules communicate via the model.

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

Retrieving a table and updating a UI component

 public class MyTable extends Page
 {
   Table 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.

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:

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 :

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:

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

The database.xml file

   <Connection id="Mortgage" driver="com.mysql.jdbc.Driver" 
               url="jdbc:mysql://localhost/mortgage" user="mortuser" pwd=""/>
   <ResultSet id="InsertApplication" conn="Mortgage" 
              sql="INSERT INTO application ( added_by, create_date, finance_amt, 
                                             property_value ) 
                   VALUES ( ?, ?, ?, ? )"/>
   <ResultSet id="GetApplication" conn="Mortgage" 
           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 . Calling a PreparedStatement

   DatabaseTableModel dtmRes = null;
   String params[] = { "testuser", "22/11/2005", "80000", "140000" };
 
   DatabaseTableModel dtm = (DatabaseTableModel)ProjectManager.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.

Using connections directly

The DataConnection class exposes some access to the underlying connection and JDBC statement objects, but these must be used with care. All the usual JDBC constraints hold, and for each connection there can only be one statement open at a time. The DataConnection object expects to manage a single statement at a time and it will close any open statements before attempting to create a new statement. For each method such as createStatement or createPreparedStatement there is an equivalent closeStatement or closePreparedStatement and the matching method must be called to properly manage the JDBC connection. Failure to honour this contract may result in unexpected results and leaking resources.

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:

Referencing multiple databases

 <?xml version="1.0" encoding="UTF-8"?>
 <Datasets>
    <Connection id="default" driver="org.hsqldb.jdbcDriver" 
                url="jdbc:hsqldb:testdb" user="sa" pwd=""/>
    <Connection id="crm" driver="org.hsqldb.jdbcDriver" 
                url="jdbc:hsqldb:crmdb" user="sa" pwd=""/>
     <ResultSet id="Voltages" conn="default" from="CS_VOLTAGES"/>
     <ResultSet id="Customers" conn="crm" 
                sql="SELECT FirstName, SecondName, Telephone FROM CUSTOMERS"/>
 </Datasets>

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. Aria includes a NamedConnectionManager facility to do this.

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. Note that if you need to configure the connection at startup you will need to reset the connection as soon as possible, possibly even subclassing the applet class. If you leave the framework to load the database configuration files then you will need to ensure that there is an entry for the Connection in the datasets file even if the user name and password are blank.

Debugging

When working with database it is often useful to use the debug versions of the Aria 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:

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 Aria applications, that is pure Java databases. Aria 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 Aria 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. Aria includes some features along these lines but this manual does not yet document such features (please contact Formaria directly for further information).

Transferring data

Aria includes a utility to help transfer data between JDBC databases. The utility can be found in the org.formaria.util.database.DatbaseTransferManager class.

For example when transferring data to a local instance of the HyperSQL database the parameters transfer manager prompts for the source database connection string and then the destination database connection string e.g:

where jdbc:hsqldb:file:/C:/CVS/RS3/resources/rs3db is the name of the HSQLDB instance.

Database synchronization

Frequently it is not enough to occasionally update a database as this might leave the local database in an out of date state. Furthermore if data is added or modified locally then this data may need to be saved back to a central server and propogated to other application users. Synchronizing the databases is one solution to this problem. Using a synchronization mechanism data from one database instance can be replicated to another in a bi-directional fashion so that both databases store the same data.

A number of commercial database replication facilities are available and they give you a range of choices in terms of platforms and in terms of capabilities, some for example support embedded databases and hetrogeneous database replication.

Aria has a built-in database replication facility and while it may not be as complete as some of the commercial systems it is already integrated with the Aria data model and requries little configuration. The Aria replication service may therefore be a good choice for some applications, but of course this should be evaluated in terms of the database technologies, scalability requirements and other system considerations (your Aria application may not be the sole user of a particular database).

Aria's replication service can be configured by adding a replicationService service

Client side replication service

 <?xml version="1.0" encoding="UTF-8"?>
 <Services>
   	<service id="replicationService" route="replicationService">
     		<arg name="table" type="string" mandatory="true"/>
     		<arg name="key" type="string"/>
   	</service>
 </Services>

and a replicationService route to your application that employs the XReplicationService as a endpoint for the replication route.

Replication route on the client side

 <?xml version="1.0" encoding="UTF-8"?>
 <Routes>
   <route id="replicationService">
     <layer class="org.formaria.service.ZipEncoderService"/>
     <layer class="org.formaria.service.HttpClientServiceProxy" 
          url="http://localhost:8084/MyReplicator/ReplicationServlet" urlEncode="true"/>
   </route>
 </Routes>

The URL references the location of the routing servlet. On the server side the replication service also needs to be named: Server side replication service configuration

 <?xml version="1.0" encoding="UTF-8"?>
 <Services>
   	<service id="replicationService" route="replicationService">
     		<arg name="table" type="string" mandatory="true"/>
     			<arg name="key" type="string"/>
   	</service>
   	<service id="getDDL" route="replicationService">
     			<arg name="table" type="string" mandatory="true"/>
     			<arg name="key" type="string"/>
   	</service>
   	<service id="getData" route="replicationService">
     			<arg name="table" type="string" mandatory="true"/>
   	</service>
   	<service id="postUpdates" route="replicationService">
     			<arg name="table" type="string" mandatory="true"/>
     			<arg name="data" type="string" mandatory="true"/>
   	</service>
   	<service id="postInserts" route="replicationService">
     			<arg name="table" type="string" mandatory="true"/>
     			<arg name="data" type="string" mandatory="true"/>
   	</service>
 </Services>

The service configuration names several methods of the replication services for the various create, update, insert and delete operations supported. Ultimately these methods are implemented by the services that comprise the server side route:

Server side replication route

 <Routes>
   <route id="replicationService">
     <layer class="org.formaria.service.ReplicationService"/>
     <layer class="org.formaria.service.ZipDecoderService"/>
   </route>
 </Routes>

The only other requirement for this service is that the central database contains a table for use by the replication service. The DDL for this table is shown below.

TODO insert the xSysServelTimestamps table DDL

With the replication service configured in this way it will attempt to connect to the relication servlet and update the databases whenever a database access/query is made on the client side. Little by way of other configuration is required.

HSQLDB, Apache Derby/Cloudscape/JavaDB and Microsoft SqlServer are supported. SQL for other databases can be configured to work with the replication service.

The sample configurations above used a Zip encoding/decoding service to compress data for transmission over the wire. While this service is not strictly necessary it may be desireable if significant amounts of data are being transferred, for example if a complete snapshot of the database is being downloaded.

HTTP is not the only communications protocol that can be used and other communications protocols and channels can be used in place of the HTTP service, however it will be important to check the timing and delivery considerations of such protocols (for example a JMS service may deliver the updates out of order and on some databases this could give rise to consistency problems).

POJOs

The modelling of tables has been refactored so that more generic implementations can be provided, the first of which is construction of tables from ArrayLists and Vectors . The new class CollectionTableModel supports the new TableModel , RowModel and FieldModel setup.

For example

Setting up a collection

 public class MyTable extends Page
 {
   ...
 
   public MyTable()
   {
     String[] names =
       { "ID", "Title", "Author" };
 
     String[][] rawData = {
       { "0", "Moby Dick", "Herman Melville" },
       { "1", "The Hunchback of Notre Dame", "Victor Hugo" },
       { "2", "The Idiot", "Fyodor Dostoevsky" },
       { "3", "Ulysses", "James Joyce" },
       { "4", "Thus Spake Zarathustra", "Friedrich Nietzsche" },
       { "5", "Bleak House", "Charles Dickens" },
       { "6", "Mansfield Park", "Jane Austen" },
       { "7", "Alice's Adventures in Wonderland", "Lewis Carroll" },	   
       { "8", "The Republic", "Plato" },
       { "9", "Kidnapped", "Robert Louis Stevenson" },
       { "10", "On the Duty of Civil Disobedience", "Henry David Thoreau" },
       { "11", "The Jungle Book", "Rudyard Kipling" },
       { "12", "The Picture of Dorian Gray", "Oscar Wilde" },
       { "13", "The Rime of the Ancient Mariner", "Samuel Taylor Coleridge" },
       { "14", "Catcher in the Rye", "J. D. Salinger" },
       { "15", "The Glass Bead Game", "Herman Hesse" },
     };
 
     ArrayList fieldNames = new ArrayList();   
     for ( String n : names )
       fieldNames.add( n );
 
     ArrayList data = new ArrayList();   
     for ( String[] row : rawData ) {
       ArrayList rowList = new ArrayList();     
       for ( String field : row ) {
         rowList.add( field );
       }
       data.add( rowList );
     }
 
     CollectionTableModel ctm = new CollectionTableModel( project, "CollectionTable", fieldNames, data );
     rootModel.append( ctm );
   }
  ...
 } 

combines with the page XML below to bind the collection to the 'collectTable' table component.

Binding components to a Java collection

 <Page class="aria.projects.sqltables.MyTable" layout="border">
   <Components>
     <Label name="title" content="XTable Demo" alignment="center" constraint="north" style="Heading"/>
     <TabPanel constraint="center" >
     ...  
 
    <ScrollPane title="Collections">
     <Table2 name="collectionTable" title="collection" interactive="true" headingStyle="TableHeading"
         style="TableData" selectionStyle="TableSelection" horizontal_scrollbar="as needed"
         updateModel="true"/>
    </ScrollPane>
  </TabPanel>
   ...  
 
   </Components>
   <Events>
      ...  
 
   </Events>
   <Data>
      ...  
 
       <Bind target="collectionTable" source="CollectionTable" output="tables/ct" />
    </Data>
 </Page> 

The POJO model may be home grown or generated with tools such as JDO or Hibernate. Most of the configuration takes place automatically via reflection and little needs to be done other than pointing Aria at the root of the POJO model.

The following steps show how to build a two-tier Aria application which uses Hibernate POJOs as its data model.

Setting the data source

The XML entry, describing the data source object tasked with loading and adapting POJOs to the Aria model, needs to be set in the file pointed to by the ModelData startup property (datasets.xml by default). The aformentioned XML entry can look like this:

Setting POJO data source

 <Datasources>
   ...
  <DataSource filename="pojos.xml" name="Pojos" 
              type="org.formaria.data.pojo.PersistentPojoDataSource"/>
   ...
 <Datasources>

In the listing above the filename attribute points to a file containing model data POJOs configuration.

Setting POJO model root node

The data model POJOs configuration file must include the <root> XML entry pointing to a class whose instance will be attached to the Aria data model and will serve as a "POJOs repository". The listing below shows the sample entry. Setting the root POJO

<Pojos>
  ...
 
  <root id="pojo" class="aria.ce.model.PojoBase">
    <param class="String" value="pojo base object"/>
 </root>
  ...
</Pojos>

The class attribute points to a class which will be instantiated and attached to the Aria data model via DataModel object, the constructor attributes are defined in the <param> entries. The id attribute of the <root> entry defines the data model path at which the DataModel object wrapping the root POJO will be located.

Accessing POJO model nodes

The general rule of accessing POJOs in the data model is: if the DataModel node wrapping a given POJO is located at the parentPath path and defines property named p1, (via accessors: getP1(), setP1(...)) then the DataModel node wrapping the value of this property is located at the parentPath/p1 path. The actual value of the property can be obtained by invoking the "get()" method on the encapsulating DataModel node. The source of the root class being used in the example is:

The source of the root POJO

 package aria.ce.model;
 
 import auction.dao.DAOFactory;
 
 public class PojoBase        
 {    
   private String pojoBaseName;
 
   public PojoBase( String name )
   {    
     pojoBaseName = name;  
   }
 
   /**
    * Gets the hibernate data access object factory, all DAOs
    * being used in the application can be otained from it.
   */
   public Object getHibernateDAOFactory()
   {            
     return DAOFactory.instance( DAOFactory.HIBERNATE );    
   }
 
   public String getId()
   {
     return pojoBaseName;
   }     
 }

As can be seen the class defines a hibernateDAOFactory property, its value will be located at the pojo/hibernateDAOFactory path, or to be more acurrate, the wrapping model node will be located at the mentioned path.

Overriding POJO properties

POJO properties can be overriden, via the data model POJO's configuration file (pojos.xml in the example). The listing below shows the sample overloading.

Overriding POJO properties

 <Pojos>
   ...
   <pojo id="categoryDAO" class="auction.dao.hibernate.CategoryDAOHibernate">		
     <property id="rootCategories" getter="all(Boolean:true)"/>
     <property id="allCategories" getter="all()"/>
   </pojo>
   ...
 </Pojos>

Each entry defines how individual class is overriden. While the id attribute is not being used at the moment, the class attribute specifies a class whose properties are customized. entries define accessors that should be used to get/set property value specified by the id attribute. With the definition as in the listing above, evaluating the model path categoryDAO/rootCategories would result in invocation of the getAll(true) method on the CategoryDAOHibernate object stored at the categoryDAO path.

Transient properties

In the data model's POJO configuration file a special type of properties, named transient properties, can be defined. The listing below shows the sample definition.

Transient properties

 <Pojos>
   ...
   <pojo id="categoryDAO" class="auction.dao.hibernate.CategoryDAOHibernate">		
 	  <transient_property id="selectedCategory" class="auction.model.Category"/>
   </pojo>
   ...
 </Pojos>

The entries define an additional accessor-less properties which serve as a place to store POJOs in the data model. Invoking the set(arg)/get() method on a model node wrapping such a property will simply store/return the POJO, neither a getter nor setter will be invoked on the parent model node. The reason for this is to provide the ability to declaratively define POJO properties, that may be useful in an application as a part of the data model, but are not connected with a business logic which the POJO's native implementation is intended to define. For example, an application may want to display the details of the selected Category object when the selection is made in the list of all categories. In such a scenario, the model node associated with the selected category would be defined as a transient property. The listing below is an example of this.

Transient property binding in a page xml source

 <Page class="mypackage.EditCategories">
 
   <Components>
     <List name="allCategoriesList" ..../>
     <Edit name="selectedCategoryName" ... />				
     <List name="subCategoriesList".../>
   <Components/>
 
   <Data>
     <Bind target="allCategoriesList" source="categoryDAO/allCategories" attrib="name"/>
     <Bind target="selectedCategoryName source="categoryDAO/selectedCategory/name"/>
     <Bind target="subCategoriesList" 
        source="categoryDAO/selectedCategory/childCategories" attrib="name"/>
   <Data/>
 
   <Events>
     <Event method="categoryListSelectionChanged" 
          target="allCategoriesList" type="ListSelectionHandler"/>
   </Events>
 <Page/>

List selection handler updating components bound to the transient property

 public EditCategories extends Page
 {
   ...
   // invoked whenever the selection on allCategoriesList is changed
   public categoryListSelectionChanged()
   {
     // get selected category model from the list
     Category category = getSelectedCategory( allCategoriesList );
     // put selected category to the transient property model 
     rootModel.get( "categoryDAO/selectedCategory).set( category );
     // update the UI (show the name and subcategories of the selected category)
     updateBoundComponentValues();
   }
   ...
 }

Setting ORM specific configuration

It is possible to use ORM (i.e Hibernate, JPA) POJOs as a data model. This requires some addiction configuration. Following the DAO pattern, the first thing that has to be done is to create a set of Data Access Object that will serve as a layer between the POJOs and the persistent store. Each of these objects has to be declared in the configuration file (pojo.xml). The sample configuration can look like this..

Declaring DAO objects

 <Pojos>
   ...
   <pojo id="userDAO" class="auction.dao.hibernate.UserDAOHibernate" dao="true"/>				
   <pojo id="categoryDAO" class="auction.dao.hibernate.CategoryDAOHibernate" dao="true"/>			 
   ...
 </Pojos>

The declaration from the listing above requires that before accessing (via accessor methods) any of the properties belonging to either UserDAOHibernate or CategoryDAOHibernate instances, the session "bound" to this property should be opened. This is being handled automatically via the Persistence Controller whose implementation will be described later.

The next thing that needs to be added to the POJO model configuration file is the definition of lazily initialized POJO properties which means properties whose values are being retrieved from the persistent store "on demand". This kind of properties are supported by either Native Hibernate and JPA ORMs. The listing below shows the sample configuration.

Lazily initialized properties configuration

 <Pojos>
   ...
   <pojo id="book" class="auction.model.Book">	
     <property id="categories" lazy="true"/>
     <property id="bids" lazy="true"/>
   </pojo>
   ...
 </Pojos>

This configuration specifies that two things have to be done before accessing any of the listed properties (before invoking getter methods). First: the session bound to the given property has to be opened, and second: the parent object (in this case: an instance of the Book class) must be in the "persistent" state. All this is handled by data model and persistent controller which transparently begin and end sessions (database transactions) when the UI is updated, bindings are evaluated, etc.

When the Native Hibernate serves as an ORM framework this configuration is retrieved from the mapping files (specified in the "hibernate.cfg.xml" file), so there is no need to provide it explicitly.

Setting up the Persistence Controller

The next thing is to create and setup the Persistent Controller object which must implement the XPersistenceController interface including methods tasked with beginning/ending sessions, saving/removing entity objects, etc. This can be done with the Native Hibernate as well as with the JPA ORMs. The sample implementataion of one of the interface's methods "persist(Object o)" is shown below.

the Native Hibernate implementation of the "persist" method

 import org.hibernate.*;
 
 public class HibernatePersistenceController
 {
   ...
 
   /**
    * Makes the specified object persistent.
    * @param object the entity to be saved.
    */
   public void persist( Object object )
   {
     Session session = HibernateUtil.getSessionFactory().
     getCurrentSession();    
     session.saveOrUpdate( object );
   }
 
   ...
 }

the JPA implementation of the "persist" method

 import javax.persistence.*;
 
 public class JPAPersistenceController
 {
   ...
   private EntityManager entityManager;  
 
   /**
    * Sets the entity manager that should be used
    * by this controller.
    * @param em the entity manager to be used
    */
   public void setEntityManager( EntityManager em )
   {
     entityManager = em;
   }
 
   /**
    * Makes the specified object persistent.
    * @param object the entity to be saved.
    */
   public void persist( Object object )
   {
     entityManager.merge( object );
   }    
 
   ...
 }

The last thing is to add an entry pointing to a class that implements XPersistenceController to the POJO model configuration file, as shown in the listing below.

setting up the Persistence Controller

 <Pojos>
   ...
   <context class="aria.ce.HibernatePersistenceController"/>
   ...
 </Pojos>