JDBC

Clever Facade makes JDBC look easy

Use the Facade pattern to encapsulate JDBC initialization, exception handling, and cleanup

JDBC

Show More

The Facade pattern is a high-level interface that encapsulates many other subsystem interfaces. In this tutorial I'll show you how to apply the Facade pattern to JDBC classes. As a result, you'll get the same JDBC functionality with a lot less typing. Your database-related code will also be smaller and cleaner.

[ Also see: What is JDBC? Introduction to Java Database Connectivity ]

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( rset.next() ) {
                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 the 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( rset.next() ) {
                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.

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.

As I mentioned a moment ago, the SQLUtil class is stateful. This means it stores and reuses data internally from one method call to another -- therefore, you can't share a single instance of the SQLUtil between threads. Also, you must call its methods in the proper order to ensure a successful operation. I know you diehard object-oriented programming fanatics are cringing right now, but bear with me. It will all be okay in the end!

How to use the tool

Enough of the theory, let's jump into the code. Before I explain how the tool works, I'm going to explain how to use it. You'll need to start out with a skeletal structure:

 

SQLUtil util = null;
    try {
        // your JDBC stuff is gonna go here...
    }
    catch( SQLException e ) {
        // handle it however you wish...
    }
    finally {
        if( util != null ) {
            util.close();
        }
    }

We want to ensure that no matter what happens, the SQLUtil receives the call to its close() method so that it can clean up internally. This is why we declare it outside the try/catch block and then close it inside the finally clause.

Now that we have the skeleton, we need to fill in the guts. Inside the try clause, you simply create a new instance of the tool:

 

util = new SQLUtil();

The tool defaults to PREPARED mode. This means internally it is going to use a PreparedStatement class. If you need to use a CallableStatement class instead, you simply tell the SQLUtil to switch modes. Here's how to switch the modes:


util.setMode( SQLUtil.CALLABLE );

Allow me to jump ahead for a second and give you a peek into the internals of SQLUtil. Since CallableStatement is a subclass of PreparedStatement, the SQLUtil has only one internal reference, declared as a PreparedStatement. Whenever it needs to call a method specific to the CallableStatement (which is why you have to set the mode properly), it uses a casting statement. More on that later.

The next thing you need to do is tell the tool what SQL it is going to be executing. This is extracted directly from the example at the beginning of this article's introduction to this new tool:


String sql = "SELECT * FROM TABLE_NAME WHERE COLUMN_NAME = ?";
    util.setSQL( sql );

As I mentioned in my discussion of PreparedStatements, the question marks (?) in the SQL statement are placeholders for values. You now need to fill in those holes. The methods for doing that are exactly the same as if you were dealing directly with a PreparedStatement. You use:


util.setString( 1, "COLUMN_VALUE" );

The next thing on the list is to execute the SQL. You have the three standard options: execute(), executeQuery(), and executeUpdate(). These methods all work exactly as if you were calling a PreparedStatement or CallableStatement directly (and internally, that is exactly what they do):

 

ResultSet rset = util.executeQuery();
    while( rset.next() ){ ... }

Now comes the nice part. If you need to perform another database transaction, you can reuse the existing instance. Simply call the reset() method, which triggers an internal cleanup, and you're ready to go again:

 

util.reset();

The internals of the tool

In the second example (listed at the beginning of this month's new tool section), you probably noticed that I don't load the driver anywhere. It's as if the SQLUtil magically knows how to create connections. It does, in fact, but you have to subclass the SQLUtilAbstract class and implement that part yourself.

SQLUtil is actually a subclass of SQLUtilAbstract. The abstract class implements all of the functionality except for two little things: how to create a connection and how to clean up a connection. Here's the SQLUtil class in its entirety:

 

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
public class SQLUtil extends SQLUtilAbstract {
    static {
        try {
            Class.forName( "<driver>" ).newInstance();
        } catch( InstantiationException e ) {
            e.printStackTrace();
        } catch( ClassNotFoundException e ) {
            e.printStackTrace();
        } catch( IllegalAccessException e ) {
            e.printStackTrace();
        }
    }
    public SQLUtil() throws SQLException {
        super();
    }
    public Connection getConnection() throws SQLException {
        return( DriverManager.getConnection( "<database>" ) );
    }
    public void freeConnection( Connection conn ) throws SQLException {
        conn.close();
    }

The methods you have to implement in order to manage the creation and release of database connections are getConnection() and freeConnection().

Why doesn't the abstract class just call Connection.close() instead of making you implement freeConnection()? I'm glad you asked. Being forced to implement your own cleanup method gives you the opportunity to combine this utility with the JDBCConnectionPool introduced in my first article on object pooling (see Resources) -- a powerful combination to be sure. How do you combine the two? I'll leave that as an exercise for the reader ... for now. After I introduce a few more tools and techniques in future installments of this column, I'll show you how to better implement object pooling by combining all of the tools and techniques discussed thus far.

SQLUtilAbstract takes care of the Connection creation inside the setSQL() method. This is also where it initializes the PreparedStatement or CallableStatement, depending on the mode you're in. This is one of the few places in the tool where an if/then clause is needed, because creation of the two statement classes differs. Here's how I handle the different methods of statement creation:


if( mode == PREPARED )
    {
        prep = conn.prepareStatement( sql );
    }
    else
    {
        prep = conn.prepareCall( sql );
    }

When you reset() the tool, it performs some internal cleanup and defaults back to PREPARED mode. It essentially calls a private method called subClose(), which closes the ResultSet and PreparedStatement, but leaves the Connection intact (so that it can be reused). The close() method calls subClose() and then closes the Connection object. When you look at the code, you'll see that these methods handle all of the null checking and exception catching, hiding it from the programmer.

The rest of the tool, SQLUtilAbstract, simply offers up the common set() methods I need to call from PreparedStatement and some get() methods from CallableStatement.

Questions

Being the psychic that I am, I predict the following questions will arise from this article:

  • Why doesn't the tool just implement the PreparedStatement interface?

    I didn't need all that functionality, so I didn't implement it. But it's certainly possible to do.

  • Why didn't you encapsulate ResultSet into the tool with everything else?

    I anticipated cases where I would need to pass around the ResultSet to other methods. In order to do this with encapsulation, the SQLUtil would have to implement the entire ResultSet interface. Again, it's entirely possible.

  • What about transaction control? How do I perform commit and rollback commands?

    Right now, you simply can't. I didn't need transaction control, so I didn't implement it. But it's possible.

Conclusion

This tutorial has introduced the Facade design pattern and demonstrated using it to simplify JDBC. By encapsulating common JDBC logic, the SQLUtil tool is able to handle the monotonous stuff of driver loading, connection initialization, exception handling, and cleanup. 

Thomas E. Davis is a Sun Certified Java Programmer. He lives in sunny South Florida, but spends every waking second indoors in front of the computer. He pays the bills by designing and building large-scale distributed applications in Java.