DODS FAQs - Using Generated Code
dods.gif (11357 bytes)This tutorial describes working with the SQL and classes that DODS generates. It does not talk about using the GUI, so it will remain accurate no matter what the new GUI looks like.

Describe Your Database Tables
As an example, let's say your application needs to store and retrieve information about cars, car dealers, and car owners.

Using DODS, you describe the database tables your app will need. Let's say your tables look like this:

Table name Column name Data type Column description
Dealer Name String Name of car dealership
Owner Name String Name of car owner
  Age int Age of car owner
Car LicensePlate String License plate # of car

Files DODS Generates
For these tables, DODS will generate the following SQL files and Java classes:

File generated Purpose
DealerSQL.sql The CREATE TABLE statement for the Dealer table.
DealerDO.java Represents a new/existing row in the Dealer table.
DealerQuery.java Retrieves DealerDO objects for rows in the Dealer table.
OwnerSQL.sql The CREATE TABLE statement for the Owner table.
OwnerDO.java Represents a new/existing row in the Owner table.
OwnerQuery.java Retrieves DealerDO objects for rows in the Owner table.
CarSQL.sql The CREATE TABLE statement for the Car table.
CarDO.java Represents a new/existing row in the Car table.
CarQuery.java Retrieves DealerDO objects for rows in the Car table.

Assume that Dealers only sell new cars.
That is, a dealer sells many cars, but only sells a given car once.
To represent this one-to-many relationship,
you would add a reference to the Car table:

Table name Column name Java type DB Type Column description
Car LicensePlate String CHAR License plate # of car
  Dealer DealerDO REFERENCE foreign key of Dealer

People can own multiple (0 to many) cars at a time.
People can also sell cars to each other,
so over time a given car can have multiple (1 to many) owners.
To represent this many-to-many relationship between cars and owners,
you would create a new table:

Table name Column name Java type DB Type Column description
CarOwner Car CarDO REFERENCE foreign key of Car
  Owner OwnerDO REFERENCE foreign key of Owner
  IsCurrent boolean BIT true if current owner

(A table that represents a many-to-many relationship is sometimes called an 'association table.')

DODS would generate these files for this new table:

File generated Purpose
CarOwnerSQL.sql CREATE TABLE statement for CarOwner table
CarOwnerDO.java An instance of the many-to-many relationship
CarOwnerQuery.java Retrieves instances of the many-to-many relationship

Creating the Tables
To use the Java classes generated by DODS, you first use the .sql files to create the tables. To make this easier, DODS creates the file

File generated Purpose
create_tables.sql CREATE TABLE statements for all tables, proper sequence

which is a concatenation of the files

  • DealerSQL.sql
  • OwnerSQL.sql
  • CarSQL.sql
  • CarOwnerSQL.sql

You would pipe (or cut-and-paste) the contents of create_tables.sql into the interactive SQL tool provided by your database vendor.


Using the DO Classes to Create Data
After the tables are created in the database you could begin using the Java classes generated by DODS.

This code snippet creates a dealer, a car, an owner, and establishes relationships between them.

    // create a dealer
    DealerDO autoWorld = DealerDO.createVirgin();
    autoWorld.setName( "Bob's Auto World" );

    // create a car
    CarDO lemon = CarDO.createVirgin();
    lemon.setLicensePlate( "ABC123" );
    lemon.setDealer( autoWorld );

    // create an owner
    OwnerDO joe = OwnerDO.createVirgin();
    joe.setName( "Joe Lanechange" );
    joe.setAge( 16 );

    // joe is the current owner of the car
    CarOwnerDO currentOwner = CarOwnerDO.createVirgin();
    currentOwner.setCar( lemon );
    currentOwner.setOwner( joe );
    currentOwner.setIsCurrent( true );
    currentOwner.save();

The call to

    currentOwner.save();

does several things:

  1. writes (saves) the OwnerDO (joe) to the Owner table in the database,
  2. writes (saves) the CarDO (lemon) to the Car table,
  3. writes the CarOwnerDO object (currentOwner) into the CarOwner table.

Because CarOwnerDO (currentOwner) holds references to other DO objects (CarDO & OwnerDO), it must write (save) them to the database first, before it can save itself.

Note that because CarDO (lemon) holds a reference to DealerDO (autoWorld),
when currentOwner.save() calls lemon.save(),
lemon.save() in turn calls autoWorld.save().
So, the dealer gets saved, too.

You could also call

    autoWorld.save();
    lemon.save();
    joe.save();
    currentOwner.save();

explicitly. The DO objects remember whether they are in sync with the database,
and their save() methods do nothing if they are already saved.
So, if you saved all the DOs explicitly, then the call to currentOwner.save(); would only perform step #3 where it saves itself to the CarOwner table.


Using the Query Classes to Retrieve Data
Now that there is data in the tables, you can use the Query classes to retrieve the objects. This code snippet finds the DealerDO created above:

    DealerQuery dealerQuery = new DealerQuery();
    dealerQuery.setQueryName( "Bob's Auto World" );
    DealerDO autoWorld = dealerQuery.getNextDO();

This code snippet returns the Cars sold by that Dealer:

    CarDO[] cars = autoWorld.getCarDOArray();

Note that the method DealerDO.getCarDOArray() made it easy.
This snippet would do the same thing:

    CarQuery carQuery = new CarQuery();
    carQuery.setQueryDealer( autoWorld );
    CarDO[] cars = carQuery.getDOArray();

You can also find the lemon by its license plate (similar to the DealerQuery snippet above):

    CarQuery carQuery = new CarQuery();
    carQuery.setQueryLicensePlate( "ABC123" );
    carQuery.requireUniqueInstance();  // plate numbers are unique
    CarDO lemon = carQuery.getNextDO();

DODS generates some convenience methods, based on inter-table relationships.
To retrieve all CarOwner rows, representing all past and current owners of the lemon:

    CarOwnerDO[] lemonOwners = lemon.getOwnerDOArray_via_CarOwner();

To find the current owner of the lemon:

    CarOwnerQuery carOwnerQuery = new CarOwnerQuery();
    carOwnerQuery.setQueryCar( lemon );
    carOwnerQuery.setQueryIsCurrentOwner( true );
    carOwnerQuery.requireUniqueInstance(); // each car has only 1 current owner
    CarOwnerDO lemonCarOwner = carOwnerQuery.getNextDO();
    OwnerDO lemonOwner = lemonCarOwner.getOwner();
    System.err.println( "The current owner is " + lemonOwner.getName() );

Using the DO Classes to Delete Data
To remove Joe from the database:

    OwnerQuery ownerQuery = new OwnerQuery();
    ownerQuery.setQueryName( "Joe Lanechange" );
    OwnerDO joe = ownerQuery.getNextDO();
    joe.delete();

The row for Joe is removed from the Owner table.

Note that since you created a row in the CarOwner table that referenced the row for Joe in the Owner table, that row in the CarOwner table must also be deleted. Otherwise the database will have lost referential integrity: the CarOwner table would have a reference to an Owner row that no longer exists.

Some database vendors (e.g. Oracle) have a feature called "cascading delete".
The database detects a dangling reference like the one in the CarOwner table,
and automatically deletes that row as well. When using DODS to describe your tables, you specify the database vendor.
If that vendor does not have the "cascading delete" feature,
DODS will generate extra code in the DO.delete() method to perform the cascading delete to preserve referential integrity in the database.


Using Transactions

To change the owner of a car, we must update two rows in the CarOwner table.
Step #1:  The entry for the previous owner
          must have the IsCurrent flag set to false.
Step #2:  The entry for the new owner will need a new row in the CarOwner table,
          with the IsCurrent flag set to true.
Both operations must succeed for the database to correctly represent the sale.
If #1 succeeds but #2 fails, the car will have no current owner.
If #1 fails but #2 succeeds, the car will have two current owners.
So if either #1 or #2 fails, the database should not be changed.

Transactions guarantee that unless all changes to tables succeed together, no changes are made.

For Joe to sell the Lemon to Ralph:

    // find the CarOwner row establishing the current owner of the Lemon
    CarOwnerQuery carOwnerQuery = new CarOwnerQuery();
    carOwnerQuery.setQueryCar( lemon );
    carOwnerQuery.setQueryIsCurrentOwner( true );
    carOwnerQuery.requireUniqueInstance(); // each car has only 1 current owner
    CarOwnerDO currentCarOwner = carOwnerQuery.getNextDO();
    OwnerDO currentOwner = currentCarOwner.getOwner();
    //
    if ( currentOwner.getName().equals( "Joe Lanechange" ) )
        System.err.println( "Yep, Joe owns the lemon" );

    // find Ralph, the intended new owner
    OwnerQuery ownerQuery = new OwnerQuery();
    ownerQuery.setQueryName( "Ralph Roadrage" );
    OwnerDO ralph = ownerQuery.getNextDO();

    // Perform step #1: Joe is no longer the current owner of the Lemon.
    currentCarOwner.setIsCurrent( false );

    // Perform step #2: Ralph is the new owner of the Lemon.
    CarOwnerDO newCarOwner = CarOwnerDO.createVirgin();
    newCarOwner.setCar( lemon );
    newCarOwner.setOwner( ralph );
    newCarOwner.setIsCurrent( true );

    // Create a Transaction to ensure "all or nothing" changes to the database.
    DBTransaction trans = CarOwnerDO.createTransaction();
    currentCarOwner.save( trans );
    newCarOwner.save( trans );
    try {
        trans.commit();
    } catch ( Exception e ) {
        // something failed, undo all changes

        trans.rollback();
    }

Line save(), the delete() method also accepts an optional DBTransaction object.

To delete all historical information about previous CarOwners, keeping only the information about current CarOwners:

    CarOwnerQuery carOwnerQuery = new CarOwnerQuery();
    carOwnerQuery.setQueryIsCurrentOwner( false );
    CarOwnerDO[] nonCurrentCarOwners = carOwnerQuery.getDOArray();
    DBTransaction trans = CarOwnerDO.createTransaction();
    // add all the delete operations to the transaction
    for ( int i = 0; i < nonCurrentCarOwners.length; i++ ) {
        nonCurrentCarOwners[i].delete( trans );
    }
    try {
        // either all the historical data is successfully removed...
        trans.commit();
    } catch ( Exception e ) {
        // ...or something failed, and all changes undone.
        trans.rollback();
    }

Using Comparison Operators
It's easy to find Owners who are exactly 16 years old:

    OwnerQuery ownerQuery = new OwnerQuery();
    ownerQuery.setQueryAge( 16 );
    OwnerDO[] troubleMakers = ownerQuery.getDOArray();

To find Owners who are younger than 21:

    OwnerQuery ownerQuery = new OwnerQuery();
    ownerQuery.setQueryAge( 21, QueryBuilder.LESS_THAN );
    /*
     * This produces the SQL query:
     *
     *      SELECT Owner.* FROM Owner
     *      WHERE  Owner.Age < 21
     */
    OwnerDO[] youngDrivers = ownerQuery.getDOArray();

Every Query class uses a QueryBuilder object. Calls like

    ownerQuery.setQueryAge( 21, QueryBuilder.LESS_THAN );

utilize QueryBuilder comparison operators. Here's the full list of QueryBuilder comparison operators:

    EQUAL
    NOT_EQUAL
    LESS_THAN
    LESS_THAN_OR_EQUAL
    GREATER_THAN
    GREATER_THAN_OR_EQUAL
    IS_NULL
    IS_NOT_NULL
    // These are only for use with strings:
    CASE_INSENSITIVE_EQUAL
    CASE_SENSITIVE_CONTAINS
    CASE_INSENSITIVE_CONTAINS
    CASE_SENSITIVE_STARTS_WITH
    CASE_INSENSITIVE_STARTS_WITH    // example usage below
    CASE_SENSITIVE_ENDS_WITH
    CASE_INSENSITIVE_ENDS_WITH

Using QueryBuilder for Advanced Queries
You can access the QueryBuilder object used by your Query to add SQL "WHERE" clauses to perform more sophisticated searches.

To find Owners who are 16 years old or older, and who have bought cars that were sold by a dealer whose name starts with "Bob":

    OwnerQuery ownerQuery = new OwnerQuery();
    ownerQuery.setQueryAge( 16, QueryBuilder.GREATER_THAN_OR_EQUAL );
    QueryBuilder qb = ownerQuery.getQueryBuilder();
    qb.addWhere( OwnerDO.PrimaryKey, CarOwnerDO.Owner );
    qb.addWhere( CarOwnerDO.Car,     CarDO.PrimaryKey );
    qb.addWhere( CarDO.Dealer,       DealerDO.PrimaryKey );
    qb.addWhere( DealerDO.Name,      "BOB", 
                                QueryBuilder.CASE_INSENSITIVE_STARTS_WITH );
    OwnerDO[] bobsCustomers = ownerQuery.getDOArray();
    /*
     * The code above uses a mix of setQueryXxx() methods
     * and QueryBuilder.addWhere() methods.
     * It produces this SQL query which JOINs the necessary tables:
     *
     *      SELECT Owner.* FROM Owner, CarOwner, Car, Dealer
     *      WHERE  Owner.Age             >=   16
     *      AND    Owner.oid             =    CarOwner.Owner
     *      AND    CarOwner.Car          =    Car.oid
     *      AND    Car.Dealer            =    Dealer.oid
     *      AND    LOWER( Dealer.Name )  LIKE "bob%"
     *
     * For each column in your table, 
     * DODS creates a static RDBColumn member in your DO class.
     * These members are used in the calls to addWhere().
     *
     * For each table (see any .sql file generated by DODS), 
     * DODS adds an 'oid' column as the primary key.
     * So, OwnerDO.PrimaryKey is the RDBColumn member
     * for the 'oid' column in the Owner table.
     *
     * QueryBuilder.CASE_INSENSITIVE_STARTS_WITH is a comparison operator
     * which produces string comparison clauses using SQL's LIKE operator.
     */

Familiarity with SQL will help you compose advanced queries.


Using QueryBuilder Alone, and Debugging Queries
Sometimes you just need the values from one (or a few) columns in some tables. An example is when you are generating a report, and you don't need DO objects because you won't be saving any changes to the data. In these cases, QueryBuilder can be used without Query classes.

To generate a report listing the license plate of each Car, the Dealer who originally sold the Car, and every Onwer of the Car:

    /*
     * create a QueryBuilder that will retrieve the desired fields...
     */
    QueryBuilder qb = new QueryBuilder();
    qb.select( CarDO.LicensePlate );
    qb.select( DealerDO.Name );
    qb.select( OwnerDO.Name );
    /*
     * compose the necessary JOINs...
     */
    qb.addWhere( OwnerDO.PrimaryKey, CarOwnerDO.Owner );
    qb.addWhere( CarOwnerDO.Car,     CarDO.PrimaryKey );
    qb.addWhere( CarDO.Dealer,       DealerDO.PrimaryKey );
    /*
     * retrieve the values for the desired fields...
     */
    RDBRow row;
    while ( null != ( row = qb.getNextRow() ) )
        System.err.println(
            "  Plate # =" + row.get( CarDO.LicensePlate ).getString() +
            "  Dealer  =" + row.get( DealerDO.Name      ).getString() +
            "  Owner   =" + row.get( OwnerDO.Name       ).getString() );

The QueryBuilder class has many addWhere... methods to help you assemble complex queries. Calling the method QueryBuilder.debug() will print the SQL that has been assembled before it is executed.


Using Database VIEWs

The example above retrieves one column from each of three tables.
As an alternative to using the QueryBuilder directly, a view can be created.

In the database:

        create view PlateDealerOwner as
            select      car.licensePlate        licensePlate,
                        dealer.name             dealerName,
                        owner.name              ownerName
            from car, dealer, owner, carowner
            where owner.oid  = carowner.owner
            and   car.oid    = carowner.car
            and   dealer.oid = car.dealer

 

In DODS, create a PlateDealerOwnerDO with the attributes licensePlate, dealerName, and ownerName.

In the .doml file, add 'isView = true' for the PlateDealerOwner table tag
(the GUI will have an 'Is view' checkbox in the future).

Run DODS to generate the classes

    PlateDealerOwnerDO
    PlateDealerOwnerQuery.

Now you can produce the report from the previous example:

 

        PlateDealerOwnerQuery q = new PlateDealerOwnerQuery();
        PlateDealerOwnerDO[] reportLines = q.getDOArray();
        for ( int i = 0; i < reportLines.length; i++ )
            System.err.println(
                "  Plate # =" + reportLines[i].getLicensePlate() +
                "  Dealer  =" + reportLines[i].getDealerName() +
                "  Owner   =" + reportLines[i].getOwnerName() );
In exchange for a little more work up front, views provide benefits:
  1. Views make business layer code easier to write and easier to understand.
    The more complicated the query, the greater the benefit.
    This can ease development bottlenecks when only a few team members are good at composing queries.
  2. A view can also be used by an administrator to see a useful report without accessing the application.

Important Notes and Warnings
Included here are several "gotchas" and related warnings associated with using the DODS-generated SQL directly, as described in this section.

  1. QueryBuilder does not work with the DO Cache
    Given a table containing just a set of valid zipcodes (or other static data),
    you may want to load the entire table into memory when your application starts.
    In DODS you can specify that the ZipcodeDO class will be "cache-enabled."
    Then, the ZipcodeDO class will have a static Hashtable containing ZipcodeDO objects.

    The ZipcodeQuery class will search the cache inside the ZipcodeDO class, rather than query the database. This can make your application run faster.

    However, it is important to understand that the cache is not a relational database, and the cache cannot support JOIN operations. So, anytime you call ZipcodeQuery.getQueryBuilder() the ZipcodeQuery object will assume your are setting up a complex query that the cache cannot support, and it send the query to the database, ignoring the cache.

For all the latest information on DODS, please refer to http://dods.enhydra.org
Questions, comments, feedback? Let us know...