How to Use Connection Pooling?

by Dr. Wenjie He

In web applications, the performance of database access is extremly important. Establishing and maintaining a database connection is a costly operation. The Connection Pooling technique provides an efficient way to manage the database resource in an application server.

1. Downloading the example

1. Download the example for this tutorial GuestBookCP.zip.
2. Examine the files in this example:

	The database script: GuestBookCP\db\guestbookdb.sql
	Two JSP files: GuestBookCP\jsp\guestBookView.jsp, guestBookLogin.jsp
	Two JavaBeans: GuestBookCP\src\org\me\jsp\beans\GuestBean.java,
	GuestDataBean.java
	Two XML configuration files: GuestBookCP\config\web.xml, context.xml,

2. Creating the database

If you have never done this before, read this tutorial A Shortcut to Create MySQL Databases first.

If you did this tutorial How to Use JavaBeans in JSPs? before, you can skip this step.

1. Copy the database script guestbookdb.sql from GuestBookCP\db\ to C:\Javux\MyScripts\MySQL.
2. Double-click the icon of createMySQLDB on the Desktop. A command window pops up.
3. Enter the password for the user javux (that is javuxpass, if it does not work, go to How to Create a User Account in MySQL?) to pass the login step.
4. Type the following command to create a database,

create database guestbookdb;
    

5. Use the database with the following command

use guestbookdb;
    

6. Populate the database with the following command

source guestbookdb.sql;
    

3. Running the example

If you want to know how to create a Dynamic Web Project, a package, and a servlet in Eclipse for Java EE, and how to run the project, see the tutorial Developing a Web Application in Eclipse for Java EE

1. Create a Dynamic Web Project called GuestBookCP in the Eclipse for Java EE.
2. Create a package called org.me.jsp.beans.
3. Copy the files GuestBean.java and GuestDataBean.java from the folder GuestBookCP\src\org\me\jsp\beans to the package org.me.jsp.beans by drag-and-drop.
4. Copy the files guestBookLogin.jsp and guestBookView.jsp from the folder GuestBookCP\jsp to the node WebContent under the project.
5. Copy the file web.xml from the folder GuestBookCP\config to the node WEB-INF under WebContent and replace the existing one.
6. Copy the file context.xml from the folder GuestBookCP\config to the node META-INF under WebContent.
7. Copy the MySQL driver's library file mysql-connector-java-5.1.10-bin.jar from C:\Javux\DBMS\MySQLdriver into your [TomcatHome]\lib if you have not done this before.
8. Run the project. You would see,
    
After you enter the data for a guest and submit, you would see,

4 Database connections

1. Database connections are often expensive to create because of the following factors:
• the overhead of establishing a network connection
• the overhead of initializing a database connection session in the back end database
• The connection session initialization often requires time consuming processing to perform user authentication, establish transactional contexts and establish other aspects of the session that are required for subsequent database usage.
2. The database's ongoing management of all of its connection sessions can impose a major limiting factor on the scalability of your application.
3. Valuable database resources such as locks, memory, cursors, transaction logs, statement handles and temporary tables all tend to increase based on the number of concurrent connection sessions.

5 Connection pool

1. A connection pool is a cache of database connections.
2. A connection pool is maintained in an application server.
3. Connections in a connection pool can be reused for future data requests from the clients.
4. After the clients finish using the connections, they are recycled to release the memory resources. Recycling connections means that the users' data in the connections is removed, and only the minimum non-user related part is kept in the cache.
5. When many users access the web application concurrently, the advantage of connection pooling becomes more significant.

6 Drawbacks of the non-connection pooling approach

1. The non-connection pooling approach used in a servlet usually has two different ways: servlet scope type connections and method scope type connections.
2. The servlet scope type connection method works in this way:
• The server creates a JDBC connection when the servlet is loaded and instantiated.
• The server destroys the JDBC connection when the servlet is unloaded.
• The JDBC connection created in this way is in the servlet scope. That means, the database connection is kept open for the entire lifetime of the servlet and that the database will have to retain an open connection for every user that is connected to your application.
• If your application using this type of JDBC connections supports a large number of concurrent users, its scalability will be severely limited.
3. The method scope type connection method works in this way: (We use the doGet method to explain the method.)
• We create a JDBC connection before we use it in the doGet method.
• After we finish using the connection, we close it before we leave the doGet method.
• In this way, the database connection resource is released after we complete the doGet method.
4. Although the method scope type connection method has an advantage over the servlet scope type connection method in the situation when the clients finishing using the database connections, there are several drawbacks in this method:
• In each session of a client, usually there are many times we need to use the doGet and/or doPost methods. That means we need to open and close the database connection many times. Each such operation consumes computing resource with significant overhead.
• If we can keep the database connection open in the whole session, and release the connection resource after the session completes, we can get better performance.
5. The run time creation of new database connections is more expensive in terms of performance and memory than the reuse of previously created connections.
6. When using the connection pooling, we recycle the database connections without destroying them. In this way, we reduce the number of objects to clean up, therefore we generally reduce the garbage collection load, which is an expensive process.
7. Connection pooling does require additional overhead for such tasks as managing the state of the connection pool, issuing connections to the application and recycling used connections.

7 How does a connection pool improve the performance?

1. The run time creation of new database connections is more expensive in terms of performance and memory than the reuse of previously created connections, so the connection pooling maintains a pool of database connections and reuse them as much as possible.
2. When using the connection pooling, we recycle the database connections without destroying them. In this way, we reduce the number of objects to clean up, therefore we generally reduce the garbage collection load, which is an expensive process.
3. Recycling database connections here means that the users' data contained in those connections is removed so as to keep each connection object skinny in memory.
4. Connection pooling does require additional overhead for such tasks as managing the state of the connection pool, issuing connections to the application and recycling used connections. That means the more requests the web application using the connection pooling technique handles, the larger the advantage over the non-connection pooling approach it would enjoy.

8 How to set up a connection pool in a server?

For different servers, the way to set up a connection pool is different. Here we only talk about how to set up a connection pool in Tomcat for the MySQL database.

1. Copy the MySQL database driver library file mysql-connector-java-5.1.10.jar from C:\Javux\DBMS\mysql-connector-java-5.1.10 to [TomcatHome]\lib.
2. In the Tomcat server specific configuration file context.xml that is located in the META-INF under your web application's root directory, provide the information of the database: the database driver, the database URL, username and password.
3. In the context.xml, define a JNDI name for the data source.
4. In the web.xml of your web application that needs to use this connection pool, specify the data source information.

9 Java Naming and Directory Interface (JNDI)

The Java Naming and Directory Interface (JNDI) is an application programming interface (API) that provides naming and directory functionality to applications written in Java. It is defined to be independent of any specific directory service implementation. Thus a variety of directories can be accessed in a common way.

1. Naming Service: In order to locate objects in a computing system, some carefully designed names are associated with those objects.
2. Names in a naming system need to follow some syntex or naming convention.
3. Binding: The association of a name with an object is called a binding.
4. Context: A context is a set of name-to-object bindings. Every context has an associated naming convention.
5. Lookup: A context provides a lookup (resolution) operation that returns the object and may provide operations such as those for binding names, unbinding names, and listing bound names.
6. Subcontext: A name in one context object can be bound to another context object (called a subcontext) that has the same naming convention.
7. Naming System: A naming system is a connected set of contexts of the same type (they have the same naming convention) and provides a common set of operations. A naming system provides a naming service to its customers for performing naming-related operations.
8. Namespace: A namespace is the set of names in a naming system.

10 Java EE Naming Services

1. Java EE application clients, enterprise beans, and web components have access to a JNDI naming environment.
2. A Java EE container implements the application component's environment, and provides it to the application component instance as a JNDI naming context.
3. The application component's business methods access the environment using the JNDI interfaces.
4. The application component provider declares in the deployment descriptor all the environment entries that the application component expects to be provided in its environment at runtime.
5. The container makes the environment naming context available to the application component instances at runtime.

11 DataSource

A DataSource object is the representation of a data source in the Java programming language. A data source is a facility for storing data.

1. A data source can reside on a remote server, or it can be on a local desktop machine.
2. Applications access a data source using a connection, and a DataSource object can be thought of as a factory for connections to the particular data source that the DataSource instance represents.
3. Using a DataSource object is the preferred alternative to using the DriverManager for establishing a connection to a data source.
4. Using the way that a DataSource object is registered with a JNDI naming service, an application does not need to hardcode driver information, as it does with the DriverManager.
5. A programmer can choose a logical name for the data source and register the logical name with a JNDI naming service.
6. The application uses the logical name, and the JNDI naming service will supply the DataSource object associated with the logical name. The DataSource object can then be used to create a connection to the data source it represents.

12 How to use a connection pool in your code?

After the connection pool is set up, a data source is created by the server to associate with a database. To access the database, we use a data source object through the JNDI name.

1. Create an InitialContext object.
2. Look up the data source for our database using the InitialContext object through the JNDI name.
3. Establishing a database connection by calling the getConnection method of the data source object.

==========The End==========