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.
Why JDBC?
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.
| Thread | Sender | Subject | Article |
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.
|
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.
|
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 {
client.close();
} 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.net.*;
import java.util.*;
import java.io.*;
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 < www.connectsw.com > FastForward driver used here...
static final String DRIVER = "connect.microsoft.MicrosoftDriver";
static final String URL = "jdbc:ff-microsoft://machine.yourdomain.com:1433";
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 (rs.next ()) {
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.
Each ResultSet has a “cursor” that points at the current row of data in the result set. The next () method advances this cursor to point to the next row. The getString (i) method looks at column i of the current row and returns a String. In this code, we are encoding and decoding everything going to and coming from the database, in order to avoid potential problems with characters the database considers magic, such as ‘ and %. We check to see if the newly obtained discussion thread is in the return Hashtable; if it isn’t, we add it.
This is the only request that this instance of ForumConnectionHandlerComm will deal with, so we close rs, stmt, com.
public Vector loadThreadArticles (String t) throws SQLException {
Vector arts = new Vector ();
String q = "SELECT Sender, Article FROM " + DB_TABLE + " WHERE Thread LIKE '" +
sqlEncode (t) + "'";
rs = stmt.executeQuery (q);
while (rs.next ())
arts.addElement (sqlDecode (rs.getString (1)) + DELIMITER + sqlDecode (rs.getString (2)));
rs.close ();
stmt.close ();
con.close ();
return arts;
}
The loadThreadArticles method returns all the articles in a thread. Because the Sender and Article fields are collapsed together by the 1.0 Forum spec, they must be retrieved from the 1.1 database as separate fields and returned together, separated by a delimiter, so that the 1.0 client will understand them.
public boolean postArticle (String art, String t) throws SQLException {
String s = art.substring (0, art.indexOf (DELIMITER));
String a = art.substring (art.indexOf (DELIMITER) + 1);
String q = "INSERT INTO " + DB_TABLE + " (Sender, Article, Thread) " + " VALUES ('" + sqlEncode (s) + "', '" + sqlEncode (a) + "', '" +
sqlEncode (t) + "')";
stmt.executeUpdate (q);
stmt.close ();
con.close ();
return true;
}
The postArticle method takes the article from the 1.0 client as a parameter, separates it into Sender and Article fields, and does an SQL INSERT. The stmt executeUpdate method is used to execute INSERT, UPDATE, and related SQL statements. It returns no ResultSet, so only stmt and con need to be closed at the end of the method.
// 1.1 API additions
public Vector loadThreadSubjects (String t) throws SQLException {
Vector subs = new Vector ();
String q = "SELECT Subject FROM " + DB_TABLE + " WHERE Thread LIKE '" +
sqlEncode (t) + "'";
rs = stmt.executeQuery (q);
// Note that the DB can return nulls for Subjects, so ignore them
while (rs.next ()) {
String next = (String) rs.getString (1);
if (next != null)
subs.addElement (sqlDecode (next));
}
rs.close ();
stmt.close ();
con.close ();
return subs;
}
The sample 1.1 API we’re developing here is designed to be used by a more full-featured client than the 1.0 Forum client. The loadThreadSubjects method retrieves all the subjects associated with discussion thread t with the appropriate SQL call.
public Vector loadSubjectArticles (String t, String s) throws SQLException {
Vector subArts = new Vector ();
String q = "SELECT Article FROM " + DB_TABLE + " WHERE Thread LIKE '" +
sqlEncode (t) + "' AND Subject LIKE " + "'" + sqlEncode (s) + "'";
rs = stmt.executeQuery (q);
while (rs.next ())
subArts.addElement (sqlDecode (rs.getString (1)));
rs.close ();
stmt.close ();
con.close ();
return subArts;
}
public Vector loadUserThreadArticles (String t, String u) throws SQLException {
Vector threadArts = new Vector ();
String q = "SELECT Article FROM " + DB_TABLE + " WHERE Thread LIKE '" +
sqlEncode (t) + "' AND Sender LIKE " + "'" + sqlEncode (u) + "'";
rs = stmt.executeQuery (q);
while (rs.next ())
threadArts.addElement (sqlDecode (rs.getString (1)));
rs.close ();
stmt.close ();
con.close ();
return threadArts;
}
The two other 1.1 API calls are implemented in a fashion similar to the first.
String sqlEncode (String txt) {
// escape character is ^
StringBuffer e = new StringBuffer ();
for (int i = 0; i < txt.length (); i ++) {
char c = txt.charAt (i);
if (c < 16)
e.append ("^0" + Integer.toString (c, 16));
else if ((c < 32) || (c > 127) || ("%_^'"".indexOf (c) >= 0))
e.append ("^" + Integer.toString (c, 16));
else
e.append (c);
}
return e.toString ();
}
String sqlDecode (String txt) {
// escape character is ^
StringBuffer d = new StringBuffer ();
for (int i = 0; i < txt.length (); i ++) {
char c = txt.charAt (i);
if (c == '^') {
String hex = txt.substring (i + 1, i + 3);
char dec = (char) Integer.parseInt (hex, 16);
d.append (dec);
i += 2;
}
else
d.append (c);
}
return d.toString ();
}
}
The sqlEncode method is used to escape certain characters so that they will not be misinterpreted by the database when they are sent as queries. The character ^ is an arbitrary choice to indicate that the hex-encoding of the escaped character is about to follow. The sqlDecode method simply looks for the escape character and translates the hex code that follows back into the original character.
Alternative implementations
This implementation we’ve covered is not necessarily the best for every situation. Alternatively, you could open a single, serialized connection instead of a new connection for each client request. In this case, you would synchronize each method body on
ForumConnectionHandlerComm
‘s class object. For example, the method
loadSubjectArticles (String t, String s)
becomes:
public Vector loadSubjectArticles (String t, String s) throws SQLException {
synchronized (getClass ()) {
Vector subArts = new Vector ();
String q = "SELECT Article FROM " + DB_TABLE + " WHERE Thread LIKE '" +
sqlEncode (t) + "' AND Subject LIKE " + "'" + sqlEncode (s) + "'";
rs = stmt.executeQuery (q);
while (rs.next ())
subArts.addElement (sqlDecode (rs.getString (1)));
rs.close ();
stmt.close ();
con.close ();
return subArts;
}
}
Using this approach, one instance of ForumConnectionHandlerComm is then created to establish the database connection and make the database calls as ForumConnectionHandler accepts requests. In this implementation, ForumConnectionHandlerComm will also need a method to close the database connection when the application is through.
A more interesting implementation option would be to create n instances of ForumConnectionHandlerComm and put them into a static queue in ForumConnectionHandler. Each ForumConnectionHandler would take the next ForumConnectionHandlerComm out of the queue to process its request against the database. If none was available, the ForumConnectionHandler would block until the queue handed up the next ForumConnectionHandlerComm. When the instance of ForumConnectionHandlerComm finished processing the request, it would return itself to the queue. This implementation would enable a fixed number of connections to be reused in parallel. This approach is helpful when creating connections on the fly is not the best option.
Conclusion
Java-based middleware is great. Among other things, it allows a scalable Java implementation that does not suffer from browser socket restrictions for applets. JDBC is also very useful, because it provides a uniform interface for accessing SQL databases.
Using JDBC to convert the Forum server into a middleware tier enables us to extend the Forum system. As an added bonus, because the API is now defined with an interface, any class that implements it may be exchanged for any other. For example, clients written to JDK 1.1 can plug ForumConnectionHandlerComm directly into the client in place of ForumComm and access databases on the server with only minor modifications. This will allow clients to access the database directly, assuming that security restrictions are not violated.
