DB2 Magazine: A shortcut to fast data access

Java is everywhere. If you're an application developer, you're probably using Java for development. If so, you should know that development is not always that interesting.

Using Java Database Connectivity (JDBC) to create an application requires many steps, many of which are cumbersome. Not only do you need to create a Java data Access object layer, you also need to deal with CRUD (create, replace, update, delete) SQL operations. To do this, you usually have to use two sets of tools: one for Java development and the other for database crud operations. Wouldn't it be more convenient if a set of tools could handle both jobs at the same time? IBM thought so, and proceeded to create such a tool. This new high-performance platform, named Purequery (preceded by the code name "Jlinq"), includes development tools, APIs, and advanced runtime libraries for applications. IBM's pureQuery is designed to simplify the development and management of applications that access data. It is useful for all phases of development, deployment, management, and governance in the application lifecycle (see Figure 1).

Figure 1. PureQuery Architecture Overview

The PureQuery tools, APIs, and run-time environments provide access to data in the database and to Java objects in memory. As a component of the IBM Data Studio toolset, a common database application development and management platform, PureQuery provides advanced Java data access capabilities and really avoids JDBC programming because it integrates the query language with Java.

Problems with data access APIs

Java developers can choose from a variety of Java data access APIs to build applications, from JDBC to Java Persistence Architecture (JPA). These APIs can be grouped into two broad categories: direct SQL access and managed objects. Each method has its supporters and opponents. Figure 2 summarizes the pros and cons of each of these methods.

Figure 2. Comparison of several data access methods

Direct SQL access is well understood. It facilitates the control of emitted SQL, thereby improving SQL performance. However, the JDBC development process can be tedious and time-consuming. In direct SQL access methodology, Java programmers must map result sets from JDBC to Java objects and create statements and binding parameter placeholders. For best performance, developers must master the JDBC API and leverage features such as batch processing and result optimization. To improve JDBC performance, some people may consider SQLJ, but this choice requires developers to rewrite the data access layer.

The monotony of JDBC development has prompted the advent of the Object Relational Mapping (ORM) framework, which provides a data access abstraction layer. Using ORM often makes it easier to create a data access layer. However, ORM has limited control over the emitted SQL, and they add a layer of complexity to the diagnostics of run-time performance problems. Tuning and diagnostics are more difficult because developers can no longer control what SQL is sent to the database, so it is difficult to change the SQL or decide which application will emit SQL.

IBM has created PureQuery to address the limitations of these methods. PureQuery provides a Java editor with SQL Intelligence, but it is not another SQL-like language-it is a language that can query relational databases and Java collections and database caches declaratively. The extended Java editor includes an integrated SQL editor that enables developers to take advantage of code completion, validation, and execution assistance, as in editing Java. In addition, IBM is working to enhance JPA support through pureQuery.

Insight PureQuery

PureQuery simplifies the work associated with data servers (especially for IBM data servers) and Java objects throughout the development lifecycle (development, deployment, management, and problem diagnostics) of Java data Access applications. The PureQuery runtime can work with all databases with JDBC drivers and provides integration with additional tools for all versions of DB2 and Informix Dynamic Server (IDS). You can choose to use PureQuery with JPA (a Java EE standard) or popular open source projects such as Spring and IBatis. PureQuery is integrated with the recently announced underlying technology in project Zero plan--zero.data. PureQuery Technology brings SQL editing into the Java development environment.

In short, PureQuery makes many Java-related data access programming tasks much easier. It provides a good environment to help you identify problems early in the development cycle and trace back to the application source code from the database. Static and dynamic SQL can be used simultaneously through the same Java APIs. Support for static SQL improves problem diagnosis and governance. DB2 DBAs should understand the benefits of static SQL access for application performance, application management, and data access security (governance).

PureQuery simplifies and unifies the implementation of all data access. It does not force you to use specific APIs (such as EJB2, JPA, Spring, or iBatis), but it does facilitate the implementation of some of the existing APIs used to access data. With PureQuery, you can write data access code directly in Java. When writing queries, you get design-time syntax and pattern checking and better tool support (such as code completion/support).

The IBM Data Studio solution automates many repetitive JDBC and object-building tasks. For example, pureQuery SQL Intelligence can help you complete SQL statements during the development process.

The SQL Editor integration (shown in Figure 3) includes the following features to provide support for semantic and syntactic validation:

Design-Time Compilation

Intelligent Content Assist

Syntax coloring

Figure 3. SQL Editor Integration

Test SQL directly in the Java editor, which can increase productivity; you can quickly test SQL on a database and mark errors as if you were dealing with Java programming errors.

By using PureQuery, you can shorten the SQL cycle in the Java edit-compile-debug development cycle. You do not have to wait for the runtime to know whether a query string is well-formed or whether an error will be returned. Figure 4 shows how pureQuery can mark a SQL error in the Eclipse Java perspective as if it were a Java error.

Figure 4. Error marks for PureQuery in Eclipse Java perspective