Data Objects Tutorial

Introduction

The following documents have been assembled to help familiarize you with the concepts and functionality of the persistence layer. These documents assume familiarity with the concepts discussed in the the section called Persistence System Overview and the the section called Persistence Glossary. They also assume that the code is being executed within a standard Red Hat Web Application Framework installation and that all transaction management is being handled by the servlet dispatchers. For more information, see the document on the section called Transaction Management.

This tutorial will walk you through the use of object persistence in Red Hat Web Application Framework. It begins by showing you how to ensure that your server is set up correctly so that the PDL will compile. It then continues with a simple example of how to create a PDL file and how to write the corresponding Java code to interact with the database.

After covering the basics of using standalone Data Objects, the tutorial describes how to associate objects to each other. It then discusses cases where simple associations are not efficient enough and predefined SQL must be used. It discusses several examples, including how to retrieve Data Objects through arbitrary queries and how to execute PL/SQL. The tutorial concludes with a list of common mistakes that are made by developers.

Related Documents:

• the section called Transaction Management

• the section called Link Attributes

Setting Up the Server

Before setting up your sever, see the following URL: http://ccm.redhat.com/doc/core-platform/5.0/

	Note
	If you are using a standard Red Hat Web Application Framework installation, you may skip this section, as you will not need to make changes.

After you have successfully configured your server to use Red Hat Web Application Framework, check the appropriate .init file (that is, enterprise.init) to make sure that the initialization of the metadata for the persistence layer is set up correctly. The initializer should have a single parameter, pdlDirectory, that is set equal to the location of the directory containing all of the PDL files. This directory is populated at compile time. By default, the enterprise.init file has the file set to /WEB-INF/pdl. You will not need to change this unless you have customized your setup. If your .init file has a section similar to the lines below, you should be ready to start your server.

init com.arsdigita.persistence.Initializer {
		pdlDirectory = "/WEB-INF/pdl"; }

Beginning with Data Objects

This document discusses the basic steps that are required to access information in the database using the persistence layer. It begins by discussing PDL and Data Objects. It then discusses how to create the database schema and how it can be represented in PDL. Finally, it covers using the properties defined in the PDL to access the database in your Java code.

Related Documents

• the section called Domain Objects Tutorial in Chapter 5

• the section called PDL Syntax

Data Objects and PDL: How are they related?

The persistence layer can be looked at as a way to access objects within the database. Examples of objects that can be stored in the database are Users, Groups, Articles, Images, and Email Addresses. In Red Hat Web Application Framework TCL, if a developer wanted to store some information in the database about the user, he had to have intimate knowledge about how users were stored within the database. Most of the time, this simply involved knowing which row of which table to look up. However, occasionally a single object (for example, a User) was stored across rows of many tables, and the object's associations with other objects in the database were stored in additional tables.

In order to abstract out the information regarding object storage within the database, the persistence layer has implemented the concept of a single Data Object. Data Objects are used by Java classes to handle all interaction with the database. Because this class is provided, other classes do not need to know how to create, retrieve, update, or delete a given object. Data Objects are defined by their Attributes, Object Key, and Events. This design pattern follows the pattern set forth in the J2EE Blueprints (http://java.sun.com/blueprints/patterns/DAO.html).

Every Data Object is associated with a single Object Type. Every object type, in turn, is associated with events and operations that specify how information is stored in the database and how information in the database is mapped to Java variables. The role of the the section called The PDL File is to provide developers with a mechanism to easily specify that object type.

Setting up the Schema

When defining your persistence layer, you need to work through two aspects of your design:

• a UML model of your Data Objects

• a database schema to handle the storage of the Data Objects

Some designers may feel more comfortable starting with a database design and then designing the objects that use the schema, or vice versa. You may begin with either one, but both should be designed as part of the persistence layer. The UML model is typically designed for the Data Objects using a UML modeling tool. Red Hat uses Rational Rose (http://www.rational.com/rose/).

This tutorial will use a hypothetical database schema composed of publications, magazines, articles, paragraphs, and authors. This goal of this tutorial is to help you better understand how this technology can be used with Red Hat Web Application Framework. In practice, the entire data model below will be automatically generated by the persistence layer using the metadata defined in the PDL file. Therefore, none of the data model below will have to be created by the developer.

The tutorial will begin by using two simple objects: a Publication and a Magazine. The schema will be expanded throughout the tutorial. To see the full schema that is used throughout the tutorial, please see the the section called PDL and SQL Used in the Tutorial.

create table publications (
    publication_id     integer
                       constraint publications_pub_id_nn
                       not null
                       constraint publications_pub_id_pk
                       primary key,
    type               varchar(400)
                       constraint publications_pub_type_nn
                       not null,
    name               varchar(400)
                       constraint publications_pub_name_nn
                       not null,
    constraint publications_pub_name_un unique(type, name)
);

create table magazines (
    magazine_id       integer
                      constraint magazines_magazine_id_fk
                      references publications
                      constraint magazines_magazine_id_pk
                      primary key,
    issue_number      varchar(30)
);

Defining an Object Type in PDL

// declare the namespace as "tutorial" using the "model" keyword
model tutorial;

// there are not any import statements because Publication does not
// extend anything.

// next comes the "object type" keyword followed by the name of
// the object type

object type Publication {

    // the first block of code within the object type is a set of
    // mappings from the Attribute Java type and Attribute name to
    // the database column to which they correspond.
    BigDecimal id = publications.publication_id INTEGER;
    String name = publications.name VARCHAR(400);
}

Object Key

For each object type, you must define an object identifier for the purposes of uniquely identifying object instances at run time. If the object type does not have a super type, the object key syntax is used. If the object has a super type, a reference key must be declared to indicate how this object joins with the supertype. (See the section called Object Type Inheritance for more details.) The following example indicates how to designate that the Publication's id attribute is the object identifier or key:

model tutorial; object type Publication { BigDecimal id = publications.publication_id INTEGER; String name = publications.name VARCHAR(400); String type = publications.name VARCHAR(400); object key (id); }

This object type definition almost models the SQL that we have defined above but it is missing any mention of the unique constraint. To allow for this and to allow the DDL generator to correctly generate unique constraints the persistence layer allows you to specify either a single property or a set of properties as being unique. The syntax for this is as follows:

SINGLE UNIQUE PROPERTY: object type User { BigDecimal id = users.id INTEGER; unique String[0..1] screenName = users.screen_name VARCHAR(100); ... } MULTIPLE UNIQUE PROPERTIES: object type Node { BigDecimal id = nodes.id INTEGER; Node[0..1] parent = join nodes.parent_id to nodes.id; String[1..1] name = nodes.name VARCHAR(100); object key (id); unique (parent, name); }

Therefore, in order to correctly model the publications, we need to add the constraint. The correct, full publications object type definition is below:

model tutorial; object type Publication { BigDecimal id = publications.publication_id INTEGER; String name = publications.name VARCHAR(400); String type = publications.name VARCHAR(400); object key (id); unique (name, type); }

	Note
	Attributes cannot have the same name as a pdl reserved word without special quoting. For a list of reserved words as well as how to escape them if you need to use the, see the section called PDL Reserved Words.

Getting Started with the API

Now that we know how to specify an Object Type using PDL, you can learn how to access the information from Java. The first step is to examine the public API that is provided by the Persistence system.

The persistence layer in Red Hat Web Application Framework is implemented in the Java package (http://ccm.redhat.com/doc/core-platform/5.0/api/com/arsdigita/persistence/package-summary.html). This package contains a set of classes and interfaces for working with persistent objects that store themselves in a relational database.

• SessionManager

  http://ccm.redhat.com/doc/core-platform/5.0/api/com/arsdigita/persistence/SessionManager.html

  This class is responsible for initializing the Session class. Users of persistence will use it to get a pointer to the Session class through SessionManager.getSession()

  http://ccm.redhat.com/doc/core-platform/5.0/api/com/arsdigita/persistence/SessionManager.html #getSession().

• Session

  http://ccm.redhat.com/doc/core-platform/5.0/api/com/arsdigita/persistence/Session.htmlx

  All persistence operations take place within the context of a session. The Session object contains methods for creating and retrieving data objects, data queries, and data operations.

• DataObject

  http://ccm.redhat.com/doc/core-platform/5.0/api/com/arsdigita/persistence/DataObject.html

  This interface defines the public methods for Data Objects (Data Object). Data Objects are normally accessed through the use of Domain Objects (http://ccm.redhat.com/doc/core-platform/5.0/api/com/arsdigita/domain/DomainObject.html).

• OID

  http://ccm.redhat.com/doc/core-platform/5.0/api/com/arsdigita/persistence/OID.html

  This class represents a unique identifier for a given Data Object. It typically holds the information in the object key (Object Key) of the object type. For the Red Hat Web Application Framework Object type, the OID is the combination of the "acs object type" (for example, com.arsdigita.User) and the "object id". This is typically used to retrieve specific objects from the database.

• DataQuery

  http://ccm.redhat.com/doc/core-platform/5.0/api/com/arsdigita/persistence/DataQuery.html

  This class represents an interface for retrieving arbitrary information from the database. It is the Java equivalent of the PDL data query (the section called Data Queries).

• DataOperation

  http://ccm.redhat. com/doc/core-platform/5.0/api/com/arsdigita/persistence/DataOperation.html

  This class represents an interface for executing arbitrary DML within the database. It is the Java equivalent of the PDL data operation.

• DataCollection

  http://ccm.redhat.com/doc/core-platform/5.0/api/com/arsdigita/persistence/DataCollection.html

  This class is used to represent a collection of data objects. DataCollections can be used to efficiently iterate over a large set of DataObjects and access the values of their properties. These can be filtered in a manner similar to DataQueries.

• DataAssociation

  http://ccm.redhat.com/doc/core-platform/5.0/api/com/arsdigita/persistence/DataAssociation.html

  This class is similar to the Java Collection interface, in that it provides methods that act on the entire set of associations, such as add(DataObject object) (http://ccm.redhat.com/doc/core-platform/5.0/api/com/arsdigita/persistence/DataAssociation.html#add(com.arsdigita.persistence. DataObject)) and methods that return other objects to access the associations, suchas cursor()(http://ccm.redhat.com/doc/core-platform/ 5.0/api/com/arsdigita/persistence/DataAssociation.html#cursor()).

• DataAssociationCursor

  http://ccm.redhat.com/doc/core-platform/5.0/api/com/arsdigita/persistence/ DataAssociationCursor.html

  Like a Java Iterator, this is used to loop over a set of associations. Developers can also add Filters to DataAssociationCursors, thereby restricting the objects returned to the desired subset.

• Filter

  http://ccm.redhat.com/doc/core-platform/5.0/api/com/arsdigita/persistence/Filter.html

  This class is used to filter the results of associations and collections. For instance, if a developer only wants the associated objects with a name starting with "l" then a filter is used.

• PersistenceException

  http://ccm.redhat.com/doc/core-platform/5.0/api/com/arsdigita/persistence/ PersistenceException.html

  This is an unchecked exception that is thrown whenever there is an error within the Persistence system. It typically contains an error message as well as some debugging information.

• DbNotAvailableException

  http://ccm.redhat.com/doc/core-platform/5.0/api/com/arsdigita/persistence/ DbNotAvailableException.html

  This unchecked exception extends PersistenceException and is used to indicate that the database is no longer available. This is typically caught only by the dispatcher that needs to show a separate "database is down" page when the error occurs.

• UndefinedEventException

  http://ccm.redhat.com/doc/core-platform/5.0/api/com/arsdigita/persistence/ UndefinedEventException.html

  This unchecked exception also extends PersistenceException and is thrown when the persistence system attempts to invoke an event, such as a property retrieve event, which is undefined.

• UniqueConstraintException

  http://ccm.redhat.com/doc/core-platform/5.0/api/com/arsdigita/persistence/ UniqueConstraintException.html

  This unchecked exception extends PersistenceException and indicates that the SQL that was executed caused the database to throw a UniqueConstraintException. It is useful to check for this exception when double-click protection is needed.

Creating and Retrieving Objects in Java

Now that you have written your PDL files and your database is loaded, the next step is to create Data Objects to access the information in the database. The main access point to Data Objects is through the Session class. Session objects allow you to instantiate data objects that are part of a logical client session. Specifically, there are three methods that allow you to create and retrieve objects as well as a method allowing you to delete an object.

Suppose you have defined a Publication and want to create a new DataObject so that you can store information about it. The first step is to use the Session.create(String typeName) method (http://ccm.redhat.com/doc/core-platform/5.0/api/com/arsdigita/persistence/Session.html#create(java.lang.String)). You can pass in the fully qualified name of the Object Type and get a Data Object back that corresponds to the defined events. For instance, to create a new Publication, you can do the following:

DataObject publication = SessionManager.getSession().create("tutorial.Publication");

If you want to retrieve an object that already exists (and you have the OID), you can use the Session.retrieve(OID oid) method (http://ccm.redhat.com/doc/core-platform/5.0/api/com/arsdigita/persistence/Session.html#retrieve(com.arsdigita.persistence.OID)). This is similar to the creation method, except that you are accessing a row that already exists in the database instead of creating a new row (or rows).

If you have an OID and you want to delete the object from the database, Session provides a delete(OID oid) method (http://ccm.redhat.com/doc/core-platform/5.0/api/com/arsdigita/persistence/Session.html#delete(com.arsdigita.persistence.OID) that allows you to perform the deletion. For most cases, however, you will have the DataObject and can just call the local delete() method (http://ccm.redhat.com/doc/core-platform/5.0/api/com/arsdigita/persistence/DataObject.html#delete()).

The above methods show how to retrieve or create a single object within the system. The system also provides the ability to retrieve all objects of a given object type. This capability is provided through the method retrieve(String typeName) (http://ccm.redhat.com/doc/core-platform/5.0/api/com/arsdigita/persistence/Session.html#retrieve(java.lang.String)), located in the Session class.

Suppose you want to print out the names of all of the Publications in the system whose ID is less than 100. One way to do this would be to loop from 1 to 100, create the corresponding OID, and look up each Publication. Another way would be to use a Data Query. However, the simplest way is to take advantage of the retrieve all event that is generated for the Magazine object type. The following example demonstrates this:

// get all of the Publications in the system.  Calling "retrieve" triggers
// the "retrieve all" event defined in the PDL file.  Note that
// the string passed in to the method is the model name (tutorial)
// followed by the object type name (Publication) separated by a dot (.).
DataCollection pub = 
 SessionManager.getSession().retrieve("tutorial.Publication";

// now we want to filter on the ID of the publication
pub.addFilter(pub.getFilterFactory().addLessThan("id", 
 new Integer(100), false));

// finally, we can loop through the publications and print out its name
//
// the query to get the information out of the database is executed
// when next() is called for the first time
while (pub.next()) {
  System.out.println(pub.get("name"));
}

DataCollections are also useful when it is necessary to perform certain tasks on a large number of Data Objects. Suppose you want to add the publications to a java.util.Collection that can then be passed around. The following code will create the java.util.Collection:

DataCollection publication = 
 SessionManager.getSession().retrieve("tutorial.Publication");
Collection collection = new ArrayList();

pub.addFilter(pub.getFilterFactory().addLessThan("id", 
 new Integer(100), false));

while (pub.next()) {
  collection.add(pub.getDataObject());
}

Note

Please note the following: Most creation of Data Objects should be handled through the use of Domain Objects. All ACSObjects are also DomainObjects and therefore can use the DomainObject API to retrieve the correct information. For more information, please see the the section called Domain Objects Tutorial in Chapter 5. In typical usage, you will not want to loop through a DataCollection to create a java.util.Collection. Rather, you will want to pass around a Domain Collection (http://ccm.redhat.com/doc/core-platform/5.0/api/com/arsdigita/domain/DomainCollection.html). The DataCollection class does not extend the java.util.Collection interface, nor does it extend the java.util.Iterator interface.

Object Type Inheritance

Now that you know how to create and access information for a specific object type, the next logical step in Object-Oriented programming is to create an object type that extends another object type. To address this problem, PDL allows object types to inherit Attributes from other object types, similar to inheritance in most Object-Oriented programming languages. Also, much as in Java, the super keyword can be used to call the event for the parent object type (the section called Overriding Object Type Insert, Retrieve, Update, and Delete Events. One difference from Java, however, is that the parent event is not called if nothing is defined. Rather, the MDSQL tries to take over, or if there is not enough metadata, the event is undefined. It is extremely rare that a developer will need to override an event and thus extremely rare that the super keyword will actually need to be used.

The following example shows a "Magazine" object type extending the "Publication" object type. The definition for the "Magazine" is very similar to that of "Publication" in that it has a block of attributes. It is different, however, in that it does not have an object key definition. Rather, it has a reference key which indicates how the table containing the Magazine information can be joined to the table containing the Publication information. If the Magazine tries to define an object key, an error will be thrown. If it does not define a "reference key" (Reference Key), no events can be automatically generated. (In this case, they must all be defined by hand. See the section called Overriding Events for more information about manually defining events.)

model tutorial.magazine;

// we do not have to import the tutorial model because both object types
// are in the same model.  However, we show the import statement here as
// an example.
import tutorial.*;

object type Magazine extends Publication {
    // we need to specify the size of the String attribute so we know
    // whether it is actually a String or if it is really a Clob
    String issueNumber = magazines.issue_number VARCHAR(30);

    // notice that because it extends Publication, there is not an
    // explicitly "object key" declaration.  Rather, there is
    // a "reference key" declaration and "id" is not defined
    // as one of the attributes
    reference key (magazines.magazine_id);
}

Associations

So far, the documentation has discussed how to create simple Data Objects to access the database. These features, while sufficient to build many systems, lack the ability to relate object types to each other. To address these needs, the persistence system contains the concept of associations.

This document discusses how the Persistence layer allows developers to associate Data Objects and how these associations can be saved in the database without needing to involve the Java code in how the associations are actually stored. More concretely, the document first discusses how an association is structured within PDL. It then defines the PDL for Articles, Paragraphs, and Authors and show hows to relate them through Two-Way Associations, Composite Associations, and One Way Associations.

Defining Associations

Associations have the predefined events retrieve, add, remove, and clear. These are analogous to the event types retrieve, insert, update, and delete that are defined within an object type. The main difference is that the associations events are named. That is, instead of having retrieve, associations have retrieve <association name>.

The retrieve event for an association is fired when an object needs to read a related object or objects. SQL statement(s) within the retrieve event are responsible for reading the association object(s) as defined by the role. The add event occurs when a relationship is created between two objects. The SQL statement(s) for the add event are responsible for adding (or updating) rows in the database that materialize the relationship between the two objects as defined by the role. The remove event occurs when the relationship between two objects is being removed. In the case of a composite relationship, this may cause the contained object's rows to be removed as well. The clear event occurs when an association between one object and a group of related objects is being removed from the database.

The first goal for a developer when declaring associations in PDL is to provide the system with enough information to automatically generate the SQL events. If this is done, the developer does not need to hard code in the SQL. The main advantage of having the system generate the SQL instead of hard coding it is that if a developer adds any attribute or alters any existing attributes for a given object type, it is not necessary to update the code of all object types with which the altered object type is associated. However, if the automatically generated SQL is not efficient enough or something special needs to be done, the developer always has the ability to override the default event.

Association Blocks

Most commonly you associate objects to each other when both objects need to know to which objects they are associated. Magazines and Articles, Users and Groups, and Employees and Offices are all examples of this type of association. In the PDL below, an Article Object Type and an "association block" are defined to associate the Articles to Magazines. Association Block definitions are similar to Object Type definitions in that they both begin with Attribute definitions and are optionally followed by blocks of events (see overriding events for more information). There are, however, several subtle differences. First, the Attribute Type is actually an Object Type, rather than simply a Java Type. Also, instead of the Attribute being set as equal to a single column within the database, the Attribute is set as equal to a Join Path.

object type Article {
    BigDecimal id = articles.title;
    String title = articles.title;

    object key (articles.article_id);
}

// this is an "association block" associating "articles" and "magazines"
association {
   // note that the Attribute Type is an Object Type (Article)
   // and not a standard Java Type.  Also notice the order of the
   // join path and see the note below.
   Article[0..n] articles = join magazines.magazine_id
                              to magazine_article_map.magazine_id,
                            join magazine_article_map.article_id
                              to articles.article_id;
   Magazine[0..n] magazines = join articles.article_id
                                to magazine_article_map.article_id,
                              join magazine_article_map.magazine_id
                                to magazines.magazine_id;
}

Note

The order of the join path is important. The information that the developer has must come first. That is, when the developer needs to retrieve articles, the information he has is the Magazine (he needs to know for which magazine to get the articles). Therefore, the first line of the join path specifies how to join the magazines table to the mapping table. From then on, it should be in order so that the last section of the join element uses the same table as the first section of the next join element.

	Note
	The composite and component keywords are not only valid in association blocks (as is shown in this example), but they are also valid with Role References.

Composite Associations

Composite relationships are a special type of Association. Composite relationships are useful for modeling relationships between objects where a contained object cannot exist outside its container object. The main difference between a Composition and a standard Association is that within a Composition, one of the objects cannot exist without the other.

In the following example, a Paragraph is a component of the Article (they therefore have a composite relationship). That is, it does not make sense for a Paragraph to exist outside of an article. There are many different ways to signify a relationship as composite but the easiest way is to add the component keyword before the component Object Type (in this case, the paragraph is a component of the article)

object type Paragraph {
    BigDecimal id = paragraphs.paragraph_id INTEGER;
    String text = paragraphs.text VARCHAR(4000);

    object key (paragraphs.paragraph_id);
}

association {
   Article[1..1] articles = join paragraphs.article_id
                              to articles.article_id;
   // notice the component keyword indicates that if the article does
   // not exist then the paragraph also does not exist
   component Paragraph[0..n] paragraphs = join articles.article_id
                                            to paragraphs.article_id;
}

Another way to make the same association is to use the composite keyword on the role that points toward the composite end of an association in order to indicate that the association is a composition. For example:

association { composite Article[1..1] articles = join paragraphs.article_id to articles.article_id; // notice the component keyword indicates that if the article does // not exist then the paragraph also does not exist Paragraph[0..n] paragraphs = join articles.article_id to paragraphs.article_id; }

The final way to signify the relationship is to use keywords for both object types. This displays the same behavior as the two examples above but it also valid.

association { composite Article[1..1] articles = join paragraphs.article_id to articles.article_id; // notice the component keyword indicates that if the article does // not exist then the paragraph also does not exist component Paragraph[0..n] paragraphs = join articles.article_id to paragraphs.article_id; }

	Note
	You may have noticed that the associations above only use a Join Path of length one where in the example with the Associations Block the Join Path was of length 2. The reason is that for this particular example, no mapping table was needed. However, it is important to realize that whether or not it is composite is completely independent of the length of the Join Path.

Role References

Developers often only need to be able to obtain associated information in a single direction. For instance, if authors have screen names that are used and can be shared, it is useful to be able to look up the screen name for a given author. However, it may not be as important (or even possible) to look up the author that corresponds to a given screen name. In this case, developers should use a Role Reference. In the following example, the developer wants to be able to easily look up a given screen name for an author.

The PDL below can be used to create Object Types for both screenName and Author. Notice that Author contains a role reference to a screenName.

model tutorial;
object type screenName {
   BigDecimal id = screen_names.name_id INTEGER;
   String screenName = screen_names.screen_name VARCHAR(200);
   Blob screenIcon = screen_names.screen_icon BLOB;

   object key (id);
}

object type Author {
    BigInteger[1..1] id = authors.author_id INTEGER;
    String[1..1] firstName = author.first_name VARCHAR(700);
    String[1..1] lastName = author.last_name VARCHAR(700);
    Blob[0..1] portrait = authors.portrait BLOB;

    // the following line is the role reference.  Notice that it
    // appears in the definition just like an Attribute.  The only
    // difference is that instead of pointing to a column in the
    ScreenName[0..1] screenName =
               join authors.screen_name_id to screen_names.name_id;

    object key (id);
}

Link Attributes

One final feature that is immensely useful for associating objects is the idea of Link Attributes. Often, some sort of relationship is needed for associations. For instance, for Magazines, it is useful to include the page number with the Article. The concept of having Articles associated with Magazines is covered by standard associations but in order to capture a page number with the association, Link Attributes are needed.

// this is an "association block" associating "articles" and "magazines"
association {
   Article[0..n] articles = join magazines.magazine_id
                              to magazine_article_map.magazine_id,
                            join magazine_article_map.article_id
                              to articles.article_id;
   Magazine[0..n] magazines = join articles.article_id
                                to magazine_article_map.article_id,
                              join magazine_article_map.magazine_id
                                to magazines.magazine_id;
   // the next line is the Link Attribute.  Note that it also specifies
   // the SQL type of INTEGER so that the DDL generator can correctly 
   // create the mapping table with the page_number column.
   BigDecimal pageNumber = magazine_article_map.page_number INTEGER;
}

For more information about Link Attributes and their use, see the the section called Link Attributes document.

Using Java to Access Associations

Now that you have seen how to declare associations within PDL, you can learn the different ways to access the information from Java. In Java, Associations are accessed with two classes: DataAssociation, similar to a Java Collection, and DataAssociationCursor, similar to a Java Iterator.

Using Standard Associations

The most common use of Associations is to filter out a subset of the results and then either perform some action on all of them or return them all as a DataCollection. In the example code below, the method gets the cursor from the association, filters the cursor so that it only returns the first N articles, and then puts those N articles into a list to be returned. This method assumes location within the Magazine Domain Object.

public Collection getArticles(int numberOfArticles) { LinkedList articles = new LinkedList(); DataAssociationCursor cursor = ((DataAssociation) get("articles")).cursor(); Filter filter = cursor.addFilter("rownum < :numberOfArticles"); filter.set("numberOfArticles", new Integer(numberOfArticles)); while (cursor.next()) { articles.addLast(cursor.getDataObject()); } return children; }

	Note
	If the code had looped through while (cursor.next() && count <= numberOfArticles), it would have needed to close the cursor explicitly so that it did not leave an open ResultSet. See the section called Closing Result Sets for more information about this.

The next example shows how to associate one item with another. In this case, you are associating an Article with a Magazine by adding the article to the "articles" association. Again, this example assumes it is within the Magazine Domain Object. After calling the method below, you must be sure to save the Domain Object with which you are working. By calling save(), you are signalling for the data object to fire the appropriate insert and update association events.

public void addArticle(Article article) { DataAssociation association = (DataAssociation) get("articles"); association.add(article); }

Note

There are two important things to realize when dealing with adding items to Associations and iterating through them: Unlike DataCollection, DataAssociation has been separated into two distinct entities. If you want to loop through the items in the association, or filter or order the association, use a DataAssociationCursor. If you want to add or remove items from the association, use the DataAssociation object. The DataAssociation is a property of the DataObject and is shared by all code accessing the data object. The DataAssociationCursor is essentially a local copy of the association that can be filtered, ordered, and iterated through without any external consequences. While this next item is actually a feature of Domain Objects, it is important to mention here as well. When adding items to associations using the DomainObject (and therefore any ACSObject), there are three choices of which method to use. If the association has an association of 0..n (or any upper bound > 1), developers should use the add(String propertyName, DataObject dataObject) method or the add(String propertyName, DomainObject dobj) method. If the association has a Multiplicity of 0..1 or 1..1 (or any upper bound = 1) then developers should use the setAssociation(String attr, DomainObject dobj) method.

Named SQL Events

So far, we have outlined how to interact with the database in a controlled, structured fashion. While creating standard data objects and associations handles most developing needs, sometimes a developer needs to perform database queries that do not fit within the standard realm of objects and associations. It is also often the case that developers are able to perform operations in a single operation that would normally take the system multiple operations. This situation has been handled in two separate ways through the introduction of Data Queries (for selects) and Data Operations (for DML).

Data Queries

Developers often come across situations in which they need information from the database and the persistence layer does not quite do what is needed. Therefore, the system has the ability to execute arbitrary queries through the use of a DataQuery.

Retrieving Information from the Database

Executing arbitrary queries through DataQueries is easy. You can retrieve them in the same way that you retrieve an existing data object, and you can execute the query and loop through the results in the same way that you use a DataAssociationCursor. Specifically, you retrieve a query through the Session object using its model and name. Then, you can use next() to loop through the results. For instance, suppose you want all paragraphs that show up in the magazine with issue number "5A". The first step is to define the query within the PDL file. A DataQuery definition has four sections. It begins with the declaration of the name of the query followed by data type mappings for each returned attribute. It concludes with two code blocks. The first block, the DO block, contains the actual SQL that will be executed. The second block, the MAP block, allows the developer to map database columns to attribute names. The attributes are the values that can be accessed from the Java code.

To accomplish the task of retrieving the paragraphs as mentioned above, you could declare the following DataQuery in your PDL file:

model tutorial; // the first line indicates that it is a query and the name of the query query paragraphMagazines { // the next section maps the attributes to the java type so that // the same type is returned regardless of which database driver // is used. BigDecimal magazineID; BigDecimal paragraphID; String issueNumber; String text; do { select m.magazine_id, p.paragraph_id, issue_number, text from magazines m, a, magazine_article_map ma, paragraphs p where ma.magazine_id = m.magazine_id and p.article_id = ma.article_id } map { magazineID = m.magazine_id; paragraphID = p.paragraph_id; issueNumber = m.issue_number; text = p.text; } }

With this PDL definition, it should be easy to see how the following code does what is desired.

DataQuery query = SessionManager.getSession().retrieveQuery("tutorial.paragraphMagazines"); query.addEqualsFilter("issueNumber", "5A"); while (query.next()) { System.out.println((String)query.get("text")); }

	Note
	The java type returned by get(String parameterName) is determined by the type specified in the query definition block.

query MagazineToAuthorMapping {
    // the next two lines are declaring that objects will be returned
    Magazine magazine;
        Author author;

    do {
        select publications.name, issue_number, publication_id,
               authors.first_name, authors.last_name, author_id
          from magazines, publications, articles, authors,
               magazine_article_map, article_author_map
         where publications.publication_id = magazines.magazine_id
           and magazine_article_map.magazine_id = magazines.magazine_id
           and magazine_article_map.article_id = 
            article_author_map.article_id
           and article_author_map.author_id = authors.author_id
    } map {
        // here we map the attributes of the objects to columns returned
        // by the query.
        magazine.name = publications.name;
        magazine.issueNumber = magazines.issue_number;
        magazine.id = publications.publication_id;
        author.authorID = authors.author_id;
        author.firstName = authors.first_name;
        author.lastName = authors.last_name;
    }
}

DataQuery query = 
 SessionManager.getSession().retrieveQuery(
    "tutorial.MagazineToAuthorMapping");

// the next line adds the filter so that we only get author's whose last
// name begins with the letter "s".  Note that we are using a standard 
// filter because we need to perform a function on the column and we are 
// positive that the value is not null.
Filter filter = query.addFilter("lower(lastName) like '%' || 
 :lastNamePrefix");
filter.set("lastNamePrefix", "s");

while (query.next()) {
    DataObject myAuthor = query.get("author");
    DataObject myMagazine = query.get("magazine");
    System.out.println("the author I retrieved is " + myAuthor);
    System.out.println("the magazine I retrieved is " + myMagazine);
}

Additional Query Options

Up to this point, the discussion has revolved around standard Data Queries. However, the data query can be extended using the options block to specify different options indicating how the query is actually executing.

OPTIONS block

The options block provides developers with the ability to alter how the query is executed. The options block can be inserted after any attribute definitions but before the first do. Currently, there is only one option that can be specified. This option, the WRAP_QUERIES option, is used to indicate how filters should be handled (see Filtering for more information).

By default, queries wrapped so that they are actually a view on the fly. Suppose that you know that you do not want your query to be wrapped when you execute it. To prevent this, you can set the WRAP_QUERIES option to false. The PDL for this would then appear as follows:

query MagazineToAuthorMapping { // the next two lines are declaring that objects will be returned Magazine magazine; Author author; options { WRAP_QUERIES = false; } do { select publications.name, issue_number, publication_id, authors.first_name, authors.last_name, author_id from magazines, publications, articles, authors, magazine_article_map, article_author_map where publications.publication_id = magazines.magazine_id and magazine_article_map.magazine_id = magazines.magazine_id and magazine_article_map.article_id = article_author_map.article_id and article_author_map.author_id = authors.author_id } map { // here we map the attributes of the objects to columns returned // by the query. magazine.name = publications.name; magazine.issueNumber = magazines.issue_number; magazine.id = publications.publication_id; author.authorID = authors.author_id; author.firstName = authors.first_name; author.lastName = authors.last_name; } }

The SQL that is executed is as follows:

select publications.name, issue_number, publication_id, authors.first_name, authors.last_name, author_id from magazines, publications, articles, authors, magazine_article_map, article_author_map where publications.publication_id = magazines.magazine_id and magazine_article_map.magazine_id = magazines.magazine_id and magazine_article_map.article_id = article_author_map.article_id and article_author_map.author_id = authors.author_id

In contrast, the following SQL is executed when the WRAP_QUERIES is not set to anything (it defaults to true):

select * from ( select publications.name, issue_number, publication_id, authors.first_name, authors.last_name, author_id from magazines, publications, articles, authors, magazine_article_map, article_author_map where publications.publication_id = magazines.magazine_id and magazine_article_map.magazine_id = magazines.magazine_id and magazine_article_map.article_id = article_author_map.article_id and article_author_map.author_id = authors.author_id) results

	Note
	In most cases, there is not an efficiency loss when using the view on the fly because Oracle is smart enough to combine the queries so that they become a single query. The view on the fly is used by default to simplify processing of filters and order by statements as well as allowing for filters to be applied to entire statements containing UNION and INTERSECT, for example.

Data Operations

As mentioned previously, developers often need to be able to execute arbitrary DML statements that do not fit nicely into the realm of data objects and data associations. To accommodate this need, the system contains the concept of a DataOperation that can be used to execute arbitrary DML statements or PL/SQL functions and procedures.

data operation createMagazine {
   do {
      insert into magazine_article_map (magazine_id, article_id)
      select :magazineID, article_id from articles where not exists
      (select 1 from magazine_article_map
      where magazine_article_map.article_id = articles.article_id)
   }
}

DataOperation operation = 
 getSession().retrieveDataOperation("tutorial.createMagazine");
// we have to pass in an Integer instead of an int so that JDBC can
// handle it correctly
operation.setParameter("magazineID", new Integer(4));
operation.execute();

Executing PL/SQL

Developers often need to execute PL/SQL procedures and functions. Therefore, it is possible to execute both using a DataOperation with additional syntax. Arguments can be passed to functions ans procedures using Parameter Binding.

PL/SQL Procedures

Suppose you want to execute the following PL/SQL procedure:

create or replace function myPLSQLProc(v_priority in integer) as begin insert into magazines (magazine_id, title) select nvl(max(magazine_id), 0) + 1, :title from magazine_id; end; / show errors

To do this, first include the above statement in your SQL file, so that it will be defined in the database when your package is installed. Next, declare it in your PDL file using a DataOperation with a special do call block, as follows:

data operation DataOperationWithPLSQLAndArgs { // the "call" keyword after the "do" indicates that the following // is actually a piece of PL/SQL. The system then uses a // java.sql.CallableStatement to execute it instead of only a // java.sql.PreparedStatement. do call { myPLSQLProc(:title) } }

You can then execute this data operation just like any other data operation, after binding the title variable.

It is also possible to use OUT parameters within the DataOperation. To do this, the only additional requirement is for the developer to specify the JDBC type of all of the parameters within the query.

Suppose you want to copy the article with the highest ID into a new row with the ID that you pass into the procedure, and you want back the ID for the row that was copied. You can declare a PL/SQL procedure such as the following:

create or replace procedure DataOperationProcWithInOut( v_new_id IN Integer, v_copied_id OUT Integer) as begin select max(article_id) into v_copied_id from articles; insert into articles (article_id, title) select v_new_id, title from articles where article_id = v_copied_id; insert into article_author_map (article_id, author_id) select v_new_id, author_id from article_author_map where article_id = v_copied_id; end; / show errors

Using the same do call syntax and adding the JDBC type, the PDL to access this procedure appears as follows:

data operation DataOperationProcWithInOut { do call { DataOperationProcWithInOut(:newID, :copiedID) } map { newID : INTEGER; copiedID : INTEGER; } }

To execute this in Java, you simply need to bind the variable and then retrieve the variable using the get(String) method in a fashion similar to retrieving a value from a DataQuery. For instance, to print out the value of the copiedID variable, the following code can be executed:

DataOperation operation = getSession().retrieveDataOperation ("tutorial.DataOperationProcWithInOut"); operation.set("newID", new Integer(4)); operation.execute(); Integer copiedID = (Integer)operation.get("copiedID"); System.out.println("The copied ID was [" + copiedID.toString() + "]");

	Note
	The do call and OUT parameters are not available for Postgres because Postgres has not yet implemented CallableStatements or OUT parameters.

PL/SQL Functions

Retrieving a single value back from a function is almost identical to using OUT parameters for procedures. First, declare your PL/SQL in your SQL file. For example, you may define the following:

create or replace function DataQueryPLSQLFunction(v_article_id in integer) return number is v_title varchar(700); begin select title into v_title from articles where article_id = v_article_id; return v_title; end; / show errors

Next, you can define the function as a DataOperation within your PDL file, as follows:

data operation DataOperationWithPLSQLAndArgsAndReturnInPDL { do call { :title = DataQueryPLSQLFunction(:articleID) } map { title : VARCHAR(700); articleID : Integer; } }

Finally, you can retrieve the value for title just like any normal data query, after binding the :articleID variable.

Note

It is currently necessary to declare the types for each variable within the function whether or not it is an OUT parameter. This is because the current implementation assumes that all arguments to the function are INOUT and therefore needs to know the types other all arguments. While is would have been possible to not require this, it would have required a significant change to the PDL syntax and a non-trivial amount of time. Therefore, we require that all variables are mapped.

	Note
	The methods described above do not allow users to return cursors from their PL/SQL functions. If this is required, the recommended workaround is to use a CallableStatement directly and bypass the persistence layer entirely.

Filtering, Ordering, and Binding Parameters

When retrieving information from the database, developers almost always need to be able to filter and order the results that are returned. Therefore, DataQuery, DataCollection, and DataAssociationCursor objects allow for ordering and filtering. DataQuery and DataOperation also allow developers to set arbitrary bind variables within the queries and DML statements. This document discusses how these features are implemented and how Filtering can be overridden to use any arbitrary filtering scheme.

Filtering

query myDataQuery {
    BigDecimal articleID;
    do {
       select max(article_id) from articles
    } map {
       articleID = articles.article_id;
    }
}

select *
from (select max(article_id) from articles) results
where lower(title) like 'b%'

select max(article_id) from articles where lower(title) like 'b%'

query myDataQuery {
    BigDecimal articleID;
    options {
        WRAP_QUERIES = false;
    }
    do {
       select max(article_id) from articles
    } map {
       articleID = articles.article_id;
    }
}

query UsersGroups {
String firstName;
String lastName;
String groupName;
do{
      select *
      from users, groups, membership
      where users.user_id = membership.member_id  
      and membership.group_id = groups.group_id
} map {
      firstName=users.first_name;
      lastName=users.last_name;
      groupName=groups.group_name;
}

Complex Filters

For more complex queries, it is helpful to understand the role of each interface that deals with Filters.

• Filter - This class represents a single expression for part of a "where" clause. For instance, a Filter could be "foo = :bar" with a value associated with "bar" (for example "foo = 3").

• CompoundFilter - This class extends Filter and provides the ability to add filters together using the "AND" and "OR" keywords.

• FilterFactory - This class is responsible for handing out filters. It has methods such as "simple", "equals", "notEquals", "lessThan", "greaterThan", "startsWith", "contains", and "endsWith". If a user is using Oracle or Postgres, all these methods check whether the value is null, and if so, act correctly (for example, use "foo is null" instead of "foo = null").

• DataQuery - This class allows you to add filters as well as get a reference to the FilterFactory. If you need a FilterFactory but do not have a DataQuery, use Session.getFilterFactory().

Suppose you want to filter the query based on certain conditions. You can incrementally build up your query as follows:

DataQuery query = session.retrieveQuery("UsersGroups"); FilterFactory factory = query.getFilterFactory(); if (beginLetter != null) { query.addFilter(factory.lessThan("firstLetter", beginLetter, true)); } if (lastLetter != null) { query.addFilter(factory.greaterThan("firstLetter", beginLetter, true)); } while (query.next()) { System.out.println("First name = " + query.get("firstName") + "; Last name = " + query.get("lastName") + "; Group = " + query.get("groupName")); }

Now suppose you want to get all users with a last name that is the same as the variable "lName" or is "Smith", and with a first name that matches the variable "fName" or is "John". You could do this as follows:

DataQuery query = session.retrieveQuery("UsersGroups"); FilterFactory factory = query.getFilterFactory(); Filter filter1 = factory.or().addFilter(factory.equals("lastName", lName)) .addFilter(factory.equals("lastName", "Smith")); Filter filter2 = factory.or().addFilter(factory.equals("firstName", fName)) .addFilter(factory.equals("firstName", "John")); query.addFilter(factory.and().addFilter(filter1).addFilter(filter2)); while (query.next()) { System.out.println("First name = " + query.get("firstName") + "; Last name = " + query.get("lastName") + "; Group = " + query.get("groupName")); }

These could also have been chained together in order to avoid creating any Filter variables, but this was not done here for clarity.

Finally, if you want to add a bunch of "foo = :foo" statements to a query, you can use the convenience methods provided by DataQuery. These methods delegate to FilterFactory and therefore handle the Oracle null problem. For instance:

DataQuery query = session.retrieveQuery("UsersGroups"); if (includeFirstName) { query.addEqualsFilter("firstName", fName); } if (includeLastName) { query.addEqualsFilter("firstName", fName); } while (query.next()) { System.out.println("First name = " + query.get("firstName") + "; Last name = " + query.get("lastName") + "; Group = " + query.get("groupName")); }

	Note
	When setting dates, java.util.Date objects should be used instead of java.sql.Date objects because java.sql.Dates do not have hours, minutes, or seconds.

When filtering a query that returns data objects (as opposed to simple java types), you must prepend the name of the object attribute. For instance, if you want to retrieve all uses using a query, you would follow the example below. In practice, you want to retrieve a DataCollection from the Session but we are using a query here for demonstration purposes.

query retrieveAllUsers { User myUser; do { select user_id, first_name, last_name from users } map { user.id = users.user_id; user.firstName = users.first_name; user.lastName = users.last_name; } } DataQuery query = session.retrieveQuery("retrieveAllUsers"); // notice how the attribute name corresponds directly to what // in the map" section of the query and is actually // the <object type>.<attribute name> query.addEqualsFilter("user.firstName", fName); while (query.next()) { DataObject user = query.get("user"); System.out.println("First name = " + user.get("firstName") + "; Last name = " + user.get("lastName") + "; Group = " + user.get("groupName")); }

DataQuery query = session.retrieveQuery("retrieveAllUsers");
query.setRange(new Integer(10), new Integer(20));
while (query.next()) {
   DataObject user = query.get("user");
   System.out.println("First name = " + user.get("firstName") +
                      "; Last name = " + user.get("lastName") +
                      "; Group = " + user.get("groupName"));
}

Filtering Using Subqueries

The filtering methods described so far handle most situations. However, sometimes developers need the ability to filter based on the results of a subquery. Therefore, the Persistence layer provides a mechanism to allow developers to use named queries within filters. Specifically, this is useful within IN clauses and EXISTS clauses.

Suppose that you want to retrieve all articles written by authors whose last name begins with the letter "b". One easy way to avoid duplicates is to use an IN clause. To perform this operation, you can create the following two DataQueries:

query retrieveArticlesBasedOnAuthor { BigDecimal authorID; do { select article_id from authors, author_article_map where authors.author_id = author_article_map.author_id and lower(last_name) like :lastName || '%' } map { authorID = authors.author_id; } } query retrieveSelectedArticles { BigDecimal articleID; String title; do { select article_id, title from articles } map { articleID = articles.article_id; title = articles.title; } }

Next, simply retrieve one query and add the other query as part of the filter, as follows:

Session session = SessionManager.getSession(); DataQuery query = session.retrieveQuery("tutorial.retrieveSelectedArticles"); Filter filter = query.addInSubqueryFilter("articleID", "tutorial.retrieveAuthors WithParam"); // we have to set the value for "lastName" since it is a bind variable // in the subquery we added. filter.set("lastName", "b"); System.out.println("The following articles have at least one author whose " + "last name starts with 'b'"); while (query.next()) { System.out.println(query.get("title")); }

The code above will actually execute the following SQL:

select article_id, title from articles where article_id in (select article_id from authors, author_article_map where authors.author_id = author_article_map.author_id and lower(last_name) like ? || '%' with ? = "b"

	Note
	While there are other, possibly better ways to obtain the same result, this example is used to demonstrate how the feature works, not as an authoritative example of writing queries.

DataCollection pub = 
  SessionManager.getSession().retrieve("tutorial.Articles");
Filter filter = 
  pub.addFilter(pub.getFilterFactory().compare("upper(title)",
                FilterFactory.EQUALS, ":title"));
// we set the title to all upper case so that we do not make oracle do
// it for us which would be slower
filter.set("title", "DISASTER STRIKES");

DataCollection pub = 
  SessionManager.getSession().retrieve("tutorial.Articles");
FilterFactory factory = pub.getFilterFactory();
Filter disasterFilter = 
  factory.compare("upper(title)", FilterFactory.CONTAINS,
                                        ":disasterTitle"));
filter.set("disasterTitle", "DISASTER");
Filter strikesFilter = 
  factory.compare("upper(title)", FilterFactory.CONTAINS,
                                       ":strikesTitle"));
filter.set("disasterTitle", "STRIKES");
pub.addFilter(factory.or()
                     .addFilter(disasterFilter)
                     .addFilter(strikesFilter));

Ordering

Use the addOrder(String order) method to order a DataQuery, DataCollection, and a DataAssociationCursor object. The addOrder method takes a String as its only parameter and the format of the string is the optional object type following by a dot and then then required attribute name ([<object type name>.]<attribute>) you wish to order by.

The string parameter passed to the addOrder(String order) method is used in an ORDER BY clause, which is appended to the SELECT statement. The order is specified by constructing a string representing the ORDER BY clause, but instead of specifying column names, you specify attribute names. The persistence layer automatically converts the attribute names to column names using the property mappings defined in the PDL. For example, see the following DataQuery defined in PDL:

query UsersGroups { String firstName; String lastName; String groupName; do{ select * from users, groups, membership where users.user_id = membership.member_id and membership.group_id = groups.group_id } map { firstName=users.first_name; lastName=users.last_name; groupName=groups.group_name; } }

You can order this query by the user's last name, as follows:

DataQuery query = session.retrieveQuery("UsersGroups"); query.addOrder("lastName asc"); while (query.next()) { System.out.println("First name = " + query.get("firstName") + "; Last name = " + query.get("lastName") + "; Group = " + query.get("groupName")); }

Finally, you can build up the ORDER BY string in the same way that you build up a filter. For instance:

DataQuery query = session.retrieveQuery("UsersGroups");
query.addOrder("lastName asc")
if {careAboutGroupName} {
    query.addOrder("groupName");
}
while (query.next()) {
   System.out.println("First name = " + query.get("firstName") +
                      "; Last name = " + query.get("lastName") +
                      "; Group = " + query.get("groupName"));
}

The ORDER BY string is any valid ORDER BY clause, except that you specify property names, not column names.

Binding Parameters

Use the setParameter(String parameterName, Object value) method to bind an arbitrary variable within a DataQuery, DataOperation, DataAssociationCursor, or DataCollection. The method getParameter(parameterName) allows you to retrieve the value of a set parameter. The setParameter takes in a string that should match the string within the defined SQL. The Object it takes should specify the value.

This functionality is useful for complicated queries that involve embedding parameters. For example, see the following DataQuery defined in PDL (bind variables are in bold):

query CategoryFamily {
  Integer level;
  BigDecimal categoryID;
  String name;
  String description;
  Boolean isEnabled;
  do {
     select l, c.category_id, c.name, c.description, c.enabled_p
        from (select level l, related_category_id
                 from (select related_category_id, category_id
                          from cat_category_category_map
                          where relation_type = :relationType)
                 connect by prior related_category_id = category_id
                 start with category_id = :categoryID) m,
            cat_categories c
        where c.category_id = m.related_category_id
  } map {
     level = m.l;
     categoryID = c.category_id;
     name = c.name;
     description = c.description;
     isEnabled = c.enabled_p;
  }
}
    

This query first retrieves all mappings that are of a particular type (for example, "child" mappings as opposed to "related" mappings). It then does a "connect by" to get all the parents (or all the related categories). Finally, it joins this result with the original categories table so that it can have the name, categoryID, description, and isEnabled for each of the selected categories. Without being able to set the categoryID and relationType variables, you could not perform this query. Creating a separate query for each kind of relationType does work, but there is no way to account for every possible categoryID.

After the query is defined, it can be used as follows:

DataQuery query = session.retrieveQuery("CategoryFamily");
query.setParameter("relationType", "child");
query.setParameter("categoryID", "3");
while (query.next()) {
  System.out.println("We retrieved Category " + query.get("name")) +
                     "with ID = " + query.get("categoryID"));
}
    

public DataCollection retrieveArticles(Collection articlesToRetrieve) {
    DataCollection articles = 
      SessionManager.getSession().retrieve("tutorial.Articles");
    Filter filter = articles.addFilter("id in :articlesToRetrieve");
    filter.set("articlesToRetrieve", articlesToRetrieve);
    return articles;
}

How it Works

You may be wondering how to add filters and bind variables after retrieving the query. The persistence system does not actually execute the required SQL query until the first call to next(). Therefore, the DataQuery (or DataCollection or DataAssociationCursor) can be passed around and have Filters and ORDER BY statements added and variables bound before the first element is retrieved. Then, when the first element is retrieved, the query is executed and the code can no longer add or remove filters or ordering criteria.

Common Mistakes Using Persistence

As developers use the persistence layer of Red Hat Web Application Framework, several types of errors are made by many developers. This document tries to highlight some of those errors to help with debugging tasks. Another document that may be of interest to developers having problems with Persistence is the the section called Frequently Asked Questions.

Use of Semi-Colons; Invalid Character Error

One of the most common and most easily fixed errors is that of an invalid character. The error is typically something such as:

com.arsdigita.persistence.PersistenceException: ORA-00911: invalid character
    

This error is caused because the developer accidentally included a semi-colon at the end of a SQL statement. For instance, the following retrieve event will cause the above error because of the semi-colon (in bold) at the end:

    retrieve {
       do {
          select publication_id, name
          from publications
          where publication_id = :id;
       } map {
          id = publications.publication_id;
          name = publications.name;
       }
    }
    

To fix the error, simply remove the semi-colon so that the event definition appears as follows:

    retrieve {
       do {
          select publication_id, name
          from publications
          where publication_id = :id
       } map {
          id = publications.publication_id;
          name = publications.name;
       }
    }
    

This error occurs is because the query is fed directly to Oracle through Oracle's JDBC driver, which does not allow a trailing semi-colon.

Incorrect Attribute Mappings

When you receive an error indicating that an attribute cannot be found or an attribute name is invalid, there are two things that you should check. First, make sure that there are no typos. Simple typos that cannot be caught by the compiler cause many errors. Second, make sure that the column name within the mapping statements is preceded by the table name. For instance, the following may give you an attribute not found error:

    retrieve {
       do {
          select publication_id, name
          from publications
          where publication_id = :id
       } map {
          id = publication_id;
          name = name;
       }
    }
    

To fix this, you should include the table names as in the following event definition:

    retrieve {
       do {
          select publication_id, name
          from publications
          where publication_id = :id
       } map {
          id = publications.publication_id;
          name = publications.name;
       }
    }
    

Problems with Transactions

Typically, two types of mistakes occur when dealing with Transactions within Red Hat Web Application Framework.

The first type of mistake is trying to access Data Objects (and hence the database) outside of a transaction. This typically happens when the developer tries to write a custom dispatcher that bypasses the standard Red Hat Web Application Framework dispatcher and does not remember to include transaction management. To fix this, make sure that the custom dispatcher properly opens and closes transactions.

The second type of mistake is the exact opposite of the first: trying to nest transactions. Unless you have written a custom dispatcher that bypasses the standard Red Hat Web Application Framework dispatcher, you should never have to open or close a transaction. Trying to do so within the standard framework of Red Hat Web Application Framework will result in an error. In almost all cases, developers building on top of Red Hat Web Application Framework should not try to manage their own transactions.

For more information, see the document on the section called Transaction Management.

Using the super keyword in retrieve events.

A common misconception about the super keyword is that it seamlessly takes care of operations without any extra overhead. The important thing to realize is that every call to super causes another database hit. Therefore, calling super within a retrieve event is not a good idea unless you do not care about database performance. Instead of calling super and then performing the select for the given object type, you should perform the entire join and mapping within a single statement within a single PDL file.

The use of the super keyword is something that should be reserved for insert, update, and delete events where the task can only be accomplished with several distinct trips to the database.

A suggestion that often arises in response to this explanation is that a child object should not have to know about the properties of its parents. This is the reason that MDSQL was implemented. It is a rare situation where developers need to define their own events and therefore it is very rare that the super keyword will even be used. When developers do define their own events, they are probably doing so for performance reasons, in which case hard coding the properties of the parents is unavoidable.

MDSQL Usage

MDSQL, as a whole, has several potential pitfalls of which you should be aware. The most common symptom is that events are simply not generated for your object, leading to error messages at run time indicating missing events (warnings are not always issued because it is not always possible to distinguish MDSQL-ready object types from those that use static SQL).

The most common mistake made with MDSQL is not giving the MDSQL generator enough information to do its job. To be absolutely safe, you need to make sure that all properties either have a column or a join path specified, and either a reference key or an object key. In addition, each attribute should have the java data type and the SQL type. If you are extending another object type, you must make sure that the supertype (and all of its supertypes) fully support MDSQL.

Other common problems that will make event generation fail are:

• Non-continuous join paths -- the second column's table in each join path element must match the table of the next join path element's first column. That is, the path must look something like join A.B to C.D, join C.E to E.F In this example, table C is the last table of the first half and the first table of the second half.

• Join paths are too long -- MDSQL only supports join paths composed of 1 or 2 join path elements. If paths are longer than that, it becomes impossible to generate events correctly given the current implementation

• Join path does not start in the primary table -- a join path must always start in the same table as the reference key or object key. If it does not, there is a gap and the event cannot be generated.

The following is a list of MDSQL functionality, along with the information needed to correctly generate the events:

• Object Retrieve: all simple properties must have columns defined, reference key or object key is required, same for all supertypes

• Object Create/Update/Delete : all simple properties must have columns defined, reference key or object key is required

• Complex Property CRUD: property's datatype and all of its super types must meet MDSQL requirements for event type, host object type must have an object or reference key, join path for property must be defined and either 1 or 2 join path elements in length

• Associations: both properties' datatypes must meet MDSQL requirements for each event type, join path must be defined and either 1 or 2 join path elements in length

Dynamic DDL Generation

Dynamic DDL Generation works well when the PDL is specified correctly. However, it is easy to think that the PDL is written to represent the model correctly when in fact you are missing a simple item to two. Below are some symptoms that you may see with your generated DDL and some suggestions as to how you may be able to fix them.

Debugging Persistence

When developing code using persistence, you may have a difficult time tracking down the reason for a PersistenceException. You may know what the problem is (for example, you are trying to use an object when the transaction is not open), but not know which specific object is causing the error. To help resolve this issue, the Session class has two methods, pushMessage(String message) and popMessage(), that can be used to include useful information within the message provided by the PersistenceException. For instance, before trying to save a given object, it may be beneficial to push the OID of the object onto the Session's stack. Then, if a PersistenceException is thrown, you will be able to see which object caused the problem. If the code succeeds, you can simply pop the message off. Popping the message off of the stack helps avoid confusion if an error is thrown later on in the code. For instance, a method that saves a DataObject may appear as follows:

Session session = SessionManager.getSession();
session.pushMessage("Saving associations for " + getOID());
saveAssociations();
session.popMessage();