Scale an application from 2 to 3 tiers with JDBC

Learn how we can use JDBC to convert our Forum server application into a middleware layer

Before we get started, you may want to review the Forum application that we created earlier this year. In part 1 we built the client application, and in part 2 we developed the client-side networking and the server. I'll wait here for you to finish.

The task at hand

Now that you're sufficiently refreshed, we're ready to move on. The first place to begin improving our Forum application is the backend, which in the original version is nothing more than a limited flat-file database. The backend needs to be re-architected to connect to a "real" database. The new architecture will be three-tiered.

Re-architecting the Forum's backend improves the application in three ways:

  • Functionality is expanded by putting a real database behind the application, which enables more advanced clients to access more granular information, such as Subject and Sender attributes, for each article. This new functionality will be exposed with new Forum 1.1 API calls.

  • The application can scale to the database's maximum, because multiple middleware servers can now process against the same database. Browser security restrictions on applets make it impossible for Web/applet servers to connect to a database tier residing on a host other than their originating host. As a result, this type of architecture is impossible with the 1.0 version, which is client/server.

  • JDK 1.0.2 clients can be migrated into future 1.1 clients. These "legacy" clients, which are supported by "legacy" browsers, don't have built-in support for JDK 1.1 features like JDBC, and have to communicate via sockets or URLs.


JDBC is Java's database API that adapts arbitrary SQL databases to Java and provides a common interface for using them. JDBC works by providing three Java interfaces -- Connection, Statement, and ResultSet -- which are implemented by vendor-specific drivers. The programmer just plugs in the correct driver and sends the correct SQL to access the database of his/her choice -- the rest of JDBC usage is essentially uniform.

For the purpose of this application, we use the simple message database structure, shown next, which we will access via JDBC calls to the Windows NT 4.0 machine that hosts it.

Message database

This database is a quasi-relational database comprised of a single table, Messages. It conforms to the first normal form requirements of flatness and atomicity of attributes, but doesn't have a real primary key. For that matter, it isn't optimized either; the database is just for demo purposes.

Migration process

Are you ready to move from Forum 1.0 to Forum 1.1? Well let's get going. We'll begin by extending the API to add a bit of new functionality, then we'll implement the classes.

Here's a complete listing of all the source code we'll use in this article.

Extending the API

The 1.0 version of Forum's API, as used by Forum and implemented in the ForumComm client communications library, looks like this:

Hashtable loadAllThreads () -- Loads all threads defined in server's config file.

Vector loadThreadArticles (String t) -- Loads all articles in thread t.

boolean postArticle (String art, String t) -- Posts article art to thread t.

When we turn the server into a middleware layer, we obviously have to continue to expose the API calls that the 1.0 client expects. But because we're now dealing with a real database, we need to add functionality that a more advanced client might want. These additional calls are suggested by what the database can offer. The following API calls, among others, would be useful additions given the new backend:

Vector loadThreadSubjects (String t) -- Loads all subjects in thread t.

Vector loadSubjectArticles (String t, String s) -- Loads articles with subject s in thread t.

Vector loadUserArticles (String t, String u) -- Loads user u's posts to thread t.

To migrate, we have to support Forum 1.0 clients and future clients simultaneously. A good way to handle this is to merge the two APIs and let the client, whichever version it may be, use the calls of its choice. We'll call the merged version Forum API version 1.1.

Implementing the classes

The 1.0 Forum server is composed of two classes, ForumServer and ForumConnectionHandler. ForumServer listens to the network and threads off a new ForumConnectionHandler for each client request that comes in. Again, for a review of this process, see part 2 of our Forum application series.

First, we're going to update ForumConnectionHandler to create an instance of ForumConnectionHandlerComm and call its methods when a client request comes in, instead of hitting the local filesystem as before. We can clean up ForumServer as well, because it no longer needs several of its original methods. Then, we'll formalize the Forum 1.1 API by creating a Java interface, ForumCommInterface, which any Forum 1.1 communications library should implement. Finally, we'll add a new class, ForumConnectionHandlerComm, which will implement the ForumCommInterface with JDBC database calls.

Class ForumConnectionHandler

An instance of ForumConnectionHandler receives an incoming client request and handles it by calling the appropriate method of ForumCommInterface. Note that with this setup it is possible to plug in different implementations of ForumCommInterface without affecting how the handler functions.

import java.util.*;
public class ForumConnectionHandler extends Thread {
  // 1.0 requests
  static final int LOAD_ALL_THREADS = 1;
  static final int LOAD_THREAD_ARTICLES = 2;
  static final int POST_ARTICLE = 3;
  // new requests for 1.1
  static final int LOAD_THREAD_SUBJECTS = 4;
  static final int LOAD_SUBJECT_ARTICLES = 5;
  static final int LOAD_USER_ARTICLES = 6;
  long id;
  Socket client;
  Vector basicThreads;
  InputStream in;
  OutputStream out;
  DataInputStream dIn;
  DataOutputStream dOut;
  public ForumConnectionHandler (long i, Socket c, ThreadGroup h, Vector bt) {
    super (h, "Forum Connection Handler 1.1 " + i);
    id = i;
    client = c;
    basicThreads = bt;
  public void run() {
    try {
      in = new BufferedInputStream (client.getInputStream());
      out = new BufferedOutputStream (client.getOutputStream());
      dIn = new DataInputStream (in);
      dOut = new DataOutputStream (out);
      ForumCommInterface comm = new ForumConnectionHandlerComm ();
      String t, type = "";
      int request = -1;
      Vector threadArts;
      request = dIn.readInt();
      switch (request) {
    // 1.0 requests 
      case LOAD_ALL_THREADS:
    Hashtable threads = comm.loadAllThreads ();
    threadSetup (threads, basicThreads);
    Enumeration en = threads.keys();
    while (en.hasMoreElements())
      dOut.writeUTF ((String) en.nextElement());
    type = "LOAD_ALL_THREADS";

This code is essentially the same as that of the 1.0 version, up to the first case statement. This case calls comm's loadAllThreads (), and then sends the result to the client according to the socket-based protocol understood by the client's communications library ForumComm.

    t = dIn.readUTF();
    threadArts = comm.loadThreadArticles (t);
    Enumeration en2 = threadArts.elements();
    while (en2.hasMoreElements())
      dOut.writeUTF ((String) en2.nextElement ()); 
    dOut.writeUTF ("");
    dOut.flush ();
    type = "LOAD_THREAD_ARTICLES for thread " + t;
      case POST_ARTICLE:
    t = dIn.readUTF();
    String art = dIn.readUTF();
    comm.postArticle (art, t);
    type = "POST_ARTICLE for thread " + t;
    // stubs for 1.1 requests
    // Vector subjects = comm.loadThreadSubjects (String t);
    // Vector sa = loadSubjectArticles (String t, String s);
    // Vector ua = loadUserArticles (String t, String u);
    type = "unknown request: " + request;
      } // end switch

The last three cases (those of the new API additions) are left unimplemented. Their implementation follows that of the previous case statements and is necessary to provide the additional Forum 1.1 features to a socket-based client.

      System.out.println ("#" + id + ": " + type + " from " + client.getInetAddress()); 
    } catch (Exception ex) {
      ex.printStackTrace ();
    finally {
      try {
      } catch (IOException ex) {
    System.out.println ("Socket close for connection #" + id + " failed.");
  void threadSetup (Hashtable h, Vector t) {
    // make sure that the basic threads are in place
    Enumeration en = t.elements ();
    while (en.hasMoreElements ()) {
      String k = (String) en.nextElement ();
      if (!h.containsKey (k))
    h.put (k, new Vector ());

Any SQLExceptions thrown by the ForumConnnectionHandlerComm class are handled in the catch (Exception ex) clause. Here they're simply displayed to the server's console.

The threadSetup method, a new method for ForumConnectionHandler, makes sure that a minimum number of discussion threads are provided to the client even if the database is empty. These "basic threads" are defined in the forum config file, forum.cfg, and passed in as the vector basicThreads when the handler is constructed. You can easily modify this particular thread-choice policy by changing or overriding threadSetup .

Class ForumServer

For the sake of brevity, we won't cover the changes I've made to this class. But take a moment to look over the modified code, which is available here.

Interface ForumCommInterface

ForumCommInterface is used to provide a uniform interface to classes that expose the 1.1 Forum API. The implementation of this interface is extremely straightforward; it consists of definitions for the methods declared in by the API.

Class ForumConnectionHandlerComm

This new class, ForumConnectionHandlerComm, requires a bit of explanation. Let's break it down piece by piece.

import java.util.*;
import java.sql.*;
class ForumConnectionHandlerComm implements ForumCommInterface {
  // 1.0 Forum client sender/message delimiter for backwards compatibility
  static final String DELIMITER = "\u0000";
  // Connect Software's < > FastForward driver used here...
  static final String DRIVER = "";
  static final String URL = "jdbc:ff-microsoft://";
  static final String LOGIN = "forum";
  static final String PASSWD = "yourpassword";
  static final String DB_TABLE = "Messages";
  static {
    try {
      // try to load the ODBC driver
      Class.forName (DRIVER);
      // enable debugging by uncommenting:
      // DriverManager.setLogStream (System.out);    
    } catch (ClassNotFoundException ex) {
      ex.printStackTrace ();

Here, we initialize the driver for the database that we are accessing. You can approach the driver issue in a number of ways, depending on the type of driver that you want to use. I have chosen a type 4 (pure Java) driver to avoid using ODBC, which adds another level of indirection to the conversation with the database. The driver class is loaded in a static initializer because it only needs to be loaded once.

  Connection con;
  Statement stmt;
  ResultSet rs;
  public ForumConnectionHandlerComm () throws SQLException {
    // single-threaded: one instance is created for each 
    // ForumConnectionHandler. only one method is executed per instance 
    con = DriverManager.getConnection (URL, LOGIN, PASSWD);
    stmt = con.createStatement ();
    // connection and statement metadata could be checked here

Each call to the API will need to utilize three JDBC objects to get information from the database. These objects consist of a Connection to the database, a Statement to execute a SQL query against the database through the connection, and a ResultSet to hold the results returned by the execution of the Statement. References for these objects are declared at the top of this section of the code.

The constructor initializes con by calling the getConnection method of DriverManager. Then it calls con's createStatement () method to create stmt, an example of a simple statement, which is useful for a simple SQL statement that has no parameters. Note that stmt is not created with a specified SQL query string.

  // 1.0 API
  public Hashtable loadAllThreads () throws SQLException {
    Hashtable threads = new Hashtable ();
    String q = "SELECT Thread FROM " + DB_TABLE;
    rs = stmt.executeQuery (q);
    // load threads from 1-column ResultSet
    while ( ()) {
      String nextThread = sqlDecode (rs.getString (1));
      if (!threads.containsKey (nextThread))
    threads.put (nextThread, new Vector ());
    // clean up
    rs.close ();
    stmt.close ();
    con.close ();
    return threads;

Now we see the JDBC calls in action. The String q represents the actual SQL query. rs, a one-dimensional result set representing the Thread column of the database, is set to the return value of stmt's executeQuery method.

1 2 Page 1
Page 1 of 2