There are a couple of things we'd like to point out here. The first one has nothing to do with convention but it's very cool. What has been generated here is a "partial update." That is, rather than generating a complete update statement that covers every column in the table, the statement only updates columns that relate to changed data object properties (i.e., just the last name).

Partial updates may not be the right way to go for some applications so CUD generation can be overridden with user-supplied CUD statements. However, partial update is a good fit for many applications and with it you can avoid a great deal of configuration or additional programming. Not only that, partial updates provide a performance boost for updates to tables with very wide rows and are also useful for avoiding database triggers.

The other point we want to make has to do with the "where" clause ("where ID =") of the generated update statement. Since we mean to update the specific table row that's associated with the modified data object, we need to qualify the update statement with a unique row identifier. So this is where another piece of convention is used. If the DAS isn't provided with configuration that defines a unique identifier for the data object Type then the DAS will look for one. There's no magic here; if there's a property named ID then the DAS will assume it's unique and use it in the "where" clause.

We've provided a description of the convention currently employed by the DAS. But there's more on the way. We're currently looking to add more capability based on conventions for generated columns, optimistic concurrency control, and relationship definition.

The use of convention isn't revolutionary or even new, but it is gaining renewed respect. This may be a reaction to the configuration-heavy frameworks we've been using in recent years. Notably, Ruby on Rails, Maven, JUnit, Wiki and many other "agile" frameworks make considerable use of convention over configuration. It's amazing what can be done easily, and how much coding and configuration can be avoided with these tools by adhering to simple conventions.

We've explained how the DAS leverages the capabilities of SDO and makes use of convention to provide a progressive programming model. Now we'll walk through a complete example that demonstrates a few more RDB DAS capabilities.

A Complete Example (CompanyWeb)
In this example we'll display the steps involved in writing a simple application to work with companies and their related departments. First we need to introduce a new DAS concept; the DAS Configuration model. Although we're adding more options for leveraging conventions, there are still capabilities in the DAS that require configuration such as relationship definitions and database-generated IDs.

The DAS Configuration can be built up programmatically or loaded via an XML file. In this example we'll use the XML file approach.

We'll begin by accessing data from the Company table, defined as follows:

COMPANY:
ID NAME

We start by creating an XML file and add descriptive information for the database tables and columns. The snippet of XML below tells the DAS that the COMPANY table has a primary key column named ID that is auto-generated by the database:

<Table name="COMPANY">
    <Column name="ID" primaryKey="true" generated="true"/>
</Table>

Notice that we do not define the NAME column. There's nothing special about this column so we'll just take the conventional behavior offered by the DAS.

In the earlier examples we had the client pass a connection instance to the DAS for use during execution. An alternative is to define connection properties in the Config and have the DAS manage the connection for us. Here we choose to use a DataSource and provide the JNDI name:

<ConnectionProperties dataSource="java:comp/env/jdbc/dastest"/>

Finally, we'll define a Command that the DAS will use to access the data. The following command will retrieve all companies from the database:

<Command name="all companies" SQL="select * from COMPANY" kind="Select"/>

Now we can write an application to access the data and create a class called CompanyClient to handle interaction with the DAS. However, first we'll introduce a new DAS concept: the CommandGroup.

A CommandGroup is a logical grouping of commands and associated configuration data that serves two main purposes. Applications will often define commands that require the same configuration information and a CommandGroup binds the defined commands and the provided configuration data. For example, commands in the same CommandGroup will share the same connection properties and relationship definitions.

Secondly, a CommandGroup is initialized with Commands that it provides by name. Since the client retrieves commands by name and then executes them, the SQL-specific configuration can be contained in the group and isolated from the application. In theory, the same application could switch to using some other data store technology by changing the way the Config is initialized. For example, a Config could be initialized to use static SQL or even a non-relational back-end.

Since our application will use commands that share configuration, we'll use a CommandGroup and create one CommandGroup instance in CompanyClient and initialize it with our XML file.

private CommandGroup commandGroup = CommandGroup.FACTORY.createCommandGroup(getConfig("CompanyConfig.xml"));

private InputStream getConfig(String fileName) {
      return getClass().getClassLoader().getResourceAsStream(fileName);
   }

Now we'll create a method to return a List of Company DataObjects:

public List getCompanies() {
     Command read = commandGroup.getCommand("all companies");
     DataObject root = read.executeQuery();
     return root.getList("COMPANY");
   }

At this point, we have an application capable of returning a list of all companies in the database. Now let's add in another database table, Department:

DEPARTMENT:
ID NAME LOCATION NUMBER COMPANYID

The Department table is also using a primary key named "ID" that is auto-generated by the database, so its table definition will be similar to that of Company:

<Table name="DEPARTMENT">
    <Column name="ID" primaryKey="true" generated="true"/>
</Table>

We have to define the relationship between Company and Department so that the DAS can construct a dynamic SDO model with a relationship between the two and correctly maintain those relationships in the database. The following XML snippet names the relationship, associates the keys, and specifies the cardinality:

<Relationship name="departments" primaryKeyTable="COMPANY"
    foreignKeyTable="DEPARTMENT" many="true">
    <KeyPair primaryKeyColumn="ID" foreignKeyColumn="COMPANYID"/>
</Relationship>

Now we can add a command to return all companies and departments:

<Command name="all companies and departments"
SQL="select * from COMPANY left outer join DEPARTMENT on COMPANY.ID =
DEPARTMENT.COMPANYID" kind="Select"/>

Next we add a method to CompanyClient to access and execute this command. This method returns a list of Company data objects, but since the command employs a join with Departments, each Company will have its related Department data objects associated with it.

public final List getCompaniesWithDepartments() {
    Command read = commandGroup.getCommand("all companies and departments");
    DataObject root = read.executeQuery();
    return root.getList("COMPANY");
   }

Next we'll add the ability to retrieve a single company and all its departments. The configuration file is updated with this command definition:

<Command name="all departments for company"
SQL="select * from COMPANY left join DEPARTMENT on COMPANY.ID =
DEPARTMENT.COMPANYID where COMPANY.ID = :ID" kind="Select"/>

Note that we have defined a named parameter ":ID" in the SQL query. The CompanyClient uses the code below to access this command:

public final List getDepartmentsForCompany(int id) {
    Command read = commandGroup.getCommand("all departments for company");
    read.setParameterValue("ID", new Integer(id));
    DataObject root = read.executeQuery();
    return root.getList("COMPANY[1]\departments");
   }

Now we'll add a write capability to CompanyClient. Since we'll let the DAS generate the CUD statements, no additions are necessary to the configuration file.

public final void addDepartmentToFirstCompany() {

    Command read = commandGroup.getCommand("all companies and departments");
    DataObject root = read.executeQuery();
    DataObject firstCustomer = root.getDataObject("COMPANY[1]");

    DataObject newDepartment = root.createDataObject("DEPARTMENT");
    newDepartment.setString("NAME", "Default Name");
    firstCustomer.getList("departments").add(newDepartment);

    ApplyChangesCommand apply = commandGroup.getApplyChangesCommand();
    apply.execute(root);

   }

A complete example based on this company and department scenario, including a Web application used to access the CompanyClient, is available at the Apache Tuscany incubator project. The readme is available at http://incubator.apache.org/tuscany/samples/java/samples/das/companyweb/readme.htm. The complete source is here: http://svn.apache.org/repos/asf/incubator/tuscany/java/samples/das/companyweb/

In the space of this article we've shown some of the main capabilities of the Relational Database Data Access Service being developed at Apache's Tuscany incubator project. Here are other important supported capabilities:

• Statically typed (generated) SDO DataObjects
• Optimistic concurrency control
• Stored procedures
• External transaction participation
• Write-operation ordering (database constraints)
• Simple name mapping (Table/Column -> SDO Type/property)
• Column-type conversions
• Paging

Integrated with SDO - The Transfer Object pattern is often used by applications to move persistent state from one part of the application architecture to another. This is especially true if the data movement requires serialization. Such an application can employ some object-to-relational technology (JDO, EJB, Entity beans, etc.) to retrieve the data from a back-end data store and then copy the data to the DTO for transfer around the application.

The creation of separate TOs isn't necessary for an SDO-integrated application using the DAS because the SDOs themselves are easily serialized to XML. As a bonus to the TO pattern, the SDOs "remember" changes made to them and this memory is preserved through serialization/de-serialization.

Conclusion
The RDB DAS and SDO provide a simple and powerful way to access and work with relational data. The RDB DAS lets developers work with SDO without building custom data access solutions since the DAS works in terms of SDOs. It simplifies data access by hiding many of its complexities while still letting developers harness more powerful features in complex scenarios.

Because the RDB DAS integrates SDO technology, it's a natural fit for data access in the SCA framework. In fact, an RDB DAS implementation is evolving as part of the "Tuscany" SOA Apache incubator project along with implementations of SCA and SDO. The DAS is also on the roadmap for the upcoming SDO 3.0 specification.

The examples and code included in this article can be had from the Apache Software Foundation and licensed according to the terms of the 2.0 Apache License.

More information about the RDB DAS and the implementation under development can be found at http://incubator.apache.org/projects/tuscany.