hy are there so many Java database access protocols? There’s JDBC, JDO, EJB, POJO, AOP methods, etc. The list goes on and on. It makes me wonder: What is the problem with straightforward JDBC access?

With that question in mind, I did some looking around and I ultimately came to the conclusion that there is a frightening number of people who simply aren’t using JDBC correctly.

JDBC is so simple though! How could somebody use it improperly? Let’s start with a basic SQL example:

SELECT name FROM book WHERE author='Jack'

This is a reasonable SQL statement that will return the name of an individual book under ordinary circumstances. And as a straight SQL statement, there’s nothing wrong with it. The problem happens when you turn this statement into Java:

Statement statement = connection.createStatement();query = "SELECT name FROM book WHERE author='"  + getName() + "'";statement.executeQuery( query );

This string concatenation style is horribly wrong. Disastrous in fact. Why? Because it kills query performance and exposes the application to bugs due to encoding problems with the string. What is worse is that writing it cleanly and correctly is far more simple.

Statement statement = connection.createStatement();query = "SELECT name FROM book WHERE author=?";statement.setString( 1, getName() );statement.executeQuery( query );

By using a ? in a query string you mark a placeholder in the query where parameters will be set at run-time. Notice also that the single quotes are gone. This is because JDBC?and the database driver support for this mechanism?will automatically encode the values correctly. How many times have you received a bug about a field that caused a JDBC error when the user put an apostrophe in the field? Using the ? operator eliminates that problem for good as it handles all of the encoding for you.

In addition, you will get better performance using the ? operator. Why? Think about query caching from the driver perspective. Every time the driver sees a unique query it compiles that query and caches the executable form. If you use the wrong method:

SELECT name FROM book WHERE author='Jack'SELECT name FROM book WHERE author='Jill'

the driver sees two unique queries. Unless you run either of these exact same queries again later, you will never get any driver caching. Alternately, when you use the ? syntax:

SELECT name FROM book WHERE author=?

the driver sees the ? in the query. So the next time you make the same query you will hit the query cache and won’t have to pay a performance penalty for compiling the query. This is a major speed improvement.

Database companies have spent thousands of man years on query caching and optimization. Unless you use the ? operator in the query your applications can’t benefit from any of that work.

Between the encoding issues and the performance advantages, it’s hard to imagine why the ? operators so often goes unused. I don’t know why people avoid it, but my guess is that many of them just don’t know about it. And software development books and Web sites aren’t helping matters.

Lead Astray?
For example, I took Bitter Java (Manning, 2002) off the shelf and skimmed through it to see the JDBC code. Of the 12 queries in the book seven did not require parameters (highly unrealistic in practice) the other five used string concatenation to build the queries, which is clearly wrong. There were no examples of proper use of parameterization in JDBC. That being said, in all other regards Bitter Java is an excellent and accurate book. In addition, Bitter EJB (Manning, 2003), the follow-on book, has examples of proper parameterized JDBC.

In a recent article on DevX, “Go From Zero to JSP 2.0 in No Time At All,” author John Haro covers the new JSP standards. In the article the sql:query tag is used, which supports parameters, in a string concatenation style, thus avoiding caching and introducing encoding bugs. This section:

INSERT INTO collection VALUES    ('${param.txtName}',    '${param.txtTitle}',    '${param.txtRating}',    '${param.txtGenre}')

is better formed like this:

INSERT INTO collection VALUES(?,?,?,?)

I don’t think the code is pretty, but it has distinct advantages over the former method. This JavaWorld article, JSP Standard Tag Library Eases Webpage Development, by Steve Small, also gets it wrong in its use of sql:query.

Old, Functional SQL
I do not wish to imply that the resources I’ve mentioned here are erroneous or devoid of value; I understand that they have merit beyond this issue. I have cited these sources because together they provide visual proof, in the public domain, that there is a far larger problem afoot. We are misusing SQL and pinning the blame for the resulting poor performance on the language. Worse, we are using these faulty assumptions, at least in part, as an excuse to replace a solid, proven technology like SQL with persistence systems like JDO and EJB.

This article will likely raise the ire of some who will say that SQL is too functional in nature or is too old of a technology. It’s true that SQL is both old and functional in nature, but it is also robust, reliable, fast, portable, and declarative. I would like to think that our customers and employers are more interested in working software then in watching us attempt to replace working technologies with experimental ones. Before we decide to move away from SQL, perhaps we should make sure we are using it properly in the first place.