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

• 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.