Modern software development is, famously, a collaborative process. Unfortunately the tools we use to develop software don’t always reflect that home truth.

Consider an application written in Java that uses SQL to manipulate information in a database. SQL and Java are two of the primary tools for developing modern data-driven applications. Unfortunately they mix about as well as oil and water. Developing in SQL and Java usually requires two sets of tools, two developers and a certain amount of pain and suffering.

From a theoretical standpoint this is called the “Object-Relational impedance mismatch“, or more provocatively, “The Vietnam of Computer Science.”

Developers working with Java and SQL don’t call it anything?at least anything printable. They just acknowledge it’s a problem, put their heads down, and keep slogging.

One of the things they have to slog their way through is that most Java IDEs don’t speak SQL. To them a SQL statement is merely a string. They can’t do syntax and semantic checking, much less optimization on SQL statements, the way they can on Java.

Since people who are skilled in both Java and SQL are rare, most Java SQL projects need to team Java developers with SQL developers and DBAs. “There’s a need for a person strong in Java collaborating with someone who is strong in SQL,” says Rafael Coss, an IBM Solution Architect leading the pureQuery solution efforts in the Data Server Solutions team at IBM’s Silicon Valley Lab. “A good running application is going to need the entire team to make it work.” However most tools do a poor job of enabling teamwork between SQL and Java developers and DBAs.

Java is object-oriented with encapsulation, inheritance and polymorphism as major features. The closest relational databases come to objects are rows with primary key values. SQL is a declarative query language as are most languages for manipulating SQL queries.

The result is that programs using Java and SQL are hard to write, prone to errors and it is very hard for database administrators and Java programmers to collaborate effectively.

Some Java programmers have attempted to solve this challenge by hiding the database with object relational mapping (ORM) frameworks like Hibernate. They have attempted to create an abstraction layer so they don’t have to concern themselves with SQL. In ORMs, developers setup a configuration or mapping file to define how their Java objects are mapped to tables with the goal of having the ORM generate some basic SQL CRUD (Create Read Update Delete). For more specific queries they also provide some Object Query Language, like HQL so they don’t have to do SQL. However most ORM object query languages are very, very SQL-like and eventually generate SQL. The “what” and “how” of the translation depends on the ORM.

What if the DBA or SQL developer wants to help tune or troubleshoot the data access layer? With an ORM the Java developers now have no idea what the actual SQL is that is being generated. The ORM is like a SQL amnesia layer which prevents the Java developers from working with the DBAs and SQL developers because the database is hidden.

Of course, as every cook knows, oil and water will mix nicely if you add an emulsifier, a third ingredient to bind them together. For Java and SQL it’s IBM’s new pureQuery data access platform. Put simply, pureQuery acts as a software emulsifier to let Java and SQL blend together seamlessly. The result is quicker, easier development and greatly improved productivity.

How much improved productivity? YMMV, of course, but according to IBM, customers have seen productivity gains of 25 percent and more in Java-SQL applications.

“pureQuery is not just for developers. It’s for developers and application-centric DBAs,” says Coss. “Data Studio Developer is an Eclipse-based tool for developing data oriented applications.” Most of pureQuery’s features will work with any SQL database that has a JDBC driver (pureQuery is the data access layer for sMash/project Zero), but others are currently available to users of DB2 and Informix.

Two Ways of Working with Java and SQL
Broadly speaking, existing data access APIs take one of two approaches. They either provide direct access to the SQL or they treat SQL code as managed objects. Both have their drawbacks.

Direct access is easier to understand and gives the developer control over the SQL, but it means that someone has to map the JDBC result sets to Java objects, create statements and bind parameter markers. That’s a lot of work, much of it fairly tedious.

The managed objects approach, such as Object Relational Mapping (ORM) makes life seem easier for the developers by creating a data access abstraction layer that often generates the SQL statements from the Java objects, thus hiding the database. This does make things easier for the Java developers, at least initially, but it provides only limited control over the SQL statements?in some cases so limited that the Java programmers don’t know which SQL relates to which Java objects. The decision to hide the database basically taxes the DBA and Developer when it comes to tuning, trouble shooting and performing problem determination because the Java to Relational mapping is hidden and you don’t have access to the actual SQL to modify.

This greatly complicates life at deployment Not only does the additional abstraction layer complicate analysis of runtime performance, tuning and diagnostics become much harder because the developers have lost control of the SQL.

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