Create your own type 3 JDBC driver, Part 3

Enhance the custom JWDriver with advanced logging, connection pooling, and Predefined Data Sets

Architecturally, type 3 JDBC (Java Database Connectivity) drivers prove flexible enough that you can easily add enhancements. In Parts 1 and 2 of this three-part series, you learned how to create a custom type 3 JDBC driver, named JWDriver, that employed RMI (Remote Method Invocation) to communicate with the middle tier. In this article, the last of the series, we demonstrate how to enhance JWDriver's functionality with three new features:

  • SQL statement logging for diagnosis
  • Database connection pooling for faster connection retrieval
  • Predefined Data Sets (PDS) for improved network latency

Let's examine each new feature in turn.

Note: You can download this article's source code from Resources.

Feature 1: SQL statement logging for diagnosis

Business applications typically contain numerous SQL data-manipulation and data-retrieval statements to implement various business features. You sometimes may wish to log for later analysis such SQL statements as they execute on the database server. Other uses for logging SQL statements include finding, during the development life cycle, performance bottlenecks from slow SQL statements, or tracking hard-to-find bugs in a complex business transaction containing multiple SQL statements.

To add SQL statement logging capability to JWDriver, begin by adding a new class, DriverLog, in the driver's middle tier. That class provides the interface to write SQL statements to a predefined file. Each executed SQL statement passes through the remote statement class's executeQuery() and executeUpdate() methods in the middle tier, so the two methods contain the call to the DriverLog methods to log the SQL statements.

Let's now see DriverLog's implementation and its use in the driver's remote statement class.

The DriverLog class

The SQL statement logging occurs in the Web server, where the driver's middle tier is deployed. Create DriverLog under the middle tier's com.jw.server package. To handle concurrent clients, DriverLog employs the Singleton pattern by making its constructor private to ensure only one instance. The getInstance() method returns the DriverLog reference. The public logQuery() method actually logs the SQL statements into the log file.

The DriverLog constructor opens the C:\TEMP\JWDriver.log disk file for logging SQL statements. The constructor uses the PrintWriter and FileWriter classes to access the JWDriver.log file. The constructor also opens JWDriver.log in append mode. Here's the constructor's code:

                                       logFileWriter = new FileWriter(logFile,true);
                                       logPrintWriter = new PrintWriter(logFileWriter,true);
                                       catch(Exception ex)
                                       logFileWriter = null;
                                       logPrintWriter = null;

Since we've marked the DriverLog class constructor as private, the class is accessed via a public getInstance() method. The getInstance() method always returns the same DriverLog class reference. The single DriverLog instance is maintained in a static class variable singleLogInstance and created only once as soon as the getInstance() method is first accessed:

public static DriverLog
                                       if(singleLogInstance == null)
                                       singleLogInstance = new DriverLog();
                                       return singleLogInstance;

The remote statement calls the public logQuery() method to log SQL statements into the file. The logQuery() method, which logs the SQL statements with the current date and time, includes a synchronized block that ensures the concurrent clients access the log file one at a time. logQuery() logs SQL statements only if you've switched the logging on, which you'll see in the next section. Here's the code:

public void
                                       logQuery(String query){
                                       if(RemoteDriverImpl.queryLog == 1){
                                       if(logPrintWriter != null){   
                                       Date theDate = new Date();
                                       String fullMsg = "Logged at:" + theDate.toString();
                                       catch(Exception ex){}

Log SQL statements in the server

The SQL statements get logged in the driver middle-tier machine in a JWDriver.log file. Switch on SQL statement logging by setting QueryLog to 1 in the file. (You can switch logging back off by setting QueryLog to 0.)

The driver client tier passes the SQL statement to the middle tier via IRemoteStatement for execution in the database server. The executeQuery() and executeUpdate() methods in RemoteStatementImpl then call the logQuery() method to log the SQL statements before execution:

public IRemoteResultSet executeQuery(String Query) throws RemoteException,SQLException{
                                       // Log the SQL statement
                                       ResultSet rs = sqlStatment.executeQuery(Query);
                                       RemoteResultSetImpl remoteRs = new RemoteResultSetImpl(rs);
                                       return (IRemoteResultSet)remoteRs;
                                       public int executeUpdate(String Query) throws RemoteException,SQLException{
                                       // Log the SQL statement
                                       return sqlStatment.executeUpdate(Query);

Feature 2: Database connection pooling for faster connection retrieval

Establishing a database connection to execute a SQL statement always takes time. To increase an application's performance, you can cache database connections, a feature you can easily implement in JWDriver. Whenever a connection request to the database server comes from the middle tier to serve the client application request, the connection returns from the pool. When the client application closes a connection, the connection in the middle tier returns to the pool so that it could serve other client requests. All that happens with the client unaware whether connection pooling is implemented in the middle tier.

We will cover the following topics to discuss JWDriver's connection-pooling support:

  • The connection pool
  • Initializing the connection pool
  • Retrieving the connection from the pool
  • Returning the connection to the pool

The connection pool

The ConnectionPool class, created in the driver middle tier under the com.jw.server package, implements the connection pool. ConnectionPool employs the Singleton pattern to handle the concurrent clients by making its constructor private to ensure only one instance. The getInstance() method returns a ConnectionPool reference. ConnectionPool provides the addConnection() method for adding the connections to the pool during the server's launch. The included getConnection() method gets the connection from the pool.

The constructor creates the empty stack that will hold the connections as:

                                       connectionPool = new Vector();

ConnectionPool is accessed via the public getInstance() method, which always returns the same reference of the ConnectionPool class. The single ConnectionPool instance is maintained in a static class variable connectionPoolInstance. The single connectionPoolInstance instance is created only once when the getInstance() method is first accessed. The code for the getInstance() method is given below:

public static ConnectionPool getInstance()
                                       if(connectionPoolInstance == null)
                                       connectionPoolInstance = new ConnectionPool();
                                       return connectionPoolInstance;

The ConnectionPool class keeps the pooled database connections in a stack implemented by the Vector object. ConnectionPool provides two methods—addConnection() and getConnection()—to access the connection pool stack. These methods push and pop the connection from the stack, respectively:

public synchronized
                                       void addConnection(Connection con){
                                       // Add the JDBC-ODBC bridge connection to the pool
                                       public synchronized Connection getConnection(){
                                       Connection con = null;
                                       if(connectionPool.size() > 0){   
                                       con = (Connection)connectionPool.lastElement();
                                       connectionPool.removeElementAt(connectionPool.size() - 1);
                                       return con;

Initialize the connection pool

The connection pool initializes when JWDriver's RMI server starts up. The RemoteDriverImpl class creates the JDBC-ODBC (Open Database Connectivity) bridge connections and adds them into the pool:

private void initializeConnectionPool(){
                                       String URL="jdbc:odbc:"+DSN;
                                       for(int i = 0; i < connectionPoolSize; i++){
                                       Connection sqlCon = DriverManager.getConnection(URL,dsUser,dsPassword);

You can configure the connection pool's size and change it by setting the ConnectionPoolSize value in the file. The setting below specifies the connection pool size as 5:


Retrieve the connection from the pool

Whenever a client program requests a connection, JWDriver's client tier delegates the request to the middle tier. The RemoteDriverImpl's getConnection() method then gets the connection from the connection pool. If the connection pool has no free connection, an exception throws:

1 2 3 Page 1
Page 1 of 3