jRelationalFramework tutorial copyright © 2000 is.com

This tutorial starts simple and gets more complex to give a more in-depth view of what can be done with this framework.  This uses a  relatively simple video store database example.  The working source code for this tutorial can be found in the examples directory of the distribution zip file.

1. Basic example

Customer table columns used in this example:
    Customer.Id
    Customer.Name
    Customer.LastUpdated

In general, it is easiest if there is a one-to-one relationship between persistent object classes and tables.  Let's say we have a Customer table already built and we want to create a Customer class whose instances will be equivalent to a row in the Customer table.  We would do the following:

1. Create a Customer class that subclasses com.is.jrf.PersistentObject.
• Create an instance variable for each column in the table.
• Create a getter and a setter for each persistent attribute.
• Inside each persistent attribute setter, call this.markModifiedPersistentState().
• Example:

  import java.sql.Timestamp;
  public class Customer
      extends PersistentObject
    {
    private Integer   i_id   = null;
    private String    i_name = null;
    private Timestamp i_lastUpdated = null;

    public Integer getId()
      {
      return i_id;
      }
    public void setId(Integer id)
      {
      i_id = id;
      this.markModifiedPersistentState();
      }

    public String getName()
      {
      return i_name;
      }
    public void setName(String name)
      {
      i_name = name;
      this.markModifiedPersistentState();
      }

    public Timestamp getLastUpdated()
      {
      return i_lastupdated;
      }
    public void setLastUpdated(Timestamp ts)
      {
      i_lastUpdated = ts;
      this.markModifiedPersistentState();
      }
    } // Customer

2. Create a CustomerDomain class that subclasses com.is.jrf.AbstractDomain.
• Create a setup() method like below.  Note that the JDBCHelperFactory class is not part of the framework.  Each persistent attribute needs an instance of ColumnSpec to map the database column to the object attribute.  Notice that there is a different ColumnSpec implementation for each type of column.  i.e. Integer, String, Timestamp.  There are other types and more can be easily added when needed.

    protected void setup()
    {
    this.setJDBCHelper(JDBCHelperFactory.create());
    this.setDatabasePolicy(new OracleDatabasePolicy());
    this.setTableName("Customer");

    this.addColumnSpec(
        new StringColumnSpec(
            "Id",                    // Column name
            "getId",                 // Getter name
            "setId",                 // Setter name
            DEFAULT_TO_NULL,         // Default value
            SEQUENCED_PRIMARY_KEY)); // Type of pk
    this.addColumnSpec(
        new StringColumnSpec(
            "Name",            // Column name
            "getName",         // Getter name
            "setName",         // Setter name
            DEFAULT_TO_NULL,   // Default value
            REQUIRED,          // Validation option
            UNIQUE));          // Validation option
    this.addColumnSpec(
        new TimestampColumnSpec(
            "LastUpdated",     // Column name
            "getLastUpdated",  // Getter name
            "setLastUpdated",  // Setter name
            DEFAULT_TO_NOW));  // Default to db time
    }

• Create a newPersistentObject() method that looks like this:

  protected PersistentObject newPersistentObject()
    {
    return new Customer();
    }
• Create a preSave() method to keep the LastUpdated column updated.

  protected void preSave(PersistentObject aPO,
                         JDBCHelper aJDBCHelper)
    {
    Customer customer = (Customer) aPO;
    customer.setLastUpdated(CURRENT_TIMESTAMP);
    // CURRENT_TIMESTAMP is a Timestamp with a special meaning.
    // The db-specific Date/Time function is used to set this
    // column in the database.
    }
• Now let's create a specialized query so we can find a customer by name:
       public List findForName(String s)
    {
    return this.findWhere(
          this.getTableAlias() + ".Name = '" + s + "'");
    }

Assuming this compiles and the table and column metadata are defined correctly, we can now do some pretty cool things like below:

• Save a new customer in the database:

    Customer customer = new Customer();
    customer.setName("Craig Laurent");
    Customer savedCustomer = (Customer)
        new CustomerDomain().save(customer);
    Integer craigId = savedCustomer.getId();
• Find a customer using the primary key:

    Customer foundCustomer = (Customer)
        new CustomerDomain().find(new Integer(1));
or by populating the primary key of a customer:
    Customer customer = new Customer();
    customer.setId(new Integer(1));
    Customer foundCustomer = (Customer)
        new CustomerDomain().find(customer);
• Update his/her name in the database (building on the previous example):

    foundCustomer.setName("Jim Slim");
    new CustomerDomain().save(foundCustomer);
• Find a customer or customers by name using the specialized query we wrote:

    List customers =
        new CustomerDomain().findForName("Craig Laurent");
• Find all customers in the table:

    List customers =
        new CustomerDomain().findAll();
• Delete a customer from the database table:

    new CustomerDomain().delete(foundCustomer);

2.  Let's get more complicated

Customer table columns used in this example:
    Customer.Id
    Customer.Name
CustomerPhone table columns used in this example:
    CustomerPhone.CustomerId
    CustomerPhone.Type
    CustomerPhone.PhoneNumber

Joins

Generally it is better to create complex objects rather than join many tables into one object.  But if it is a simple join and/or efficiency demands it, don't hesitate to use this feature.  For this example, let's say we decide that we need to include the customer's home phone number from the already existing CustomerPhone table.  This table has a compound primary key of CustomerId and Type (Type can be 'Home','Work', etc.).  Open up our CustomerDomain class and add some JoinTable instances to the setup() method like this:

  public void setup()
    {
    // ....
    JoinTable joinTable =
            new OuterJoinTable(
                "CustomerPhone", // table name
                "Id",            // customer (main) table column(s)
                                 // separated by commas if more than one
                "CustomerId,Type='Home'"); // customerphone (join) table
                                           // column(s) separated by commas
    joinTable.addJoinColumn(
        new StringJoinColumn(
            "PhoneNumber",       // Column Name
            "HomePhone",         // Alias in case of name conflict
            "setHomePhone"));    // Setter method name
    this.addJoinTable(joinTable);
    }

Notice that since we want Customer rows even where there is no corresponding CustomerPhone row, we use an OuterJoinTable.  Generally, the JoinTable constructor should have the same number of main table (Customer) columns as join table columns.  However, In this case we only want the Type='Home' rows from the CustomerPhone (join) table so we have a columnname=value pair (Type='Home') for the join table column and we leave out the name of the main table column.

Complex Objects

One reason to avoid joined columns is that they cannot be updated like columns in the main table.  To update or insert CustomerPhone table rows it would be necessary to build a CustomerPhone persistent object and a CustomerPhoneDomain class and update the table that way.  A vector of the CustomerPhone persistent objects in the Customer object is more flexible (but not necessarily more efficient) than joining a column directly into the Customer object.

To place a vector of CustomerPhone objects inside each Customer do this (This assumes you have already created a CustomerPhoneDomain class like the CustomerDomain class):

1. Implement a custom query in CustomerPhoneDomain called findForCustomer():

    public List findForCustomer(Customer c)
 {
 return this.findWhere(
       this.getTableAlias() + ".CustomerId = " + c.getId());
 }
2. Add a phoneNumbers List field with getter and setter to the Customer persistent object.
3. In CustomerDomain, override the postFind()method like this:
         protected void postFind(PersistentObject aPO, JDBCHelper aJDBCHelper)
           {
           Customer customer = (Customer) aPO;
           List phones =
               new CustomerPhoneDomain().findForCustomer(customer);
           customer.setPhoneNumbers(phones);
           }
Once this compiles cleanly, every Customer object returned from the find methods in CustomerDomain will have a vector (possibly empty) of CustomerPhone instances.  Considering that this executes N+1 SQL statements when finding N customers, this is not the most efficient way of doing this, but it is the simplest way.

If performance is important, it is necessary to override each of the find methods in CustomerDomain with one call to CustomerPhoneDomain() to fetch all desired CustomerPhone persistent objects and match them with the appropriate Customer objects.  That would look something like this for the Customer#findAll() method:

    public List findAll()
      {
      // This is more efficient than using the postFind() method
      // but it requires putting similar code in all find methods.
      List customers = super.findAll();
      List customerPhones = new CustomerPhoneDomain().findAll();
      //
      // put customer to customerPhone match logic here...
      //
      }

When a Customer instance is deleted, it is important to be able to delete the CustomerPhone instances at the same time.  The preDelete() and postDelete() methods are used for this purpose.  Here is one way to delete the CustomerPhone objects before deleting the Customer object.  This preDelete() method goes into the CustomerDomain class.  Assume the CustomerPhoneDomain#deleteFor(...) method is already written.

    protected void preDelete(PersistentObject aPO, JDBCHelper aJDBCHelper)
      {
      Customer customer = (Customer) aPO;
      // Use the passed-in JDBCHelper so it is part of the same transaction
      new CustomerPhoneDomain().deleteFor(customer, aJDBCHelper);
      }

The methods  preSave(aPersistentObject, aJDBCHelper) and postSave(aPersistentObject, aJDBCHelper)  can be used to do similar things when saving.
 

Sequenced Primary Keys

We used a SEQUENCED_PRIMARY_KEY in the Customer example.  This is an integer primary key column that is arbitrarily assigned by the framework.  There must be one and only one primary key ColumnSpec (sequenced or otherwise) defined for each domain.  The database-specific SQL for this is defined in the implementation of the DatabasePolicy.sequenceSQL(tableNameString) method.

Compound Primary Keys

Let's examine what the column specs would look like for a table with compound primary keys like  the CustomerPhone table.   This would need to be done anyway since we will most certainly need to add and update rows in the CustomerPhone table.  The columns of compound primary keys are by default natural keys (They are not automatically generated).

    this.addColumnSpec(
        new CompoundPrimaryKeyColumnSpec(
            new IntegerColumnSpec(
                "CustomerId",
                "getCustomerId",
                "setCustomerId",
                DEFAULT_TO_NULL),
            new StringColumnSpec(
                "Type",
                "getType",
                "setType",
                DEFAULT_TO_NULL)));

Notice how the primary key columns are wrapped in a CompoundPrimaryKeyColumnSpec instance.

Natural Primary Keys

Some tables have a natural primary keys.  Natural primary keys are not generated by the system and must be set by the application.  Here is an example of a ColumnSpec for a natural primary key:

    new StringColumnSpec(
        "PartCode",
        "getPartCode",
        "setPartCode",
        DEFAULT_TO_NULL,
        NATURAL_PRIMARY_KEY));
 
 

3. Let's get really complicated

Customer table columns used in this example:
    Customer.Id
    Customer.Name

Rental table columns used in this example:
    Rental.CustomerId
    Rental.VideoId

Video table columns used in this example:
    Video.Id
    Video.Title

In this section we'll look at how to create your own, custom find SQL if you've gone beyond the easy capabilities of the framework.   We'll implement an associative (many-to-many) relationship between the Customer and Video tables.  The Rental table tells us who has which videos checked out.  The most direct and simple way would be to have Customer, Video, and Rental persistent objects and domains that would map directly to the underlying tables.  This would involve no joins and harmony would exist between the object and relational world.  This would be done similar to the way we put a vector of CustomerPhone objects into each Customer instance up in the "Complex Objects" section.

Unfortunately, in this case, this involves making the object model look like the relational model, which can make the object model feel clunky and is inefficient to boot.  So let's explore some more complex joins using custom SQL.

The goal is to have a Customer persistent object have a vector of Video objects and a Video object with a vector of Customer objects.  (Be warned that this could cause an infinite loop, hence the NO_POST_FIND option below):

• Create a subclass of PersistentObject named Video with the appropriate getters and setters.
• Create a subclass of AbstractDomain named VideoDomain.
• In VideoDomain, create findForCustomer(Customer c) with a custom join like this:

    public List findForCustomer (Customer c)
      {
      SelectSQLBuilder builder = this.getSelectSQLBuilder();
      builder.addJoinTable( // Add a temporary join table
          new JoinTable(
              "Rental R",   // Table name with alias
              "Id",         // Video column(s)
              "VideoId"));  // Rental column(s)
      builder.setWhere("R.CustomerId = " + c.getId());
      return this.find(builder);
      }
• In CustomerDomain, create findForVideo(Video video) with a custom join like this:

    public List findForVideo (Video video)
      {
      SelectSQLBuilder builder = this.getSelectSQLBuilder();
      // Add a temporary join table
      builder.addJoinTable(
          new JoinTable(
              "Rental R",     // Table name with alias
              "Id",           // Customer column(s)
              "CustomerId")); // Rental column(s)
      builder.setWhere("R.VideoId = " + video.getId());
      return this.find(builder);
      }
• In CustomerDomain override the postFind() method like this:

    protected void postFind(PersistentObject aPO, JDBCHelper aJDBCHelper)
      {
      Customer customer = (Customer) aPO;
      List videos =
          new VideoDomain(NO_POST_FIND).findForCustomer(customer);
      customer.setVideos(videos);
      }
The NO_POST_FIND option keeps us from going into an infinite loop when the VideoDomain.postFind() method asks the CustomerDomain objects for it's Customers which would ask for it's Videos, and on and on and on.
• Similarly, In VideoDomain override the postFind() method like this:

    protected void postFind(PersistentObject aPO, JDBCHelper aJDBCHelper)
      {
      Video video = (Video) aPO;
      List customers =
          new CustomerDomain(NO_POST_FIND).findForVideo(video);
      video.setCustomers(customers);
      }

is.com