Sample connection pool manager for use with Microsoft SQL Server 2000 Driver for JDBC (313173)


The information in this article applies to:

  • Microsoft SQL Server 2000 Driver for JDBC
This article was previously published under Q313173

IN THIS TASK

SUMMARY

This step-by-step article describes connection pooling through Microsoft SQL Server 2000 Driver for JDBC.

Pool.zip is a sample that demonstrates connection pooling by using Microsoft SQL Server 2000 driver for JDBC. This sample is not a complete connection pool manager; this sample shows you how the com.microsoft.jdbcx.sqlserver.SQLServerDataSource provides hooks to implement a connection pooling. All major J2EE Application servers provide fully implemented connection pool managers.

To use the sample, unzip the files to the location where you want to store the files, and then update your class path. The .zip file contains the Mspool.jar, JNDI.properties, and Pool.log files.

For detailed instructions about how to set up the sample pool manager in a J2EE environment, see the Sample connection pool manager section of this article.

back to the top

MORE INFORMATION

Connection pooling

Connection pooling is a technique that you can use to share database connections among requesting clients. When a connection has been created and is placed in a runtime object pool, an application can use that connection again. Each application does not have to perform the complete connection process every time that it uses a connection.

When the application closes the connection, the connection is cached in the runtime object pool again. Connection pooling permits an application to use a connection from a pool of connections that do not have to be reestablished for each use.

By using pooled connections, applications can realize significant performance gains because applications do not have to perform all of the tasks that are involved in making a connection. This can be particularly significant for middle-tier applications that connect over a network, or for applications that repeatedly connect and disconnect, such as Internet applications.

back to the top

JDBC2.0 connection pooling framework

The JDBC2.0 API provides a general framework to support connection pooling; however, this API does not specify a particular connection pooling implementation. The driver vendor should provide implementation for the framework. The JDBC2.0 API contains the following classes and interfaces:
  • ConnectionEvent
    The ConnectionEvent class provides information to the pool manager about the pooled connection that generated the event, and about the SQLException in the case of an error event.
  • ConnectionPoolDataSource
    The driver vendor must implement the ConnectionPoolDataSource interface. This is used as a factory to create PooledConnections.
  • PooledConnection
    The driver vendor must implement the PooledConnection interface, which provides hooks for connection pooling.
  • ConnectionEventListener
    The pooling component must implement the ConnectionEventListener interface to receive the events that are generated by the PooledConnection object.
back to the top

Microsoft SQL Server 2000 Driver for JDBC DataSource implementation

Microsoft SQL Server 2000 driver for JDBC provides the following classes, which implement the required interfaces that are specified by the JDBC2.0 specification to support connection pooling:

The com.microsoft.jdbcx.sqlserver.SQLServerDataSource class implements javax.sql.DataSource, javax.sql.ConnectionPoolDataSource, and javax.naming.Referenceable interfaces.

The com.microsoft.jdbcx.sqlserver.SQLServerDataSourceFactory class implements javax.naming.spi.ObjectFactory.

By using these classes, you can register the data source under a naming context. These classes also provide hooks for the connection pooling that the J2EE environment provides.

Microsoft SQL Server 2000 Driver for JDBC provides all of the hooks that you must have to implement a pool manager for the third-party vendors. Generally, J2EE containers come with a connection pool manager. You can use the com.microsoft.jdbcx.sqlserver.SQLServerDataSource to set up a connection pool.

For this demonstration, use the sample connection pool manager to test the driver data source.

back to the top

Create a DataSource object and register the DataSource object in the JNDI environment

The following sample code shows you how to create the PooledConnectionDatasource object, and then bind it to naming context.

In the code, you get the naming context (getContext) to which you bind the data source. Then the code instantiates the SQLServerDataSource object, sets the required attributes for SQLServerDataSource, and then binds the object to the naming context.

The name to which the SQLServerDataSource binds is used by pool manager as an object factory. 
/** The constructor expects the naming server URL and the context provider class. 
*    For example, the sample URL for ldap server on localhost can be 
*	ldap://localhost:389/ou=jdbc,cn=manager,dc=microsoft,dc=com,
*    and the provider class name can be com.sun.jndi.ldap.LdapCtxFactory		
*/ 
import com.microsoft.jdbcx.sqlserver.SQLServerDataSource;
import java.util.Hashtable;
import javax.naming.*;
import javax.naming.directory.*;
import javax.sql.*;
import java.sql.*;

public class JNDISetup
{
	
	
     Context ctx = null;
     String url;
     String factory;    
     JNDISetup(String url, String factory){
          this.url=url;
               this.factory=factory;    
          getContext();
     }
	
         private void getContext(){
          try{
               Hashtable env = new Hashtable();
               env.put(Context.INITIAL_CONTEXT_FACTORY, factory);
               env.put(Context.PROVIDER_URL, url);
               ctx = new InitialContext(env);
          }catch(Exception e){
               System.out.println("Error in SetupJNDI:getContext() "+e.getMessage());
               e.printStackTrace();
          }	
		
     }
     public boolean bindDataSource(String bindName)
         {
               boolean isRegistered =false;
                    try
                    {
                         SQLServerDataSource mds = new SQLServerDataSource();
                         mds.setDescription("MS SQLServerDataSource");
                         mds.setServerName("sqlserver");
                         mds.setPortNumber(1433);
                         mds.setDatabaseName("pubs");
                         mds.setSelectMethod("cursor");
                         ctx.rebind(bindName, mds);
                         System.out.println("Bind success");
                         isRegistered=true;
                    }
                         catch(Exception e)
                    {
                         System.out.println("Error Occurred in JNDISetup:     " + e.getMessage());
                         e.printStackTrace();
                    }
                    return isRegistered;
                    } 
			
          public javax.sql.ConnectionPoolDataSource getDataSource(String bindName){
			
               javax.sql.ConnectionPoolDataSource ds = null;
			
               try{							
                         ds = (javax.sql.ConnectionPoolDataSource) ctx.lookup(bindName);
               }catch(Exception e){
                    System.out.println("Error in JNDISetup:getDataSource() : "+e.getMessage());
                    e.printStackTrace();
               }
               return ds;
			
          }
}
back to the top

Create a connection by using the sample connection pool manager

The client application requests a DataSource object from the JNDI environment, and then uses the DataSource object to get the connection and to release the connection. The pool manager verifies that the connection will be reused by storing the connection in memory cache, for example: 
javax.sql.DataSource ds = (javax.sql.DataSource)context.lookup("jdbc/mypool");
      Connection con = ds.getConnection();
          ...............
      con.close();
In the sample, the jndi lookup name "jdbc/mypool" is bound to a poolmanager that returns a PooledDataSource.

back to the top

Sample connection pool manager

To download a sample connection pool manager, visit the following Microsoft Web site:

Pool.zip
http://download.microsoft.com/download/1/8/d/18d7832c-5c96-460b-9264-80e424c7625a/pool.exe

Use this connection pool manager to test the SQLServerDataSource connection pooling support.

Important note No questions about the information that is contained in the document, or concerning the use, modification, or implementation of the sample connection pool manager will be addressed by Microsoft. The user accepts all risk that is associated with the use of these files, the information contained therein, its dissemination, and accuracy.

The files that are contained in the attachment are for testing and demonstration purposes only. Do not implement these files in a production environment. Do not interpret these files as a recognized Microsoft product.

Sample ConnectionPool implementation details

Class hierarchy:
  1. com.microsoft.jdbcx.sqlserver.SQLServerDataSourcecom.microsoft.jdbcx.sqlserver.SQLServerDataSourceFactorycom.microsoft.mspool.PoolDataSourcecom.microsoft.mspool.PoolDataSourceFactorycom.microsoft.mspool.Poolcom.microsoft.mspool.Pool$ConnectionEventManagercom.microsoft.mspool.Pool$PoolMonitorcom.microsoft.mspool.PoolHandler
  2. com.microsoft.jdbcx.sqlserver.SQLServerDataSourceFactorycom.microsoft.mspool.PoolDataSourcecom.microsoft.mspool.PoolDataSourceFactorycom.microsoft.mspool.Poolcom.microsoft.mspool.Pool$ConnectionEventManagercom.microsoft.mspool.Pool$PoolMonitorcom.microsoft.mspool.PoolHandler
  3. com.microsoft.mspool.PoolDataSourcecom.microsoft.mspool.PoolDataSourceFactorycom.microsoft.mspool.Poolcom.microsoft.mspool.Pool$ConnectionEventManagercom.microsoft.mspool.Pool$PoolMonitorcom.microsoft.mspool.PoolHandler
  4. com.microsoft.mspool.PoolDataSourceFactorycom.microsoft.mspool.Poolcom.microsoft.mspool.Pool$ConnectionEventManagercom.microsoft.mspool.Pool$PoolMonitorcom.microsoft.mspool.PoolHandler
  5. com.microsoft.mspool.Poolcom.microsoft.mspool.Pool$ConnectionEventManagercom.microsoft.mspool.Pool$PoolMonitorcom.microsoft.mspool.PoolHandler
  6. com.microsoft.mspool.Pool$ConnectionEventManagercom.microsoft.mspool.Pool$PoolMonitorcom.microsoft.mspool.PoolHandler
  7. com.microsoft.mspool.Pool$PoolMonitorcom.microsoft.mspool.PoolHandler
  8. com.microsoft.mspool.PoolHandler
Configuration files:
  1. JNDI.propertiesPool.log
  2. Pool.log
About the classes:
  • com.microsoft.mspool.PoolDataSource
    This class is used to register the pool properties and uses com.microsoft.mspool.Pool to return pooled connection objects. This class holds the pooling related properties such as the following:
    • maximum and minimum connections the pool can provide
    • IdleTimeout to invalidate a connection after sitting idle for a long time
    • refreshPoolTime to check connections status
    • JndiRefName of the datasource.
    This class is registered in the JNDI environment and can be reached by any client by using JNDI lookup. Before you can register this class, you must register the com.microsoft.jdbcx.sqlserver.SQLServerDataSource in the JNDI environment.
  • com.microsoft.mspool.PoolDataSourceFactory
    This class is used as a factory that returns com.microsoft.mspool.PoolDataSource objects.
  • com.microsoft.mspool.Pool
    This class contains the mechanism to cache connections and to monitor connections by using two inner classes, ConnectionEventManager and PoolMonitor.
  • com.microsoft.mspool.PoolHandler
    This class contains methods to store and to write log data. You can extend this class to provide more utilities. This class holds the static reference to the pools. When you are integrating the pool manager into a Java application server (such as tomcat), this class is initiated at server startup. This class is implemented as a servlet that can be instantiated when the Java application server starts.
Setup:
  1. Register com.microsoft.jdbcx.sqlserver.SQLServerDataSource in the JNDI environment. The following code is a sample configuration that is used in tomcat:
         <Resource name="jdbc/sql2000" auth="Container" 
               type="com.microsoft.jdbcx.sqlserver.SQLServerDataSource"/>
     <ResourceParams name="jdbc/sql2000">
          <parameter>
               <name>factory</name>
               <value>com.microsoft.jdbcx.sqlserver.SQLServerDataSourceFactory</value>
          </parameter>
          <parameter>
               <name>driverClassName</name>
               <value>com.microsoft.jdbcx.sqlserver.SQLServerDataSource</value>
          </parameter>
          <parameter><name>driverName</name><value>SQLServer</value></parameter>
          <parameter><name>description</name><value>SQL Server DataSource</value></parameter>
          <parameter><name>serverName</name><value>wdserver876</value></parameter>		

          <parameter><name>portNumber</name><value>1433</value></parameter>
          <parameter><name>selectMethod</name><value>cursor</value></parameter>
          <parameter><name>databaseName</name><value>pubs</value></parameter>
          <parameter><name>user</name><value>sridhar</value></parameter>
          <parameter><name>password</name><value>password</value></parameter>
          <parameter><name>loginTimeout</name><value>3000</value></parameter>
     </ResourceParams>
  2. Register com.microsoft.mspool.PoolDataSource in the JNDI environment, as in the following sample code:
         <Resource name="jdbc/mspool" auth="Container" type="com.microsoft.mspool.PoolDataSource"/>
     <ResourceParams name="jdbc/mypool">
          <parameter>
               <name>factory</name>
               <value>com.microsoft.mspool.PoolDataSourceFactory</value>
          </parameter>
          <parameter>
               <name>className</name>
               <value>com.microsoft.mspool.PoolDataSource</value>
          </parameter>
          <parameter>
               <name>description</name>
               <value>MS_SQL_Pool_DataSource</value>
          </parameter>
          <parameter><name>poolName</name><value>wdserverpool</value></parameter>
          <parameter><name>maxConnections</name><value>20</value></parameter>
          <parameter><name>minConnections</name><value>1</value></parameter>
          <parameter><name>checkEvery</name><value>20000</value></parameter>
          <parameter><name>idleTimeout</name><value>500000</value></parameter>
          <parameter><name>dsJndiName</name><value>jdbc/sql2000</value></parameter>
     </ResourceParams>
    • In the descriptor in the sample code, the maxConnections and minConnections properties specify the maximum and minimum number of connections that the pool permits.
    • The CheckEvery property specifies at what time interval the pool checks for idle connections. The sample value for this property is every 900000 milliseconds (15mns).
    • The idleTimeOut property specifies how much time a connection can exist as an idle connection in the free pool. The sample value is 1800000 milliseconds (30mns).
    • The dsJndiName property specifies the name to which the SQLServerDataSource binds in JNDI.
The PoolHandler object is initiated to hold the pools that are created. In the sample deployment, which uses Tomcat 4.0, this is deployed as a servlet in a Web application that uses the pool. In this Web.xml servlet registration is the sample descriptor tag that is specified in Tomcat's demo application: 
  <servlet>
    <servlet-name>MSPool</servlet-name>
    <servlet-class>com.microsoft.mspool.MSPoolHandler</servlet-class>
    <init-param>
      <param-name>wdserverpool</param-name>
      <param-value>jdbc/mypool</param-value>
    </init-param>
   </servlet>
In this servlet tag, the name of the servlet, the implementation class, and one parameter are specified. In this sample, the parameter name is wdserverpool and value is jdbc/pool.

The first parameter name should be the name of the poolName property that is specified in the PoolDataSource binding, and the value should be the name to which the PoolDataSource binds in the JNDI environment. The PoolHandler class creates pools by using this parameter. If you must create two pools, register two PoolDataSources, and then specify the parameters accordingly.

The sample uses a JNDI.properties file to pass the JNDI environment properties to this servlet. In this file, specify variables and values for the following:
  • InitialContext, which is application server specific. In the case of Tomcat, this is null.
  • Base, which is application server specific. In the case of Tomcat, this is java:comp/env.
  • logfile, which is the fully qualified path to a log file where the pool writes its activities or errors.
When you have completed this configuration, use the following sample code to retrieve the connection from the pool: 
Context ctx = new InitialContext(); 
Context envCtx=(Context)ctx.lookup("java:comp/env");
DataSource ds = (DataSource)envCtx.lookup("jdbc/mypool");
if(ds!=null){
    java.sql.Connection dbConnection = ds.getConnection();
    java.sql.Statement dbStatement = dbConnection.createStatement();
    java.sql.ResultSet dbResultSet = dbStatement.executeQuery(query);
    while(dbResultSet.next()) {
       .........................
    } 
    dbResultSet.close();
    dbStatement.close();
    dbConnection.close();
The first two lines of this sample code are different for different J2EE servers. You get a DataSource object from the JNDI by using the name to which your PoolDataSource class binds, you take a connection, use the connection, and then close the connection. The client is not aware of whether you use pool; the code is always same. The only thing that you must change is the different bind name variable in the lookup method.

You can set up this sample pool manager on any Java application server; only the setup process changes. For each different application server, you must know how to create JNDI reference objects to register PoolDataSource and SQLServerDataSource, and you must know how to initialize the startup classes to initialize PoolHandler.

The sample pool manager download files contain a Readme file that shows you how to set up pool manager in Apache/Tomcat 4.0, and includes a sample that uses the pool manager. You can extend this pool manager to support TX Connections and any custom features that you want.

The sample also has a Test .jsp file that you can use to test the pool manager.

back to the top

REFERENCES

The following file is available for download from the Microsoft Download Center:

Pool.zip
http://download.microsoft.com/download/1/8/d/18d7832c-5c96-460b-9264-80e424c7625a/pool.exe

Release Date: June-12-2002

For more information about how to download Microsoft support files, click the following article number to view the article in the Microsoft Knowledge Base:

119591 How to obtain Microsoft support files from online services

Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help prevent any unauthorized changes to the file.

The Pool.zip file contains the following files:

Mspool.zipFile name
Jar FilePool.jar
Log FilePool.log
JNDI setup fileJNDI.properties
Readme fileReadme.txt
Test fileTestPoolManager.jsp


For more information, see the following documents:
  • JDBC2.0 Specification
  • Tomcat 4.0 Documentation
back to the top
Modification Type:MinorLast Reviewed:11/22/2005
Keywords:kbdownload kbfile kbinfo KB313173
©2004 Microsoft Corporation. All rights reserved.