Simple Solution for Complex Java Web Applications: Connection Pools and JDBC

Practically every request from a user's JSP/servlet creates a new database connection, which takes time and exhausts system resources. Using a limited number of database connections substantially improves Web server productivity and eases the load on your database. To the extent that servlets enable you to store information between user requests, distributed programming solutions, such as connection pools, can help generate Web content dynamically, quickly loading data from your database to your Web site.

A connection pool helps you manage and control the database connections from JSP/servlets, where you can store information between client requests. This article discusses the advantages of using a connection pool in your Java distributed systems programming. It requires a working knowledge of Java Web programming, databases, and JDBC, as well as an environment where you can access these technologies.

 

What Exactly Is a Connection Pool?
To understand connection pools, first think of servlets. Servlets are easy; they're just ordinary Java classes. A servlet request loads a Java class into the JVM and creates an instance of this class. Further, this one class instance processes all client requests in a separate thread. Between breaks, the instance remains in resident condition and is not removed. This way, it keeps all available information as instance objects, which is exactly what the connection pool is.

 

Connection Pool Implementation
In the implementation in this article, a connection pool is a class with a number of JDBC Connection objects and access methods. You could also think of it as a container with a simple interface for controlling and managing database connections. To execute a SQL request, get data from the database, or change the data, open a database connection and get an object of Connection type. You will process all subsequent operations with this object. You can specify the size of your connection pool according to your available resources.


 

One class, SimpleConnectionPool, realizes a connection pool. It's comprised of a limited set of methods for database connection. In each case, functionality and class realization will vary depending on the existing task. This implementation acts as a basic skeleton for a connections storehouse.

The connection pool will do the following:

• Enable a load-specific database driver for each database;
• Get the reference to the SimpleConnectionPool object, which represents a connection pool exactly;
• Get an available connection (of Connection type) from your storehouse;
• Put a connection back into the storehouse;
• Free all resources and closes all opened connections in your storehouse

Different implementations of a JDBC connection pool exist in different utility packages and frameworks (for example, the Jakarta Struts framework has org.apache.struts.util.GenericDataSource).

The SimpleConnectionPool class represents a uniform connections storehouse to a previously specified database. You need at least three arguments to access a database: the URL ID of the database, a user login, and a password. The URL ID consists of a protocol ID, a driver ID, and a database ID. The protocol ID is always jdbc, the driver ID could be odbc, oracle, mysql, etc., and the database ID depends only on the implementation of the database driver. For example, the URL ID "jdbc:odbc:test" can be used for database access via a JDBC-ODBC bridge, and the URL ID "jdbc:mysql:localhost/test" can be used to access a MySQL test database on localhost (some MySQL driver implementations require you to specify a user access login/password in the URL ID).

 

Initialize your storehouse and all the additional arguments in the constructor of the SimpleConnectionPool class:

 

 

 

 

The static method getInstance() creates a single instance of the SimpleConnectionPool class and returns a reference to it. Here, the Singleton design pattern allows you to create only one class instance and then return a reference to any number of requested objects:

 

...
static synchronized public SimpleConnectionPool getInstance(String URI, 
							String dbuser, 
							String dbpass, 
							String drivername, 
							int maxconn) {
	if (this.ref == null) {
		this.ref = new SimpleConnectionPool(URI, dbuser, dbpass, 
		drivername, maxconn);
	}
	this.clients++;
	return this.ref;
}
...

 

 

 

To get a database connection as a Connection object, use the getConnection() method:

 

...
public synchronized Connection getConnection() {
	Connection rescon = null;
	if (!this.freeConnections.isEmpty()) {
		rescon = (Connection)this.freeConnections.get(this.freeConnections.size()-1);
		this.freeConnections.remove(rescon);
		try {
			if (rescon.isClosed()) {
				System.out.println("Removed closed 
				connection!");
				// Try again recursively
				rescon = getConnection();
			}
		} catch (SQLException e) {
			System.out.println("Removed closed connection!");
			// Try again recursively
			rescon = getConnection();
		} catch (Exception e) {
			System.out.println("Removed closed connection!");
			// Try again recursively
			rescon = getConnection();
		}
	} else {
		rescon = createConnection();
	}
	return rescon;
}
...

 

 

As you can see, the getConnection() method checks the ArrayList container to see whether it has any elements from which it can get an available connection and return it to the client. If it doesn't find any elements (no connections found), it creates a new connection to return to the client. So, getConnection() checks for available connections. In case you get an exception (for any reason), you repeat the process recursively--until you find a connection, you don't create a new connection. When ArrayList doesn't have any connections, you create a new connection using the createConnection() method:

 

...
private Connection createConnection() {
	Connection rescon = null;
	try {
		if (this.dbuser == null) {
			rescon = DriverManager.getConnection(this.URI);
		} else {
			rescon = DriverManager.getConnection(this.URI, 
			this.dbuser, this.dbpass);
		}
		// new connection in connection pool created
	} catch (SQLException e) {
		System.out.println("Cannot create a new connection!");
		Rescon = null;
	}
	return rescon;
}
...

 

 

Also include the returnConnection() method, which returns a connection back to your storehouse and puts it at the end of the list:

 

...
public synchronized void returnConnection(Connection con) {
	if ((con != null) && (this.freeConnections.size() <= 
	this.maxconn)) {
		this.freeConnections.add(con);
	}
}
...

 

The last method from SimpleConnectionPool that you need to consider is release(), which closes and releases all connections from your connection pool:

 

...
public synchronized void release() {
	Iterator allc = this.freeConnections.iterator();
	while (allc.hasNext()) {
		Connection con = (Connection)allc.next();
		try {
			con.close();
		} catch (SQLException e) {
 			System.out.println("Cannot close connection! 
 			 (Probably already closed?)");
		}
	}
	this.freeConnections.clear();
}
...

 

 

Connection Pool in Practice
  To demonstrate an effective use of a connection pool, use a SimpleConnectionPool object in a standard servlet. The code is pretty easy and can be rewritten in JSP without any problems. The behavior of the servlet will be defined by the following methods:


• init() — servlet creation and initialization
• service() — control of received client requests
• destroy() — servlet termination

The following code creates a SimpleConnectionPool object in init(), gets an available connection in service() with the getConnection() method, and releases the connection pool in the destroy() method:

 

// TestOurConnectionPoolServlet.java
import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class TestOurConnectionPoolServlet extends HttpServlet {
	
	private SimpleConnectionPool pool;
	
	public void init(ServletConfig conf) throws ServletException {
		super.init(conf);
		pool = SimpleConnectionPool.getInstance("jdbc:odbc:test", 
		"Administrator", "Admin", 30);
	}

	public void service(HttpServletRequest request, 
			   HttpServletResponse response) throws 
			   IOException {
		response.setContentType(text/html);
		PrintWriter out = response.getWriter();
		Connection con = pool.getConnection();
		if (con == null) {
			out.println("Can't receive connection!");
			return;
		}
		
		ResultSet rs = null;
		Statement stmt = null;
		ResultSetMetaData rsmd = null;

		try {
			stmt = con.createConnection();
			rs = stmt.executeQuery("SELECT * FROM Test");
			rsmd = rs.getMetaData();

			while(rs.next()) {
				for (int i = 1; i < rsmd.getColumnCount(); 
				i++) {
					out.print(rs.getString(i) + ", ");
				}
				out.println("");
			}
			stmt.close();
			rs.close();
		} catch (SQLException ex) {
			ex.printStackTrace(out);
		}
		pool.freeConnection(con);
	}

	public void destroy() {
		pool.release();
	}
}

 

 

Connection Pool: Essential for Large, Complex Web Applications
Although the code is easy, you obviously wouldn't use a connection pool in small Web applications. However, it is essential technology for large, complex applications. The example in this article shows the simplicity of a connection pool, as well as its programming solutions for dealing with database productivity and its methods for accessing databases. The important thing is to initialize the pool only once. You do not need to instantiate a new pool in each servlet or JSP. You can successfully apply connection pool technology to Internet shops or dynamic interactive sites, effectively using all your available resources.

 

 

 

This article originates from http://www.devx.com/Java/Article/20891/0/page/2

...
private SimpleConnectionPool(String URI, 
			      String dbuser, 
			      String dbpass,
			      String drivername, 
			      int maxconn) {
	this.URI = URI;
	this.dbuser = dbuser;
	this.dbpass = dbpass;
	this.drivername = drivername;
	this.maxconn = maxconn;

	loadJDBCDriver();
}
...

 

 

Because it's defined as private, you can't call SimpleConnectionPool from outside the class. You also need to define your constructor with an access modifier to be able to use the Singleton design pattern in this example. As you can see in the above code, after the constructor executes, you will have the variables set and the JDBC driver loaded.

The loadJDBCDriver() method is easy:

 

...
private void loadJDBCDriver() {
	try {
		Driver driver = (Driver)Class.forName(this.drivername).newInstance();
		DriverManager.registerDriver(driver);
	} catch (Exception e) {
		System.out.println("Can't load/register JDBC driver ");
	}
}
...