Standalone Datasources | Real World Web Services

Under certain circumstances, however, you may not have the option of deploying (or testing) application code within the context of your application server, but you might still want to rely on a pooled javax.sql.DataSource for your connectivity. Here, we will look at an example of executing an application that makes use of a custom DataSourceand test to see just how much of a performance improvement pooling a series of database connections affords an application. Figure 6-2 shows our two classesa series of worker threads, executing a test SQL string, and a test harness to create the shared DataSource.

Figure 6-2. Testing pooled versus non-pooled JDBC connectivity.

Client Connectivity

As shown in Listing 6-1, our worker thread is given a connection at creation, runs the test SQL, and then carefully returns the connection. There is no reference to a DataSource or any notion of how the connection is obtainedthe thread merely runs the code and terminates. In many ways, this is similar to the activity of a typical web application as might be found in a JSP, servlet, or when using EJB's Bean-Managed Persistence.

Listing 6-1. Simple Database Connectivity

 package com.cascadetg.ch06; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class PerformConnection extends Thread {     // The test SQL we are sending to the database.     private static final String testSQL = "SELECT NOW()";     Connection connection;     // Note that by providing this constructor, the compiler will not     // generate a no-op default constructor for us.     PerformConnection(Connection myConnection)     {         this.connection = myConnection;     }     public void run()     {         Connection myConnection = connection;         Statement myStatement = null;         ResultSet myResult = null;         try         {             // There's no advantage to creating a prepared             // statement for a simple SQL statement such as NOW()             myStatement = myConnection.createStatement();             myResult = myStatement.executeQuery(testSQL);             // Get the resulting data back, and loop through it             // to simulate the data retrieval.             int numcols = myResult.getMetaData().getColumnCount();             while (myResult.next())             {                 for (int i = 1; i <= numcols; i++)                 { myResult.getString(i); }             }         } catch (SQLException e)         {             e.printStackTrace();         } finally         {             // We want to be agressive about ensuring that our             // connection is properly cleaned up and returned to             // our pool.             try { myResult.close(); } catch (Exception e) { }             try { myStatement.close(); } catch (Exception e) { }             try { myConnection.close(); } catch (Exception e) { }         }     } }

Building the DataSource

The code shown in Listing 6-2 shows how a pooled DataSource can be set up and configured. Pay attention to configuration details specified in the init() method , such as the maximum size of the pool.

Listing 6-2. Simple Database Connectivity

 package com.cascadetg.ch06; import java.sql.DriverManager; import java.sql.Connection; import java.sql.SQLException; // commons-dbcp.jar import org.apache.commons.dbcp.cpdsadapter.DriverAdapterCPDS; //Note that the SharedPoolDataSource relies on the //commons-collection.jar, so you will need that on your classpath //as well. import org.apache.commons.dbcp.datasources.SharedPoolDataSource; public class ConnectionPoolExample {     // You'll want to change these strings for the database you're     // using.  This example was performed using a default     // installation of the MySQL database (www.mysql.com)     private static final String driver = "com.mysql.jdbc.Driver";     private static final String username = "root";     private static final String password = "";     private static final String jdbcConnection =         "jdbc:mysql://localhost/commons";     private static SharedPoolDataSource sharedDatasource;     static void init()     {         DriverAdapterCPDS myConnectionPoolDatasource =             new DriverAdapterCPDS();         try         { myConnectionPoolDatasource.setDriver(driver);         } catch (Exception e) { }         myConnectionPoolDatasource.setUrl(jdbcConnection);         myConnectionPoolDatasource.setUser(username);         myConnectionPoolDatasource.setPassword(password);         sharedDatasource = new SharedPoolDataSource();         sharedDatasource.setConnectionPoolDataSource(             myConnectionPoolDatasource);         sharedDatasource.setMaxActive(10);         sharedDatasource.setMaxWait(50);     }     public static Connection getConnection() throws SQLException     { return sharedDatasource.getConnection(); }     public static void main(String[] args)     {         init();         try         {             // First we fill up the pool before starting our timings             for (int x = 0; x < 15; x++)             {                 Connection conn = getConnection();                 new PerformConnection(conn).start();             }             long currentTiming = System.currentTimeMillis();             // Perform the SQL operation many times.  Notice that             // we are kicking these off as simultaneous threads,             // closer to the behavior one might expect in a             // multi-threaded environment such as a web application             for (int x = 0; x < 1000; x++)             {                 Connection conn = getConnection();                 new PerformConnection(conn).start();             }             while (sharedDatasource.getNumActive() > 0)             {   // Need to wait until all of the connections                 // have completed.             }             float timing =                 (System.currentTimeMillis() - currentTiming)                     / 1000f;             System.out.println("Seconds with pool : " + timing);             currentTiming = System.currentTimeMillis();             // Now, we do the same number of tests, this time             // opening and closing the connection using the driver             // directly.             for (int x = 0; x < 1000; x++)             {                 Connection conn =                     DriverManager.getConnection(                         jdbcConnection, username, password);                 new PerformConnection(conn).run();             }             while (sharedDatasource.getNumActive() > 0)             {   // Need to wait until all of the connections                 // have completed.             }             timing =                 (System.currentTimeMillis() - currentTiming) / 1000f;             System.out.println("Seconds without pool : " + timing);             // Display some pool statistics             System.out.println(                 "NumActive: " + sharedDatasource.getNumActive());             System.out.println(                 "NumIdle: " + sharedDatasource.getNumIdle());         } catch (Exception e) { e.printStackTrace(); }     } }

Even though the code in Listing 6-2 uses an org.apache.commons.dbcp.datasources.SharedPoolDataSource, if needed that object could have been cast or passed as a javax.sql.DataSource.

When run, this application reports the timings. An example of the output on my system, connecting to a local MySQL database, is shown in Listing 6-3. Notice that even though the pool was configured to allow up to ten possible connections, only eight are actually usedthis is because the connections are returned to the pool fast enough by our client code that only eight connections are ever opened. It's easy to imagine certain lengthy client operations that might take longer to return, causing the connection pool to grow to the maximum possible size.

Listing 6-3. Sample Pooling vs. No Pooling Timings

 Seconds with pool : 1.542 Seconds without pool : 6.219 NumActive: 0 NumIdle: 8

As can be seen in Listing 6-3, the use of connection pooling allows the application to run the test suite in roughly one-quarter the timenot bad, considering no modifications to the client code were necessary to take advantage of the pool.