RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Smoothly Blending Java and SQL with pureQuery : Page 2

Just like oil and water don't mix effectively without an emulsifier, so too is the problem with Java and SQL. With IBM's pureQuery data access platform, developers can more simply blend the two together.


Introducing pureQuery
pureQuery is technically between a Table Gateway and ORM-type tool, but with important differences. One of the most important is that instead of trying to hide the SQL, pureQuery exposes it fully. The Data Studio Developer Java editor is SQL-aware and can manipulate SQL as readily as it does Java, including syntax and semantic checking of SQL statements as well as design-time execution of SQL to check for errors.

If you don't want to deal with the SQL part of the application, you don't have to. pureQuery can automatically generate blocks of SQL statements to perform common CRUD functions and it includes the ability to ignore the SQL statements and concentrate on the Java.

pureQuery is designed to make life as easy as possible for developers and DBAs working on Java-SQL projects. It focuses on giving the entire design team, developers and DBAs alike, the tools they need and automating as much as possible through the use of wizards, drop down menus and other content assist features. As a result, team members can work more easily and efficiently through the entire data life cycle, not just the developers working on the Java portion of the project. But more importantly, it also allows developers to easily customize the SQL in data access layer when necessary.

In addition, pureQuery is fast. It uses optimizations such as batch updating and database-specific APIs to improve performance over the standard JDBC APIs.

The combination of speed and ease of use for the entire team is that pureQuery significantly increases productivity throughout the Java-SQL development cycle. Almost as importantly, the teams find that a lot of pain points have been eliminated for everyone.

For example, a homogenous batch update in pureQuery can easily combine SQL and JDBC:

pureQuery Inline Style:

For example, a heterogenous batch update in pureQuery can easily combine SQL and JDBC:

pureQuery Inline Style:

For one thing, there is just plain less typing with pureQuery—a lot less. pureQuery's wizards and menus allow you to do even fairly complex jobs with just a few mouse clicks.

Multiple Choices
For example, pureQuery provides not one, but three methods of mapping Java objects to database constructs. All three reflect common methods of developing Java-SQL software.

The most common is database-driven Objectification (DDO) or bottom up database objects. The programmer selects a table, view or stored procedure from the database and pureQuery creates the classes and interfaces needed to handle the CRUD—the rather inelegant acronym for Create, Replace, Update and Delete—shorthand for database operations.

The following is the result of selecting a table from the Data Source Explorer. This can also be done for Views, Nicknames and Stored Procedures.

This will start the process of generating a Java Bean to contain the data coming from the database object.

By default the Name of the Java Bean generated is the same name of the table, but I customized it to OrderDetail below:

The wizard also facilitates the creation of unit test cases:

The wizard can customize the Field Names to be camel cased as expected from Java Beans:

Lastly the wizard can specify which SQL CRUD methods get generated:

This will result with the following files being generated.

The code generated by pureQuery is only the beginning. With Data Studio developer you have extensive tooling available to customize and evolve the SQL code as necessary.

This is the most common method when creating a program around an existing database. But what about situations where you've got the program and you need to integrate a table through SQL? Or where you've got a bean class that represents the table, but there's no existing link between the table and the class?

For these situations, pureQuery offers two other methods of working. With Query-driven Objectization or bottom up from SQL you can start with the SQL statements and work from there to create the object hierarchies and convert the SQL queries to Java objects.

The third method is a meet in the middle approach. It makes it easy to map an existing bean to an existing table in the underlying database and to map the class's protected variables to the table columns.

All three approaches are handled easily and quickly through an intuitive IDE with a lot of drop-down menus and point-and-click commands for even complex operations, not to mention a lot of wizards to walk you through operations.

pureQuery also offers two coding styles: Annotated-method and inline.

Inline coding is a quick simple style designed to be easy for Java developers familiar with JDBC to master. One of its key goals is to reduce the repetitive coding involved in writing JDBC.

Annotated-method coding has many of the same goals as Inline with the addition of maximizing performance and security. It supports both static and dynamic SQL execution modes and can automatically switch between them. pureQuery moves the SQL execution mode decision from design time to deploy time. An application using the dynamic mode can be converted to leverage static SQL (generally better performance) without modifying or writing any code.

The annotated method style uses a Java interface to setup a DAO pattern. The developer defines their API and associates the SQL to the method via Java annotations.

The Big Picture
pureQuery is designed not just to make life easier for Java developers—although it does that—but to make everyone on the development team more productive. To that end, it devotes considerable effort to simplifying things for SQL developers and DBAs working on the project as well.

This is especially obvious when it comes to tuning applications for performance. DBAs will find that pureQuery's method of handling SQL helps avoid long, involved "treasure hunts" to try to tie a performance bottleneck to specific parts of the application. At a simple level, just the fact that pureQuery can collect all the SQL statements in the application into a single file, without missing any, is an enormous help in debugging and auditing. With the SQL statements fully exposed and referenced back to the Java code, it is easier to trace performance problems back to the code that produced them and to modify that code to improve performance.

pureQuery's single-minded focus on making life easier for the entire team pays off in reduced development times, greater productivity—and less stress on the Java and SQL developers, as well as the DBAs.

More Resources

  • Trial: IBM Data Studio Developer and pureQuery Runtime

  • Learn What's New in Data Studio Developer 2.1 and pureQuery

  • Learn More About Data Studio Batching

  • List of Data Studio Articles and Resources

  • pureQuery JavaDocs

  • Data Studio Team Blog

  • Data Studio Newsgroup

  • pureQuery Wikipedia page

  • Rick Cook is the author of hundreds of articles about information technology and its impact on business.
    Email AuthorEmail Author
    Close Icon
    Thanks for your registration, follow us on our social networks to keep up-to-date