Clever Facade makes JDBC look easy

The Facade design pattern lets you focus on the big picture rather than becoming mired in the details of JDBC

Before I dive into this second installment of my new Cool Tools column, allow me to offer up some corrections and clarifications to last month's column, "How to easily reconfigure your applications -- while they're running." I received lots of feedback on that article, most of it constructive, some of it abusive, but if it makes me a better writer and/or programmer, it's all welcome. (Although, I do prefer the polite messages over the vulgar ones.)

Static finals and compilers

Last month I discussed the scenario wherein the values of static final variables are being compiled into the classes that reference them, thus requiring a recompile of the entire project for any changes to propagate. I stated that this behavior is (or was two years ago) different from one compiler to the next. JavaWorld reader Ethan Nicholas was kind enough to correct me on the matter:

This behavior is specified by the Java Language Specification, section 13.4.8. The gist of it is that references to primitive constant fields (static final with a constant primitive initializer) are compiled into other classes by value rather than by reference. What you probably saw was a constant number refusing to change, even though you were accustomed to constant strings changing -- this is correct behavior (according to the spec), as strings are not primitive and so must be passed by reference. It certainly should not vary from compiler to compiler -- if a compiler compiles a reference to a primitive constant, rather than just the value, it is in error.

The singleton

A few of you commented to me that I should have made the GlobalValues object into a singleton. The term singleton comes from the realm of design patterns. A singleton object strives to ensure that it exists in only one instance and that it offers a global point of access.

In essence, the GlobalValues object is a singleton. All of the tool's methods are static, and the internal Hashtable is static, so you can (and should) reference the tool via the class name. I demonstrated this in my article, and that is how I intended the tool to be used. As some of you pointed out, however, I can go a couple steps further to make GlobalValues a singleton in the purest sense.

First of all, I should have declared the class to be final, as in:

public final class GlobalValues

This ensures that nobody can subclass the GlobalValues and override the functionality. A nice safeguard.

Second, I should have declared a private constructor:

private GlobalValues(){}

This ensures that no one can create instances of the object. At first, having numerous instances doesn't seem all that troublesome because the internal Hashtable is static, which means all of the instances would share the same data. There is a nasty race condition, however: a static synchronized method does not synchronize across multiple instances of its class. For further reading on this, see Part 7 of Allen Holub's Java Toolbox series, "Programming Java threads in the real world."

The Factory

A couple of you also suggested that I make the tool serializable and thus RMI-friendly. I don't see the practicality of this, but it certainly can be done. The first thing I need to do is make all the internal data nonstatic. In the case of GlobalValues, the internal data is the lonely Hashtable. Once the internal data is nonstatic, it doesn't make any sense for the get() method to be static. We must now instantiate the tool in order to use it -- thus, the Value inner class doesn't need to be static either. Also, the JDBC driver loading block should be moved out of the static block and into the private constructor. Now, nothing in GlobalValues is static.

In order to preserve the singleton nature of the tool, we must control its instantiation. We must make sure that only one instance of the tool can be created, and that everyone uses that lone instance. In order to accomplish this, we will use yet another design pattern, called Factory. A factory in the real world is a big building where merchandise is produced. We, the consumers, don't care how the factory creates the merchandise; we're only interested in purchasing it. In the programming realm a Factory is an object that produces other objects.

In the case of our GlobalValues tool, we can make it a Factory of itself. This requires us to add in a couple more static entities. The first is a reference to an instance of itself:

private static GlobalValues self = null;

Making the constructor private has already ensured that no other class (except the tool itself) can instantiate a GlobalValues object. All we need now is to give the outside world access to a single internal instance:

public static synchronized GlobalValues getRef() { if( self == null ) self = new GlobalValues(); return self; }

And, don't forget to implement the Serializable interface:

public final class GlobalValues implements

Now the usage of the tool changes slightly. Here is the old way:

String value = GlobalValues.get( "MY_VALUE" );

And here is the new way:

String value = GlobalValues.getRef().get( "MY_VALUE" );

Here is the complete source of the new and improved GlobalValues tool.

General errata

In last month's column, there is a typo in the SQL of the load() method. Since I am using a PreparedStatement, the apostrophes around the question mark aren't necessary. Here's the correct line:


Also, it's possible to get a NullPointerException from the get() method because I am not checking the return value of the load() method. If the requested value isn't in the database, then load() will return null, and you can't put null into a Hashtable. Here's a better way to do it:

value = load( name ); if( value != null ){ cache.put( name, value ); } else { return null; }

Reader initiative

Anthony Eden dropped me a note to let me know that he expanded on my idea -- the way in which I solved the problem and implemented the tool. He calls his version of the tool a PropertyManager. The most notable improvement in his version is how he abstracted the database layer so that the tool can get the key-value pairs from anywhere; pluggable PropertyLoader modules allow you to access data in a standard database, a flat file, an XML document, and so on. Check it out at

Now, on to this month's tool

Design patterns seem to be the underlying theme this month. I discussed a few above when I addressed comments from readers regarding last month's tool. And we're going to talk about yet another one in this part of my article, which is devoted to the new tool. Called Facade, this design pattern is a high-level interface that encapsulates many other subsystem interfaces. This month I'm applying Facade to the more popular JDBC classes. What does this do for us? It means you get the same JDBC functionality -- high-level database abstraction -- with a lot less typing. Your database-related code will thus be smaller and cleaner.

Before we jump into the internals of the tool, allow me to demonstrate its value. Below are two examples of Java code, each performing the exact same task. The first example uses standard JDBC. The second uses the tool for this month's column. I've named this tool SQLUtil.

The Java code using standard JDBC is as follows:

import java.sql.DriverManager;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.CallableStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

public class DBCompare { public static void main( String[] arg ) { Connection conn = null; PreparedStatement prep = null; CallableStatement call = null; ResultSet rset = null; try { Class.forName( "<driver>" ).newInstance(); conn = DriverManager.getConnection( "<database>" ); String sql = "SELECT * FROM <table> WHERE <column name> = ?"; prep = conn.prepareStatement( sql ); prep.setString( 1, "<column value>" ); rset = prep.executeQuery(); if( ) { System.out.println( rset.getString( "<column name" ) ); } sql = "{call <stored procedure>( ?, ? )}"; call = conn.prepareCall( sql ); call.setInt( 1, 1972 ); call.registerOutParameter( 2, java.sql.Types.INTEGER ); call.execute(); System.out.println( call.getInt( 2 ) ); } catch( SQLException e ) { e.printStackTrace(); } catch( InstantiationException e ) { e.printStackTrace(); } catch( ClassNotFoundException e ) { e.printStackTrace(); } catch( IllegalAccessException e ) { e.printStackTrace(); } finally { if( rset != null ) { try { rset.close(); } catch( SQLException ex ) { ex.printStackTrace(); } } if( prep != null ) { try { prep.close(); } catch( SQLException ex ) { ex.printStackTrace(); } } if( call != null ) { try { call.close(); } catch( SQLException ex ) { ex.printStackTrace(); } } if( conn != null ) { try { conn.close(); } catch( SQLException ex ) { ex.printStackTrace(); } } } }


And here's this month's new SQLUtil tool in action:

import java.sql.ResultSet;

import java.sql.SQLException;

public class DBCompare2 { public static void main( String[] arg ) { SQLUtil util = null; try { util = new SQLUtil(); String sql = "SELECT * FROM <table> WHERE <column name> = ?"; util.setSQL( sql ); util.setString( 1, "<column value>" ); ResultSet rset = util.executeQuery(); if( ) { System.out.println( rset.getString( "<column name>" ) ); } util.reset(); util.setMode( SQLUtil.CALLABLE ); sql = "{call <stored procedure>( ?, ? )}"; util.setSQL( sql ); util.setInt( 1, 1972 ); util.registerOutParameter( 2, java.sql.Types.INTEGER ); util.execute(); System.out.println( util.getInt( 2 ) ); } catch( SQLException e ) { e.printStackTrace(); } finally { if( util != null ) { util.close(); } } }


The first example is 72 lines long; the second is 31 lines -- less than half the size of the first. Why is this the case? Two things are missing from the second example: initialization and cleanup. It takes a few lines of code to load the driver, a few more lines to establish the connection, a whole bunch of code to catch all of the possible exceptions, and another half-dozen lines to clean up after all is said and done. All of this is taken care of inside the tool.

The secret to the second example's conciseness is the SQLUtil object. I've been doing a whole lot of JDBC work lately, and I got real tired of typing and retyping all the excess code you see in the first example. So, in the interest of enriching the lives of all humankind (okay, okay, to save myself some typing), I set out to encapsulate as much of the logic as possible into a small set of utility classes. As it turned out, I managed to squeeze all of the logic into a single stateful class. I'll explain the stateful concept in a bit.

This image demonstrates in visual terms how the Facade design pattern is applied:

The cleanest way to code with JDBC is to avoid the Statement class entirely. Creating your own SQL statements by concatenating strings almost always ends up giving you a syntax error. You miss white spaces or forget to escape ticks (apostrophe marks). Also, since strings are immutable, you can potentially clutter up valuable memory. Managing your SQL this way can be a nightmare.

With all of those pitfalls in mind, I try to work exclusively with the PreparedStatement class. The PreparedStatement class takes care of all the problems I outlined above. You write the SQL statement as a single string, using question marks in place of the values. You then call some set() methods, and the PreparedStatement fills in the values, taking care of the syntax (putting ticks around strings, and so on). Also, it keeps the statement precompiled in memory so that you can reuse it -- a sweet performance bonus.

The PreparedStatement doesn't cover all your needs, however. Since I'm working with a legacy system, I have to deal with some stored procedures, which are precompiled SQL statements that reside inside the database. You call them similarly to a remote method (and that's essentially what they are).

The CallableStatement class is JDBC's solution for this dilemma -- that the PreparedStatement isn't as well suited for dealing with stored procedures as is the CallableStatement.

1 2 Page 1