QCI Java/SQL Utilities Package (QJSQL)

What Is It?

This package contains utilities for safe and flexible handling of dynamic SQL statements in Java code which makes queries through JDBC. The main goals of this package are to enable the SQL statements used by Java code to be moved outside of the code itself, leverage the performance benefits of prepared statements and yet maintain the integrity of the code with regard to changes in the SQL.

The problem arises with prepared statements where substitutable parameters are marked only by question marks in the SQL statement:

  SELECT description, serial_no, item_type_id from item
  WHERE item_id = ?;

  INSERT INTO item (item_id, description, serial_no, item_type_id)
  VALUES (?, ?, ?, ?);

The code must the set parameters according to the index of the parameter in the statement, keeping careful track of the correspondence. This correspondence makes it risky to store the SQL separately since it is very likely that inconcistent changes will be made to, e.g. column names, parameter order, or the relevence of particular parameters.

This package solves this problem by allowing the database programmer to name the parameters in the query and apply key/value pairs to its execution. The DynamicStatement class keeps track of the correspondence and provides the ability to optimize and reorganize queries or adjust to structural changes in the database with minimal code impact. We even takes this a step farther by providing a second layer of substitution, allowing parametized queries of a type not allowed by standard prepared statements (e.g. table or column names may be parameterized) to be generated easily.

Once this was done, a natural structure grew up around the DynamicStatement allowing statments to be readily loaded, created, filed, executed, debugged and reused within the program. This library is the result of that evolution.

This package was originally part of the QCI JUtils package and is still distributed with it. The continued specialization of the code here and, in particular, its diferring unit test requirements, let us to split the sourcecode out into a separate tree. The QCIJUtils library is still a dependency of these SQL utilities. In particular, com.qlue.util.logging is used for tracing and debugging of SQL statements. This facility can be readily disabled, however, and, through the design of the QCI logging package, would be relatively straightforward to redirect to some other logging system.

How Can I Use It?

This package is not GPLed, and therefore can be used in commercial packages. This was very important to us (see LICENSE.txt for info).

To install the package under a JDK 1.2+ VM, simply copy the jar(s) to your extensions directory as described in your VM documentation. Otherwise, put the jars somewhere convenient and make sure they are listed in your class path. On our workstations, we have our Jars available on a network server, so they are found in '/netpkg-noarch/javalib/'. Most of our dependent projects (e.g.: QAT) look there first, but they all provide a means of telling them to look somewhere else.

This project comes with two jars, qjsql.jar and qjsql-test.jar. qjsql.jar contains the classes you need to use this project and depends on qciutil.jar from the QCI Java Utilities. qjsql-test.jar contains the test suite. It depends on both qciutil-test.jar and on qjsql.jar. The test suite jar, in addition to the test cases themselves, contains abstract test classes, stubs and other minor test utilities which may be useful for testing classes which use this library's facilities.

What platforms/VMs does it run on?

At this time, we fully expect the packages to run on any compliant 1.2.x, 1.3.x or 1.4.x VM. Compatibility with 1.x JDKs has been dropped. We do have a requirement to remain compatible with 1.2.x and will probably maintain that requirement until at least 4Q2002. We have recently made changes to ensure portability to JDK 1.4 (e.g.: dealt with the fact that "assert" is now a reserved word.

Now that JDK standardization seems to have improved markedly, we are no longer testing major releases on as large a variety of JDKs/platforms. One can be reasonably certain that we or other contributors are running on at least Linux and Windows plus one or two other platforms which are being used in active projects (e.g. Solaris, SGI). We have an in-house portability lab, so we have access to different machines and configurations for dealing with problems as they are encountered. If you encounter problems on a particular platform/VM, let us know and we will try to duplicate it. If you are actively using this library on a less common platform, let us know and we will consider adding it to our test cycle.

As far as database compatibility is concerned, our primary database platform for unit testing is PostgreSQL at the moment. This may soon be replaced or augmented by HSQL, a lightweight pure Java RDBMS. We perform intermittent testing against Oracle and have access to a number of other platforms for diagnosing reported problems. The library does not, at the moment, access anything which is typically non-portable.

What Documentation Is Available?

Javadoc is available for all packages from the project web site. It can also be downloaded from the file/release manager or built from source. In addition to the raw class documentation, overview and package summary pages plus a few example classes are included.

Why Is This Package Being Released?

We could say (and would mean) all of the usual things about giving back to the community, creating goodwill, etc. QCI uses Linux and other open-source software heavily for internal development (though we sometimes can't deliver them to our clients) and these tools have saved uncounted programmer-hours and software purchase dollars.

However, when it gets down to it, we have four "practical" reasons:

Now that our in-house portability lab is online, we have local access to two dozen platform/VM combinations. The number of possible test environments however, is still a bit larger. We want to encourage users to send us bug reports (or preferably patches) for other platforms.
We want to make this library more commonly used so that it will appear more acceptable to our clients.
Marketting--- If people use it and like it, people may give us more business (or so the theory goes...). At the very least, it gives us an example of our work that we can readily point to without breaching confidentiality or intellectual property rights with previous clients.
We have found that it is convenient to have a sizable publicly accessible codebase that we control to use for examples in discussion lists, courseware, presentations of techniques and so forth. These packages, plus QAT are now being used as the case study for an upcoming book on Java/JUnit testing.

How Can I Help?

Download it, try it, send us fixes and improvements. We'll incorporate what we can and make new releases. Be aware that we (and others) have deployed software based on this package, so backwards compatibility is a concern.

We have a coding standard that is used in this package. It also contains generic advice for writing good code that was mainly learned the hard way and therefore may be interesting. Please look through it. We won't reject fixes or improvements that don't fit in with our scheme of things, but they may take longer to incorporate.

When sending in bug reports, fixes, comments, suggestions, please tell us what platform, VM, compiler, etc., you are using. We don't have the resources to track down bugs from incomplete reports.

We would be most appreciative if people with highly unusual portability concerns would let us access hardware for testing/diagnostic purposes. The hardware does not have to be fast, recent, etc., but just capable of running the correct software and available for network login.

Eric Vought

Version Info: $Id: README.html,v 1.1 2002/04/01 15:12:11 evought Exp $