Chapter 9. Database Objects

Table of Contents

Introduction
Database Objects Advantages
Scaling Your Applications
Feature Overview
Expresso Database Objects
Object/Relational Mapping
Automatic Generation
DBMaint
Security
Hiding Changes
Virtual Fields, Referential Integrity, Valid Values, Next Number
MultiDBObject
Schema Objects
AutoDBObject
Server-Side Objects
Database Connections
Using JNDI to retrieve your connections
Compatibility with Other Applications
Database Objects
Why Use Database Objects?
An Introduction To The Expresso DataObject API
Creating Database Objects
Using Database Objects
Database Objects in Multiple Databases
Why Multiple Databases?
Independent Multiple Databases
DBOtherMap
Using DataObjects in conjunction with Serialization
Getting an instance of a DataTransferObject
Rebuild a DBObject with a DataTransferObject
Conclusion
Contributors

Note

If you find this EDG documentation helpful please consider DONATING! to keep the doc alive and current.

 Maintainer:Malcolm Wise

Expresso Database objects use database connections to provide a means of mapping from objects to relational database tables. Database objects are similar to (and can be deployed as - see the Expresso Enterprise Project) BMP Entity Enterprise JavaBeans. They are primarily intended to provide persistence for business objects, but frequently include business logic as well. Database objects provide methods for add, update, delete, and a number of different retrieval methods. Expresso implements CRUD a la in Grand's book "Java Enterprise Pattern" Wiley 2001. The searchAndRetrieve method integrates the search process to retrieve records with a means to access the retrieved records, and is frequently used to apply some processing to a set of records.

Introduction

Expresso is organized around a number of database objects. A database object is simply a java object whose state can be preserved by storing in a database. In this way, a database object is analogous to a table in a relational database. The difference is that in addition to attributes (or "fields" or "columns"), the database object also has methods. For example, a database object called "Report" might have an attribute called "ReportNumber", and a method called "publish". In this way the storage of data is grouped with the actions to be performed on this data.

Database Objects provide a means to map relational database tables to Java objects, as well as a way to associate the actions of business objects with their data storage. Database objects are mapped to one or more database tables in the external database (for example, Oracle). These mappings define which columns in the table store which attributes in the object, what the primary key of the table(s) are, and other information required to create a database on the external database.

Note

For more research material on mapping objects to relational databases please see Scott Ambler's paper at IBM's site.

For example, an Invoice object can be defined as relating to a table "INVOICE" in the database, and the object then has an attribute "InvoiceNumber", "Customer", and all other columns in the table.

The database object, unlike the database table, then has associated actions as well - such as "AcceptPayment" or "Ship". These actions operate directly on the data stored by the object, and bind the actions and their data together.

A database object is similar to, (and soon to be compatible with) the Entity Enterprise JavaBean object. Expresso is intended to work both in the non-EJB environment as well as scale to EJB. Just as we have optional support for the 2.0 Servlet API in Expresso, we have support for both the non EJB and EJB environments. DBObjects are more "lightweight" than Entity beans, allowing applications to scale in BOTH directions.

In Expresso there is the functionality to create a "database object" by reading the database & generating a java object that corresponds to a table. Then you can use the DBMaint servlet to automatically create an HTML form for add/update/delete/search capability on this new object (and the underlying table). This allows you to create basic database maintenance forms in a matter of minutes from an existing database.

A database object can also span multiple tables, so that it is a high-level object: for example an Invoice object might also contain data for the detail items on an invoice, which are stored in an "INVOICEDETAIL" table - rather than having many InvoiceDetail database objects, which might create undue performance load on the server. Instead, the Invoice object would contain methods for manipulating invoice details, such as "AddDetail". Database objects also make "virtual fields" easy to produce - such as a "Total" field, which is calculated on the fly, not stored in the database itself.

Database Objects Advantages

Database Objects provide a number of advantages, including:

  • Database Independance: Database objects are built from the ground up to be database independent - they do not rely on the features of a specific underlying database to provide their functionality. This allows an application build using database objects to be ported from one database platform to another in literally moments, providing the potential for great scalability.

  • Automatic Connection Handling:

  • Declarative Referential Integrity: By embedding access logic directly in the database object, you can achieve the same advantages as using stored procedures for accessing a database without the platform-dependence. Referential integrity becomes database independent, and complex relationships between database objects become portable. For example, business rules can be integrated into the database object, so that all applications accessing the object are assured of following the established rules.

  • Multi-Level Validations:

  • Avoiding embedded SQL: Using database objects allows an application to avoid embedded SQL and it's maintenance difficulties and system dependencies. Applications simply interact with other Java objects, allowing the design of a system to be entirely object-oriented.

  • Security, and much more: Every interaction with a database object can be secured, and the security data is easily maintainable via Expresso's built-in capabilities.

By using Database Objects in your application, you can take advantage of all of these capabilities without writing additional code.

Database objects provide a functionality that is similar to an extension to the Entity EJB specification, but do not require EJB's to be used, with the attendant complexities of an Application Server. Where EJB's are used, database objects can be implemented as Entity EJB's easily, allowing scaling both up AND down in size and complexity.

Scaling Your Applications

Expresso in a non-J2EE environment provides pre-built services and components for putting together database-based web applications. The way we approach the J2EE environment is that Expresso applications are able to scale, fairly "painlessly" from a non-J2EE environment to a J2EE environment. This actually involves very little redundancy overall, although there is certainly some overlap. In the areas of overlap, our goal is to not re-invent any wheels, but to provide app-server independant alternatives to things that might bind an application to any single application server implementation.

For example: many application servers provide a connection pooling mechanism. So does Expresso. Expresso's connection pooling, however, is not tied to any specific implementation by a J2EE-server vendor, so your application is not tied either. It also provides a consistent set of features that your application can depend on being there that go beyond the typical services available in a J2EE compliant JDBC2.0 connection pool - for example, reconfigurable type mapping, error message translation, timeouts, tracing, multi-database capability and connection testing, to name a few.

In the area of database objects and entity beans, db objects give a much more "filled out" implementation of a database-aware object. They provide, for example, for db object-level referential integrity, are integrated with the automatic database maintenance servlets that are part of Expresso, can be automatically generated (bi-directionally), provide built-in methods for multi-valued fields, automatic change logging on critical objects, validation, and so forth. So, we think of entity EJB's as a mechanism for using DBObjects in a distributed environment, not as a "competitive" capability to dbobjects. DB Objects also do not have the inheritance problems that exist with Entity EJB's.

Security is another area: All J2EE servers provide some means of administering security, both for session and entity beans. Expresso's security is explicitly designed to be stored in a database, and can easily be extended to integrate with other models (e.g. LDAP integration is in the works already). It's tight integration with database objects, and it's existing thin-client administration capabilities mean again that an application can have superior functionality right away, and avoid any vendor-specific features. Expresso's security layer allows web-based administration of dynamic security that is in addition to & complementarty to EJB container's declarative (deployment-descriptor) based security.

The Transaction package (probably a bad name for it) is probably the least understood and quite possibly the most powerful area of Expresso. Again, we see session EJB's as a perfect mechanism to provide Transaction's functionality in a distributed way. The finite-state machine design of Transactions is an extension of the sesion bean's functionality, and is again tied into the security model. The new enhancements being done now to the transaction package provide a much easier means of tying your business logic to UI-independant interaction with the user than having to create custom JSP's or servlets to do the same thing. The way Transactions can be used with TrxActionServlet and TrxServlet to "automatically generate" user interfaces (including the new XSLT UI generation in the Expresso XML project) allows an application to be prototyped and tested without any though to the UI code at first, then customized at the UI level completely independantly of the business logic, promoting good application design (MVC architechture).

Logging: A singleton-based multithreaded logging service that is independant of any specific EJB implementation is provided by Expresso's logging classes.

Event Handling: The built-in integration of email event handling provides immediate administrative notifications to an Expresso application, whether in an EJB environment or not. This is again something that would have to be built without the framework.

Job Handling: Even in an EJB environment, Expresso's ability to "queue" jobs for handling on the server side is valuable for longer tasks than are suitable interatively. E.g. an accounting system might need to "post" thousands of entries in response to a user request. This is an ideal use of the Job object in Expresso, and applies equally well to EJB environments.

In summary J2EE provides a good environment to host applications, but it doesn't provide pre-built tools to snap business logic - Expresso helps to fill the gap between J2EE's capabilities and your finished application, while still allowing the ability to scale both up (EJB/J2EE) as well as DOWN (e.g. non-J2EE environments) with the same applications.

Feature Overview

Expresso Database Objects

Expresso Framework provides ancestor objects for Database objects (DBObject), which provide means for defining attributes (columns to be stored in the database), defining keys, specifying relationships to other objects (referential integrity) and defining default values and valid values for columns (e.g. multi-valued columns, such as Yes and No, etc).

Database objects also provide all basic operations, such as add, update, delete, search and retrieve. Methods are provided that allow objects to be searched for either by primary key or by any combination of field values (wild-card searches), and searches can be set up to return only a specific database object or a Vector of database objects. With a few lines of code you can create a database object that includes all of this functionality, allowing applications to be built extremely rapidly.

Object/Relational Mapping

Database objects map capability between Java objects and a relational database (RDBMS) in a standard an highly extensible way, so that the objects themselves can be used in your application, removing the need to embed SQL code directly into your Java applications.

Many enterprise applications require access to the data stored in multiple databases. The "Database Object Mapping" functionality in Expresso allows a developer to define a new DB context to describe a particular database, and then associate particular dbobjects with that DB context so that when that object is manipulated, it is always operating against the correct DB context.

Expresso applications can now be run with a "split context", with one database handling the control tables (such as USERLOGIN, etc.), and one database storing the application data (like a data repository, shared by a few different Expresso applications). This allows you to create different "looks" or "views" to the same data, with completely independent logins, security groups, etc. The bottom line is that now you can specify many different database contexts, and define DBObjects as belonging to one of these contexts at the schema level. After this step, the system will always look to the right database when you use that DBObject.

Automatic Generation

DBObject code can be generated from an existing database, or the database required for an applications DB objects can be automatically created, making working with DB Objects in either new or existing environments extremely straightforward.

DBMaint

A servlet is supplied with Expresso that provides immediate database-maintenance capability for any database object (in fact, for any database table through the use of AutoDBObjects). This facility uses the security defined for the object and automatically generates forms to add, update, delete and search for data in the underlying database tables.

Security

Security can be specified for any operation on any database object in a database-independant layer of Expresso, again making your security portable between database engines and immediately usable to any Expresso application, including the DBMaint servlet discussed above.

Hiding Changes

Changes made to a database table have no affect on code using the database object, as these changes only need be made to the database object itself. This decreases maintenance burden on code and enhances portability between different databases.

Virtual Fields, Referential Integrity, Valid Values, Next Number

Database objects allow fields to be defined that are calculated by any specified method (and not stored in the database). They allow referential integrity (the relationships between objects that must be maintained (e.g. invoice master/detail) to be defined in a database-independant way, they allow a set of valid values to be defined for a field and verify field validation against that list automatically, they allow serial number fields to be automatically assigned correct values, and much more. Please see the detailed documentation in the Expresso developer's guide for details.

MultiDBObject

A special extension of the DBObject, MultiDBObject, is provided which can also span multiple tables. As it is often necessary to deal with Join relationships between tables in relational databases - the MultiDBObject exists to handle this. If a DBObject is analogous to a table, a MultiDBObject is analogous to a view (of joined tables). For example an Invoice object might also contain data for the detail items on an invoice, which are stored in an "INVOICEDETAIL" table - rather than having many InvoiceDetail database objects, which might create undue performance load on the server. Instead, the Invoice object would contain methods for manipulating invoice details, such as "AddDetail". Database objects also make "virtual fields" easy to produce - such as a "Total" field, which is calculated on the fly, not stored in the database itself. Many of the same operations available to DBObjects are available to MultiDBObjects - including searchAndRetrieve(), clear(), setField (although with different arguments), etc.

Schema Objects

Collections of database objects can be gathered together into a "Schema" - for example, all of the database objects required for the Invoicing application could be gathered into a Schema. This schema can then be used to automatically generate the database tables required by each of the database objects (as well as setting up required configuration values and reports). This makes installing the application on a new server a matter of minutes, and eliminates the need to tediously create the database tables individually.

AutoDBObject

The AutoDBObject is the easiest way to get access to your database tables and can be very valuable for prototyping your application. AutoDBObject can populate its fields automatically from the schema information of its target table. This allows an AutoDBObject to be instantiated and used to access a table without any coding at all!

Server-Side Objects

The Expresso Framework also contains server-side objects without data, similar to (and soon to be compatible with) Session Enterprise JavaBeans.

Database Connections

Establishing and cleaning up connections to the database can be very time-consuming to an application, slowing it's performance. Instead of creating new connections as needed, it is better to use an existing pool of connections that are held open and available at all times.

Expresso provides facilities for managing database connection pools, abstracting the connection process even further than the JDBC API. A sophisticated connection pool object provides access to one or more databases in an efficient manner, providing the following facilities:

  • Multiple Connection Pools/Contexts

    Expresso's connection pooling capability provides access to many different connection pools, each potentially working against a separate database, even from another vendor. For example, your core Expresso tables might be stored in Oracle, but you could have an alternate connection pool to a DB2/400 database on a different server.

  • Maximum Size

    A setup value can specify the maximum size for a connection pool. When the pool reaches this size, the connection pooling code tries to "clean up" stale connections and re-use them if possible to meet requests for new connections.

  • Timeouts

    The connection pool is protected against inadvertent errors where the connections are not released by means of a timeout mechanism. This timeout returns a connection to the pool after a certain interval, in case the client program neglected to release the connection normally. This timeout can be overridden for long-running requests.

  • Verified Connections

    The connection pool can optionally verify each connection before it is supplied to the client program by running a small query against the connection, thereby handling situations where the database may have closed the connection from it's end and avoiding the client program being handed a closed connection. This mechanism uses both a test query and the isClosed() method, as isClosed() can give false indications in some situations. This results in an overall increase in resilience and reliability of your applications written with Expresso.

  • Automatic Cleanup

    The connection pool will automatically clean up idle connections after a certain amount of time, reducing the size of the pool to a specified minimum during periods of inactivity.

  • Database-Specific Configurations

    The connection pool allows database-specific options to be specified as setup options in the expresso-config.xml file. See the expresso-config.xml file documentation for details.

Using JNDI to retrieve your connections

DBConnectionPool can also grab JNDI connections for use in enterprise systems. The initial setup of JNDI DataSources is dependent upon your site admin and app server. We will only specify how to actually get at that JNDI DataSource for use in the ConnectionPool.

To use JNDI Datasources, you will need to modify the expresso-config.xml to something along the lines of the following example that uses a Firebird driver from a JNDI DataSource:

<jdbc 
	driverType=datasource
	driver="org.firebirdsql.jdbc.FBDriver" 
	url="jdbc:firebirdsql:localhost/3050:c:/expresso/db/expresso.gdb"
	connectFormat="1" 
	login="SYSDBA" 
	password="masterkey" 
	cache="y" 
	createTableIndicies="true" 
	limitationPosition="" 
	escapeHandler="com.jcorporate.expresso.core.db.DoubleQuoteEscapeHandler">
<jndi/>
</jdbc>

There are two keys here:

  1. driverType=datasource: This tells the database connection pool that the connections will be through a JNDI Datasource rather than through the normal JDBC Driver manager

  2. <jndi/> This tells the system to use JNDI for lookup with blank properties.

For many items you will want other attributes inside the jndi tag. Most of these are optional. Check with your appserver administrator for possible values for these attributes:

  • initialContextFactory - Initial Context Factory ClassName

  • providerURL - Datasource provider URL

  • securityPrincipal - The principal user for security purposes

  • securityCredentials - Security credentials for the client to server connection

  • objectFactories - ?

  • stateFactories - ?

  • urlPKGPrefixes -

  • dnsURL -

  • authoritative - (true/false)

  • batchSize - The batch size for number of records to retrieve in one roundtrip to the server

  • securityProtocol - Security protocol for authenticating against the data source

  • securityAuthentication

  • language

Compatibility with Other Applications

Sometimes you will need to integrate with other libraries such as the JSTL SQL tags, Reporting libraries, and others. Many of these libraries need an implementation of javax.sql.DataSource to use for grabbing new database connections. Expresso 5.3 now has code to allow you to retrieve a "dumb" datasource from the DBConnectionPool. Example code to do this is:

javax.sql.DataSource dataSource = DBConnectionPool.getDataSource("default");
java.sql.Connection connection = dataSource.getConnection();
//... Use the connection here
connection.close();

Database Objects

Database objects use the database connections described above to provide a means of mapping from objects to relational database tables. Database objects are similar to (and can be deployed as - see the Expresso Enterprise Project) BMP Entity Enterprise JavaBeans. They are primarily intended to provide persistence for business objects, but frequently include business logic as well. Database objects provide methods for add, update, delete, and a number of different retrieval methods. The searchAndRetrieve method integrates the search process to retrieve records with a means to access the retrieved records, and is frequently used to apply some processing to a set of records.

Why Use Database Objects?

Database Objects provide a number of advantages, including database independence, automatic connection handling, declarative referential integrity, multi-level validations, security, and much more.

By using Database Objects in your application, you can take advantage of all of these capabilities without writing additional code.

Using database objects allows an application to avoid embedded SQL and it's maintenance difficulties and system dependencies. Applications simply interact with other Java objects, allowing the design of a system to be entirely object-oriented.

Database objects are built from the ground up to be database independent - they do not rely on the features of a specific underlying database to provide their functionality. This allows an application build using database objects to be ported from one database platform to another in literally moments, providing the potential for great scalability.

By embedding access logic directly in the database object, you can achieve the same advantages as using stored procedures for accessing a database without the platform-dependence. Referential integrity becomes database independent, and complex relationships between database objects become portable. For example, business rules can be integrated into the database object, so that all applications accessing the object are assured of following the established rules.

Database objects provide a functionality that is similar to an extension to the Entity EJB specification, but do not require EJB's to be used, with the attendant complexities of an Application Server.

Since Entity Beans can easily be molded to access the legacy data of DBObjects, it is fairly simple to migrate to Entity Beans from DBObjects if it is needed for particular tables. It should be noted, that while Entity Beans may shine under high traffic sites where transactional integrity is needed, DBObjects can be significantly faster than Entity beans since they don't try to add all the complexities of data hiding from the client programmer that Entity Beans use.

An Introduction To The Expresso DataObject API

Astute observers of Expresso code will have noticed the emergence of a new package in the expresso code base at: com.jcorporate.expresso.core.dataobjects. This is the location of the new Expresso DataObject API that will be used to refactor DBObjects into a more modular scalable entity. Its goal is to provide a unified interface to multiple data sources such as JMS providers, Databases, and even comma delimited files. The Expresso DBObject implements the DataObject interfaces. So if you learn the ways to manipulate a DataObject, you will automatically learn the ways to operate on a DBObject

Note

The DataObject API takes the minimalistic approach of providing a minimum set of methods that provide, in essence, maximum flexibility, while still providing the same base services as DBObjects. While convenience methods such as 'addIfNeeded()' do not exist in the API, the ready availability of the DataObject interfaces make it simple to add such methods in utility classes that can function with all dataobjects, and not just JDBC specifics.

DataObject Core Interfaces

The following picture shows the core DataObject interfaces and their relationships to one another. The green objects are the primary interfaces, while the brown objects are what's known as "metadata" interfaces. In other words, they provide descriptive information about the green objects, such as what data type they are, how many fields, etc.

Now let's cover the responsibilities of each of the objects.

  • DataObject This is the primary interface that you will be using. Every time you create a DBObject you're in essence creating a DataObject. It provides methods to get and set field values, and methods to provide CRUD capabilities: Create, Read, Update, Delete. The Expresso specific terms are: add(), find(), update(), delete().

  • DataField The DataField's job is to provide a placeholder for field values in the DataObject. It also provides methods to determine if the field value is null, if it has changed since it was created, if it has, what was the last value it had, etc. It also provides type conversions (if possible) from things like Strings to and from BigDecimal objects, or Boolean capabilities etc.

  • DataObjectMetaData Each unique DataObject 'type' has specific data about it. For example, how many fields does it have? Which fields are the key fields? Are there any generic attributes for the DataObject? What about Master-Detail Relations? DataObjectMetadata provides methods to get at such information. It allows certain programs to generically operate on DataObjects without necessarily knowing the specifics about the object. The prime example of this is the Controller DBMaint. It provides a basic administrative interface for all DataObject implementations in the classpath of Expresso.

    You can get to the object's metadata by calling DataObject.getMetaData()

  • DataFieldMetaData This class is similar to DataObjectMetadata in that it provides descriptive information, but in this case, it is specific to individual fields. For example, it contains information about the field's data type, field size, field name, etc. A concrete implementation of this interface is com.jcorporate.expresso.core.dbobj.DBField

    You can get to the DataFieldMetaData object by calling: DataObject.getFieldMetaData("fieldname")

A quick note about metadata to data relationships.

One distinction about DataObjects and their corresponding metadata objects, is the number of instances created at runtime. There is only one metadata instance created for each unique 'type' of data object. For example, if you instantiate 1000 com.jcorporate.expresso.services.dbobj.MimeTypes objects, there will only be one corresponding MetaData object created. This is an optimization to significantly save runtime memory and greatly increase the application speed.

Showing the bigger picture.

The following diagram shows how the DataObject API relates to several other objects. When you first see this diagram, DON'T PANIC!. It is easy to have your eyes become like that of a deer in the headlights at night, but don't worry we'll go over the items one by one.

Ok, so now that you haven't listened and let your eyes glaze over anyway, let's try to tackle this diagram in small pieces.

First, start up with what you know. You've already seen DataObject, DataField, DataFieldMetaData, and DataObjectMetaData. They're the base. Now let's first focus on the other interfaces we've introduced in the diagram. They are the boxes that are green.

  • ContextNested Expresso can work with multiple, independent databases at once. You will come across code like: DBObject.setDataContext("default") often. We call these independent databases Database Contexts. The ContextNested interface simply provides getDataContext/setDataContext methods. This is necessary since objects other than DataObjects may need to be sensitive to the database being currently used. If an object implements ContextNested, it means it should have setDataContext() called before it is ever used. If you don't know what this all means yet, don't worry. Multiple database are a fairly advanced topic, yet simple once you get a hold of it.

  • Securable Expresso uses an integer number to represent a user. This allows for quick internal workings of Expresso and quick lookups in the databases. Any object that wants to act with the client's credentials should have the user's uid set. Securable simply has setRequestingUid(), getRequestingUid(), and a single access check method called isAllowed(); Any object implementing Securable expects to have isAllowed() called before any operation is done on it. If you ever need to bypass security and act as the 'superuser', then call setRequestingUid(Securable.SYSTEM_ACCOUNT) to set the system account for the Securable object.

  • Defineable Most DBObjects in Expresso are simply differentiated between each other by their class name. But there are some data object implementations that must be defined more uniquely than just class name. A prime example of this is AutoDBObject. Every instance of AutoDBObject might be unique because the real issue is what table AutoDBObject points to. This is where Defineable comes in. Defineable simply has the methods getDefinition/setDefinition. The meaning of the definition may be interpreted differently by each object, so you need to consult each Defineable object with how to pass a definition to it. For example, AutoDBObject is defined by telling it what database table name to use. JoinedDataObject requires the path to the XML definition file.

If you understand these interfaces in the least bit, you can now understand the rest of the diagram. Let's cover it starting with the classes that are metadata classes.

Metadata:

  • DBObjectDef Every interface must somewhere have a corresponding concrete implementation. DBObjectDef is a concrete implementation of DataObjectMetaData that is used by DBObjects.

  • DBFieldJust like DBObjectDef, DBField is a concrete object that implements the DataFieldMetaData interface. If you call DBObject.getFieldMetaData("blah"), while the type returned is DataFieldMetaData, it is, underneath the interface, a DBField instance.

Now the rest of the objects are various combination of DataObject Implementations:

  • BaseDataObject This is a blank base object that is designed to eventually provide any features common to all DataObjects. Right now it is basically blank.

  • JDBCDataObject JDBCDataObject is a subclass of BaseDataObject that also contains methods that are very specific to JDBC type environments. Examples are getTargetTable(). This would never make sense in a comma-delimited file environment. So it is included here further down the object chain.

  • DBObject As a subclass of JDBCDataObject, you can guess that DBObject is designed to be specific to JDBC environments. It is probably the oldest class in the Expresso framework, and can also be the most valuable. For the near-term, almost all database access classes in Expresso are derived from DBObject or SecuredDBObject in some way.

  • DefaultDataField As mentioned a few times... every interface must have a concrete implementation. DefaultDataField is the DataField interface implementation that DBObject uses.

  • SecuredDBObject Since this class derives from DBObject and implements the Securable interface, you can probably guess that this class has all the features that DBObject does, but is security conscious. Almost all DBObjects in a web environment should use a security system of access. Expresso provides the framework for that access through SecuredDBObject. SecuredDBObject checks if a particular user has Add,Update,Search, and Delete access through a role-based security matrix. If you derive your class from SecuredDBObject, you automatically get this security without one extra line of code.

  • AutoDBObject Here we pull a bit of a twist. If you notice AutoDBObject implements the Defineable interface. This means that it is configurable in other ways than through class instances. In fact, an autoDBObject is meant to create instant access to legacy tables by reading a table's catalog and configuring itself to read the table dynamically. To use it, you instantiate the AutoDBObject and call setDefinition("TABLENAME"); and use the AutoDBObject normally from there.

UML Conclusion

So that is the basic philosophy of the DataObject API and how the different objects relate to one another. The rest of the chapter will deal with concrete examples of constructing and using DBObjects, which as you can see above, simply implement the DataObject interface and add some special functions such as setupFields(), and other items.

Creating Database Objects

This section describes all of the different options available when creating your database objects - you may not use many of them on any one database object, and the process of creating the simplest type of Database Object is described in the chapter about the "DBMaint" controller.

The most direct use of DB Objects is to write a class that extends the com.javacorporate.dbobj.DBObject class. Your class then needs to implement a few simple methods to describe its relationship to a database table. A shortcut here is to use the DBTool utility to generate the code for you by reverse-engineering the database. See the documentation for DBTool for details on this. There is also an Ant target, "generatefromdb" that can be used to launch the DBTool reverse-engineering routine. It depends on the DB context specified in the /config/expresso-config.xml file.

A DBObject must implement at least the constructors for DBObject and a setupFields method. In the setupFields method, a number of method calls are used to establish the relationship of this DBObject to the database and to a specific table or tables. At a minimum the setupFields object must call the setTargetTable method to specify a table in the database, and one or more addField calls to specify the fields in the table. For example, lets say we are setting up a customer information DBObject. Lets say that the table in the database should have a unique Customer Identifier, a name and a customer type. You might name the DBObject "Customer" and specify a setupFields method like this:

1. public void setupFields() throws DBException {
2.   setTargetTable ("Customer");
3.   addField("CustomerID", "auto-inc", 0, false, "Customer Identifier");
4.   addField("CustomerName", "varchar", 80, false, "Customer Name");
5.   addField("CustomerType", "char", 2, false, "Customer Type");
6.   addKey("CustomerID");
7. }

Let's examine this method, line-by-line:

Line 1 establishes the method signature which must be correct for your DBObject to compile.

Line 2 specifies the "Target Table" or the table in the database that will (primarily) be associated with this database object. Note that no database name is prepended to the table name - this allows the same DBObject to be used against any appropriate database. The content of the Expresso application will specify the actual database connected to at run-time.

Line 3 specifies the first of the fields, or columns, of this database object and of the corresponding table.

Although it is possible for the database table to have columns that are not specified in the DBObject, this is not recommended.

In the call to addField, we specify the following parameters:

  • Column Name: this is the name by which this column is referred to within both the database object and the database.

    This name must match the name of the column in the database, but keep in mind that the DBObject can be used to create the table automatically which we will explore later. We recommend the naming convention shown here, with a leading capital letter and a capital at the beginning of each subsequent word, with no embedded spaces or underscores. Again, no prefix is specified.

    Be careful when naming your columns not to use reserved words from any database system, as even though a particular word might not be reserved in your database system of choice, it is prudent not to restrict the portability of your applications. The name used must be unique within the DBObject but need not be unique across all tables in the database. In other words, you can have only one "CustomerID" column in this DBObject but other DBObjects can use "CustomerID" as well.

  • Data Type: the string used here should specify the Expresso data type used for this field.

    This may or may not be the same data type that is used by your database - the Expresso data types are "mapped" at runtime to the appropriate type for a particular database engine. You can even define new Expresso types and map them as required to take advantage of specified database capabilities (although this again can limit portability and is not recommended for this reason).

    This particular field uses a special Expresso-only datatype called "auto-inc" that provides for unique integer numbers. The underlying column in the database table will be of type integer, but Expresso will automatically provide incremented values every time a record is added to the table.

  • Field Size: certain data types require a field size or maximum length to be defined. Others, such as "int" typically do not. If a field size is needed, it is specified in this parameter - otherwise this parameter is left 0. An additional addField method exists to specify field sizes for fields with two size specifications; such as "float" fields, where the first size is the length of the field and the second is the number of decimal places.

  • Allow Nulls: this boolean parameter indicates if nulls can be stored in this field: false indicates that no nulls are accepted, true indicates that null is a permitted field value.

  • Field description: this is a short human-readable term or phrase that describes the field, often used as a column header or listings and reports. For best local language support, this description should be specified as a call to the getString method of the application's schema object which can select the appropriate name in the correct language at runtime. See Documentation on Internationalization for more details.

Lines 4 and 5 in our listing specify additional fields in our table - although the order in which fields are added is not relevant, it does affect the sequence in which the columns are listed when a table is created from the DBObject or when using the Expresso components to list records from this table, so some logical order should be followed.

Line 6 specifies the primary key field for this DBObject. You must specify at least one primary key field and you can specify more than one by repeated calls to addKey with each field name that makes up the primary key. Primary field keys can not allow nulls.

Specifying Databases

So far, all of the examples we have seen of DBObjects assume they are accessing the default database/context as defined when Expresso is deployed. DBObjects can access any available Database/Context though, by using the setDataContext(String) method. This method takes as its argument the code of a database/context, which is the same as the name of the .property file for that context in Expresso. For example:

custList.setDataContext("oracle");

says that the custList DBObject should use the context "oracle", which presumably has been defined to connect to an Oracle DBMS.

Most other Expresso objects allow the current Database/Context of the user to be retrieved with a "getDataContext()" call - Controller objects, for example, do this. So a DBObject being used within a Controller simply says:

1. custList.setDataContext(getDataContext());

to specify that it should use the Database/Context of the Controller - this makes the whole Controller object portable across databases.

It is good practice to always set the Database Context of a DBObject just after it is initialized.

Type Mapping

All of the java.sql.Types data types are supported, and can be mapped to any database data type for complete DBMS independence. Fields can be accessed as any appropriate Java data type with automatic conversion. Special methods make handling date/time fields easier.

Virtual Fields

DBObjects sometimes need to present fields that are not stored in the target table. They may be calculated (such as an invoice total) or retrieved from other tables (such as a code lookup table).

For example, you could define an Invoice DBObject to have a field for Customer Name, showing the name of the Customer to whom the invoice is issued but it would be bad relational design to store the customer name in the invoice table (not normalized correctly).

You can instead set up a "virtual field" for the DBObject, and provide the logic to look up the value. We add a call to our setupFields method:

1.  addVirtualField("CustomerName", "varchar", 30, 
	  "Customer Name");

In our previous customer example we specified that Customer has a field Customer Name. We want our virtual field to look up the proper name automatically, making it seem as though the name is stored with the invoice.

In order to do this we extend the usual getField(String) method:

1.  public String getField(String fieldName) throws DBException {
2.    if (fieldName.equals("CustomerName")) {
3.     Customer ourCustomer = newCustomer();
4.     ourCustomer.setDataContext(getDataContext());
5.     ourCustomer.setField("CustomerID", getField("CustomerID"));
6.     ourCustomer.retrieve();
7.     return ourCustomer.getField("CustomerName");
8.   }
9.   return super.getField(fieldName);
10. }

As you can see, it is important to return the value from the superclass method for all other fields.

Read-Only Fields

Some fields in a DBObject may have values that are set only when the record is first stored, such as a creation date or sequential serial number. In order to specify this, the setReadOnly method can be used. For example:

setReadOnly("CreationDate");

If you need a sequential serial number, such as an invoice number, then you can use the "auto-inc" data type to have Expresso automatically assigns unique values for your records when you add the record to the database.

Multi-Valued Fields & Framework Managed Relationships

Some fields in a DBObject may have only a specific set of allowed values. A simple example might be a Yes or No type of field. The only valid values are Y or N. In order to make this restriction, we call the setMultiValued method in setupFields():

1.   setMultiValued("AccountOpen");

Once you've specified that a field is multi-valued, you must enumerate the possible values by one of two methods.

In the first case, you extend the getValidValues() method to supply the values available for the field. This is most appropriate for static values, such as in our example above:

1.   public Vector getValidValues(String fieldName) throws DBException {
2.
3.     if (fieldName.equals("AccountOpen")) {
4.       Vector myValues = newVector();
5.       myValues.addElement(new ValidValue("Y", "Yes"));
6.       myValues.addElement(new ValidValue("N", "No"));
7.       return myValues;
8.     }
9.     return super.getValidValues(fieldName);
10.  }

Another method of providing valid values is to specify what is called a "lookup object" for the field. This is appropriate if the values for the field come from another DBObject, such as a code lookup table. For example:

1.  setMultiValued("CustomerType");
2.  setLookupObject("CustomerType", "com.yourcompany.dbobj.CustomerType");

If you choose the second option then you are allowing the framework on your behalf to manage the relationships between two data objects.

By setting metadata with setLookupObject(), this code assumes that the Customer Type object specified exists and implements the getValues() method, which returns the vector of valid values. The setMultiValue() defines the meta field to be multi-valued, that is accepts a value that belongs to a group of enumerated members. The setLookupObject() defines which database object to load, search and retrieve these enumerated values. In other words it instructs Expresso to lookup the enumeration values of this field name inside another database object. The first parameter is the field name and the second name is the fully qualified java class name of the target dataobject.

An example will make this clearer. Suppose we have an EMPLOYEE and DEPARTMENT database tables, and they have the following schemas.

CREATE TABLE IF NOT EXISTS EMPLOYEE (
    INT          Employee_Id,
    VARCHAR(25)  First_Names,
    VARCHAR(25)  Last_Names,
    CHAR(1)      Sex,
    INT          Dept_Id,
    PRIMARY KEY (Employee_Id ))

CREATE TABLE IF NOT EXISTS DEPARTMENT (
    INT          Dept_Id,
    VARCHAR(25)  Dept_Name,
    PRIMARY KEY  (Dept_Id ))

You can see that DEPARTMENT has a foreign key Dept_Id into the EMPLOYEE table. When presenting a user interface to insert new employee's into the database, you want to display a selection of department name rather than identity names. Likewise with the new employee's sex you want to present a readable menu. You need to write your Employee DBObject to return a vector of valid values according to the right field and also set up the lookup meta data accordingly.

package org.fooey.testapp.dbobj;

public class Employee extends DBObject {

    // ...

    public void setupFields() {
        // ...

        setMultiValued("Sex");
        setMultiValued("Dept_Id");
        setLookupObject("Dept_Id", "com.fooey.testapp.dbobj.Department" );
    }


    public Vector getValidValues(String fieldName)
         throws DBException {

        if (fieldName.equals("Sex")) {
            Vector myValues = new Vector();
            myValues.addElement(new ValidValue("M", "Male"));
            myValues.addElement(new ValidValue("F", "Female"));
            return myValues;
        }
        return super.getValidValues(fieldName);
    }

    // ...
}

So we have an Employee database object with the attribute "Sex" that handles the appropriate hard-coded valid values. To complete the look up code we implement the valid value look up in the corresponding Department database object. In order to handle this automatically we override the getValues() method.

package org.fooey.testapp.dbobj;

public class Department extends DBObject {

    // ...

    public Vector getValues() throws DBException {
        Vector myValue = this.getValuesDefault( "Dept_Id", "Dept_Name" );
        return myValues;
    }

    // ...
}

The method getValuesDefault() in the base class DBObject performs a search and retrieve of records from the database according to criteria. The first parameter is the target field name, and the second parameter is the description field name. It is functionally equivalent writing to the SQL code SELECT Dept_Id, Dept_Name FROM DEPARTMENT, in other words calling directly searchAndRetrieveList() except that the base class method as convenience stores the results, the valid values, automatically inside Expresso's standard cache manager. Brilliant!

To this issue clear again. The code `this.getValuesDefault( "Dept_Id", "Dept_Name" )' automatically retrieve the data from the database as if it is stored like this:

DEPT_ID    DEPT_NAME
========================================
100        PAYROLL
200        SALES
300        MARKETING
400        RESEARCH AND DEVELOPMENT
500        CUSTOMER SERVICES

The end result is an employee record that is controllable from DBMaint that renders a drop-down list for entering the sex of the new employee, and another drop-down list for choosing a list of departments.

A shortcut exists for implementing getValues() methods where the values to be returned are simply a key field and a description. This is the getValuesDefault(String) method.

Multi-valued fields are handled by the DBMaint program in a specified manner: When records are listed, the value description is displayed instead of the value itself (e.g.. "Yes" is shown rather than "Y") and during field entry, a drop-down list will be presented for the user to choose from.

Future Framework Managed Relationship Possibilities

In the current Expresso Framework 5.5 the current method of specifying the target source data object is incomplete. If the target database table has a different named column name to the one in the source database table, then the framework cannot cope automatically. For example if the employee table has a column called DEPARTMENT_ID and the department table has a column DEPT_ID

It is envisaged that a new method call will be introduced handle this requirement in the near future.

Automatic Look-up of valid values

A field in a DBObject which is set to LookupObject is expected to refer to a DataObject which implements the LookupInterface.

package com.jcorporate.expresso.core.dbobj;
public interface LookupInterface {
    public Vector getValues() throws DBException;

}

The DBObject implements the LookupInterface. If you would rather not write code to generate valid values look-ups automatically, then you use this method signature and the interface to return a single list of valid values. Not with this interface LookupInterface you can only ever return one vector list of valid values. You cannot return valid values that depend on different field names. This is what the method getValidValues() is designed for! So for example consider a EMPLOYEE data object, then has a look-up values in another data object ACCOUNT such as

package org.fooey.testapp.dbobj;

public class Employee extends DBObject {
    // ...

    public void setupFields() {
        // ...

        setLookupObject("Account_Id", "com.fooey.testapp.dbobj.Account" );
    }
}

The account object is rather simply written as the following code. Notice that it uses the convenience getDefaultValues() method to retrieve valid values and cache them simultaneously.

package org.fooey.testapp.dbobj;

public class Account extends DBObject {

    // ...

    public Vector getValues() {
        return this.getValuesDefault( "Account_Id", "Account_Description" );
    }
}

When working with multi-values data field names, there are basically two choices.

  • Override getValidValues() and provide your enumerations for the field name by returning a list collection of ValidValue objects. The advantage here is that you have maximum control of what constitutes a valid value, you can specify what the label and the value is. You define the source of the valid values, whether it is hard coded, or if it comes from another dataobject, or wherever. There is a further advantage you can further internationalise the valid values. This is best practice.

  • Do not override getValidValues() and let the framework try to provide enumerations for the field name. The default DBObject.getValidValues() expects each field name to be a multivalue and it will treat each multi-valued field name as look up on another DBObject. If this condition is not met, then you will get an DBException.

    The javadoc makes this clear. "Database objects should extend this method to return Vectors of ValidValue objects for multi-value fields. A specific [database] object can return its own list [collection] of ValidValues, or it can call this superclass method to use the lookup object to get the list of valid values instead."

Secret Fields

A field in a DBObject can also be set to "secret" so that its value cannot be seen by users with only "search" ability - the field is also shown as asterisks during data entry. Password fields, for example, might use this feature.

Field Validation

DBObjects can be set up so that only appropriate values are accepted for fields. Several features facilitate this:

  • Null/not null:

    The boolean parameter to the addField method is a simple form of validation - a non-null value must be specified for all fields where this value is false.

  • Data-type checking:

    The data type is also a basic form of validation - only the appropriate type of value will be accepted, even if the field is set with a setField(String, String) call.

  • Valid Values:

    Multi-valued fields are also validated - only a value found in the list of valid values is permitted.

  • Extend setField:

    A more specific way of validating fields is to extend the actual setField method, like this:

    1. public void setField(String fieldName, String fieldValue)
               throws DBException {
    2.   if (fieldName.equals("Priority")) {
    3.     if(!(fieldValue.equals("A") ||
                   fieldValue.equals("B") ||
                   fieldValue.equals("C"))) {
    4.       throw new DBException ("Priority must be A, B or C");
    5.     }
    6.     super.setField(fieldName, fieldValue);
    7.   }
    8. }

Field Filtering

For security purposes, fields are able to be "filtered" on the way in and out of the database. In the setupFields method, where the fields of a DB object are specified, you can use the setFilter method to specify a filter for a field. By default, a field will be filtered via the "standard" filter which .

Also available for use are the following filters:

Field "Masks"

Fields can also be validated against a regular expression. In the setupFields method, the setMask(fieldname, Mask) method can be called to specify a regular expression that is associated with a particular field. The field then must "match" this expression in order to be considered valid. See the documentation for the Jakarta ORO project for more information on setting up Regular Expressions. The project is located at http://jakarta.apache.org/oro/ .

Expresso also currently provides two predefined field masks for you: DBObject.INT_MASK and DBObject.EMAIL_MASK to properly check for valid integer and email address entries respectively.

Virtual Fields

By adding virtual fields (e.g. calculated fields) to the database object, and by using "nested" database objects, data which is in fact stored across multiple tables can be dealt with by the application as a single object. For example, if a virtual field in an invoice header gives the invoice total, the application does not need to deal with the invoice detail objects in order to get the invoice total information - this is an implementation detail which is hidden by the Invoice database object.

Master/Detail Records

Data is often represented in related tables with a master/detail, or one-to-many relationship. DBObjects support this kind of relationship explicitly, and by using the "addDetail" method, other database objects can be declared to be detail records of a master DBObject. This allows the detail records to be maintained easily by the DBMaint controller, and supports cascading deletes - that is, detail records can be automatically deleted when the master record is deleted.

Declarative Referential Integrity

Database objects can be set up with referential integrity between themselves by means of simple one-line method calls in the objects themselves. These integrity constraints are then automatically verified for any operations on these objects. It is also possible to easily implement cascading deletes and updates based on referential constraints.

To programmatically implement referential integrity, you can override the CheckRefs method in your own DBObject to make sure no other DBObject that might depend on you is still pointing to your data.

Multi-Valued Fields

Fields can be specified as multi-valued, in which case the valid values for the fields can be returned by a call to the getValues method. These valid values can be cached for performance (via the ConfigManger class), and can be retrieved from another database object, or computed as needed. Special methods make adding validation checks for both multi-valued and ordinary fields very easy. The validation is then applied to all access to the database object, ensuring data validity.

Field Descriptions

The database object can return extended information on it's fields, including a long description (other than the database name) and other information.

If the value of this fieldDescriptions happens to be defined in your schema's MessagesBundle_XXXX.properties then Expresso will automatically substitute the appropriate string from the appropriate Message Bundle instead, thus providing field name i18n support.

Lookup Objects

Any fields in a database object that is looked up or validated against the values in another database object can have the reference recorded. The client application can then request the name of the referenced object, perhaps in order to provide a list of valid values to the client or for displaying the relationship between the objects.

Read-Only Fields

Fields can be set as read-only if they are only manipulated from within the database object. An example of this is a sequential number key object.

Sequential Numbers

Current Expresso does not, by and large support Database native auto incrementing features. One example of a class that creates it's own Sequence type is located in the demo package called DBSequence. The biggest reason that Expresso does not at this time support this is due to the fact that databases all have their own semantics for auto incrementing types. So Expresso provides its own through the auto-inc fields and thus the underlying "NextNumber" classes.

Default Values

Default values can be specified that populates the tables created for each database object. The "populateDefaultValues" method of each DBObject is run when DBCreate is executed, and the default values for the DBObject are added in this method.

Read-Only Fields

Fields can be set as read-only if they are only manipulated from within the database object. An example of this is a sequential number key object.

Lookup Objects

Any fields in a database object that is looked up or validated against the values in another database object can have the reference recorded. The client application can then request the name of the referenced object, perhaps in order to provide a list of valid values to the client or for displaying the relationship between the objects.

Using Database Objects

Now that you have created the DBObject, you can begin using it in your programs. We will examine each of the most common operations with DBObjects.

Creating an Instance

Setting a Database Context

Virtually all of the standard programs that come with Expresso will utilize the currently logged-in context for their database objects. Whenever a user is logged in, they establish, either explicitly or by default, a "current" database/context. Usually this is done by selecting a context from the drop-down list when logging in via the Login Controller, but it can also be implied by means of a special tag in JSP pages.

DBMaint, for example, will always set any DBObjects that it uses for a particular user to use the currently logged-in context for that user. So, if the user is logged in to the "demo" database/context, all DBObjects that DBMaint utilizes will have setDataContext("demo") called before they are used.

This provides the ability to have entirely separate contexts with distinct databases running on the same servlet/JSP/app server, with only a single VM.

Multiple database contexts are set up by having more than one "context" sub-element in the expresso-config.xml file - each "context" element can define a JDBC section to specify database connection information for that context.

Adding Records

Adding new records is as easy as updating: simply populate the fields of the record (especially the primary key) and call add(). If a record already exists with the same primary key, an exception will be thrown.

You can also populate default values in the fields by calling setField("fieldName", getDefaultValue("fieldName"));

Retrieving Records

Database objects can be used in an "aggregate" mode where a single DBObject represents a list of records, or other database objects. This allows searching and the result sets from searches to be manipulated. Result sets can be sorted on any fields, and searches can be made on any fields, including wild card and range criteria. The count of records retrieved by a search can be obtained without accessing the entire result set.

It is also possible to return only the keys of records retrieved, in order to reduce the size of the data that must be manipulated.

In order to retrieve a specified DBObject corresponding to a particular row in the database, you must first initialize the object in your program:

import com.yourcompany.dbobj.Customer;
.
.
.
    Customer oneCustomer = newCustomer();

This initializes one instance of Customer, called oneCustomer. Now to retrieve a specific customer, we must specify a value for the key field (or fields if there were more than one key field).

                oneCustomer.setField("CustomerID", "1");

This specifies a value for the Customer ID field as "1". Note that we specify the value as a string - there are also setField methods for other types but we can always use strings - the value will be converted to the appropriate type for us.

Everything we have done so far has been in memory, no access to the database has been made until we say:

oneCustomer.retrieve();

This will access the database (or potentially the cache - more on this later) and retrieve the appropriate record for the specified key. Now we can access other fields in the record with getField(fieldName);

System.out.println( getField("CustomerName") );

Will print Customer 1's name.

What if we don't know the Customer's ID but want to locate customers based on other criteria? The retrieve() method requires that the fields that make up the primary key each have a value specified - it will throw an exception if this is not the case. (Most methods of DBObjects may throw DBException - you must enclose the above code in a catch/try block to handle this exception or your method must also throw DBException.)

Other methods for retrieval exist for when the key retrieved is not suitable. For example:

/* Erase any current values in fields */
oneCustomer.clear();

oneCustomer setField("CustomerName", "Jones");

if (oneCustomer.find()) {
    System.out.println("Jones found!");
}

The find() method, if successful, returns true and populates the DBObject with the field values from the database for the first matching record. If the find() does not locate any records it returns boolean "false" and the fields are not populated.

Retrieving Multiple Records

DBObjects can also be used to retrieve whole sets of records rather than one at a time. For example, lets say we want to perform some processing on all customers of type "AB" in our database. We can use code like this:

import java.util.*;
1.      Customer custList = newCustomer();
2.      Customer oneCustomer = null;
3.      custList.setField("CustomerType", "AB");
4.      for (Iterator e = custList.searchAndRetrieveList().iterator();
            e.hasNext(); ) {
5.          oneCustomer = (Customer) e.next();
6.          /* do whatever we need to do to oneCustomer */
7.      }

Let's again examine this code line-by-line:

Line 1: We initialize a new Customer DBObject, custList. Rather than being used to deal with one customer record, this DBObject is used to retrieve a whole list of Customer objects.

Line 2: We declare a second instance of Customer to hold each individual customer that we retrieve. We do not need to initialize this instance, so we set it to "null" for now.

Line 3: Here we supply the search criteria to the custList object, specifying that we will be looking for records where CustomerType equals "AB".

Line 4: This is a complex line. We start a "for" loop by creating an iterator and initializing this iterator to the results of the "searchAndRetrieveList()" method from custList. We could write each step of this line separately like this:

1.    java.util.Iterator i = custList.searchAndRetrieveList();
2.    while (i.hasNext()) {
3.        oneCustomer = (Customer) e.next();
4.    }

Line 5: Here we get each Customer object retrieved individually, re-using the oneCustomer object to hold each record.So, the first time through the loop oneCustomer would hold the first customer record matching the criteria. The second, the next customer, and so forth. We can then use the information in oneCustomer to perform whatever processing we need in line 6.

In this way, we can process as large a list of records as is required, while still handling the database access as an efficient single lookup. This minimizes the access to the database for greatest efficiency.

Handling Ordered Data

If it is necessary to process the records in a specific sequence, another version of searchAndRetrieve can be called with a parameter to specify sort fields. For example:

custList.searchAndRetrieveList("CustomerName");

Retrieves the specified records in Customer Name order (ascending). To specify descending (reverse) order, specify "Desc" on the end of the string, such as:

custList.searchAndRetrieveList("CustomerName Desc");

You can also specify multiple sort field by specifying more than one field name separated by a pipe "|" symbol, like so:

custList.searchAndRetrieveList("CustomerName|CustomerID");

This specifies that the records are to be retrieved in Customer Name order but with any Customers having the same name further ordered by Customer ID.

If no sort criteria is specified, it is *NOT* safe to assume that the records will be returned in any particular order. Often a database will return records in the order they were inserted, or in key order, but you cannot count on this being the case. If you need the records in a particular order, ask for it by specifying the parameter to searchAndRetrieve.

Handling Large Data Sets

As a general practice, you should specify your search criteria as narrowly as possible, in order to retrieve as few records as are needed to do the specified task. You can set criteria on as many fields as are needed and all will be combined to create the resulting record set. For example:

1.    custList.setField("CustomerName", "Jones");
2.    custList.setField("CustomerType", "AB");

This specifies that you want customers whose name is Jones and whose type is "AB".

Alternately you can use DBObject.setMaxRecords(int), and DBObject.setOffset(int) to limit the number of results set. The effectiveness depends on your database and config file setup, however, even if your database does not support limitation at all, you will save significant memory so you do not have to load large datasets into memory.

Using Ranges and Wild Cards

You can specify more than just exact matches when using search criteria. Wild cards and ranges can also be used, the exact syntax depending on the database engine being used. See the properties file for documentation or details on setting up the appropriate wild card characters for your specific database.

For example:

custList.setField("CustomerName", "A%");

Specifies a search for all customers with a name beginning with "A".

custList.setField("CustomerName", "[A-M]%");

Specifies a search for all customers with a name beginning with "A" through "M".

Also,

custList.setField("CustomerID", "BETWEEN 1 AND 20");

Specifies a search for customers with a Customer ID between the numbers 1 and 20.

Specifying search criteria carefully can reduce the number of records processed and speed up your application.

You may also specify that a search only return a specific number of records at a maximum. This can be helpful when you need, say, only the first 100 customers matching certain criteria:

custList.setMaxRecords(100);

Says that the searchAndRetrieve will retrieve a maximum of 100 records, even if more match the criteria.

You can use the "count()" method to see how many records will match your search without actually retrieving the records themselves:

1.    custList.setField("CustomerName", "A%");
2.    int ct = custList.count();
3.    System.out.println("There are " + ct + "customers with names starting with A");

If it is necessary to process a very large record set, you can use a flagging technique, such as this example. Here we assume that all records have a field "Processed" that is initially set to "N":

1.  Customer custList = new Customer();
2.  Customer oneCust = null;
3.  custList.setField("Processed", "N");
4.  custList.setMaxRecords(100);
5.  boolean moreRecords = true;
6.  while (moreRecords) {
7.      for (Iterator i = custList.searchAndRetrieveList().iterator();
8.          e.hasNext();) {
9.          oneCust = (Customer) e.next();
10.         /* Process Customer */
11.         oneCust.setField("Processed", "Y");
12.         oneCust.update();
13.     }
14.     if (custList.count() == 0) {
15.         moreRecords = false;
16.     } /* if */
17. } /* while */

The above code will process all customers by retrieving only 100 at a time.

Specific Fields

For large tables (e.g. a table with many fields), it is often more efficient for some queries to request and retrieve only the necessary fields: e.g. do something like a "SELECT a, b, c FROM..." rather than "SELECT * FROM...". DBObjects support this facility, by means of the "setFieldsToRetrieve(String)" method, which takes a pipe-delimited list of the fields that should be retrieved for subsequent queries.

Max Records

The setMaxRecords method can be used to tell a database object that subsequent calls to retrieve multiple objects (such as searchAndRetrieve) are only expected to return a certain number of objects - this can be useful when showing only the "first n" records that match a query, or providing other "query governor" functions to prevent extremely large result sets from being processed. If the database being used supports it, this function can be used along with methods that set the starting record of the selected set to be retrieved as well, making "page by page" operations very efficient.

Caching

In order to improve performance, DBObjects and valid values can be "cached" or stored in memory. As memory access is many times faster than disk access (or Database access) this can result in significant performance enhancements.

Caching of DBObjects can be enabled by adding an entry to the DBObjPageLimit table, specifying a non-zero value for the Cache Limit field. This field sets the number of that DBObject that will be cached, at a maximum. The cache manager will populate the cache on a most-frequently-used basis, so performance will be enhanced over time as the item's cache fills. The best settings for the cache limits for each DBObject depends on the exact nature of your application and the memory available to your JVM. Options on the JVM's command line (-Xmx for example) can adjust the amount of memory available for caching.

In addition, valid values can be cached and this can also be a significant performance enhancement. When using the getDefaultValues method, caching will automatically be used.

The DBObject and valid value caches are affected by updates, deletions and additions, so the cache values never become out of date. This is another important reason to use only DBObject database access in your applications - if you do, the cache values for records will always be up-to-date. On the other hand, if you were to use a direct SQL update, then the values in cache would be out of date compared to the latest values in the database.

For more details on caching, see the Caching documentation.

Custom Where Clauses

If you need to specify "or" relations, or other special conditions, you can use the ability of DBObjects to set a custom "where" clause for the SQL query to be executed. For example:

1.  custList.setCustomWhereClause
2.   ("CustomerType = \"AA"\ OR CustomerType = \"BA"\");

You would use this version of the setCustomWhereClause() method instead of setting search criteria by calling setField(). If you wish to append the custom "where" clause to that generated by calls to setField(), the setCustomWhereClause() method also takes an optional boolean parameter. If set to 'true' the specified custom "where" clause will be appended to that generated by any values set by calls to setField(). For example:

1.  custList.setField("CustomerName", "Jones");
2.  custList.setCustomWhereClause
3.   ("(CustomerType = \"AA"\ OR CustomerType = \"BA"\)", true);

The above code will generate the following SQL "where" clause:

CustomerName = "Jones" AND (CustomerType = "AA" OR CustomerType = "BA")

The custom where clause only effects the next query run: it is reset after each query is executed, for safety.

Updating Records

As you can see above, updating records in the database is very easy. Call update() on the object you have changed and the changes are written back to the database. Only a single record is ever updated and it is always safest to retrieve() the record first. (See section later about transaction control for information about commit and rollback operations or updates.)

Deleting Records

Like add, delete requires that the DBObject have field values for at least the primary key field. The call

oneCustomer.delete();

removes the record specified by the oneCustomer object (see the later section on referential integrity).

Delete, like update and add, only affects one record at a time.

Security

Every interaction with a database object can be secured, and the security data is easily maintainable via Expresso's built-in capabilities. This allows the security to be updated from any location, and for the changes to take effect immediately.

An extension of the basic DBObject called SecuredDBObject uses a series of tables containing user and group information to supply database security at an object level. Users are collected into groups, and these groups given only what permissions they require to appropriate database objects. Database objects that inherit from SecuredDBObject make use of this security automatically, with no further effort on the part of the developer (or the DBA).

A DBObject can be accessed by any program and can read and write to any database object that the user specified in the property file (as the database user) has permission for - often this is the entire database. In order to define a standard and database-independent way of specifying database permissions, an extension of DBObject, called SecuredDBObject, is available.

By extending SecuredDBObject rather than just DBObject, your DBObject automatically gets to take advantage of this security capability.

Using built-in maintenance functions in Expresso, authorized users (such as system administrators) organize users into groups, then give these groups any or all of 4 possible permissions on each SecuredDBObject: Add, Update, Search, Delete. You can specify, with the setUser(String) method, the user running your program and the SecuredDBObject will automatically verify each operation requested against this security information. For example:

1.  Customer oneCustomer = newCustomer();
2.  oneCustomer.setUser("Fred");
3.  oneCustomer.setField("CustomerID", "1");
4.  oneCustomer.retrieve();

the above code will succeed only if the user with user ID 1 has "Search" permission on the Customer SecuredDBObject. If he does not, the call to retrieve() throws a Security Exception.

You can also check permission before calling a method, e.g.

1. custList.isAllowed("S");

will return true if the current user is allowed Search ("S") permission, false if permission is denied. A, U, and D can also be used to check Add, Update and Delete permission respectively.

This can be used to present only the appropriate choices to the user, showing only the options that are available to that user.

Multi-Database Capability

A database object can be set up to be accessed from an alternate data store, e.g. another database on the same server, or another database on a different server. This is very valuable in data warehousing scenarios, or where the control data for Expresso is stored in one database and the application data in another.

In order to use this ability, a few preparations must be made: Let's say for the sake of example, we have a DB2 database called "SALES" with a table in it called "CUSTOMER" that we want to access. The "SALES" database will not contain any Expresso specific tables, these will all be kept in our MYSQL "default" database. The XML tag that should be used is called <hasSetupTables>. The body content of this tag should be boolean value such "yes" or "no" , "true" or "false". Please not the default is to assume that the database context has Expresso specific configuration tables. Please also note the order and placement of the <hasSetupTables> it comes after the <description> ( checked against the Expresso Configuration DTD; Peter Pilgrim 30th September 2002).

  • First, we must prepare an appropriate expresso-config.xml entry for the new "SALES" database.

    <?xml version="1.0" encoding="ISO-8859-1" ?>
    
    <!DOCTYPE expresso-config PUBLIC
              "-//Jcorporate Ltd//DTD Expresso Configuration 4.0//EN"
              "http://www.jcorporate.com/dtds/expresso-config_4_0.dtd">
    
    <expresso-config>
       <logDirectory>%web-app%WEB-INF/log</logDirectory>
       <strongCrypto>n</strongCrypto>
       <userInfo>com.jcorporate.expresso.services.dbobj.DefaultUserInfo</userInfo>
       <servletAPI>2_3</servletAPI>
    
      <class-handlers>
        <class-handler name="userInfo" 
          classHandler="com.jcorporate.expresso.services.dbobj.DefaultUserInfo"/>
      </class-handlers>
      
      <!-- This database context does have Expresso configuration tables -->
    
      <context name="default">
        <description>Hypersonic Database</description>
    
        <hasSetupTables> true </hasSetupTables>
    
        <jdbc
           driver="org.hsqldb.jdbcDriver"
           url="jdbc:hsqldb:%web-app%WEB-INF/db/default/default"
           connectFormat="3"
           login="sa" password="letmein" 
           cache="y" />
    
        <type-mapping>
           <java-type>LONGVARCHAR</java-type>
           <db-type>LONGVARCHAR</db-type>
        </type-mapping>
    
        <images>%context%/%expresso-dir%/images</images>
        <startJobHandler>n</startJobHandler>
        <showStackTrace>y</showStackTrace>
     
        <mailDebug>n</mailDebug>
    
      </context>
    
    
    
      <!-- This database context does NOT have Expresso configuration tables -->
      <context name="other" >
        <description>Other DB (Non-Expresso)</description>
    
        <hasSetupTables> false </hasSetupTables>
    
        <jdbc 
            driver="org.gjt.mm.mysql.Driver"
            url="jdbc:mysql://localserver/sales"
            connectFormat="4"
            login="root"
            password=""
            cache="y"/>
        <type-mapping>
          <java-type>LONGVARCHAR</java-type>
          <db-type>text</db-type>
        </type-mapping>
    
        <images>%context%/%expresso-dir%/images</images>
        <startJobHandler>n</startJobHandler>
        <showStackTrace>y</showStackTrace>
      </context>
    </expresso-config>

    As you can see, we have flagged this database as not containing the regular expresso tables by means of the <hasSetupTables> element. This prevents Expresso from trying to read from the usual setup tables in this database.

  • Now we code (or generate for) the Customer database object. This object is just like any other database object, containing fields that match the columns in the "CUSTOMER" database.

  • Now we register the Customer dbobject with our Schema object for our application. We can choose to specify the dbcontext name that the object will be "mapped" to at this time, with the following:

    addDBObject("com.jcorporate.expresso.services.dbobj.Event", "sales");
  • Alternatively, we can leave the mapping until runtime, and make an entry for the customer object in the DBOtherMap table. This allows us to change the name of the mapped context without having to recompile anything.

Once you have defined and "mapped" your DB object, you access it just like any other DB object - it will "know" that it is always to interact with the "SALES" context and will ignore any calls to setDataContext to set its context elsewhere. In this way, the DBMaint controller can be used, even with "mapped" objects.

Security for the mapped object is still read from the "current" context - e.g. whatever context the user of the object has established by logging in. DB object security is otherwise applied normally.

Database Object Mapping

Many enterprise applications require access to the data stored in multiple databases. The "Database Object Mapping" functionality in Expresso allows a developer to define a new DB context to describe a particular database, and then associate particular dbobjects with that DB context so that when that object is manipulated, it is always operating against the correct DB context.

Expresso applications can now be run with a "split context", with one database handling the control tables (such as USERLOGIN, etc.), and one database storing the application data (like a data repository, shared by a few different Expresso applications). This allows you to create different "looks" or "views" to the same data, with completely independent logins, security groups, etc. The bottom line is that now you can specify many different database contexts, and define DBObjects as belonging to one of these contexts at the schema level. After this step, the system will always look to the right database when you use that DBObject.

Here is how it is done:

  • Create a new DB context, other then your default context. For examples sake, lets say we are creating a DB context called "hr" that maps to our human resources database.

    To do this, we add a "context" sub-element to the expresso-config.xml file for this new context, with appropriate db connection information in a JDBC sub-element. (See the documentation on the expresso-config.xml file for details of the format to be used for this).

    You may wish this new context to not contain any of the usual Expresso database tables. You can indicate this through use of the <hasSetupTables>false</hasSetupTables> element, as described in the expresso-config.xml file documentation and the DTD for this file. This specifies to Expresso that the context so labelled will not be used to store any of Expresso's own tables, such as the security and group tables, setup tables, and so forth. This is usually preferable if the context is to be used strictly for application data, with Expresso's setup information stored in a different context.

  • We create a few new DBObjects that map to tables in the hr database. For example, we create a DBObject called "Employee" that maps to an employee table in the hr database, and we create a DBObject called "Certification" that maps to the certification table in the hr database.

  • In our schema, we define the DBObject as "belonging" to our new "hr" context. We do this by adding the DBObject to the schema in the following manner:

    1.  add(Employee(), "hr");
    2.  add(Certification(), "hr");
  • We now run DBTool (or DBCreate). DBTool will see these directives and automatically create a DBOtherMap entry to tell Expresso to always use these two DBObjects against the "hr" DB Context. After running DBTool, the following two entries will appear in the "DBOTHERMAP" expresso table in the default context:

    com.mypackagename.Employee | hr | Employee Table
    com.mypackagename.Certification | hr | Certification Table

    As you can see, the DBOTHERMAP table is used by Expresso to map DBObject class names to particular DB Contexts. This table can be directly manipulated to change the mappings of objects to DB Context locations, but running DBTool with the proper schema entries is generally safer and easier.

  • The DBOTHERMAP table entries are read into memory when Expresso starts up. Therefore, if Expresso is currently running and you have made changes to the DBOTHERMAP entries, you need to restart Expresso for the mappings to take effect.

  • When Expresso starts, you will see a message that states: "Reading otherdb mappings...", "2 otherdb mappings found".

  • These objects will now always be fetched and saved to the "hr" context, rather then the default context or the currently logged-in context.

Important Note: DBObjects mapped to otherdb locations will work in all cases where the object is allowed to create its own connection. However, if you specify a connection object that is pointed to some database, and then create the DBObject with this explicit connection, the DBObject will operate against THAT database connection, regardless of what is specified in the DBOTHERMAP table. This is often the case with a multi-part transaction against the database, where an explicitly allocated DBConnection is used to be able to use "commit" and "rollback". When you create a DBObject with an explicit connection, you have the responsibility of making sure that the connection was made against the correct database. One way to do this is to initialize the db object normally (e.g. without the explicit connection) and use the "getDataContext()" method to see what connection it allocates for itself, then use this context for subsequent connections.

Change Logging

Database objects can be set up to automatically log and track changes to their data, providing automatic audit ability for critical data without additional development.

Transaction Control

When writing sophisticated applications you will sometimes need to have transaction control, that is, the ability to perform several database operations either all successfully or not at all.

Until now, our examples with database objects have all relied on the object's ability to manage their own database connection. Other than specifying the correct Database/Context, we have allowed the database objects to request connections from the appropriate connection pool and release them automatically.

Transaction control requires us to specify a particular database connection, which in turn allows us access to the connection's commit().

Note

MySQL users need to take care to make sure the tables for which tranasction control is being used actually support transactions. With other databases, Expresso will throw an error if transactions are attempted to be used but not supported. Due to technical limitations of MySQL, these errors will not be thrown.

1.  DBConnectionPool myPool = null;
2.  DBConnection myConnection = null;
3.  try {
4.      myPool = DBConnectionPool.getInstance(getDataContext());
5.      myConnection = myPool.getConnection();
6.      myConnection.setAutoCommit(false);
7.      Customer oneCustomer = newCustomer(myConnection);
8.      Invoice oneInvoice = newInvoice(myConnection);

9.      /* populate the Invoice fields */
10.     oneCustomer.setField("Balance", newBalance);
11.     oneInvoice.add();
12.     oneCustomer.update();

13.     myConnection.commit();
14. } catch (DBException de) {
15.     if (myConnection != null) 
            myConnection.rollback();
16.         throw new DBException(de);
17. } finally {
18.     if (myPool != null) {
19.         myPool.release(myConnection);
20.     }
21. }

Line 1 and 2 declare the connection pool and connection objects that we will be using, which we declare outside of the try/catch block so that they are available in the "catch" and "finally" blocks.

Line 3 begins the try block. All of the operations in the block must succeed or must not be done at all. For the purposes of our example, we assume we are creating a new invoice and recording the new customer balance when the invoice is added. If the invoice cannot be added correctly, the customer should not be updated, and visa versa - else the customer's balance would not agree with the total of invoices for that customer.

Line 4 Here we request a reference to the appropriate connection pool object from the connection pool class. We pass the Database/Context name.

Line 5 We request a connection from the connection pool and ...

Line 6 tell this connection that it should not automatically commit updates, but should instead wait for commit() to be called. This effectively begins the transaction.

Line 7 and 8 We now instantiate the Customer and Invoice database objects, passing the connection object we want them to use. The objects will now use the connection passed to them rather than requesting their own connections.

Line 9 and 10 We assume the appropriate code (perhaps a method call) to populate the fields of the invoice object and compute the new customer balance. We'll assume the new balance is stored in the variable "newBalance". This balance is set into the Customer object in line 10 (we also assume a field called "Balance" has been defined in our Customer object).

Line 11 and 12 are the statements that the transaction logic is concerned about. The customer is updated and the new invoice is stored in the database. If either of these operations fail, they will throw a DBException and execution will continue at line 15.

Line 13 calls commit() on the connection which confirms both operations to the database.

Line 15 and 16 handle the situation where either one of the updates or some other database operation has failed. The rollback() method ensures that no partial operations are written to the database and the throw clause re-throws the exception that occurred, allowing Expresso's error handling to deal with it appropriately.

Line 18 and 19 are executed in either the success or failure case and are extremely important: the connection is released back to the connection pool for use by other objects. Without these lines, the connection would be held forever and the connection pool would rapidly run out of available connections.

You can determine whether or not the currently connected database supports transactions by means of the "supportstransactions()" method on the DB connection pool object, allowing your application to determine at run-time if transaction control is available to use. Many JDBC drivers will throw an exception if the setAutoCommit(false) method is called and they do not support transactions. For portability, it is best to check first.

Status Information

The database object can tell the client it's own status, via the getStatus() call, to allow the client to determine if updates are required, if the item has been deleted, if it needs to be stored, etc.

Indexing DBObjects

For performance consideration, you can add an index on a field(s) with the following syntax, using commas to indicate multi-column indices. Of course, primary keys already have automatic indices. Add an index only for column(s) which are not already a primary key.

protected synchronized void setupFields() throws DBException {
    ...
 
    // use syntax  addIndex( "someIndexName" , "someFieldName", whetherIndexIsUnique )
    addIndex("myIndexName", MY_FIELD_NAME, false);		
    addIndex("myIndexName", MY_FIELD_NAME + "," + MY_SECOND_FIELD_NAME, false);
}

Database Objects in Multiple Databases

Expresso has the capability to define and maintain connection pools for multiple databases, and to tie database objects to particular data sources.

Why Multiple Databases?

There are several cases where you would like to deal with multiple databases. The most obvious reason for development purposes is to test your application against many databases at one time without having to modify many configurations. Other reasons include having a connection to a read only data warehouse application, virtual hosting, and others.

There are two ways to set up databases. One way is to define a full Expresso-like environment where each database has it's own security tables, isolated data, etc. Each data context will also have it's own security as well. The other way is for Expresso to store all of it's own database bookeeping in one data context and link to some pre-defined tables used in another database. This method is especially excellent for data warehousing applications where the back-end database must not have any bookkeeping tables installed in it.

Independent Multiple Databases

Expresso must have at least one database connection defined to operate called 'default'. The default database context contains setup and configuration information that Expresso needs to operate. The default database is always the database that Expresso will assume is being used when a DBObject is manipulated unless setDataContext() [Or the older name setDBName()] is called first either directly or through an appropriate constructor.

If you take a look at your expresso-config.xml file you'll see some information like so:

<context name="default">

  <description>Hypersonic Database</description>
  <jdbc driver="org.hsqldb.jdbcDriver" 
        url="jdbc:hsqldb:%web-app%WEB-INF/db/default/default" 
        connectFormat="3" 
        login="sa" password="letmein" 
        cache="y" 
        createTableIndicies="true" 
        limitationPosition="" 
        escapeHandler="com.jcorporate.expresso.core.db.DoubleQuoteEscapeHandler" />

   <type-mapping>
     <java-type>LONGVARCHAR</java-type>
     <db-type>LONGVARCHAR</db-type>
   </type-mapping>
   <images>%context%/%expresso-dir%/images</images>
   <startJobHandler>y</startJobHandler>
   <showStackTrace>y</showStackTrace>
   <mailDebug>n</mailDebug>

</context>
As you can see, at the top of the file, the context is named default as expected. The context definition includes a definition of a JDBC connection to a hypersonic database.

<!-- Old Context Handler -->
<context name="default">

  <description>Hypersonic Database</description>
  <jdbc driver="org.hsqldb.jdbcDriver" 
        url="jdbc:hsqldb:%web-app%WEB-INF/db/default/default" 
        connectFormat="3" 
        login="sa" password="letmein" 
        cache="y" 
        createTableIndicies="true" 
        limitationPosition="" 
        escapeHandler="com.jcorporate.expresso.core.db.DoubleQuoteEscapeHandler" />

  <type-mapping>
    <java-type>LONGVARCHAR</java-type>
    <db-type>LONGVARCHAR</db-type>
  </type-mapping>
  <images>%context%/%expresso-dir%/images</images>
  <startJobHandler>y</startJobHandler>
  <showStackTrace>y</showStackTrace>
  <mailDebug>n</mailDebug>

</context>

<!-- Here we add our new context handler -->
<context name="test">

  <description>A sample test database</description>
  <jdbc driver="org.hsqldb.jdbcDriver" 
        <!-- Database URL points to a new location -->
        url="jdbc:hsqldb:%web-app%WEB-INF/db/test/test" 
        connectFormat="3" 
        login="sa" password="warl0rd" 
        cache="y" 
        createTableIndicies="true" 
        limitationPosition="" 
        escapeHandler="com.jcorporate.expresso.core.db.DoubleQuoteEscapeHandler" />

  <type-mapping>
    <java-type>LONGVARCHAR</java-type>
    <db-type>LONGVARCHAR</db-type>
  </type-mapping>
  <images>%context%/%expresso-dir%/images</images>
  <startJobHandler>y</startJobHandler>
  <showStackTrace>y</showStackTrace>
  <mailDebug>n</mailDebug>
</context>
When the servlet engine now loads Expresso, Expresso will load connections to both a data context named 'test' and one named 'default'. Using this configuration, each data context will be completely independent. You are in a position to run DBCreate on your new context. Go to the setup page, and click on the "Create/Verify Database Structure & Perform Initial Setup ", and you'll see a screen similar to that shown here.

In this I have 3 different contexts defined. I can choose to run DBCreate against any of them to define what I want.

So once we've run how do we use this? An application can have a user deal with multiple database contexts by calling the function setDataContext([Database Name]); So given the config file above, you could have the following code to retrieve a list of all UserPreference settings for the data base context 'test':

UserPreference userPreference = new UserPreference();
userPreferences.setRequestingUid(SecuredDBObject.SYSTEM_ACCOUNT);
userPreference.setDataContext("test");
ArrayList allPreferences = userPreference.searchAndRetrieveList();
When browsing code, you may find code that uses setDBName() rather than setDataContext(). The two functions are currently equivalent, although setDBName() will be eventually phased out of use, and thus it is recommended that you call setDataContext();

When working with controllers, there is a shortcut available for setting the security context of the database object as well as the user's id. If you are within a State handler for a controller (to be discussed shortly), the above code can be shorted to.

UserPreference userPreference = new UserPreference(request);
ArrayList allPreferences = userPreference.searchAndRetrieveList();
Where request is the ControllerRequest object handed to you by the Expresso framework.

Application Integration

There is a user table in Expresso called AppIntegration that provides the ability for a single user to have the security in one database and operate in the other database. It provides the ability to set up, for example, a sample database for onsite demonstrations that provide the same user base as the primary site.

TODO: Explain This

DBOtherMap

DBOtherMap is used in the situation where you have all your Expresso book keeping tables in one database and certain data tables in another. Using DBOtherMap, you can make both datasources appear as one single data context.

DBOtherMap Config File Setup

First off, you'll want to set up your multiple contexts in a similar way as to above with one attribute change:

<!-- Old Context Handler -->
<context name="default">

  <description>Hypersonic Database</description>
  <jdbc driver="org.hsqldb.jdbcDriver" 
        url="jdbc:hsqldb:%web-app%WEB-INF/db/default/default" 
        connectFormat="3" 
        login="sa" password="warl0rd" 
        cache="y" 
        createTableIndicies="true" 
        limitationPosition="" 
        escapeHandler="com.jcorporate.expresso.core.db.DoubleQuoteEscapeHandler" />
  <type-mapping>
    <java-type>LONGVARCHAR</java-type>
    <db-type>LONGVARCHAR</db-type>
  </type-mapping>
  <images>%context%/%expresso-dir%/images</images>
  <startJobHandler>y</startJobHandler>
  <showStackTrace>y</showStackTrace>
  <mailDebug>n</mailDebug>
</context>

<!-- Here we add our new context with setup tables == false -->
<context name="test">

  <description>A sample test database</description>
  <hasSetupTables>false</hasSetupTables>
  <jdbc driver="org.hsqldb.jdbcDriver" 
        <!-- Database URL points to a new location -->
        url="jdbc:hsqldb:%web-app%WEB-INF/db/test/test" 
        connectFormat="3" 
        login="sa" password="warl0rd" 
        cache="y" 
        createTableIndicies="true" 
        limitationPosition="" 
        escapeHandler="com.jcorporate.expresso.core.db.DoubleQuoteEscapeHandler" />

  <type-mapping>
    <java-type>LONGVARCHAR</java-type>
    <db-type>LONGVARCHAR</db-type>
  </type-mapping>
  <images>%context%/%expresso-dir%/images</images>
  <startJobHandler>n</startJobHandler>
  <showStackTrace>y</showStackTrace>
  <mailDebug>n</mailDebug>
</context>
Notice the <hasSetupTables>false</hasSetupTables> addition to the above XML configuraton. This tells Expresso Framework that it isn't going to find any setup tables such as Database object security, controller security, or Jobs under the "test" database context.

Define your Schemas and DBObjects

Next you'll want to define your schema and dbobjects that use the other database map. To do this, you first define your DBObject as you would normally. But you define it in the Schema a little differently using the Schema function:

protected synchronized void addDBObject(String dbobjectClass,
                                        String otherDBName)
rather than addDBObject(Class); The otherDBName is the context name that this dbobject will eventually map to. So a concrete example is:
addDBObject("com.mycompany.myapp.dbobj.myDBObject","test");

Rerun DBCreate

Ok, now rerun DBCreate, but run it against the DEFAULT context. This is a very important point since Security entries for your new dbobject will still need to be created in the default context. Once you finish it, if you take a look at the table in the setup page with the link name of 'Database Object DB/Context Mappings' You should now see your newly created otherdbobject sitting in that table.

Program to use your new OtherMap object

So now you're ready to use it. How do you go about it? It's actually really simple, you just program as if you're accessing the default data context and Expresso will do the rest of the work for you. So to access your object like so:

MyDBObject myobj = new MyDBObject ();
myobj.setRequestingUid(SecuredDBObject.SYSTEM_ACCOUNT);
myobj.setDataContext("default");
ArrayList allPreferences = myobj.searchAndRetrieveList();
In the above code, Expresso will automatically access the test context because of the entry in the other db map.

Using DataObjects in conjunction with Serialization

Expresso DataObjects are tested for serializability in their unit tests, to allow one to serialize dataobjects "over the wire", to disk file, or whatever you need for your application. The biggest issue is that DataObjects have a lot of precomputed member variables included in their state. What a typical user wants in more simply a HashMap of the field names keyed by value. Enter the DataTransferObject in package com.jcorporate.expresso.core.dataobjects

The only thing the DTO contains is the database context, the classname of the parent, and the value of the database fields. Nothing else is stored in it. The end result is that serialization size (and thus times) is approximately 1/3 that of the corresponding DBObject.

Getting an instance of a DataTransferObject

DataTransferObjects are not instantiated directly. Rather you use an existing DBObject to create a DataTransfer Object with the following code:

DBObject myDBObj = new MyDBObject();
DataTransferObject dto = myDBObj.getDataTransferObject();

Rebuild a DBObject with a DataTransferObject

Obviously, once you've sent your DataTransfer object wherever you need to, you'll want to rebuild a corresponding DBObject based upon the contents of the DTO. You can do that with the following code snippet:

//This code is the same as that above, we create the DTO
DBObject myDBObj1 = new MyDBObject();
DataTransferObject dto = myDBObj1.getDataTransferObject();
...
//Here we reconstruct the dbobject based upon the data transfer object
//Notice that the result constructs the right kind of DBObject
MyDBObject myDBObj = (MyDBObject)DBObject.getThisDBObj(dto);

Note

As of the time of this writing, DataTransferObjects have not been officially included into the rest of the DataObject API. The feature for Serialization is completely desired, so you can rest assured that it will be included, however the actual implementation may slightly change by the time it is fully incorporated into the DataObject API

Conclusion

Contributors

The following persons have contributed their time to this chapter:

Note

Was this EDG documentation helpful? Do you wish to express your appreciation for the time expended over years developing the EDG doc? We now accept and appreciate monetary donations. Your support will keep the EDG doc alive and current. Please click the Donate button and enter ANY amount you think the EDG doc is worth. In appreciation of a $35+ donation, we'll give you a subscription service by emailing you notifications of doc updates; and donations $75+ will also receive an Expresso T-shirt. All online donation forms are SSL secured and payment can be made via Credit Card or your Paypal account. Thank you in advance.

Copyright © 2001-2004 Jcorporate Ltd. All rights reserved.