When object-oriented programming languages began to be used in enterprise applications, designers had problems fitting the object-oriented model with the relational model. In the object-oriented model, data is encapsulated. The opposite holds true in the relational model: data is exposed and not encapsulated at all. From this, many designers concluded that if relational databases could not be avoided, they should be as well hidden from the business logic as possible.
The dream for object-oriented developers was an object-oriented database. But such databases have failed to gain a large market share, and, most likely, object-oriented databases will never win over relational databases. These days, most developers, even object-oriented purists, admit that, in most cases, relational databases prove superior to object-oriented databases.
Despite the dominance of the relational database, the mainstream persistence frameworks (Enterprise JavaBeans (EJB), Java Data Objects (JDO), Hibernate) refuse to model objects in a relational database. All relational databases are built from tables, columns, primary keys, foreign keys, records, queries, etc. But none of the mainstream products has an object model that corresponds to these entities.
An alternative model, the table-oriented programming model, allows the application source code to be aware of the actual database structure instead of hiding it in a mapping layer deep in the application stack. Many enterprise applications have a lot of CRUD-related (create, read, update, delete) logic, and developing CRUD functionality is much simpler if the database structure is not hidden.
One argument for having an object model that doesn't correspond with the actual database structure is that the business logic should remain the same even if the database structure changes. But this argument neglects the fact that much business logic is implemented in a relational database schema. Changing the database schema will, by definition, also change the business logic.
One consequence of this refusal to model a relational database's real structure is an extra layer of abstraction. Table, columns, and foreign keys must be mapped to classes, properties, and associations. In most cases, there is one class for each table, one property for each column, and one association for each foreign key. Besides, SQL must be reinvented (Hibernate Query Language (HQL), JDO-QL, EJB-QL, etc). Why bother adding this extra layer? It adds complexity and provides few additional features.
A big advantage with table-oriented programming is its ability to create data-aware GUI components. If you are making a Swing application that shows and/or updates data from a database, JTables, JComboBoxes and JFormattedTextFields connected to the database can save a lot of time in development. For Web applications, a data-aware tag library would also speed development. None of the mainstream persistence frameworks have this possibility, but data-aware components fit very well into a table-oriented framework.
Many in the object-oriented world argue against data-aware components. One common contention is that the GUI and database don't share the same structure. This is indeed true for some clients, but for CRUD-oriented clients, data-aware components could save a lot of development time. Just because data-aware components should not be used everywhere is not reason enough for avoiding them altogether.
To add to this skepticism, developers are wary of becoming dependent on a specific database or IDE vendor. Yes, some database and IDE vendors have generated GUI component libraries that tie the components to the specific database or IDE. But if you have a component library that uses JDBC (Java Database Connectivity) and ANSI SQL-92 syntax, this library can be vendor independent.
As a matter of fact, the understanding for the importance of data-aware components is slowly growing in the object-oriented world. Even Sun Microsystems seems to have recognized this need in their open source project JDesktop Network Components.
This article describes an existing table-oriented persistence framework, Butler.
The object model
Butler has a set of classes for describing a database schema's structure:
Table: Describes the structure of a relational tableColumn: Represents a table columnForeignKey: A foreign key relation between two tables
These classes only represent the database's structure (DDL, or data description language). When database content is retrieved from and inserted into the database, a value object is needed. What could be more natural than a Java class named Record, representing a record in a table?
Fetching records
To fetch a record using the primary key value, you need to only call the method findByPK() on the table instance representing the table you want to select from. The argument to this method is the primary key value. An example of calling this method would look something like this:
Record rec = tableRef.findByPK("ABC123");
To fetch all records, just call the method findAll(). This method returns a list of records, instead of a single record.
For more complex queries, the Query class must be used. This class represents a SQL select statement. Most mainstream persistence frameworks use a string representation for queries. In many cases, a string representation gives the most compact source code, but introduces some disadvantages. Using objects and methods for constructing a query is more type-safe. Most of the errors would be detected by the compiler instead of causing runtime errors. IDE features like code-completion would also make it simpler for the programmer to define queries using Java language constructs, instead of strings.
A query in Butler always has a start (or main) table. This is the table from which records are selected. But it is also possible to join other tables. The result will still be a list of records from the start table. But each record from the start table is associated with a related record from the joined table(s), which can be accessed by the getRelatedRecord() and getRelatedRecords() methods. This offers a big difference/advantage over pure JDBC. In JDBC, a query result is always two-dimensional. By making the result hierarchical, it can still be updateable and simplifies further result processing.
To specify the records to select, Butler uses Filter subclasses. For example, an EqualsFilter simply compares a supplied value to a column value. Filter expressions are available for all the logical operations you would use in a SQL statement.
Specifying sort order is done through the method addSortCritera(). This corresponds to the order-by clause in SQL.
Putting all this together, a simple query would look like this:
Query q = orderTable.createQuery();
q.join(orderDetailTable);
q.addColumn(orderTable.getColumn("OrderID"));
q.addColumn(orderTable.getColumn("OrderDate"));
q.addColumn(orderDetailTable.getColumn("ProductID"));
q.addColumn(orderDetailTable.getColumn("Quantity"));
EqualsFilter filter;
filter = new EqualsFilter(orderTable.getColumn("CustomerID"))
q.setFilter(filter);
q.addSortCritera(orderTable.getColumn("OrderDate"));
QueryInstance qi = q.createInstance();
filter.populate(qi, "BLONP");
RecordList recList = qi.run();
Butler has built-in XML support for many classes. A query result (RecordList) can be converted into XML like this:
<records table="Order">
<record>
<column name="OrderID">123</column>
<column name="OrderDate">2004-08-29</column>
<records fk="FK_Order_OrderDetail">
<record>
<column name="ProductID">P01</column>
<column name="Quantity">2</column>
</record>
<record>
<column name="ProductID">P02</column>
<column name="Quantity">4</column>
</record>
</records>
</record>
</records>
This proves useful for report generation. The above XML can easily be transformed to formatting objects for building PDF documents. The query result's hierarchical structure (as opposed to a two-dimensional structure) makes this possible.
Updating data
Updating database records is extremely simple in Butler. Just call the set() method on the record instance, with column and value as arguments, for the columns you want to change. Then call the save() method:
orderRec.set("OrderDate", orderDate);
orderRec.set("ShippingDate", shippingDate);
orderRec.save();
To insert a record, first call the appropriate table object's addRecord() method. Then set the column values and save the record:
orderRec = orderTab.addRecord();
orderRec.set("OrderID", new Integer(123));
orderRec.set("OrderDate", orderDate);
orderRec.save();
Deleting a record is also simple; just call the delete() method on the Record object. If you want to delete multiple records, use the DeleteQuery class. It works nearly the same way as the Query class; you specify a filter for selecting the records you want to delete:
deleteQ = orderTab.createDeleteQuery();
filter = new EqualsFilter(orderTable.getColumn("CustomerID"));
deleteQ.setFilter(filter);
QueryInstance qi = q.createInstance();
filter.populate(qi, "BLONP");
qi.run();
Butler also has a feature for registering record event listeners (triggers). A RecordListener can be registered to a table, and before and after every update, insert, and delete, the corresponding event method will be called.
Generator
As you see in the previous examples, column and table names are given as strings. A programmer could type the wrong name, which would appear as a runtime error. To avoid that risk, Butler has a generator that can generate Table and Record subclasses with get and set methods for each column.
The generator can also be used in another way. The generated subclasses also contain information about the database structure. Butler doesn't have to retrieve database metadata through JDBC during runtime, which can prove time-consuming for some databases. Metadata is extracted during generation. If generated subclasses are not needed, an XML file describing the database structure can be generated.
Data types
So far, this article has described a simplified way of making JDBC calls. But Butler also has additional features that don't exist in JDBC.
Database-centric applications usually have data validation and formatting. When the user enters data, it must be validated before it can be saved in the database. And before data can be presented to the user, it must be formatted in some way.
In Butler, every table column can be associated with a Datatype object. A Datatype can validate and format column values. Butler has built-in implementations of Datatype for strings, numeric values, and dates. But the programmer can also make custom Datatype implementations for handling custom data types like phone numbers, addresses, and even binary large object types like pictures.
The Butler classes described above should be regarded as an additional layer on top of JDBC to make the database simpler and more powerful. The use of these classes doesn't require any architectural change in your application. They can be placed wherever the JDBC calls would have been placed.
Next, I introduce data-aware Swing components. The use of these classes requires an architectural change, and the database structure will no longer be invisible to the client.
Swing components
In database-centric client applications, you will find many examples of JTables backed by database tables. Implementing such tables by hand usually takes many hours of work. But using the class RecordListTable saves that time. The programmer only needs to tell what database table and columns to show and assign a RecordList to the RecordListTable. Columns from related records (in a many-to-one relationship) can also be shown. RecordListTable allows records to be updated, inserted, or deleted. Here is an example of using RecordListTable:
recListTable = new RecordListTable(orderTab);
recListTable.addColumn(orderTab.getColumn("OrderID"));
recListTable.addColumn(orderTab.getColumn("CustomerID"));
recListTable.addColumn(orderTab.getForeignKey("FK_Orders_Customers"),
customerTab.getColumn("CompanyName"));
recListTable.recordListSelected(orders);
If records should be edited in a detail view instead of inside the RecordListTable, use the RecordEditor class. This class creates a panel for editing one record, with editor components for each column in the table (or a specified subset of columns).
The RecordEditor implements the ActionListener interface. The action command new tells the RecordEditor to create a new record. And the save command tells it to save the current record, as illustrated below:
editor = new RecordEditor(orderTab);
editor.add(orderTab.getColumn("OrderID"));
editor.add(orderTab.getColumn("OrderDate"));
saveButton = new JButton("Save");
saveButton.setActionCommand("save");
saveButton.addActionListener(editor);
To customize the layout, use the RecordController class instead of RecordEditor. No visible components will generate automatically. RecordController has a factory method for creating editor components. The programmer just needs to put these components at the desired locations: