Despite the perky name, Java Database Connectivity (JDBC) programming methods can be somewhat less than stimulating. IBM is taking the drudgery out of the process with an integrated language that queries relational databases as well as Java collections and database caches.

Java is everywhere. If you're an application developer, there's a pretty high likelihood that you're developing in Java. If so, you know it's not always a glamorous task.

Using Java Database Connectivity (JDBC) to create an application requires a lot of steps, many of which are tiresome. Not only do you have to create a Java data access object layer, you also need to handle the CRUD (create, replace, update, delete) SQL operations. To do so, you typically have to work with two sets of tools: one set for Java development and another for database CRUD operations. Wouldn't it be easier if there were a single set of tools for both? IBM thinks so, and set out to create one. Called pureQuery (and previously code-named "JLINQ"), this new high-performance data access platform includes development tools, APIs, and advanced runtimes for Java applications. IBM's pureQuery is designed to simplify the tasks of developing and managing applications that access data. Its benefits cover the development, deployment, management, and governance stages of the application life cycle. (See Figure 1)

Figure 1. The pureQuery architecture overview. Click on image for larger view.

The pureQuery tools, APIs, and runtime environment provide access to data in databases and to in-memory Java objects. A component of the IBM Data Studio toolset, a common database application development and administration platform, pureQuery provides advanced Java data access capabilities and essentially eliminates the need for JDBC programming because it integrates the query language with Java.

THE PROBLEM WITH DATA ACCESS APIs

Java developers can choose from a wide spectrum of Java data access APIs to build their applications, from JDBC to the Java Persistence Architecture (JPA). These APIs can be categorized into two major camps: direct SQL access and managed objects. Each approach has its supporters and detractors. Figure 2 summarizes the benefits and drawbacks of each.

Figure 2. Two approaches to Java development.

Direct SQL access is well understood; it allows easy control over the issued SQL which, in turn, can improve performance. However, the JDBC development process can be tedious and time consuming. In the direct SQL access methodology, Java programmers have to map results sets from JDBC to Java objects and create statements and bind parameter markers. For optimal performance, developers must master the JDBC API and exploit features such as batching and result optimizing. To improve JDBC performance, some might consider SQLJ; however, this choice requires developers to rewrite the data access layer.

The tedium of JDBC development led to the creation of the object relational mapping (ORM) framework, which provides a data access abstraction layer. ORMs generally require less initial effort to create the data access layer. However, they provide limited control of the SQL issued from them, and they add another layer of complexity when diagnosing runtime performance issues. Tuning and diagnosis become more difficult because the developers no longer control what SQL gets sent to the database; as a result, it's difficult to change the SQL or determine which application issued it.

IBM created pureQuery to alleviate the limitations of these approaches. pureQuery provides a Java editor with SQL intelligence, but it isn't just another SQL-like language — it's a language that can declaratively query relational databases as well as Java collections and database caches. The extended Java editor includes an integrated SQL editor, giving developers the same level of code-completion, validation, and execution assistance for SQL as they have for Java. Additionally, IBM is working on enhancing its support for JPA with pureQuery.

INSIDE pureQUERY

pureQuery simplifies Java data access application development for data servers (with additional benefits for IBM data servers) and Java objects across the development life cycle (development, deployment, management, and problem determination). The pureQuery runtime works with all databases that have a JDBC driver and provides additional tools integration with all versions of DB2 and Informix Dynamic Server (IDS). You can choose to use pureQuery with JPA (a J2EE standard) or with popular open source projects such as Spring and iBatis. pureQuery is integrated with the recently announced Project Zero initiative (http://projectzero.org) — it's the underlying technology within zero.data (http://www.projectzero.org/ wiki/bin/view/Community/DataDesign). pureQuery technology brings SQL editing into the Java development environment.

To put it simply, pureQuery makes a lot of Java-related data access programming tasks a lot easier. It provides an environment that helps you find problems earlier in the development life cycle and tie them from the database back to the application source. You can use static and dynamic SQL via the same Java API; the support for static SQL improves runtime problem determination and governance. DB2 DBAs understand the many benefits of static SQL access in application performance, application management, and data access security (governance).

pureQuery simplifies and unifies the implementation of any kind of data access. It doesn't force you to use a specific API (such as EJB2, JPA, Spring, or iBatis), but it does facilitate the implementation of several existing APIs for accessing data. With pureQuery, you can write your data access code directly in Java. You get design-time syntax and schema checking, and better tools support (such as code completion/assist) for authoring your queries.

The IBM Data Studio solution automates many repetitive JDBC and object construction tasks. For example, pureQuery's SQL intelligence can help you complete a SQL statement right in the middle of the development process.

The SQL editor integration (shown in Figure 3) includes comprehensive support for semantic and syntactic validation through:

• Design time compilation
• Intelligent content assistance
• Syntax colorization.

Figure 3. SQL statement completion made easier because of the SQL intelligence built into pureQuery and IBM Data Studio.

Testing SQL directly within the Java editor improves productivity; you can quickly test the SQL against your database server and the errors are flagged, just as Java programming errors are.

With pureQuery, the SQL cycle can be more productive in a Java edit-compile-debug development cycle. You won't have to wait until runtime to know whether a query string is well-formed or will return an error. Figure 4 shows how pureQuery flags SQL errors in the Eclipse Java Perspective in the same manner that Java errors are highlighted.

This tool integration can boost Java programmer productivity by providing interactive, intelligent code assistance and immediate SQL error awareness as Java developers are writing the code. pureQuery also allows developers to query in-memory unmanaged objects. It can query the original objects on the heap without copies or storage. These objects could optionally be joined with the results from a data server query, because pureQuery uses fully standard SQL. For example, the query in Listing 1 joins two Java collections (Department and Purchase) using pureQuery's in-memory query feature.

Figure 4. Design-time SQL error identification.

Department[] depts = ...;
Employee[] emps = ...;
Data qoc = DataFactory.getData();
List < Employee > e = data.queryList(
"select e.firstname, e.lastname, e.empno, e.workdept from ?test.Employee as e, 
?test.Department as d where e.workdept = d.deptno", 
Employee.class, emps, depts);

Objects aren't managed, copied, duplicated or changed; they are accessed in memory (heap) where they already live. Note that the ? parameter marker is used to identify predicate values in the WHERE clause and to identify Java collections in the FROM clause.

pureQuery uses regular SQL to query in-memory objects. This capability can provide a cache of commonly accessed fixed data sets such as a lookup table for the states within the U.S., which can then be joined with other data to display the state's full name and avoid an unnecessary roundtrip access to the database server.

Another interesting pureQuery feature is the single API for both Java static and dynamic SQL data access. Previously, JDBC provided dynamic SQL data access and SQLJ provided static SQL data access. The support for static SQL in pureQuery can lead to enormous benefits for your application — improved runtime performance, easier identification of problems and code errors, and better management capabilities. Static SQL improves manageability by providing the ability to monitor the static SQL packages. Static SQL not only provides the fastest database access method, but it also allows developers to lock in data access paths that can provide more predictable performance (very important for meeting service level guarantees to the end user). Finally, static SQL provides a stronger authorization model. At deployment time, the same pureQuery code can be switched between dynamic and static SQL via a runtime configuration, which allows developers to build their applications with dynamic SQL at development time and switch to static SQL at deployment time without changing their code.

DATA WEB SERVICES

The IBM Data Studio solution includes much more than pureQuery. Another new feature, Data Web Services, provides the capability to generate Web service access to data without writing a single line of code. With Data Web Services, queries (SQL or XQuery) and stored procedures are made available as Web services; these services can form the basis of a service-oriented architecture (SOA) solution.

More and more companies are adopting SOA for their applications and moving the underlying components toward loosely coupled, services-based access. On the data access side, over the years companies have encapsulated or enforced a lot of business logic via stored procedures, triggers, and so on. The capability to take these data access operations and underlying rules and make them instantly available as Web services will help jumpstart SOA adoption. Enabling database access via services currently requires a lot of application development expertise. It requires wrapping SQL operations or stored procedure calls in application code and then deploying this onto some application server. Typically, Web developers aren't SQL experts, and vice versa, so developing and deploying such applications tends to be painful — at the end of it all, you may end up with a poorly performing application.

Data Web Services eliminates all coding. Using drag-and-drop, you can enable database operations as Web services and generate both SOAP and REST (Representational State Transfer)-styled end points with the capability to GET or POST data. The data can then be accessed via any Web service consumer. Data Web Services supports a variety of end points, providing plenty of options in terms of clients that can invoke these services, including SOAP and browser-based clients and Xforms, HTML forms, and so on.

The service-generation process produces a WSDL (Web Service Definition Language) file describing the services. The resulting Data Web Service solution can then be deployed into your existing Java application server infrastructure. Key features include:

• Drag-and-drop creation of Web services
• Deployment without programming
• Full Web service interface
• SOAP over HTTP and WSDL generation
• REST-style service interface
• HTTP GET/POST binding
• Web 2.0 capabilities (JSON, feeds via XSLT, AJAX)
• Capability to expose legacy application components as services

The IBM Data Studio solution, of which Data Web Services is a component, is based on Eclipse and can be easily integrated into other Eclipse solutions.

Data Web Services significantly simplifies Web services development by eliminating programming. Data Studio tools provide wizard-driven development steps and an integrated test environment for Web services. Furthermore, you can apply XSLT to the input and output to suit your services format needs. Auto-generation of WSDL under the covers allows the user to take the WSDL and provide it for consumption by any Web services client.

WHICH CAME FIRST...

...the database or the application? If the application came first, then the system is being designed top-down, starting from the object domain model. pureQuery supports a top-down approach by generating table definitions from objects; it then helps you generate a Java data access model (persistence layer) that conforms to the object domain model.

On the other hand, suppose the database came first. In this case, the system is being designed bottom-up from an existing database, and pureQuery can generate a Java data access model for it.

But many existing systems will have a "meet in the middle" scenario in which the database and the object model already exist. In those cases, pureQuery provides tools you can use to map the Java data access model to the existing objects and the existing database to the Java data access model.

pureQuery supports two styles of SQL execution: the annotated-method style and the inline-method style. The annotated-method style uses Java annotations in an interface or SQL in an XML file to define the object to relational mapping. It creates the interface (where the CRUD methods are defined), an implementation of the interface, and a Java bean representing the data. With the annotated-method style, the interface doesn't show any persistence technology to the application developer consuming the Java method invocation. This approach encapsulates the SQL mapping in the interface using Java annotations. The inline-method style executes the SQL directly. For example, the following code is an example of the inline-method style using the employee bean:

employee = db.queryFirst("SELECT NAME,

ADDRESS, PHONE FROM EMP

WHERE NAME=:name", Employee.class,

name);

The tools can easily generate Java collections-based tables, views, and stored procedures result sets.

PURE AND SIMPLE

pureQuery provides a straightforward programming model and tools for Java data access, including performance and problem determination. Operating as a fairly thin layer that sits on top of JDBC, pureQuery simplifies the most common tasks associated with Java data access development. It will also execute faster than most JDBC programs because it encapsulates the "best practice" use of JDBC-like batch updates, leverages database-specific APIs to provide performance gains beyond what is possible with standard JDBC APIs, and offers developers the most efficient JDBC options. pureQuery unlocks the benefits of static SQL by making it possible to generate static SQL without changing any code. In addition, it provides out-of-the-box support for storing and retrieving Java beans and maps to and from the database. pureQuery is an extensible platform with pluggable custom result processing patterns.

The bottom line? pureQuery makes SQL programming in Java as productive as Java programming itself.

Both pureQuery and its IBM Data Studio companion Data Web Services (see sidebar, page 33) are geared toward boosting developer productivity and shortening the development cycle by simplifying these normally complex tasks. Find out more about pureQuery and the IBM Data Studio solution at ibm.com/software/data/studio.

Rafael A. Coss [rcoss@us.ibm.com] is a solution architect leading the pureQuery enablement efforts in the Data Server Solutions team at the IBM Silicon Valley Lab. During his time at IBM he has contributed to pureQuery, DB2 pureXML, Information Integrator, and DB2 Spatial Extender.

Stephen A. Brodsky [sbrodsky@us.ibm.com] is a senior technical staff member at the IBM Silicon Valley Lab, where he is lead architect for data application development tools, runtime, and SOA. He has led or contributed to numerous software architectures, products, and standards, including IBM Data Studio, pureQuery, DB2 pureXML, Service Data Objects (SDO), JDBC XML and many others.

Vijay Bommireddipalli [vijayrb@us.ibm.com] is an advisory software engineer in the Data Servers Solutions team at the IBM Silicon Valley Lab, where he assists customers and partners in developing solutions using IBM database servers and Data Web Services.