Overview

The pureQuery data access platform provides benefits to data access applications by enabling improved performance, improved developer productivity, improved management, and improved problem determination. The Resources section of this article includes references to help you understand the benefits of pureQuery. These benefits do not require the use of the pureQuery API, made possible by using a process called client optimization. However, the focus of this article series is on new development and the use of the pureQuery APIs, which you can use with the DB2 family, Informix, and Oracle databases.

This article covers the following topics related to pureQuery data access objects (DAOs), created using annotated methods:

• Description of pureQuery DAOs
• Motivation for writing pureQuery DAOs using annotated methods
• Steps for developing pureQuery annotated method data access objects
• Description of code generation and examples of generated code
• Requirements for defining annotated methods in a pureQuery data access object
• Use of pureQuery data access objects to execute SQL
• Introduction to a few select features of pureQuery DAOs:
  • Batching
  • Generated RowHandlers and ParameterHandlers
  • Generated keys
  • Use of XML configuration files to modify code generator output

If you are ready to start coding, skip to the technical breakdown. A brief example follows to show why developers might choose to use pureQuery data access objects.

Back to top

What are annotated method data access objects?

To introduce pureQuery data access objects, it helps to understand the background and use cases behind both inline and annotated methods.

Inline methods were developed in response to customer demand for quick, simple coding style that would be easy to learn by developers familiar with Java™ Database Connectivity (JDBC) -- only simpler and quicker to code. The inline style originally was targeted to reduce the repeated coding tasks familiar to the JDBC programmer, as well as to provide an API that tools could easily use to tie in data access development with Java development. The coding is referred to as "inline" because of the way SQL statements are defined in the application. With inline methods, SQL statements are declared or constructed at runtime, and passed as instances of String to common Data interface methods. Inline methods maximize coding speed and development flexibility, and supports dynamic execution. An overview of the common Data interface APIs are included in the second article in this series, and you can find more information in the pureQuery documentation (see Resources).

This article focuses on data access objects created using annotated methods, which evolved with the additional goal of maximizing configurability and security for the resulting pureQuery application. pureQuery data access objects using annotated methods are designed specifically to support both dynamic and static database access. They were developed in response to customer demand for a named query programming interface for data access that was similar to Java Persistence API (JPA) -- only simpler, quicker to code, and capable of supporting static execution when required.

As with inline methods, annotated methods are rooted in the way SQL statements are defined in the application. With annotated methods, the SQL string is defined as an element of a Java 5.0, pureQuery Annotation. The method annotations defined by pureQuery for this purpose are @Select (which annotates SQL queries), @Update (which annotates SQL DML statements), and @Call (which annotates SQL CALL statements). The annotations are placed on user-defined method declarations within a user-defined interface. A code generator pre-processes the interface to generate implementation code for each declared, annotated method. The generated implementation of the resulting data access object executes the SQL statements defined in the annotations using the pureQuery runtime. Pre-defining the SQL string in an annotation element simplifies static execution support.

Optim Development Studio tooling support for pureQuery data access objects includes a code generator which creates implementations of annotated methods written by the user. The result of code generation is a DAO implementation class which is compiled and used to execute the SQL statements declared in the original interface. Figure 1 illustrates the relationship between the user-defined data access interface, the code generator, and the generated implementation class.

Back to top

A motivating example

This section introduces a ficitonal example: the data access development team for Silver Castles, a growing company which sells a variety of silver products. The company is developing a new web-based storefront on the Silver Castles website and has decided to use the pureQuery environment to develop their data acces application's persistence layer. It was an easy decision to use pureQuery once they ran through the pureQuery tutorial for themselves. They learned how easy it could be to quickly develop, test, and deploy a data-access, Java-based persistence layer targeted for multiple platforms using pureQuery data access objects. A few additional factors come into play as the fictional team decides when to create data access objects, and when to use the pureQuery built-in inline methods. One of the developers named Bob decides to become an expert on creating pureQuery data access objects using annotated methods. A description follows of the knowledge he gathers to help the team decide when to use each solution.

The decision to create data access objects

Bob begins by assessing data access objects. After a few hours, he comes back to his team with a list of highlights that describe annotated methods.

The paradigm:

• Follows the named-query style, targeted to match or exceed the capabilities of existing object-relational mappers and persistence solutions
• Encourages separation of persistence layer (CRUD) statements from the rest of the application inside the data access object. The result is that development changes to the SQL statements are not scattered throughout the application and will not impact developers working on other layers. The annotated interface that defines the data access object provides a central control point to manage the SQL statements for the entire application.
• SQL statement text is known at development time. It can be analyzed ahead of time, allowing developers to optimize assignment to output objects.

Static support:

• pureQuery data access objects make the decision to execute static SQL a deployment option rather than a design option. Annotated method data access objects can either be deployed dynamically or by using static SQL packages on DB2 Data Servers without a single line of code change
• Transparent transition to static execution from the perspective of application developers and front-end applications
• A decision to deploy for static execution obtains the universal benefits of static SQL: security, performance, monitoring, and pre-runtime optimization

Code generation:

• Minimal handwriting of code: interface method signatures and the SQL statement are declared in the data access object interface definition; then the pureQuery code generator produces the data access code implementation
• Query results are returned pre-processed by the pureQuery runtime into a wide choice of objects, such as Iterators of populated pureQuery beans, or Lists of column name to column value Maps
• Customize behavior of generated code using a Hook for specialized pre- and post- data access processing
• Use XML configuration files to define different SQL for each target database

Back to top

Developing and using pureQuery data access objects

Listing 1 shows part of a pureQuery interface Bob writes to practice writing annotated methods for data access objects. He uses the interface to explain the fundamentals of the annotated method style to the team. The pureQuery annotated interface is the building block for each data access object. A pureQuery interface consists of the following:

• A Java interface definition containing:
  • One or more method declarations.
  • A pureQuery data access annotation for each method declaration - @Select, @Update, or @Call. Each annotation includes an SQL element containing the SQL statement to execute when the method is called. (The SQL statement can optionally be provided separately in a configuration file instead of in an annotation element.)
  • A Java return type for each method declaration which indicates the desired object format or type for the pureQuery runtime to return data access results to the caller.
  • Java parameter types for each method signature, including parameters indicating the types for parameters used to execute the SQL statement.

The interface definition file contains no other method declarations or definitions other than those described above. Once the pureQuery data access interface is defined, other layers of the application can access data using the DAO simply by calling the declared Java data access methods. When another application layer calls one of the annotated methods, it receives the result of executing the associated SQL statement in a conveniently formatted Java object. No knowledge of SQL is required to develop the other application layers.

                
package com.ibm.db2.pureQuery; 

import java.util.Iterator;
import com.ibm.pdq.annotation.Select; 

public interface CustomerData {
   // Select all PDQ_SC.CUSTOMERs and populate Customer beans with results
   @Select(sql="select CID, NAME, COUNTRY, STREET, CITY, PROVINCE, ZIP, PHONE,
                      INFO from PDQ_SC.CUSTOMER")
   Iterator<Customer> getCustomers();

   // Select PDQ_SC.CUSTOMER by parameters and populate Customer bean with results
   @Select(sql="select CID, NAME, COUNTRY, STREET, CITY, PROVINCE, ZIP, PHONE, 
                      INFO from PDQ_SC.CUSTOMER where CID = ?") 
   Customer getCustomer(int cid); 
  ...
}

Generating an implementation

Once the interface above is defined, it is automatically fed to the pureQuery code generator by pureQuery project tooling in Optim Development Studio. The pureQuery code generator produces an implementation of the interface in a file named CustomerDataImpl.java. For the reader's information, the generated file is available in Listing 2. Note: None of the code in the generated file (Listing 2) is handwritten by a developer.

It is not necessary to examine the generated code. If you would rather see how the Silver Castle application uses the interface above, skip to the next step. Continue to read to learn more about the generated code.

The code generator and generated code

The code generator takes user-defined pureQuery data access interface as input, such as the simple CustomerData defined by the SilverCastles developers. It generates the code required to execute each SQL statement annotated for each method in the interface. It also generates code to process the results into the declared result types. The generated code calls the pureQuery runtime to perform the processing required for each method.

A few important notes about the generated code:

• Generated elements: For each declared method, the generated elements include the method definition, an internal pureQuery StatementDescriptor, a generated RowHandler or ResultHandler, as required, and an internal pureQuery ParameterHandler, as required.
• Implementation class name: The name of the generated implementation file is based on the name of the original user-defined interface, with "Impl" appended at the end of the name. In this example, the developers wrote the CustomerData interface, and the code generator produced the CustomerDataImpl class. The generated class implements the CustomerData interface. The implementation class name is never used by other layers of the data access application; they always reference and use the user-defined data access interface. However, it's useful to know the name of the implementation file in case the generated code needs to be examined.
• Implementation superclass: In addition to implementing the user-defined interfacee, the generated implementation class extends the internal pureQuery class BaseData, which in turn implemnents the external Data interface. This superclass is part of the pureQuery runtime and handles the rote, repeated operations required to access a database and process results.
• Don't get confused looking at the generated code. Developers never need look at it unless they choose to.

Using pureQuery data access objects

At this point, the project has been built, Optim Development Studio pureQuery tooling has invoked the pureQuery code generator, and the interface implementation has been generated and compiled. The developers are ready to access data from other layers of the application by calling the annotated methods declared in the pureQuery data access interface. This process is simple. The application instantiates an instance of the pureQuery interface (the conceptual database) with a request to the DataFactory. It then calls the data access methods, as follows:

                
...
java.sql.Connection con = ...;

// use the DataFactory to instantiate the CustomerData interface
CustomerData cd = DataFactory.getData(CustomerData.class, con);

// execute the SQL for getCustomers() and get the results in Customer beans
Iterator<Customer> cust = cd.getCustomers();

// the application can now consume the Iterator of Customer beans
...
  

With a few lines of code, the database is accessed, the desired SQL statement is executed, and the results are processed into a convenient Iterator of data beans of class Customer, ready to be consumed by the application. What is even more impressive is that even to implement the persistence layer method, the developers still had no more work to do than to declare the method called here. The pureQuery code generator and runtime handled the rest.

Back to top

Breaking down the pureQuery data access object

This section breaks down the example into the important elements of the data access object. The pureQuery documentation contains complete coverage of these and other pureQuery coding concepts (see Resources).

The pureQuery data access interface

In the example above, the user-defined pureQuery interface is named CustomerData. It is helpful, as in the Silver Castles example, to name the interface to reflect the information source it represents. For example, CustomerData methods retrieve and update information about the company's customers.

As described above, the pureQuery interface contains only annotated method declarations.

Annotated method declarations

Each method declaration in the pureQuery interface contains the following required elements:

• One of three pureQuery annotations: @Select, @Update, or @Call
• One sql=<string> element for each annotation, where the string contains a valid SQL statement to be executed when the method is called (optionally, the SQL statement can be provided in an XML configuration file instead)
• A standard Java method declaration

Declared return types

The return type of the method declaration indicates in what object format the results of the SQL statement will be returned. Supported return types vary depending on the annotation used. For example, a variety of collection and simple types are returned from queries. Standard update count formats are available for updates. And among others, a StoredProcedureResult return type conveniently encapsulates the results of a stored procedure call.

Results can also be processed by the pureQuery engine into a collection of user-defined pureQuery beans. In our example, the developers declare that an Iterator of Customer beans should be returned. pureQuery uses a set of bean conventions and requirements to map database query results directly to the user-defined bean class. Annotations in pureQuery beans can override the default mapping behavior. Also, manual mapping of results into beans can be performed by a user-provided RowHandler. Otherwise, the generated RowHandler automatically carries out default mapping to the user-defined bean class, prior to returning bean results to the caller.

Declared parameter types

The parameter types in the annotated method declaration determine how pureQuery obtains SQL statement parameter values at runtime. There can be, but there is not necessarily, a one-to-one mapping of parameter markers in the SQL statement to declared parameters in the annotated method. pureQuery follows the rules for parameter marker syntax to determine how SQL statement parameters are mapped from the declared parameters in the annotated method's parameter list.

Parameters can more easily take on a wide variety of types in pureQuery than in other data access APIs. For example, a single declared pureQuery bean parameter can potentially provide runtime values for several SQL statement parameter markers. pureQuery beans and Java collection types are supported for pureQuery annotated methods. View the annotated method syntax diagram for a complete listing of possible declared parameter types. The declared parameter type for an annotated method can make a significant difference in how the SQL statement is executed. For example, batch updates can be initiated by the use of certain parameter types in an @Update annotated method. For batch updates, a collection parameter is used vertically to execute an SQL statement more than once, with different parameter values each time.

Annotated method parameters can be used in a straightforward, simple way, or they can be assigned to take advantage of specific processing capabilities of the pureQuery engine. Batch update processing is one example of the underlying power of the pureQuery engine to automatically optimize code and streamline development effort. Specialized pureQuery processing for data bean parameters also saves development time and efforts. For example, the @GeneratedKey pureQuery bean annotation allows the pureQuery engine to automatically update a bean parameter's field with a database-generated value following an insert or update operation.

Hooks

Some of the pureQuery development team is initially wary of the fact that most code is generated using annotated method style. Bob explains that a Hook provides an easy way to conduct specialized processing during execution of generated code. Rather than hand code method calls to surround each application call to an annotated interface methods, another option is to define a Hook to register with the annotated interface. The Hook is called back by the pureQuery runtime at entry to and exit from each annotated method. This provides a callback mechanism to surround generated data access code with specialized processing. A Hook defines the required pre() method which, when the Hook is registered, is called immediately upon entrance to an annotated method. A Hook will also define a post() method which, when the hook is registered, will be called immediately before control is returned from an annotated method.

Hook methods provide context awareness for specialized runtime callback processing in case it is needed by the user-defined implementation of pre() and post(). This awareness is enabled by the parameters on the pre() and post() method calls. Special processing can be designed to vary according to the values of these parameters. For example, processing may vary depending on the name of the interface method calling the Hook method, the runtime parameter values provided to that method, or the SQL statement type which the method executes. A handle to the pureQuery interface with which the Hook is registered is also available, in the form of a Data parameter. Each of these values is available for examination and modification by the implementer of Hook. In addition, the return value is available to the implementer of post() before it is returned to the caller. Here an example of Hook processing code Bob wrote as a demonstration for the Silver Castles team:

                
public static class TrackingHook implements Hook {

   public void pre(String methodName, Data objectInstance,
                    SqlStatementType sqlStatementType, Object... parameters)  {
      System.out.println(methodName + "**Customer data has been accessed**"); 
   }

   public void post(String methodName, Data objectInstance,
                    Object returnValue, SqlStatementType sqlStatementType,
                    Object... parameters) {
     // do nothing
  }
}
  

Now that specialized processing has been defined using Hook, the developers ensure it will run by registering an instance of their Hook with the interface upon instantiated. Listing 5 shows how to register a Hook:

                
... 
Connection con = ...;

// use the DataFactory to instantiate the interface and
// provide an instance of Hook to be registered with the instance
   
CustomerData cd = DataFactory.getData(CustomerData.class, con, new TrackingHook());

// execute the SQL for getCustomers() and get the results,
// the pre() and post() methods are automatically called
Iterator<Customer> cust = cd.getCustomers();

// the application now consumes the Iterator of Customer beans 
...

This is a very simple example of specialized processing with Hook. See the Hook example in the pureQuery documentation for a more complex example of the type of processing Hook can be used to implement.

Developing for multiple targets

The final feature of annotated methods Bob explains to his team is the use of an XML configuration file to completely separate SQL statements from Java code implementation of their application. This provides the ability to avoid re-coding a Java application when it is deployed against a target database which requires different SQL statements. For example, if the team wants to deploy the same application against a legacy data source with a slightly different schema, they won't need to re-code their annotated interface or pureQuery bean.

Listing 6 shows the SQL statement for the original schema.

                
select CID, NAME, COUNTRY, STREET, CITY, PROVINCE, ZIP, PHONE, INFO
     from PDQ_SC.CUSTOMER

Listing 7 shows the SQL statement for the legacy schema.

                
select CUSTID, NAME, COUNTRY, STREET, CITY, PROV, ZIP, PHONE, INFO
     from PDQ_SC.CUSTOMER
  

Note that in Listing 7, the names of the CID and PROVINCE columns become CUSTID and PROV. This changes the SQL statement required to issue the query, and it changes the default mapping of results to the Customer data bean.

Listing 8 shows the customer pureQuery bean.

                
package com.ibm.db2.pureQuery;

public class Customer  {

     // Class variables
     protected int cid;
     protected String name;
     protected String country;
     protected String street;
     protected String city;
     protected String province;
     protected String zip;
     protected String phone;
     protected String info;
...

Instead of writing a new annotated interface or Customer bean class to support the legacy schema, the team uses an XML configuration file to provide additional input to the generator to support the legacy schema. A fragment of the XML configuration file is listed below, showing how one of the SQL strings and the user-defined bean class mapping are overridden. To prepare to deploy their application against the legacy system, the team provides their original annotated interface definition to the generator along with the XML file below. The generator produces correct generated code to deploy against the legacy database:

                
<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings xmlns="http://java.sun.com/xml/ns/persistence/orm">

<named-native-query name=" com.ibm.db2.pureQuery.CustomerData#getCustomers()">
  <query><![CDATA[select CUSTID, NAME, COUNTRY, STREET, CITY, PROV, ZIP, PHONE, INFO 
                     from PDQ_SC.CUSTOMER]]>
  </query>
</named-native-query>
...
<entity class="com com.ibm.db2.pureQuery.Customer">
        <attributes>    
                    <basic name="cid">
                      <column name="CUSTID" />
                    </basic>
                    ...                
                    <basic name="province">
                       <column name="PROV" />
                    </basic> 
                    ...           
        </attributes>
        </entity> 
</entity>
</entity-mappings>

For more information about providing XML configuration files to the generator to provide alternate SQL statements or object mappings for pureQuery interfaces, refer to the pureQuery online documentation.

Back to top

Summary

This article provided a high-level introduction to pureQuery data access objects using annotated methods, along with likely motivations for a development team to choose to code using pureQuery data access objects. It also outlines the basic steps required to develop an application that uses pureQuery data access objects. Selected features of the style were introduced.

If you are interested in learning more about developing using pureQuery data access objects, please follow the links throughout the article and in the Resources section to the pureQuery online documentation, additional articles, and helpful tutorials.

Resources

Learn

• Read the pureQuery documentation for a complete description of the pureQuery Runtime.
  
  
• Tutorial: Follow along step by step with the tutorial in the Integrated Data Management Information Center.
  
  
• See the video demo series based on the Integrated Data Management Center tutorial on how to create a JAva data access application using annotated methods.
  
  
• Visit the pureQuery platform page for an overview of the pureQuery platform, including the value it brings, which products it ships with, and an FAQ.
  
  
• Read how one large project made the decision to move to pureQuery from EJB2 in this series: "Migrate from EJB2 container-managed persistence to pureQuery for IBM Master Data Management Servery".
  
  
• The article "What's new and cool in Optim Development Studio 2.2" updates you on the latest enhancements since the original series above was written, including tooling support for accessing Oracle databases.
  
  
• Watch the video Blackbox/Whitebox pureQuery Made Simple to see a short introduction comparing pureQuery to other data access approaches.
  
  
• Optim family page on developerWorks: Learn more about Integrated Data Management. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  
• If you're interested in pureXML, read the article on "Handling pureXML data in Java applications using pureQuery" for an overview of the pureQuery platform, including what it is, which products it ships with, and an FAQ.
  
  

Get products and technologies

• pureQuery javadoc is available as part of the online documentation.
  
  
• Download and try IBM Optim Development Studio and pureQuery Runtime for a free 30-day trial.
  
  

Discuss

• Participate in the discussion forum.
  
  
• Participate in the Integrated Data Management team blog.
  
  
• Participate in the Integrated Data Management community space and find a comprehensive resource for demos, videos, articles, upcoming events, and more.
  
  

About the author

Heather Lamb is a developer on the pureQuery runtime team, in Silicon Valley.

Rate this article

Comments

Back to top

Table of contents

• Overview
• What are annotated method data access objects?
• A motivating example
• Developing and using pureQuery data access objects
• Breaking down the pureQuery data access object
• Summary
• Resources
• About the author
• Comments

Next steps from IBM

• Try: Data Studio
• Demo: Use Ajax with DB2
• Briefing: Virtual tech briefings on integrated data management
• Community: DB2
• Buy: Data Studio

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers