ONJava.com -- The Independent Source for Enterprise Java
oreilly.comSafari Books Online.Conferences.


AddThis Social Bookmark Button

Using Castor JDO for SQL Mapping
Pages: 1, 2, 3, 4, 5, 6, 7

Storing and Retrieving Objects From the Database

The storing of class instances in the relational database is straightforward, thanks to the JDO mapping file and Castor's support of Object Query Language (OQL). OQL is a query language developed by the Object Database Management Group (ODMG), a consortium of object database vendors and users. OQL is a lot like SQL with objects thrown in.

Unlike SQL, where a myriad of statements must be used to store even a simple Java object (and requiring an inate understanding by the programmer as to how each object is mapped to the various database tables), OQL can store entire hierarchies of objects in a single statement.

Opening the Database Connection

Castor relies primarily on connection information in the database.xml file. To make the connection, first a JDO instance is created using a default constructor. The JDO is then configured with the database name to open and provide the location of the database.xml file. A database.xml file may contain connection information for more than one database, hence the need to specify a database name.

Optionally, database log information can be sent to an output stream of your choosing. The log information is useful for debugging, since it records all SQL commands and return statuses that Castor sends to and receives from the database during transaction commits.

package com.example.shipping; 

import org.exolab.castor.jdo.*; 

import java.io.PrintWriter; 
import java.io.ByteArrayOutputStream; 

class PersistenceFactory
   private static String _databaseName = "shippingDB"; 
   private static String _databaseConfiguration = "d:/shipping/database.xml";

   public PersistenceFactory() throws PersistenceException 
      _jdoLogWriter = new PrintWriter(new ByteArrayOutputStream(400));

   private void open() throws PersistenceException 
     JDO jdo; // Define the JDO object 
     jdo = new JDO(); 
     jdo.setConfiguration(_databaseConfiguration);  // Obtain a new database 
     _db = jdo.getDatabase();

   // other persistence methods... 

The core of the connection procedure is highlighted above. Once the connection is made, all subsequent database transaction go through Castor's Database class instance.

Storing Objects in the Database

As mentioned earlier, Castor uses OQL to store and retrieve data from the database. OQL is an object-oriented extension of SQL, so it is possible to use SQL commands, although for object manipulation, it's not the preferred choice.

Before doing anything with the database, you must first start a transaction using the begin() method of org.exolab.castor.jdo.Database. Changes are not made to the database until the transaction is committed using the commit() method. Both methods' calls are highlighted below:

public void store(Object obj, boolean create)
throws PersistStoreException
  try {
     if (create)
  catch (ClassNotPersistenceCapableException ex) 
    { throw new PersistStoreException(ex.toString()); } 
  catch (DuplicateIdentityException ex) 
    { throw new PersistStoreException(ex.toString()); } 
  catch (TransactionNotInProgressException ex) 
    { throw new PersistStoreException(ex.toString()); } 
  catch (TransactionAbortedException ex) 
    { throw new PersistStoreException(ex.toString()); } 
  catch (PersistenceException ex) 
    { throw new PersistStoreException(ex.toString()); } 

Once a transaction is started, you may save a new object in the database or, if changes were made to an object previously fetched from the database (as it could have been from a prior transaction), you may "update" it. If the Database create() method is called, then an object with the same identifier must not already exist in the database, or an exception will occur. Updates, on the other hand, require that the object be in the database, inasmuch as it has the same identifier.

Dependent objects are not created or updated directly, as you may recall. What counts is whether the master object exists previously in the database or not.

In the code above, I've elected to collapse all of the possible exceptions that Castor can throw during the transaction into one exception that I've defined. I'm assuming that any exception that gets thrown here is essentially fatal, but another (more clever?) person might be able to program a recovery under certain circumstances. Also, while having the transaction begin/commit calls inside of this one method simplifies the storage and update of single objects, you would normally want to commit multiple objects at one time in a single transaction for better efficiency. In that case, you'd begin your transaction before the first object is stored and commit after the last object is stored.

Retrieving the ShippingLine Objects

OQL queries for fetching object data look very much like SQL queries, except that you don't get a row cursor returned (where each row then has to be manhandled into a class instance); instead, you receive a cursor to a list of fully-formed objects. Very nice.

public Object fetch(String objName, String id) 
   throws PersistFetchException 
   Object object = null;  
   try { 
     Query oql; 
     QueryResults results;  
     oql = _db.getOQLQuery(  
         "SELECT o" + 
         " FROM com.example.shipping." + objName + " o" +
         " WHERE o.id=\"" + id + "\"" );
     results = oql.execute();
     while (results.hasMore() ) 
        object = results.next();

  catch ( TransactionAbortedException ex )
    { throw new PersistFetchException( ex.toString() ); } 
  catch ( TransactionNotInProgressException ex ) 
    { throw new PersistFetchException( ex.toString() ); } 
  catch ( PersistenceException ex ) 
    { throw new PersistFetchException( ex.toString() ); }

  return object;  

To fetch an object from the database, we first construct an OQL query using the getOQLQuery() method of org.exolab.castor.oql.Database. The query in this case might be:

FROM com.example.shipping.Ship o
WHERE o.id="Waterdown"

Once the query object is constructed, it can be executed. The results are returned in a QueryResults object. The QueryResults object functions as an array of Object instances. I'm assuming in this code that I have only one result (bad programmer am I). However, when I originally wrote this example, I called the undocumented results.size() method and checked for a value equal to 1. I was unpleasantly surprised to find that size() always returned 0! In this case, it's a safe assumption that I'm only going to get one object per identifier, but it's not very defensive coding.

The returned object(s) can be cast to the expected class type by the caller.


Castor JDO provides a lot of capability for simplifying the storage of Java data objects in relational tables. The use of a mapping file to direct the Castor JDO engine on how to store object member data to table rows and columns really reduces the amount of grunge code involved in query/update procedures. Mapping files maintain information about object dependencies and primary/foreign keys, also; the result is that you don't have to futz with such dependencies in either the application code or database schema.

There are many additional features of Castor JDO that I was not able to cover in this article. A lot of these are not yet well-documented; however, there's usually some example code that you can download from the Castor Web site, either as a source repository snapshot (taken daily and for each release build) or by using WebCVS.

As of this writing, Castor sits at pre-release version A call for contributors recently went out on the the Castor mailing list, so if storing objects in databases is your kind of gig, you might check out the possibilities available in getting Castor 1.0 out the door. Hey, it just might save you a lot of work!

Jeff Lowery is a JDO expert and advocate and is experienced at using Exolab's Castor, Jakarta Ant, and more.

Return to ONJava.com.