At the core, pureQuery is a table gateway framework. However, all the patterns we have examined in this book can be implemented to some degree within pureQuery because it has multiple layers. For example, you can use the runtime API directly and implement the transaction script pattern. Using the pureQuery tools, you can generate a set of data objects based on SQL statements and database schemas and implement the gateway pattern. Finally, pureQuery can be used as an underlying layer to another ORM, which then can implement a full Domain Mapper—in this case, you might be using pureQuery indirectly through another mechanism and not even know it!

Java persistence has been dominated primarily by ORM frameworks. History has shown that since Java EE was created, there have been various attempts at ORM (CMP, JDO, Hibernate, and so on.). JPA seems to be the current emergent winner in that space. However, as stated, there is a considerable amount of time invested in tuning ORM-based applications for performance. In addition, there is a class of applications that use SQL directly for reasons we stated in the introduction to this chapter. Beyond these reasons, enterprise customers were explicitly asking IBM for a persistence framework to meet a number of additional requirements that we discussed briefly in Chapter 2. Figure 9.1 (repeated from Chapter 2) illustrates some of these requirements.

Figure 9.1. Persistence requirements considered in developing pureQuery.

IBM decided to create pureQuery as a common query engine that could be used in various environments to meet these and other requirements.

The pureQuery technology itself is currently not part of a standard. That said, pureQuery-based applications can run inside Java SE and Java EE environments, and can be used in scripting environments such as Groovy. With pureQuery, your code will work with most JDBC-compliant databases, so you will not lose portability. In addition, if you use pureQuery under another framework, you can achieve better development productivity and runtime performance without your end users needing to know about it.

pureQuery was built by the IBM Information Management team in response to these requirements and is being extended by ProjectZero as needed to support Web 2.0 applications. Project Zero is an IBM incubator project focused on agile development of Web 2.0 applications based on Service Oriented Architecture (SOA). Web 2.0 applied to SOA allows Web artifacts based on HTTP and RESTful principles to extend the reach of SOA. This new architecture is referred to as Web Extended SOA. We will discuss these principles throughout this chapter. Fielding is the definitive source for more information on RESTful principles [Fielding].

The way the two are related is that pureQuery is used as a query engine for Project Zero. In addition to using the data APIs, Project Zero provides the capability to make use of scripting languages to access data directly through SQL. Project Zero allows for executing pureQuery using Groovy and PHP. Additionally, Milestone 2 includes a Resource Mapping for creating a standard mapping between RESTful services and data that exploits pureQuery.

Another thing that makes ProjectZero very exciting is its exciting new development approach called Community-Driven Commercial Development (CD/CD, or CD²). CD² enables the user community to observe and influence technical decisions for ProjectZero (also called “Zero”). Furthermore, users have direct access to the Zero development team and the source code itself.

This approach should not be confused with Open Source, where the community can contribute to any aspect of the technology. Although you can contribute requirements, prototypes, and extensions to Zero, you cannot contribute directly to the core.

But similar to Open Source and Standards bodies, Project Zero’s goals are both technical and social in nature.

The technical goals are to provide a scalable platform that simplifies Web Extended SOA application development in three important dimensions:

The social goals relate to the CD² process itself. In keeping with CD², all the information about Zero is “open” and available at www.projectzero.org. At the time we wrote this chapter, Project Zero had released Milestone 1 and was close to releasing Milestone 2. We expect the platform to evolve as time goes on, and may even become a de facto standard in its own right.

The pureQuery runtime requires an underlying JDBC driver like most persistence frameworks. You can use it to build Java SE applications. Other dependencies will be specific to the environment in which pureQuery is used. For example, when using pureQuery inside of Project Zero, you need to download its platform from the website [Zero 1].

Besides the JDBC drivers and environment-specific dependencies, there are no other dependencies for the pureQuery runtime. The pureQuery technology also includes tools to generate code and optimizations. They are based on the Eclipse Platform and require an Eclipse runtime with Data Tool support. The IBM Data Studio contains the pureQuery tools. We show examples of this tooling throughout the chapter. See the IBM Data Studio web page for more details [IBM DS].

The pureQuery technology is licensed by IBM, and its runtime and tools ship with the IBM Data Studio product. Please refer again to [IBM DS] for details on the license. The pureQuery runtime also comes with Project Zero. For details on the Project Zero license, see the FAQ page [Zero 2].

The pureQuery technology is a fairly new technology. However, some practiced articles and tutorials are already available. Table 9.1 lists some examples.

Table 9.1. Available Literature

Title	Source	Description
“Overview of pureQuery Tools” A.9.1	www.ibm.com/developerworks/db2/library/techarticle/dm-0709surange/	General overview of the pureQuery tools
“pureQuery: IBM’s New Paradigm for Writing Java Database Applications” A.9.2	www.ibm.com/developerworks/db2/library/techarticle/dm-0708ahadian/	General introduction to pureQuery
“Detect and Fix SQL Problems Inside Java Program”	www.ibm.com/developerworks/db2/library/techarticle/dm-0709surange2/	Article showing how to use the pureQuery tools to detect SQL problems with your application A.9.3
“Configuring Data Access with Project Zero” [Zero 3]	www.projectzero.org/wiki/bin/view/DocumentationMilestoneOne/DataAccess	Developer Guide for using Data Access APIs with Project Zero
“Building RESTful Services for your Web Application with Project Zero”	www.ibm.com/developerworks/ibm/library/i-zero1/	Tutorial on Project Zero showing Data APIs in Action A.9.4
“RESTful applications in an SOA: Part 2”	www.ibm.com/developerworks/ibm/library/i-zero2/	Follow-on tutorial on Project Zero showing Data APIs and ZRM in Action A.9.5
“Use Project Zero’s data access APIs to build a simple wiki”	www.ibm.com/developerworks/web/library/wa-pz-wiki/	Article illustrating the data access API’s in Project Zero in the context of building a Wiki. A.9.6

We will show pureQuery initialization both for the pureQuery API and for the Groovy extensions. It is worth noting that the pureQuery Data API can be used directly in Project Zero based applications.

Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
String url = "jdbc:derby://localhost/test";
String username = "myUserName";
String password = "myPassword";
Connection connection = DriverManager.getConnection(
    url, username, password
);
Data data = DataFactory.getData (connection);

InitialContext ic = new InitialContext()
DataSource ds = ic.lookup("java:comp/env/jdbc/Derby/DataSource");
Data data = DataFactory.getData (ds);

[/app/db/pie/config]
class=org.apache.derby.jdbc.ClientDataSource
serverName=localhost
portNumber=1527
databaseName=PWTE
connectionAttributes=create=true

In this section, we show how to access connections using both the Data API and Project Zero.

Data API

We showed in the “Initialization” section how the Data API makes a connection. After you bootstrap Connections or DataSource objects into your application context, all you need to do is get an instance to a data object. This was shown earlier in Listings 9.1 and 9.2. The Data Interface contains the query method necessary to execute SQL. Figure 9.4 shows the Interface for the Query API.

Figure 9.4. Data Interface.

Manager db = Manager.create("pie");

In this section, we show how to handle transactions using pureQuery’s data API and Project Zero.

Data data = DataFactory.getData (connection);
data.setAutoCommit(false);
...  //Execute Code
data.commit();

db.startTransaction();
try {
    db.update("INSERT...");
    db.update("UPDATE...");
    db.update("DELETE...");
    db.commitTransaction();
}
catch (Exception e) {
    db.rollbackTransaction();
} finally {
    db.endTransaction();
}

public Order openOrder(int customerId)
throws CustomerDoesNotExistException, OrderAlreadyOpenException {
    Order newOrder = new Order();
    db.inTransaction
    {
        db.update("INSERT...");
        db.update("UPDATE...");
        db.update("DELETE...");

    }
    return newOrder;
}

Now that we have the bootstrapping completed, the connection made, and the transaction started, we are ready to create some persistent data. We will start with the Data API and then examine how you can create data in Project Zero applications.

Data API

As stated earlier, there are two styles of development in pureQuery: the inline code style and the annotated method style. The inline code style requires you to use the Data API where you can use the special :<property_name> convention to insert variables from a specified bean into the queries. This saves you from having to explicitly map fields. Listing 9.8 shows an example that creates an Order in a bean and uses an insert statement.

Example 9.8. Create Order

<LINELENGTH>90</LINELENGTH>

Order newOrder = new Order();
newOrder.setCustomer_id(1);
newOrder.setStatus("OPEN");
newOrder.setTotal(new BigDecimal(0));
db.update (
    "insert into ORDERS (CUSTOMER_ID, STATUS, TOTAL)
            values( :customer_id, :status, :total)",
    newOrder
);

The other style of development is the annotated method style. In this style, you create a Java Interface that contains a method prefaced with an @Update annotation that contains the SQL insert statement. Listing 9.9 shows an example.

Example 9.9. Create an Order with the Annotated Method style

<LINELENGTH>90</LINELENGTH>

public interface OrderData {
@Update(
    sql="insert into ORDERS (CUSTOMER_ID, STATUS, TOTAL)
                values( :customerId, :status, :total)"
)
int createOrder(Order order);
...

To create an order anywhere in your code, you simply invoke the createOrder method with an Order bean as specified. This annotated interface is then used to generate the actual code using the pureQuery tooling. Figure 9.5 shows an example of the generated artifacts.

Figure 9.5. Generated code.

For more detail on the pureQuery tools, refer to the developerWorks website.

A.9.1

Like OpenJPA, pureQuery also allows you to externalize the SQL into an XML file and run the generator against the interface and XML file. As a matter of fact, pureQuery uses the JPA syntax for naming native queries so that they can be used in your code. Listing 9.10 shows an example of the XML syntax.

Example 9.10. Native Query Method

<LINELENGTH>90</LINELENGTH>

<entity-mappings xmlns="java.sun.com/xml/ns/persistence/orm">
  <named-native-query
     name="org.pwte.domain.OrderData#createOrder(Order)"
  >
    <query>
        insert into ORDERS (CUSTOMER_ID, STATUS, TOTAL)
               values( :customerId, :status, :total)
    </query>
  </named-native-query>

  <entity class="Order">
  </entity>
</entity-mappings>

If you prefer to create fewer artifacts and do not mind modifying the SQL directly in your code, you will likely prefer the annotated style. Otherwise, if you prefer to externalize your SQL so that it can be reviewed and maintained separately, then you will likely prefer the XML approach.

      LineItem lineItem = new LineItem();
      lineItem.setOrderId(openOrder.getOrderId());
      lineItem.setProductId(product.getProductId());
      lineItem.setAmount(amount);
      lineItem.setProduct(product);
      lineItem.setQuantity(quantity);
      db.update(
        "INSERT INTO LINE_ITEM (ORDER_ID,PRODUCT_ID,amount,quantity)
         VALUES (
            ${lineItem.orderId},
            ${lineItem.productId},
            ${lineItem.amount},
            ${lineItem.quantity}
          )"
      );

Object order = // obtained perhaps through defaultJsonInput
Customer customer = // obtained somehow through another source

data.update(
    "INSERT INTO
         orders (
             orderId, customerId, itemId, quantity, price, address
         )
         VALUES (
              ?1.orderNum, ?2.custId, ?1.itemNum, ?1.quantity,
              ?1.price, ?2.address
         )
    ",
    order, customer
);

As we did with create, we will first examine approaches to retrieve data enabled by the pureQuery Data API, and then examine those enabled by ProjectZero.

Customer getCustomer = db.queryFirst (
    "select
         CUSTOMER_ID, OPEN_ORDER, NAME, BUSINESS_VOLUME_DISCOUNT,
         BUSINESS_PARTNER, BUSINESS_DESCRIPTION,
         RESIDENTIAL_HOUSEHOLD_SIZE, RESIDENTIAL_FREQUENT_CUSTOMER,
         TYPE
     from CUSTOMER where CUSTOMER_ID = ?
    ",
    Customer.class, customerId
);

Iterator<Customer> getCustomers = db.queryIterator (
    "select * from CUSTOMER", Customer.class
);

Product product = db.queryFirst(
    "select * from Product where product_id = ${productId} ",
    Product.class
);

def data = zero.data.groovy.Manager.create('mydb')
def xml = new groovy.xml.MarkupBuilder(request.writer)

xml.records() {
        data.eachRow('SELECT * FROM table'){ row ->
          item(id: row['id'], name: row.name)
        }
}

// creates the following XML:
// <records>
//   <item id="10" name="Geoff" />
//   <item id="11" name="Roland" />
//   <item id="15" name="Kyle" />
// </records>

Again, we will look at how to update persistent objects using the pureQuery Data API followed by examples using ProjectZero and Groovy.

Customer customer = // get instance.
db.update(
    "update CUSTOMER
         set CUSTOMER_ID = :customer_id, OPEN_ORDER = :open_order,
             NAME = :name,
             BUSINESS_VOLUME_DISCOUNT = :business_volume_discount,
             BUSINESS_PARTNER = :business_partner,
             BUSINESS_DESCRIPTION = :business_description,
             RESIDENTIAL_HOUSEHOLD_SIZE = :residential_household_size,
             RESIDENTIAL_FREQUENT_CUSTOMER =
                 :residential_frequent_customer,
             TYPE = :type
     where CUSTOMER_ID = :customer_id
    ",
    customer
);

// Update one ORDERS by Order object
@Update(
    sql="update ORDERS
             set CUSTOMER_ID = :customer_id,
                 STATUS = :status,
                 TOTAL = :total
         where ORDER_ID = :order_id"
)
int updateOrder(Order o);

public void submit(int customerId)
throws CustomerDoesNotExistException,
       OrderNotOpenException, NoLineItemsException {
    db.inTransaction
    {
        //Code to get and make changes to openOrder
        db.update(
            "UPDATE ORDERS
                 SET STATUS = ${Order.Status.SUBMITTED.toString()}
             where ORDER_ID = ${openOrder.orderId}"
        );
        db.update("UPDATE CUSTOMER SET OPEN_ORDER = NULL");
    };
}

This section shows that deleting data in the pureQuery Data API or Project Zero and Groovy is no different from the other CRUD methods.

Order order = //
db.update("delete from ORDERS where ORDER_ID = :order_id", order);

// Delete ORDERS by parameters
  @Update(sql="delete from ORDERS where ORDER_ID = ?")
  int deleteOrder(int order_id);

  // Delete one ORDERS by Order object
  @Update(sql="delete from ORDERS where ORDER_ID = :order_id")
  int deleteOrder(Order o);

public void removeLineItem(int customerId,int productId )
throws CustomerDoesNotExistException, OrderNotOpenException,
       ProductDoesNotExistException, NoLineItemsException,
       GeneralPersistenceException {
    db.inTransaction
    {
        //Code to retrieve and determine object can be deleted
        db.update(
            "DELETE FROM LINE_ITEM
             WHERE PRODUCT_ID = ${productId} AND
                   ORDER_ID = ${customer.openOrder.orderId}"
        );
    }
}

The Data API enables you to invoke stored procedures using the call statement, much like you can call any other SQL statement. Listing 9.23 shows an example.

Connection con = DriverManager.getConnection(...);
Data db = DataFactory.getData(con);

StoredProcedureResult spr = db.call(
    "CALL shipOrder(?)", orderId
);
spr.close();

This same method is used to call stored procedures in Project Zero.

You can also cause a method to be generated using the special @Call annotation, as shown in Listing 9.24.

@Call(sql="CALL CALL shipOrder(?)")
public StoredProcedureResult shipOrder(int orderId);

You then use this method in your application code, effectively separating the SQL from the code that uses it. The Manager API can be used to call stored procedures in a similar fashion.

The Data API in pureQuery has an updateMany method that enables you to send a batch of statements to the database. Listing 9.25 shows an example of passing an ArrayList of LineItems into the updateMany statement. Project Zero’s DataManager API has a version of this method as well.

ArrayList<LineItem>  lineItems = //
rowsAffected = data.updateMany(
    "INSERT INTO lineItems (LINE_ITEM_ID, PRODUCT_ID,  QUANTITY)
     VALUES (:lineItemId, :productId, :quantity)",
    lineItems
);

There are various ways of extending pureQuery. Project Zero is an example of extending pureQuery to support various additional patterns. One of the most common ways to extend pureQuery is with ResultHandlers and RowFactories. Figure 9.6 shows the relationship between the Data Interface and the handlers.

Figure 9.6. Extending the handlers.

The most common scenario is to write your own handlers to provide custom result formats. For example, pureQuery provides result handlers to return specialized results. Listing 9.26 shows an example of using a JSONResultHandler to create a custom JSON result handler. Notice that you have to pass the ResultHandler into the generic query method.

//code that uses the custom handler
String json = d.query(
    "select * from Customer", new JSONResultHandler()
);
...
//Definition of Handler...
class JSONResultHandler implements ResultHandler
{
    String handle(ResultSet rs)
    {
        sb.append("{");
        for (int x=1; x<=cols; x++) {
          sb.append("""+ m.getColumnName(x) +""="");
          sb.append(rs.getString(x) +""");
          if (x<cols) sb.append(",");
          }
        sb.append("}");
    }
}

You can also use a RowHandler to create custom results a row at a time within the context of the queryFirst method, as shown in Listing 9.27. Unlike a ResultHandler, which gets passed to the generic query method, The RowHandler can be passed into the various query methods, such as queryFirst or queryList. After the RowHandler, you can add any of the inputs necessary, like the hard coded 100 in Listing 9.27.

CustomClass myCustCls = data.queryFirst(
    "SELECT column1,column2 FROM sometable WHERE id=?",
    new RowHandler() {
        public CustomClass createNext(
            ResultSet resultSet, CustomClass obj
        )
        {
            CustomClass custom = new CustomClass();
            custom.customProp1 = resultSet.getString(1);
            custom.customProp2 = resultSet.getDate(2);
            return custom;
        }
    },
    100
);

The Data API throws an unchecked DataRuntimeException for most errors. This exception is shown in class diagram notation in Figure 9.7.

Figure 9.7. Exception class.

Class names that match table names do not need to be mapped. However, pureQuery provides Java Annotations to override default mappings. The annotations are very similar to those of the JPA specification. Listing 9.28 shows the use of the @Table annotation.

@Table(name="PRODUCT", schema="APP")
public class Product implements Serializable {
    ...
}

pureQuery can populate objects that are part of an inheritance hierarchy based on a query; however, you have to determine the type of mapping as described in Chapter 3. There is no specific support for a particular inheritance mapping. Listing 9.29 shows the AbstractCustomer and ResidentialCustomer used in our example, using the @Table annotation to map both to the same table. The listing shows how you would map using the single table inheritance style. Listing 9.30 shows how you would populate the Objects.

@Table(name="CUSTOMER")
public abstract class AbstractCustomer implements Serializable {

    ...
}

@Table(name="CUSTOMER")
public class ResidentialCustomer
extends AbstractCustomer implements Serializable {
    ...
}

In Listing 9.30, we first query the type to check what instance to populate when we load a Customer from the database.

public AbstractCustomer loadCustomer(int customerId)
throws CustomerDoesNotExistException {
    String customerQuery = "
        SELECT
            c.CUSTOMER_ID, c.NAME, c.BUSINESS_VOLUME_DISCOUNT,
            c.BUSINESS_PARTNER, c.BUSINESS_DESCRIPTION,
            c.RESIDENTIAL_HOUSEHOLD_SIZE,
            c.RESIDENTIAL_FREQUENT_CUSTOMER, c.TYPE,
            o.ORDER_ID, o.STATUS, o.TOTAL,
            l.PRODUCT_ID, l.QUANTITY, l.AMOUNT,
            p.PRICE,p.DESCRIPTION
        FROM CUSTOMER c
        LEFT OUTER JOIN ORDERS o ON c.OPEN_ORDER = o.ORDER_ID
        LEFT OUTER JOIN (
            LINE_ITEM l JOIN PRODUCT p ON l.PRODUCT_ID = p.PRODUCT_ID
        ) ON o.ORDER_ID = l.ORDER_ID
        WHERE c.CUSTOMER_ID = ${customerId}
    ";

    def typeRow = db.queryFirst(
        "SELECT type FROM CUSTOMER c
         WHERE c.CUSTOMER_ID = ${customerId}
        "
    );
    AbstractCustomer customer = null;
    if(typeRow == null)
        throw new CustomerDoesNotExistException();
    else if (typeRow.type.equals(CustomerType.RESIDENTAL.toString())) {
        customer = db.query(
            customerQuery,
            new JoinResultHandler(ResidentialCustomer)
        ).get(0);
    else if (typeRow.type.equals(CustomerType.BUSINESS.toString()))
        customer = db.query(
            customerQuery,
            new JoinResultHandler(BusinessCustomer)
        ).get(0);
    else
        throw new CustomerDoesNotExistException();

    if (customer == null)
        throw new CustomerDoesNotExistException();

    return customer;
}

Using this technique, we must issue an extra query each time we load an object. Another technique is to write your own RowHandler and check the type first as you traverse the ResultSet. We leave this as an exercise to the interested reader using similar techniques as described in the section “Extending the Framework.”

Because pureQuery exposes SQL, you do not need to explicitly create keys; however, pureQuery provides the @Id annotation for use in your POJO class, as shown in Listing 9.31. The pureQuery tools do not make use of it directly, but extensions can make use of it for optimizations. We show an example in the “Relationships” section.

@Table(name="PRODUCT", schema="APP")
public class Product implements Serializable {
    private static final long serialVersionUID = 2435504714077372968L;

    @Id
    @Column(name="PRODUCT_ID")
    protected int productId;

In Project Zero, there is a specialized insert method that extends the pureQuery support for keys. In cases in which tables generate the key, the insert method will return that key to the program. Listing 9.32 shows an example of this.

newOrder.setStatus(Order.Status.OPEN.toString());
newOrder.setTotal(new BigDecimal(0));
newOrder.setCustomer(customer);
def orderId = db.insert(
    "INSERT INTO ORDERS (STATUS,TOTAL,CUSTOMER_ID)
     VALUES (
        ${Order.Status.OPEN.toString()},
        ${new BigDecimal(0)},
        ${customerId}
     )",
    ['ORDER_ID']
);
newOrder.setOrderId(orderId.intValue());

You can use the @Column annotation to map attributes to columns. Listing 9.33 shows this pattern.

@Table(name="PRODUCT", schema="APP")
public class Product implements Serializable {
    private static final long serialVersionUID = 2435504714077372968L;
    @Id
    @Column(name="PRODUCT_ID")
    protected int productId;
    @Column(name="PRICE")
    protected BigDecimal price;
    @Column(name="DESCRIPTION")
    protected String description;
    //setters and getters

There is no direct support for contained objects. However, you can easily populate a contained object using a RowHandler.

The pureQuery runtime currently does not support populating an object graph; however, the Project Zero extension does. In Listing 9.34, you can see the use of the special @Join annotation and JoinResultHandler to populate Object graphs from joins.

@Table(name="ORDERS")
public class Order implements Serializable {
    private static final long serialVersionUID = 7779370942277849463L;
    @Id
    @Column(name="ORDER_ID")
    protected int orderId;
    protected BigDecimal total;

    public static enum Status { OPEN, SUBMITTED, CLOSED }
    protected String status;
    protected AbstractCustomer customer;
    @Join
    protected Set<LineItem> lineitems;

After you have defined which fields you want to Join, you can issue a query and pass in the JoinResultHandler, as shown in Listing 9.35. The JoinResultHandler will use the @Id annotation of the related class to determine the foreign key.

String customerQuery = "
    SELECT
        c.CUSTOMER_ID, c.NAME, c.BUSINESS_VOLUME_DISCOUNT,
        c.BUSINESS_PARTNER, c.BUSINESS_DESCRIPTION,
        c.RESIDENTIAL_HOUSEHOLD_SIZE,
        c.RESIDENTIAL_FREQUENT_CUSTOMER, c.TYPE,
        o.ORDER_ID, o.STATUS, o.TOTAL,
        l.PRODUCT_ID, l.QUANTITY, l.AMOUNT,
        p.PRICE, p.DESCRIPTION
    FROM CUSTOMER c
    LEFT OUTER JOIN ORDERS o ON c.OPEN_ORDER = o.ORDER_ID
    LEFT OUTER JOIN (
        LINE_ITEM l JOIN PRODUCT p ON  l.PRODUCT_ID = p.PRODUCT_ID
    ) ON o.ORDER_ID = l.ORDER_ID
    WHERE c.CUSTOMER_ID = ${customerId}
";
AbstractCustomer customer = db.query(
    customerQuery,
    new JoinResultHandler(ResidentialCustomer)
).get(0);

Constraints in a full ORM framework are hints to the persistence framework from metadata specified in various ways. These hints are often used to either (or both) generate application code to enforce the constraints, or to generate DDL with constraints in the underlying datastore. Because you deal directly with SQL in pureQuery and Project Zero, they do not have any special meta data considerations for constraints beyond those already discussed.

And because all objects are “disconnected” in pureQuery and Project Zero, there is no special transient field. You can calculate any field with SQL functions or custom Java logic associated with a get method—depending on which is most natural to the application.

Using the Annotated method of development, you can choose to generate a static SQL implementation of the services. Using the pureQuery API with the JCC DB2 drivers, the static SQL is stored in the DB2 catalog. Figure 9.8 shows the benefits of using static SQL optimization.

Figure 9.8. Dynamic versus static SQL.

It is worth noting that similar performance enhancements can be achieved with dynamic SQL and a prepared statement cache solution. However, static SQL can be significantly faster because the optimization happens at compile time and can create elaborate plans for processing the associated statements on the database. A PreparedStatement cache for dynamic SQL is somewhat slower, because the plan must be re-created during the first invocation or whenever the cache is invalidated. In addition, the memory requirements in the application and the database to maintain the cache can be significant, especially if there are a huge number of queries to be cached. Table 9.2 gives some more details to consider between static SQL and dynamic SQL.

You can use an external cache like ObjectGrid to cache resultsets and write a customer handler. In Project Zero, you can cache data inside the Global Context.

In the relationship section, we showed the use of the @Join annotation to load related objects “eagerly” as part of your query. Judicious use of this feature, balancing it with lazy loading approaches, is important to your application performance.

Locking can be achieved through the underlying JDBC or database settings concerning isolation levels. There is no explicit support for this in pureQuery or Project Zero.

In this section, we show the domain objects in our common example. We have used some of the pureQuery annotations to map attributes to tables. Next, we show our domain objects. The domain objects themselves are still Java objects. At the time we wrote and reviewed the bulk of this chapter, Project Zero was based on Groovy 1.0, so no Java Annotations were available. Project Zero now supports Groovy 1.5, which allows for Java Annotations.

It is worth saying that you may not choose to implement a Domain Model this way in Project Zero because the goal for Web 2.0-based applications is to create RESTful services. Project Zero provides the capability to query directly into JSON objects that can be consumed by AJAX style browser applications. We kept the example the same for the evaluation purposes. We show a short listing of the Zero Resource Model as a “forward look” in the Summary section.

For now, Listing 9.36 shows the AbstractCustomer class. Notice the use of the annotations to substitute names. You will also notice we use the @Join annotation for the current order that will allow a Join statement to populate the object graph correctly.

@Table(name="CUSTOMER")
public abstract class AbstractCustomer implements Serializable {

    @Id
    @Column(name="CUSTOMER_ID")
    protected int customerId;
    @Column(name="NAME")
    protected String name;
    @Column(name="TYPE")
    protected String type;
    @Join
    protected Order openOrder;
    protected Set<Order> orders;

    //Insert setters and getters

The ResidentialCustomer class is shown in Listing 9.37. We annotate the table name to Customer.

@Table(name="CUSTOMER")
public class ResidentialCustomer
extends AbstractCustomer implements Serializable {
    @Column(name="RESIDENTIAL_HOUSEHOLD_SIZE")
    protected short householdSize;
    @Column(name="RESIDENTIAL_FREQUENT_CUSTOMER")
    protected boolean frequentCustomer;

    //Insert setters and getters

Similarly, Listing 9.38 shows the BusinessCustomer class.

@Table(name="CUSTOMER")
public class BusinessCustomer
extends AbstractCustomer implements Serializable {
    @Column(name="BUSINESS_VOLUME_DISCOUNT")
    protected boolean volumeDiscount;
    @Column(name="BUSINESS_PARTNER")
    protected boolean businessPartner;
    @Column(name="BUSINESS_DESCRIPTION")
    protected String description;

    //Insert setters and getters

The Order class is shown in Listing 9.39. You will notice we use the @Join annotation for the collection of LineItems. The @Join annotation can be used on collections as well as single valued objects.

@Table(name="ORDERS")
public class Order implements Serializable {
    @Id
    @Column(name="ORDER_ID")
    protected int orderId;
    protected BigDecimal total;
    public static enum Status { OPEN, SUBMITTED, CLOSED }
    protected String status;
    protected AbstractCustomer customer;
    @Join
    protected Set<LineItem> lineitems;

    //Insert setters and getters

The LineItem object is shown in Listing 9.40. It uses the substitution annotations in the same way as the other objects. It also uses the @Join annotation to load the Product.

@Table(name="LINE_ITEM")
public class LineItem implements Serializable {
    @Id
    @Column(name="ORDER_ID")
    protected int orderId;
    @Id
    @Column(name="PRODUCT_ID")
    protected int productId;
    @Column(name="QUANTITY")
    protected long quantity;
    @Column(name="AMOUNT")
    protected BigDecimal amount;
    @Join
    protected Product product;

    //Insert setters and getters

Listing 9.41 shows the Product object for our example.

@Table(name="PRODUCT")
public class Product implements Serializable {
    @Id
    @Column(name="PRODUCT_ID")
    protected int productId;
    @Column(name="PRICE")
    protected BigDecimal price;
    @Column(name="DESCRIPTION")
    protected String description;

    //Insert setters and getters

The Service Implementation is a Groovy class that implements the common Java Interface we used throughout the book. The code in Listing 9.42 assumes you have created an instance of the Zero Manager for the class using the configuration shown earlier in Listing 9.3 and code shown in Listing 9.4.

public AbstractCustomer loadCustomer(int customerId)
throws CustomerDoesNotExistException {
    def typeRow = db.queryFirst(
        "SELECT type FROM CUSTOMER c WHERE c.CUSTOMER_ID = ${customerId}"
    );
    String customerQuery =
        "SELECT c.CUSTOMER_ID, c.NAME, c.BUSINESS_VOLUME_DISCOUNT,
             c.BUSINESS_PARTNER, c.BUSINESS_DESCRIPTION,
             c.RESIDENTIAL_HOUSEHOLD_SIZE,
             c.RESIDENTIAL_FREQUENT_CUSTOMER, c.TYPE,
             o.ORDER_ID, o.STATUS, o.TOTAL,
             l.PRODUCT_ID, l.QUANTITY, l.AMOUNT,
             p.PRICE, p.DESCRIPTION
         FROM CUSTOMER c
         LEFT OUTER JOIN ORDERS o ON c.OPEN_ORDER = o.ORDER_ID
         LEFT OUTER JOIN (
             LINE_ITEM l JOIN PRODUCT p ON  l.PRODUCT_ID = p.PRODUCT_ID
         ) ON o.ORDER_ID = l.ORDER_ID
         WHERE c.CUSTOMER_ID = ${customerId}
    ";
        AbstractCustomer customer = null;
        if(typeRow == null)
            throw new CustomerDoesNotExistException();
        else if (typeRow.type.equals(
                    CustomerType.RESIDENTAL.toString()))
                 customer = db.query(
                     customerQuery,
                     new JoinResultHandler(ResidentialCustomer)
                 ).get(0);
        else if (typeRow.type.equals(CustomerType.BUSINESS.toString()))
                 customer = db.query(
                     customerQuery,
                     new JoinResultHandler(BusinessCustomer)
                 ).get(0);
        else
            throw new CustomerDoesNotExistException();

        if (customer == null)
            throw new CustomerDoesNotExistException();
        return customer;
    }

public Order openOrder(int customerId)
throws CustomerDoesNotExistException, OrderAlreadyOpenException {
    Order newOrder = new Order();
    db.inTransaction
    {
        AbstractCustomer customer = loadCustomer(customerId);
        if (customer.getOpenOrder() != null )
            throw new OrderAlreadyOpenException();

        newOrder.setStatus(Order.Status.OPEN.toString());
        newOrder.setTotal(new BigDecimal(0));
        newOrder.setCustomer(customer);
        def orderId = db.insert(
            "INSERT INTO ORDERS (STATUS,TOTAL,CUSTOMER_ID)
              VALUES (
                 ${Order.Status.OPEN.toString()},
                 ${new BigDecimal(0)},${customerId}
              )
            ", ['ORDER_ID']
        );
        newOrder.setOrderId(orderId.intValue());
        db.update(
            "UPDATE CUSTOMER SET OPEN_ORDER = ${newOrder.orderId}"
        );
    }
    return newOrder;
}

public LineItem addLineItem(
    int customerId, int productId, long quantity
)
throws CustomerDoesNotExistException,
       OrderNotOpenException,InvalidQuantityException,
       ProductDoesNotExistException {
    if(quantity <= 0 ) throw new InvalidQuantityException();
    LineItem li = null;
    db.inTransaction
    {
        Product product = db.queryFirst(
            "select * from Product where product_id = ${productId}",
            Product.class
        );
        if(product == null) throw new ProductDoesNotExistException();
        AbstractCustomer customer = loadCustomer(customerId);
        Order openOrder = customer.getOpenOrder();
        if (openOrder == null) throw new OrderNotOpenException();
        BigDecimal amount = product.getPrice().multiply(
            new BigDecimal(quantity)
        );
        db.update(
            "UPDATE ORDERS SET
                 TOTAL = ${amount.add(openOrder.getTotal())}"
        );
        LineItem el = db.queryFirst(
            "select * from LINE_ITEM
             where ORDER_ID = ${openOrder.orderId} and
                   PRODUCT_ID = ${productId}
            ", LineItem.class
        );
        if(el == null)
        {
            LineItem lineItem = new LineItem();
            lineItem.setOrderId(openOrder.getOrderId());
            lineItem.setProductId(product.getProductId());
            lineItem.setAmount(amount);
            lineItem.setProduct(product);
            lineItem.setQuantity(quantity);
            db.update(
                "INSERT INTO LINE_ITEM (
                     ORDER_ID,PRODUCT_ID, amount, quantity)
                 VALUES (
                     ${lineItem.orderId}, ${lineItem.productId},
                     ${lineItem.amount}, ${lineItem.quantity})"
            );
            li =  lineItem;
        }
        else {
            el.setQuantity(el.getQuantity()+ quantity);
            el.setAmount(el.getAmount().add(amount));
            db.update(
               "UPDATE LINE_ITEM SET
                   quantity = ${el.getQuantity()},
                   amount = ${el.getAmount()}"
            );
            li =  el;
        }
    }
    return li;
}

public void removeLineItem(int customerId,int productId )
throws CustomerDoesNotExistException, OrderNotOpenException,
       ProductDoesNotExistException, NoLineItemsException,
       GeneralPersistenceException {
    db.inTransaction
    {
        Product product = db.queryFirst(
            "select * from Product
             where product_id = ${productId} ", Product.class
        );
        if(product == null) throw new ProductDoesNotExistException();
        AbstractCustomer customer = loadCustomer(customerId);
        Order openOrder = customer.getOpenOrder();
        if (openOrder == null) throw new OrderNotOpenException();
        db.update(
            "DELETE FROM LINE_ITEM
             WHERE PRODUCT_ID = ${productId} AND
                   ORDER_ID = ${customer.openOrder.orderId}"
        );
    }
}

submitOrder

This service method changes the status of the order and removes it from the openOrder property of the abstract customer class. Listing 9.46 shows the implementation.

Example 9.46. submitOrder

<LINELENGTH>90</LINELENGTH>

public void submit(int customerId)
throws CustomerDoesNotExistException,
       OrderNotOpenException, NoLineItemsException {
    db.inTransaction
    {
        AbstractCustomer customer = loadCustomer(customerId);
        Order openOrder = customer.getOpenOrder();
        if (openOrder == null) throw new OrderNotOpenException();
        if (openOrder.getLineitems() == null ||
            openOrder.getLineitems().size() <= 0)
            throw new NoLineItemsException();
        db.update(
            "UPDATE ORDERS
             SET STATUS = ${Order.Status.SUBMITTED.toString()}
             where ORDER_ID = ${openOrder.orderId}"
        );
        db.update("UPDATE CUSTOMER SET OPEN_ORDER = NULL");
    };
}

Figure 9.12 shows the common exceptions. You can examine them in code form from the download.

Figure 9.12. Application exceptions.

Project Zero applications have a particular layout for their directory structure. This layout provides organization around the parts that make up your application and convention around how your application works. If you follow the conventions and structure defined by Project Zero, your application will be easier to construct and maintain, and you will have less configuration information to provide with the application. Figure 9.13 shows the basic layout of a Project Zero Module and folder structure. The Project Zero Website has a section explaining the complete details [Zero 7].

Figure 9.13. Project Zero layout.

In the config directory, you will find an ivy.xml file. Ivy is a build system from Apache that can ensure your application is using the right versions of the libraries you declare as dependencies in the config file. In Ivy, these libraries are called modules. A module represents an artifact, which is code packaged as either a JAR file or a Zero ZIP file. Zero then uses Ivy to find a module that matches each dependency you declared. Ivy can search the local repository that Zero creates on your system, or it can search remote repositories such as the Project Zero package repository. When a match is found, Ivy downloads the module’s artifact into your local repository as necessary and updates your application’s classpath to include the dependency. For more information on Ivy, see [Ivy]. Listing 9.47 shows the ivy.xml file for our example where we specify Zero packages and database drivers.

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<ivy-module version="1.3">
...    <!--removed package info in text-->
<dependencies>
  <dependency name="zero.core.webtools" org="zero" rev="1.0+"/>
  <dependency name="zero.core" org="zero" rev="1.0+"/>
  <dependency name="commons-dbcp" org="commons-dbcp" rev="1.2.1"/>
  <dependency name="commons-logging" org="commons-logging" rev="1.0.4"/>
  <dependency name="commons-pool" org="commons-pool" rev="1.3"/>
  <dependency name="derbyclient" org="org.apache.derby" rev="10.2.2+"/>
  <dependency name="zero.data" org="zero" rev="1.0+"/>
  <dependency name="junit" org="junit" rev="4.1"/>
  <dependency name="ant" org="ant" rev="1.6.5"/>
 </dependencies>
</ivy-module>

The other important configuration file is zero.config, which contains runtime properties stored in the Global Context. Listing 9.48 shows the zero.config we use to define the DataSource that the Zero Manager will use—it should look familiar to you as it is nearly identical to Listing 9.3.

# HTTP port (default is 8080)
[/app/http]
port=8080
# This config is for Apache Derby databases:

[/app/db/pie/config]
class=org.apache.derby.jdbc.ClientDataSource
serverName=localhost
portNumber=1527
databaseName=PWTE
connectionAttributes=create=true

We created a separate Project Zero application with specialized unit testing in a Web 2.0 environment. The Unit Test Project includes our application via the ivy.xml file. Figure 9.14 shows the layout of the Unit Test Zero application.

Figure 9.14. Unit Test case.

The source for our unit test is implemented in Groovy. It is the same common unit test we used to test the other frameworks examined in this book, except we used the Groovy extensions to JUnit instead of Java. Examine the downloadable source for details (see Appendix A, “Setting Up the Common Example”).

The Project Zero applications execute in a standalone VM. The goal is to just run the application. For testing, you can right-click on any Zero Project and run it as illustrated in Figure 9.15. In our case, you run the Unit Test Project, which in itself is a Project Zero application. As stated earlier, you can include Zero packages inside of another.

Figure 9.15. Running the test.

The persistence layer of a Project Zero application does not have any extra considerations for deployment. The Project Zero architecture allows you to override configurations, so you can provide an override for the Data Source definition as part of your deployment process.

The Project Zero tooling allows you to export applications to be copied for deployment.

You would use the command-line tools for Project Zero to run the application. As a matter of fact, Project Zero allows for pure command-line development of applications as well. The deployment process would just involve running the application using the command-line tools. See the Project Zero developer guide for how to use the command line features [Zero 8].