DBAccessor: A JDBC Wrapper Package

Print-friendly Version

Articles Index

Contents

• Why Use a Wrapper?
• Part 1: Database Connectivity
• Part 2: Working with Database Schema Information
• Part 3: Working with Database Data
• Part 4: Additional Functionality
• Summary
• For More Information
• About the Author

Given all the features of the JDBC API, why should any developer need the added functionality of the DBAccessor wrapper?  After all, the JDBC API provided as part of the Java 2 Standard Edition (J2SE) gives you an extremely useful and popular way to access database systems from within Java programs. The API offers methods to open and close database connections, issue query and update statements, and also retrieve information -- such as table and column structure -- in a database-independent way.

So Why Use a Wrapper?

In analyzing several development projects, I found that three areas emerged where the class and interface structure of the JDBC API did not really reflect the "natural" object hierarchy, or where the same methodology had to be reimplemented time and again. Such cases of repeat implementation obviously call for a different approach: you should have to implement functionality once, the right way -- and then never again.

The three key areas that can benefit from the DBAccessor wrapper are database connectivity, working with database schema information, and working with database data. Here's a summary of what I found.

Database Connectivity

A careful analysis reveals three groups of objects to deal with when connecting to databases:

• The JDBC driver itself, required to connect to a specific type of database
• A database entity (a particular instance of a database), which can be described by the database instance name, the host where the database resides, and the port number used by the JDBC driver. A database entity also refers to a JDBC driver, of course, but multiple entities can do so.
• The actual database user that allows clients to connect to a database entity. A user can be described by the user name and the password. Users reference a database entity. Naturally, multiple users can reference one entity.

A general solution should both reflect this natural object structure, and also facilitate working with multiple databases by providing support for the assembly of JDBC URLs (which differ depending on the underlying JDBC driver).

A generic solution could also free the user from managing JDBC connections and statements (by the java.sql.Connection, java.sql.Statement, and java.sql.PreparedStatement interfaces), because the actual coding required is almost always identical.

Working with Database Schema Information

You can retrieve database schema information with the java.sql.DatabaseMetaData interface, but the form of the information is not very well suited for use within Java applications. For example, the getIndexInfo() method in this interface returns one line for each column within each index, which requires additional analysis to obtain a complete picture of an index (that is, all columns the index is defined on).

One place where schema information is useful is the type and length of database columns. This information can be retrieved by the getColumns() method of the java.sql.DatabaseMetaData interface, but again in a somewhat inconvenient form. Nevertheless, you can exploit this information to automatically set the maximum input length allowed in a JSP form for an input field receiving data for this column. The alternative, hard coding this maximum length within the application, is difficult to maintain and error-prone should the column format change.

You can also exploit the primary key schema information for tables to make sure that data items -- which need to be written to the database -- have all these primary key fields properly defined. If you use the schema information rather than hard coding the primary key field information into the application, you gain flexibility in case the primary key needs to be modified.

A generic solution requires a set of classes dedicated to schema management, which overcome the limitations of the java.sql.DatabaseMetaData interface and present the data in a format more suitable for use within Java applications.

Working with Database Data

When you work with data holder objects backed by a database, you always require the same set of methods for SQL SELECT, INSERT, UPDATE, and DELETE, sometimes enhanced by a Boolean-valued method that checks whether an element exists in the database or not. This is not a new insight (after all, this is what container-managed persistence in Enterprise JavaBeans is all about). But for applications not designed to run within the scope of an EJB (Enterprise JavaBeans) container, or not requiring enterprise-level API support like transactions or security, it's annoying and time-consuming to reimplement the SQL statements each time.

Part 1. Database Connectivity

First, let's have a look at what's required to access a database using plain JDBC:

1. Determine the name of the JDBC driver class for the database and instantiate it, if required.
2. Determine the correct URL string for this type of driver and for the selected database (depending on the database instance name and JDBC port).
3. Open a java.sql.Connection using this URL and the name and password of a database user.
4. Create a java.sql.Statement(or java.sql.PreparedStatement) using this connection to the database.
5. Execute the desired SQL query or update using this statement.
6. Close the connection.

An application typically accesses the database from within several methods, so this programming logic needs to be implemented multiple times. Additionally, in a scenario that involves several different databases, those steps require even more programming logic. There's driver, connection, and statement management to handle, and you need to assemble the URL patterns, which differ from driver to driver.

Java 2 Enterprise Edition (J2EE) offers some support here in the form of the javax.sql.DataSource interface. This interface encapsulates the information about driver type, database location, instance name, and JDBC port, effectively handling the first two groups of objects involved in connecting to databases.

The user and password information provided in the deployment descriptor complete the information required to connect to a database. A client application then only needs to refer to the appropriate Java Naming and Directory Interface (JNDI) string defined in the deployment descriptor to make use of this connectivity information.

There are, however, disadvantages to this approach:

• The information required for a javax.sql.DataSource needs to be made known to the J2EE container in advance, sometimes requiring a restart of the server. Thus, this approach cannot achieve dynamic connectivity to databases not known in advance.
• javax.sql.DataSource represents two of the groups of connectivity information I identified previously, which translates to reduced flexibility: in actual use, multiple database entities might exist for the same type of database, each requiring a separate javax.sql.DataSource definition at the J2EE container level.
• This interface can be used only in a J2EE environment, and thus the programming approaches differ depending on whether Java 2 Standard Edition (J2SE) or J2EE is the target platform.

The DBAccessor package tries to address all these issues with a class hierarchy modeled after the natural connectivity structure, while supporting both J2SE and J2EE as transparently as possible.

Generic Representation of JDBC URLs

Let's take a look at how to express JDBC URLs in a more general way, and thereby relieve the JDBC user of this task.

The DBAccessor package relies on a generic description of JDBC driver properties. These descriptions are provided in the form of an XML file, and a typical entry could look like Code Sample 1.

Code Sample 1. A typical entry describing the properties of a JDBC driver in the DBAccessor XML configuration file

  <driver class       = "oracle.jdbc.driver.OracleDriver"
          shortName   = "ora"
          type        = "NET"
          description = "Oracle (thin JDBC driver)"
          URLString1  = "jdbc:oracle:thin:@"
          URLString2  = ":"
          URLString3  = ":"
          defaultPort = "1521"/>

The URLString attributes assemble the JDBC URL according to the following expression:

URLString1hostnameURLString2portURLString3dbname

The remaining attributes are pretty much self-explanatory, except for the type attribute. To understand the motivation for this attribute, you must know that there are two types of JDBC drivers:

• Drivers that operate only on the same host where the database system resides; that is, they do not require a hostname and port specification. I refer to them as APP drivers in this article.
• Most drivers, however, do require you to specify a hostname and a port because they access databases over a network. I call these NET drivers in this article.

In Code Sample 1, for a type NET driver, an actual JDBC URL for an Oracle database named Customers located on host hugo.company.com with a JDBC driver listening on port 1521 could look like:

jdbc:ora:thin:@hugo.company.com:1521:Customers

Note that the situation is slightly different for JDBC drivers of type APP. In this case, the general URL expression looks like this:

URLString1dbname

An entry in the XML configuration file for an APP driver would look like Code Sample 2.

Code Sample 2. A typical entry describing the properties of a JDBC driver of the APP type in the DBAccessor XML configuration file

<driver class       = "COM.ibm.db2.jdbc.app.DB2Driver"
        shortName   = "db2app"
        type        = "APP"
        description = "DB2 (local driver)"
        URLString1  = "jdbc:db2://"/>

The immediate benefit of this approach is that these definitions are generic enough to be made only once for all applications using the DBAccessor package.

The Actual Connectivity Classes

The UML diagram in Figure 1 gives an overview of the main DBAccessor connectivity classes.

Figure 1. Core connectivity classes

These are the main connectivity classes of DBAccessor:

• DBBean.class DBBean is a JavaBean component holding the following six properties that completely identify a JDBC database connection:
  • Database user
  • Database user's password
  • Host where the DB resides
  • Database name (for example, SID)
  • Database type (vendor)
  • JDBC port to be used
• DriverData.class DriverData is a JavaBean that holds all the information available for a specific JDBC driver. (Read on in this section of the article for more details.)
• DBAccessor.class DBAccessor is a wrapper for JDBC drivers. The XML file containing the driver descriptions is read once in a static initializer block in this class using the JDOM API for XML parsing. The driver data is then stored in instances of the DriverData JavaBean.

  You create instances by specifying a unique string (for instance, ora for Oracle) that identifies each of the driver types. The string corresponds to the shortName attribute in the XML definitions:

  DBAccessor dba = new DBAccessor("ora");

  
  

  DBAccessor handles JDBC driver instantiation, if necessary, using java.sql.Driver, and also contains preloaded configuration information like the required driver class or how to assemble the URL string for each of the JDBC driver types, which is used in the other classes of the package.

  DBAccessor maintains two static HashMaps, one for all known JDBC drivers (that is, those for which descriptions have been found in the XML configuration file or have been added dynamically at runtime), and one for all actually instantiated JDBC drivers. Thus, all clients using the DBAccessor package in the same JVM have immediate access to all JDBC driver definitions.

  In many cases, databases use the default JDBC port (for example, 3306 for MySQL). The DBAccessor class also supplies these default ports where necessary, and so the client does not need to know them.

  Currently, DBAccessor provides tested XML definitions for Oracle, DB2 (NET and APP type driver), MySQL, Cloudscape (NET and APP type driver), and PostgreSQL.

  DBAccessor also offers a query method that allows the client to retrieve information about the supported (known) drivers. You can use this information to present users with a list of database types they can connect to. For example, you could set up a JSP front end as a generic tool to approach all kinds of back-end databases with very limited configuration (and coding) effort.

• DBEntity.class DBEntity encapsulates all information identifying a specific database system.

You create instances by specifying a DBAccessor (for the DB type) and a specific database identified by a database name, and optionally a host name and a JDBC port number, depending on the type (APP or NET) of the JDBC driver used.

Instantiating a DBEntity is all that's required to allow different users to access a specific database instance.

DBEntity has three constructors:

• DBEntity dbe = new DBEntity(dba, "dbhost.sun.com", 1521, "IAS"); This constructor is the most general one; it requires a host name, a JDBC port, and the database name.
• DBEntity dbe = new DBEntity(dba, "dbhost.sun.com", "IAS"); This constructor uses the default JDBC port (which is retrieved internally from the appropriate DriverData bean).
• DBEntity dbe = new DBEntity(dba, "IAS"); This covers a JDBC driver of the APP type and requires only the database name.
• DBUser.class DBUser encapsulates all information identifying a database user in a specific database system. In addition, this class contains the methods required to issue SQL statements against that database system.

DBUser.class: The Central Connectivity Class

DBUser is the central class of the DBAccessor connectivity package, because all database interaction for data (or, as you will see later, schema information) relies on this class. This class is also the point where several different concepts of defining a database connection come together:

Concept 1: Create a DBUser instance in a three-step sequence:

1. Create a DBAccessor instance specifying the database type.
2. Create a DBEntity instance using the DBAccessor instance and specifying a particular database.
3. Create a DBUser instance using the DBEntity instance and specifying user credentials, and then use it to access the database.

   For example:

   DBAccessor dba = new DBAccessor("ora"); DBEntity   dbe = new DBEntity(dba, "dbhost.sun.com", 1521, "IAS"); DBUser     dbu = new DBUser(dbe, "scott", "tiger");

   
   

Concept 2: Create a DBUser instance in a two-step sequence:

1. Create a DBBean instance specifying all the required connectivity information (host, port, database name, and so on).
2. Create a DBUser instance using the DBBean instance and then use it to access the database.

   For example:

   DBBean bean = new DBBean("scott", "tiger", "IAS", "ora", 1521); DBUser dbu  = new DBUser(bean);

   
   

   Note that in this case, a DBAccessor and a DBEntity instance are created internally. Note also that, when using Concept 1, a DBBean is created internally that can be accessed by the getBean() method, effectively establishing the equivalence of the first two approaches.

Concept 3: Create a DBUser instance in a one-step sequence:

1. Create a DBUser instance by specifying the JNDI name of a javax.sql.DataSource known to the J2EE container, and then use it to access the database.

   For example:

   DBUser dbu = new DBUser("java:comp/env/jdbc/DatabaseBrowser");

   
   

   Note that the getBean() method returns null when used with a DBUser instance created by this approach because, naturally, the database connectivity data required to populate a DBBean class is not available when relying on javax.sql.DataSource.

Regardless of the approach you employ to obtain a DBUser instance, any further use is independent of that approach. Clients can access the database without having to worry about, for example, connections or statements (as known from the java.sql package).

Note that, while the third approach looks like the easiest way to go here, it is actually targeted at web and/or EJB container environments where a JNDI service is available because the accessed data source must be known to the container as well as to the application by a resource reference in the deployment descriptor. In such environments, the DBAccessor approach can be used for every type of Java application, such as JSPs, servlets, EJBs, or application clients.

You can use the first two approaches in any Java application, whether it runs in the context of a container or not.

DBUser is also the class in the package which is most closely integrated with the JDBC API. It uses the following classes and interfaces:

• java.sql.Connection Database connections are handled transparently to the user.
• java.sql.DriverManager The getConnection() method is used to open a connection to the database in a J2SE or a J2EE context.
• javax.sql.DataSource The getConnection() method is used to open a connection to the database in a J2EE context.
• java.sql.Statement SQL statements are handled transparently to the user.
• java.sql.PreparedStatement Prepared statements are handled transparently to the user.
• java.sql.DatabaseMetaData The database metadata is made available to clients to retrieve information about the database structure. This is exploited in the DBAccessor classes dealing with structural elements (as I explain further in the next section of this article).
• java.sql.ResultSet The data returned by queries is made available through this interface.

Configuring JDBC drivers

Before we get into some hands-on examples for the connectivity classes, you'll need to understand the options for configuring JDBC drivers within the DBAccessor package.

The package comes with an XML configuration file for several JDBC drivers. The file, called accessorconf.xml, loads automatically in a static initializer block in the DBAccessor class. You will probably need to adapt the definitions contained in the file to specific environments. For instance, it may require new JDBC drivers. You can handle this several ways:

• The XML file contained in the DBAccessor JAR file can be extracted (using jar), modified, and inserted into the JAR file. This simple approach makes the changes available to every user of the modified JAR file. It is not very elegant, though, because having to modify the JAR file for each configuration change is quite inflexible.
• Another approach is to extract accessorconf.xml from the JAR file and store a modified version under some different name or path. This modified file can then be made available to the DBAccessor package on startup using the dbaccessor.config property, such as:
  
  

  java -Ddbaccessor.config=/home/hugo/config/mydrivers.xml ...

  
  

  This is quite elegant, since the JAR file is not modified (imagine a new version of the package needs to be installed!). When using the package in a web container or an application-server environment, where an application is not directly invoked with the java command, you need to determine the appropriate method to specify this property for the application.

• Yet another approach is to add custom JDBC driver definitions directly from within the application by instantiating DriverData objects, which are populated with the desired JDBC driver properties. These objects can then be added to the set of known drivers by the static DBAccessor.addDriver() method. Note, however, that these definitions are lost after such a program terminates, unless they are exported to an XML configuration file by the exportDrivers() method. You can reuse such a file in either of the two ways I've described.

Using the Connectivity Classes

So far -- apart from the (interesting, I hope) technical details -- I have shown little more than how to create DBUser instances. But what can you do with them? That is what this section is all about.

Once one of the several constructors outlined above has created a DBUser instance, methods of the DBUser class can issue SQL statements (queries and updates as statements and prepared statements). DBUser also manages java.sql.Connection, java.sql.Statement, and java.sql.PreparedStatement instances transparently to the Java client, so there's no need to worry about them.

A simple example using the DBAccessor package could look like Code Sample 3.

Code Sample 3. Issuing a query with DBAccessor in three lines of code

DBEntity dbe = new DBEntity(new DBAccessor("msq"), 
  "db.sun.com", "Bank");
DBUser  dbu = new DBUser(dbe, "manager", "mypwd01");
ResultSet res = dbu.executeQuery("select * from account 
  where owner = smith");

You need only three lines of Java code to do all of this: connect to a MySQL (thus the msq in line 1) database instance named Bank located on host db.sun.com by a JDBC driver listening on the default port as user manager -- and to run the specified query!

A subsequent SQL INSERT into this database would also be very easy; all you need to do is add a row to the account table is this one line:

dbu.executeUpdate("insert into account values ('savings', '100.00')");

A different user could reuse the DBEntity instance to connect to this database using Code Sample 4.

Code Sample 4. Reusing a DBEntity instance for a different database user

DBUser teller = new DBUser(dbe, "teller", "hugo01");
ResultSet res = dbu.executeuery("select * from stocks 
  where owner = miller");
dbu.executeUpdate("delete from stocks where stock_name = 
  'ACME'");

You can rewrite the example in Code Sample 3 using a DBBean instance, as shown in Code Sample 5.

Code Sample 5. Selecting from a database using a DBBean instance

DBBean    bean = new DBBean("manager", "mypwd01", 
  "db.sun.com", "Bank", "msq");
DBUser    dbu  = new DBUser(bean);
ResultSet res  = dbu.executeQuery("select * from 
  account where owner = smith");

Note that in Code Sample 5, I chose a bean constructor that takes no port argument, which means that it will use the default JDBC port.

Similarly, a J2EE component, like a servlet or an EJB, could use coding like Code Sample 6 to access a back-end database.

Code Sample 6. Code for a J2EE component to access a back-end database

DBUser    dbu = new 
  DBUser("java:comp/env/jdbc/DatabaseBrowser");
ResultSet res = dbu.executeQuery("select * from 
  account where owner = smith");

Connections, Statements, and Their Lifetime

Each DBUser instance holds exactly one instance of java.sql.Connection. This instance is opened and closed within the DBUser methods as required; it's left open in between calls if possible. At any point, a client can opt to close the connection using the closeConnection() method.

The DBUser class also supports statements using the java.sql.Statement and the java.sql.PreparedStatement interfaces from the java.sql package. For java.sql.Statement, each DBUser instance holds exactly one instance of this interface, which is opened and closed as required.

For prepared statements, two different implementations are supported: There is one anonymous prepared statement backed by exactly one instance of java.sql.PreparedStatement. The use of this statement is very similar to the simple java.sql.Statement; that is, it's opened and closed as required. The real power of prepared statements, in terms of efficiency (there are other benefits, of course), is their reuse, and thus the second implementation supported by the DBUser class allows clients to define any number of named prepared statements. These can then be reused as often as required, and they will stay alive as long as the database connection stays open.

The current set of methods supporting SQL-statements against the database system includes:

• For simple statements:
  • void              executeUpdate(String sql)
  • void              executeUpdate(String[] sql)
  • ResultSet         executeQuery(String sql
• For anonymous prepared statements:
  • PreparedStatement definePreparedStatement(String sql)
  • void              executePreparedUpdate()
  • ResultSet         executePreparedQuery()
• For named prepared statements:
  • PreparedStatement definePreparedStatement(String name,
                    String sql)
  • void              executePreparedUpdate(String name)
  • ResultSet         executePreparedQuery(String name)

The methods available to define prepared statements return a reference to the instance created. You can then use this reference to set the variable values in the statements using the appropriate setXXX() methods. This approach is different from the one used for simple statements, where the actual java.sql.Statement instance is completely managed internally and thus hidden from the user. The alternative, however, would be to create wrapper methods in DBUser for all the setXXX() methods in the java.sql.PreparedStatement interface, an approach that is both inelegant and hard to maintain.

The general lifetime strategy is to leave the connection and any statement open as long as possible. Clients can close any statement selectively, or the entire connection (which in turn closes all open statements)-- for example, to limit the number of open connections (which are an expensive resource!) if many DBUser instances are to be used in an application.

Of course, you can easily add other methods to the DBUser class -- for instance, from java.sql.Statementor java.sql.Connection -- should the need arise. For example, DBUser includes a method to retrieve the database meta data:

DatabaseMetaData getMetaData()

Let's have a look at the specific implementation of one of these methods in Code Sample 7.

Code Sample 7. Implementing a DBAccessor method for updating the database

public void executeUpdate(String[] sql) throws 
  AccessorException {
  if (sql == null) { throw new 
    IllegalArgumentException("ERROR: DBUser: sql 
      may not be null"); }
  try {
    closeStatement();
    openConnection();
    for (int i = 0; i < sql.length; i++) {
      statement = connection.createStatement();
      statement.executeUpdate(sql[i]);
      statement.close();
    }
  } catch (java.sql.SQLException e) {
    throw new AccessorException("ERROR: Could not 
      execute update: " + e.getMessage(), e);
  }
}

The method in Code Sample 7 takes an array of SQL update statements (INSERT, UPDATE, DELETE), opens a database connection (or reuses one already open; the openConnection() method takes care of that), creates statements, and executes the SQL statements.

Part 2: Working with Database Schema Information

Within the DBAccessor package, a hierarchy of classes has been implemented which allow for the representation of database schema information within the application scope.

This UML diagram in Figure 2 shows the main DBAccessor structural classes:

Figure 2: Core structural classes

DBAccessor has five main structural classes. Each of them contains a hash map holding any number of attributes, as required by an application using this class (that is, any attribute name can be used to store any Object valued data item within such an instance). This is the most generic approach possible. When schema information is imported directly from a database by one of the methods provided in the Transfer class, these attributes are set such that they hold the information provided by the corresponding java.sql.DatabaseMetaData methods. Read on for more details:

• ColumnSchema.class ColumnSchema holds the schema information describing a table column.

  When using Transfer methods to retrieve schema information from the database, the attributes are set to the values returned from the java.sql.DatabaseMetaData.getColumns() method, which are standardized, so that an application can rely on the attribute names and the type of data they refer to.

  Column names are unique within the scope of a database table.

• IndexSchema.class IndexSchema holds the schema information describing a table index. In addition to the attributes, an IndexSchema refers to one or more ColumnSchemas on which it is defined.

  When using Transfer methods to retrieve schema information from the database, the attributes are set to the values returned from the java.sql.DatabaseMetaData.getIndexInfo() method, which are standardized, so that an application can rely on the attribute names and the type of data they refer to.

  Note that, contrary to the information returned by java.sql.DatabaseMetaData.getIndexInfo(), an IndexSchema instance holds the entire information for an index, meaning all columns, even for indexes defined on multiple columns.

  Index names are unique within the scope of a user schema.

• PrimaryKeySchema.class PrimaryKeySchema holds the schema information that describes the primary key of a table. In addition to the attributes, a PrimaryKeySchema refers to one or more ColumnSchemas on which it is defined.

  Just as with the other classes, when using Transfer methods to retrieve schema information from the database, the attributes are set to the values returned from the java.sql.DatabaseMetaData.getPrimaryKeys() method, which are standardized, so that an application can rely on the attribute names and the type of data they refer to.

  Note that, contrary to the information returned by java.sql.DatabaseMetaData.getPrimaryKeys(), a PrimaryKeySchema instance holds the entire information for a primary key, meaning all columns, even for primary keys defined on multiple columns.

  There can be at most one primary key for a table.

• TableSchema.class TableSchema holds the schema information describing a table. In addition to the attributes, a TableSchema refers to one or more ColumnSchemas, to a PrimaryKeySchema (if one is defined), and any number of IndexSchemas defined for it.

  As before, when using Transfer methods to retrieve schema information from the database, the attributes are set to the values returned from the java.sql.DatabaseMetaData.getTables() method, which are standardized, so that an application can rely on the attribute names and the type of data they refer to.

  Table names are unique within the scope of a user schema.

• UserSchema.class UserSchema holds the schema information describing a user schema. In addition to the attributes, a UserSchema can refer to any number of TableSchemas.

  Transfer offers methods to retrieve entire user schemas as well as specific table schemas.

You can generate structural information using these core structural classes in two ways:

• Manually by instantiating column, index, primary key, and table schema elements; attaching columns to indexes, primary keys, and tables; attaching indexes and primary keys to tables; and setting the attributes as required.
• Automatically by analyzing external data sources. Currently, generating schemas directly from a database is supported. This is the recommended approach because it is completely automated.

All these structural classes contain convenient methods for accessing the structural information and the attributes so that applications can easily access and use that information.

Admittedly, this set of classes represents a minimum set required to discuss and work with schemas. The java.sql.DatabaseMetaData interface offers more information on other database structures (such as foreign keys or stored procedures) that DBAccessor does not yet support with corresponding classes. Given the basic set of classes here, however, it should be fairly straightforward to add more functionality should the need arise.

Note that there is also a RowSchema class in the package. This class, however, is not directly backed by a corresponding method in the java.sql.DatabaseMetaData interface because the structure of rows is already completely specified in terms of the columns. RowSchema is a concept added in this package to support working with database data, rather than database schemas, and I describe it in detail in Part 3 of this article, below.

Using the Structural Classes

The recommended approach to work with schema elements is to import them directly from the database. The Transfer class offers several methods to do this:

• TableSchema importTableSchema(String tableName, DBUser dbu)
• TableSchema importTableSchema(String tableName, DBUser dbu,
                    boolean debug)
• UserSchema importUserSchema(String schemaName, DBUser dbu)
• UserSchema importUserSchema(String schemaName, DBUser dbu,
                    boolean debug)

The method signatures are pretty much self-explanatory. Note that here the connectivity classes -- and especially the DBUser class -- integrate nicely with the schema-related classes to supply the required database connectivity.

The Transfer class also offers an exporting facility. Currently, an export in XML format is supported. Part 4 of this article, below, provides more details on the various export/import capabilities of the DBAccessor package):

void exportUserSchema(UserSchema userSchema,
  java.io.Writer out, int mode)

Sample XML output could look like Code Sample 8.

Code Sample 8. Sample output from the facility to export in XML

<?xml version="1.0" encoding="ISO-8859-1"?>
<schema name="TEST">
  <table name="CONTEXTS" TABLE_TYPE="TABLE" TABLE_CAT="" 
    REMARKS="">
    <column name="CONTEXT" TABLE_CAT="" DECIMAL_DIGITS="0" 
      />
    <column name="CONTEXT_ID" TABLE_CAT="" DECIMAL_DIGITS="0" />
    <column name="DATE_MODIFIED" TABLE_CAT="" DECIMAL_DIGITS="0" />
    <index name="PRIMARY" FILTER_CONDITION="" />
      <column name="CONTEXT_ID" TABLE_CAT="" DECIMAL_DIGITS="0" />
    </index>
  </table>
  <table name="BASE_ID" TABLE_TYPE="TABLE" TABLE_CAT="" REMARKS="">
    <column name="CONTEXT_ID" TABLE_CAT="" DECIMAL_DIGITS="0" />
  </table>
  <table name="USERS" TABLE_TYPE="TABLE" TABLE_CAT="" REMARKS="">
    <column name="FIRST_NAME" TABLE_CAT="" DECIMAL_DIGITS="0" />
    <column name="INITIALS" TABLE_CAT="" DECIMAL_DIGITS="0" />
    <column name="DATE_MODIFIED" TABLE_CAT="" DECIMAL_DIGITS="0" />
    <index name="USERS_INDEX2" FILTER_CONDITION="" />
      <column name="LAST_NAME" TABLE_CAT="" DECIMAL_DIGITS="0" />
      <column name="FIRST_NAME" TABLE_CAT="" DECIMAL_DIGITS="0" />
    </index>
    <index name="USERS_INDEX1" FILTER_CONDITION="" />
      <column name="USERNAME" TABLE_CAT="" DECIMAL_DIGITS="0" />
    </index>
  </table>
</schema>

In Code Sample 8, I omitted some of the attributes as returned from java.sql.DatabaseMetaData methods for the sake of clarity.

Once you have retrieved the required schema information with one of the import methods, you can query it for any structural details.

Code Sample 9 shows a sample code snippet.

Code Sample 9. Retrieving a TableSchema from a database, along with the DATA_TYPE for each column

TableSchema  tableSchema  =  
  Transfer.importTableSchema("USERS", dbu);  // Table name =
    "USERS"
ColumnSchema columnSchema = null;

for (java.util.Iterator i = tableSchema.getColumnSchemas(); 
  i.hasNext();) {
  columnSchema = (ColumnSchema)i.next();
  System.out.println(columnSchema.getAttribute("DATA_TYPE"));
}

Code Sample 9 retrieves a TableSchema from the database. Then, within an iteration over all the columns defined for the table, the DATA_TYPE attribute (which identifies the java.sql.Types type for the column) is retrieved and printed for each of these columns. Similarly, you could retrieve the COLUMN_SIZE attribute (indicating the column length in the database) and use that to define the maximum length of an input field in an HTML form (to refer back to the use cases described in the introduction of this article).

With a similar coding, you could retrieve the primary key schema of a table and check within a loop over all key columns whether all required attributes for a data item are set before trying to store that data in the database.

The DBUser class additionally offers some convenience methods for frequently required functionality:

• boolean exists(String tableName)    Check whether a table exists
• int     getRowCount(String tableName)  Determine the number of rows
• boolean isEmpty(String tableName)    Check whether a table is empty

This section has shown how well the connectivity and the structural classes interact. Next I'll explain the functionality provided to work directly with database data.

Part 3: Working with Database Data

You can best see the real power of the structural classes when you start to work with actual database data. So let me explain how that aspect of the package works.

Direct Data Export

A fundamental feature of the DBAccessor package is an exporting capability for the entire contents of a table. The Transfer class offers a set of methods to that effect:

• void exportTableData(TableSchema tableSchema, java.io.Writer out,
                        DBUser dbu, int mode)
• void exportTableData(String tableName, java.io.Writer out,
                        DBUser dbu, int mode)

These two methods export the table contents through a Writer (typically to some file) using either an XML file or SQL INSERT statements.

Note that with this approach the data is not held in memory explicitly; it is merely accessed by the standard java.sql.ResultSet interface. Note also that the first exportTableData() method exports only those columns that are known as ColumnSchema objects within the TableSchema instance, whereas the second exportData() method exports all columns for this table (in this case, the column names are retrieved from the database directly).

A full table export could look like Code Sample 10.

Code Sample 10. A sample full-table export

DBUser         dbu = new DBUser(...);
BufferedWriter out = new BufferedWriter(new 
  FileWriter("accounts.xml"));

Transfer.exportTableData(tableName, out, dbu, 
  Constants.XML);

The code in Code Sample 10 exports the contents of the accounts table to an XML file named accounts.xml (again, using JDOM).

A word of caution is in order here: While these export methods provide a convenient and easy-to-use database-independent exporting mechanism, they would not satisfy enterprise-scale data-backup requirements because the size of the data increases significantly when stored in XML or SQL format. In addition, there is no support for columns holding binary data; such information cannot be directly represented. In those cases, it is definitely recommended to stick with the backup tools that come with the database you're using.

Working with Database Data in Memory

Admittedly, this is a difficult topic. After all, the JDBC API goes to great lengths to avoid using main memory to hold table data. Instead, the JDBC provides the java.sql.ResultSet interface -- which allows the client to iterate through the data -- but there is no facility to retrieve the entire contents of a table, for example, within a Java collection.

In many scenarios, though, using main memory to hold data makes good programming sense. This is what the RowSchema and RowData classes of the DBAccessor package are all about.

Let's start with RowSchema. A row schema describes the format of table rows, with the data represented by RowData instances. The row data is backed by a specific table, of course, and the row schema defines the subset of the columns in the table that the RowData actually represents (that is, not all columns of the table are necessarily used within the row schema).

An additional abstraction layer is added by the concept of attribute names, which act as aliases for column names. For example, an attribute name user is defined for a table column with the name UMC_USER. An application typically uses the attribute name rather than the column name, so that the column names can be changed, if necessary, without affecting the application.

To be used with RowSchema and RowData, a table must have a primary key defined because each RowData instance has to be uniquely identified by its settings for the primary key columns.

The RowSchema holds the following configuration information for RowData classes:

• The primary key schema for the table
• the table name (this is derived from the TABLE_NAME attribute of the primary key)
• Any number of ColumnSchema instances, identifying the columns to work on
• The mapping information between attribute names and column names

The primary key columns are always part of the set of columns (this is automatically enforced by the constructor for RowSchema), while any number of additional columns can be added with the addColumnSchema() method.

Take a look at the sample code in Code Sample 11.

Code Sample 11. Setting up a RowSchema instance

TableSchema tableSchema = Transfer.importTableSchema("USERS", 
  user);
String[]    columns     = { "name" };
RowSchema   rowSchema   = new RowSchema(columns,
                          tableSchema.getPrimaryKeySchema());

rowSchema.addColumnSchema("salary", 
  tableSchema.getColumnSchema("UMC_SALARY"));

First, the table schema is retrieved from the database (assume that the DBUser instance user has been initialized previously). Then the row schema is created, where the attribute name name is provided for the primary key column (assume that there is only one column in this example that can be retrieved from the primary key schema, and this column holds the user name). In the last step, another column (UMC_SALARY) is added to the row schema definition and also mapped to the attribute name salary.

Once a row schema is created, you can use it to create any number of RowData instances, so that the coding in the example above is required only once.

RowData is a very generic data holder object with built-in back-end database connectivity. The behavior of RowData is completely defined by a RowSchema instance, which is supplied in the constructor. Any data can be stored in and retrieved from a RowData instance with these methods:

public Object getAttribute(String attributeName) 
public void   setAttribute(String attributeName, 
       Object value)

Note that attribute names (as defined in the row schema) are used here to refer to data items -- not database column names.

The real power of RowData comes from the database connectivity methods that are built into it:

• boolean exists(DBUser dbu)
• void insert(DBUser dbu)
• void select(DBUser dbu)
• void update(DBUser dbu)
• void delete(DBUser dbu)

These five methods use the information in the row schema to assemble the SQL statements for exactly the required columns (accounting for the primary key) and to perform the actions implied by their names. Note that no additional coding is required once the row schema has been assembled for a specific RowData type.

Code Sample 12. Using the rowSchema and user variables from Code Sample 11 to insert a data row into the database

RowData rowData = new RowData(rowSchema);
rowData.setAttribute("name", "hugo");
rowData.setAttribute("salary",
new Integer(40000));
rowData.insert(user);

These four lines in Code Sample 12 are all you need to create a row data element, set the name and salary attributes, and store in the database! The other connectivity methods also work this intuitively, and you can witness again the power of the connectivity classes, when integrated with the other classes in the package.

It's interesting that RowData somewhat resembles an entity EJB -- without the enterprise APIs (transactions, security, and so on) -- but it's more generic because RowData has no hard-coded member variables; it is dynamically configurable. Plus, RowData requires no container to run in, but comes with the database connectivity that otherwise is typically reimplemented time and again for data objects requiring some form of persistence.

RowSchema can be extended by (or included in) other, application-specific data-holder objects. It's especially notable that you can use it to supply JavaBeans with database connectivity! See how in Code Sample 13.

Code Sample 13. Using RowSchema to supply JavaBeans with database connectivity

public class TestBean extends ml.jdbc.RowData implements 
java.io.Serializable {

  public void setName(String name) {
    setAttribute("name", name); 
  }
  public String getName() { 
    return (String)getAttribute("name");
  }

  public void setSalary(int salary) { 
    setAttribute("salary", new Integer(salary));
  }

  public int getSalary() { 
    return ((Integer)getAttribute("salary")).intValue();
  }
 
}

The simple JavaBean in Code Sample 13 would have two member variables (for which no actual private members need to be defined, however!), and you just have to map the usual get/set methods to the getAttribute/setAttribute methods of the underlying RowData instance. Be sure to take care that only object-valued attributes are used.

There is, of course, also the JavaBean requirement for a no-arg constructor, which seems to contradict the RowData constructor requiring a RowSchema instance (without a no-arg constructor, it would, for example, be impossible to use the JSP tag library for JavaBeans). However, a no-arg constructor is also available for RowData, since it is perfectly fine to get/set attributes without having the database connectivity defined. Thus, you can use RowData to construct JavaBeans as outlined in Code Sample 13, and you can also use the JSP tag library. The only requirement is that prior to any access to the database, you must define the RowSchema with the RowData.setRowSchema() method -- that's all! This you can easily accomplish; for example in JSP pages, just one scriptlet line would define the row schema to use. Then, just one more line of code would insert the bean data into the database:

bean.setRowSchema(rowSchema);
bean.insert(dbUser);

So far, life is good. I must mention one caveat, however: RowData interacts with the back-end database to store and retrieve any of its attributes. These are Java objects, and their Java type is unknown to the RowData instance (because they are stored in a java.util.HashMap and accessed by setAttribute() and getAttribute(); thus, they are of class java.lang.Object). The data is retrieved from the database with the java.sql.ResultSet interface's getXXX() methods; in order to use the correct method, RowData needs to know which Java object type to use for a particular data item. This information is expected to be contained in the ColumnSchema instances, which are part of the RowSchema for this RowData. Specifically, the DATA_TYPE attribute is used, which is standardized in the java.sql.DatabaseMetaData.getColumns() method. When using any of the database schema importing facilities provided with this package, this information is always retrieved for any ColumnSchema, so no further work is necessary at that end.

The DATA_TYPE attribute contains a short value with the type from java.sql.Types for this column. So you can retrieve any object from the java.sql.ResultSet in one of two ways:

• Use getObject() and then cast the object to the appropriate Java type
• Directly retrieve the data using the getXXX() method for the appropriate type as determined from the column schema.

According to the latest JDBC specification, there is a one-to-one mapping from the java.sql.Types type for a column to the corresponding Java object type, on which the RowData database access methods are based.

Not all databases and JDBC drivers fully support this specification yet, however. And at least for Oracle, there is another problem; on the database side, Oracle supports only one type for any kind of numeric data: NUMBER. INTEGER, FLOAT, DOUBLE, or any other data type used in a CREATE TABLE statement is mapped internally to NUMBER (with different levels of precision, of course). The column schema information, or, more specifically, the DATA_TYPE attribute for any such column, however, contains the value java.sql.Types.DECIMAL; it is not possible to determine the correct Java object type just from the column schema information returned by Oracle.

You might think that this is not really a problem because java.sql.Types.DECIMAL can hold all of the data that can be stored in an Oracle NUMBER, but this behavior is also not helpful. It is not possible to retrieve a column created with type INTEGER with java.sql.ResultSet.getInt(); that leads to a java.lang.ClassCastException because the JDBC driver returns a java.lang.BigDecimal instance (which is the correct Java object type for java.sql.Types.DECIMAL).

For now, the only way around this problem -- that avoids any database-specific coding -- is to adjust the DATA_TYPE column in the application when using databases that do not support the expected type mapping. While databases like MySQL do, others (like Oracle, at least with the Oracle Thin driver), don't. But the adjustment is quite simple:

columnSchema.setAttribute("DATA_TYPE", 
  String.valueOf(java.sql.Types.INTEGER)):

Keep in mind that the column schema information required to create a RowSchema instance need not be retrieved from the database to begin with! If you know in advance that one or more of the JDBC-driver-and-database combinations that an application must support does not fully support the required SQL-type-to-Java-object-type mappings, you can use an alternative approach: Just create the ColumnSchema instances manually and specify the DATA_TYPE attributes (and any other attributes the application requires), and then assemble the RowSchema from these. The manual approach requires a bit more programming effort, but it is guaranteed to be portable across databases without additional schema adjustments. Plus, with this limited overhead, you preserve the significant benefits of using RowData.

Part 4: Additional Functionality

In addition to the basic functionality that I've already described, DBAccessor comes with a small JSP tag library that lets you use DBAccessor capabilities from within JSP pages. This part of the article gives more details about the infrastructure provided to export and import schema information and table data.

JSP Tags

It's a straightforward process to use DBAccessor in JSP pages, because you can use the same coding outlined in the previous sections within scriptlets embedded in the web page. But because that practice is usually discouraged (from the perspective of decoupling web design and application logic), I have implemented a JSP taglib building on the DBAccessor classes.

Code Sample 14 displays the contents of a table ACCOUNTS, which can be accessed using the database connectivity information in the DBUser instance user.

Code Sample 14. Using DBAccessor tags within a JSP page to display table content

<%@ taglib uri="/WEB-INF/mltags.tld" prefix="mlt" %>

...

<table border=1 cellspacing=1 cellpadding=3>
  <mlt:DynamicTable user="<%=user%>" query="select * from
    ACCOUNTS">
    <tr>
      <mlt:ColNameIterator name="colName" type="String">
        <td bgcolor="blue"><b> <%=colName%> 
        </b></td>
      </mlt:ColNameIterator>
    </tr>
    <mlt:RowIterator>
      <tr>
        <mlt:ColIterator name="cellData" type="Object">
          <td> <%=(String)cellData%> </td>
        </mlt:ColIterator>
      </tr>
    </mlt:RowIterator>
  </mlt:DynamicTable>
</table>

Table 1 shows what the output might look like in a web page.

Table 1. Hypothetical output from Code Sample 14.

The following tags are used with Code Sample 14:

• DynamicTable This tag is the wrapper for the table output. It takes a DBUser type argument, as well as the SQL query string used to retrieve table data.
• ColNameIterator This subtag to DynamicTable iterates through the column names returned by the query.
• RowIterator This subtag to DynamicTable iterates through all the data rows returned by the query.
• ColIterator This subtag to RowIterator iterates through all the columns of the current row.

Another example, shown in Code Sample 15, checks whether a table exists in the database:

Code Sample 15. Using DBAccessor tags within a JSP page to check the existence of a table

<mlt:IfTableExists user="<%=user%>" table="USERS">
  <mlt:True>
    Table exists!
  </mlt:True>
  <mlt:False>
    Table does not exist!
  </mlt:False>
</mlt:IfTableExists>

In addition to the IfTableExists tag, the taglib also supports the IfTableEmpty tag with identical parameters and the RowCount tag, which returns the number of rows found in the table.

Using (and possibly extending) this JSP tag library, you can use the DBAccessor package directly within JSP pages -- without having to resort to Java scriptlets.

The Exporter/Importer Infrastructure

DBAccessor implements its actual exporting and importing capabilities through specific classes for each of the supported modes (XML, SQL, and database). A hierarchy of interfaces and abstract base classes has been established that provide a clear separation of functionality -- and that allow for the simple addition of new functionality.

Figure 3 gives an overview of all the current classes and interfaces:

Figure 3: Exporter/Importer infrastructure

The Exporter/Importer infrastructure includes four main trees:

• Importing schemas
• Exporting schemas
• Importing data
• Exporting data

Note that the import infrastructure is only one aspect of the actual capabilities: it is only possible to retrieve RowData instances from the database. The main functionality for working with database data is, of course, provided by the DBUser class, more specifically by the executeQuery() and executePreparedQuery() methods, which directly interface with the functionality provided by JDBC. After all, JDBC is primarily about working with database data, and this functionality can readily be accessed with executeXXX() methods.

Each of these tree structures starts with a marker interface, which enables applications to address each of the interfaces and classes further up the tree as one unique type. This is exploited in corresponding factory classes, which return instances of the marker interface for each of the supported modes. These modes are specified as constants in the Transfer class:

• Transfer.DB: data/schema as available in the database
• Transfer.SQL: data/schema expressed as SQL statements
• Transfer.XML: data/schema expressed in XML format

Not all of the modes are supported for each of these main trees. For example, currently the import of schemas is supported only directly from the database, not from XML or SQL files.

To perform the actual export/import, these classes are currently available:

• DBSchemaImporter
• XMLSchemaExporter
• DBDataImporter
• DBDataExporter
• SQLDataExporter
• XMLDataExporter

Table 2. DBAccessor classes that support exporting and importing schema information and data

The Transfer class holds static convenience methods that applications can invoke to use the capabilities of the classes.

Summary

The DBAccessor package has been used in several Java applications (standalone Java apps and JSP/servlet-based web applications) to conveniently access back-end databases. It offers more flexibility with respect to host and port configurations than javax.sql.DataSource instances, which need to be configured on the J2EE container side before being used. DBAccessor is very flexible, and avoids some of the hassle usually associated with JDBC accesses (driver class names and instantiation, URL formatting, connection management, and so on). In addition, it nicely integrates J2SE as well as J2EE applications in a unified manner.

The classes offered for schema and data management make DBAccessor a very powerful tool that relieves application programmers from tedious coding tasks typically required for database interaction. The classes also facilitate the development of more generalized applications, because schema information can be retrieved from the live database; developers don't need to hard code schema information into applications. The RowData class offers a simple base class with integrated database connectivity, which can be extended or included in derived classes -- for example, JavaBeans -- to supply them with database connectivity without requiring additional coding.

Note that while DBAccessor can also make working with multiple databases and multiple database users quite convenient, the limitations imposed by the underlying platform in a J2EE context still apply; for example, J2EE 1.3 does not require support for the two-phase commit protocol for accesses to multiple databases within the same transaction.

Note that the DBAccessor package already contains JDOM and Xerces, which you need for XML parsing. In addition, the package contains the tag library.

I'd be interested to hear from you! If you find the package useful, if you are using the package in a project, if you have questions or find a bug, or if you would like to see functionality added to the package, just let me know at matthias.laux@sun.com.

For More Information

• DBAccessor download
• The article "Clever Facade makes JDBC look easy" by Thomas E. Davis
• JSR 102 (JDOM)
• JDBC specifications
• JDBC API Tutorial and Reference, Second Edition, by Seth White, Maydene Fisher, Rick Cattell, Graham Hamilton and Mark Hapner
• MySQL

About the Author

Dr. Matthias Laux is a systems engineer working in the Global SAP-Sun Competence Center in Walldorf, Germany. His main interests are Java and J2EE technology and programming; XML technology; databases; and SAP benchmarking. Although he also has a background in aerospace engineering and HPC / parallel programming, today his languages of choice are Java and Perl. You can reach him at matthias.laux@sun.com.

Have a question about programming? Use Java Online Support.