Database ConnectionPooling with MiniConnectionPool Manager

Creating a Web Application will obviously require a Database in the backend and as a developer, you will always love to have an efficient and robust way of managing database connectivity.

If you have already done some academic or student projects related to web application development, then you have probably worked with raw database connections without having any connection pool management. But while working with database connections without pooling facilities, please be aware of the following facts.

A. When you are not using any connection pooling, you are essentially creating a database connection as and when required, which means you do not have any control over the number of connections coming out of DataSource, rather you are creating database connections from DriverManager as shown in the following code snippet.

Class.forName(CHOSEN_DRIVER_URL);
Connection conn = DriverManager.getConnection(CHOSEN_DB_URL, DB_USER_NAME,DB_PASSWORD);

B. Every time, you are establishing a new connection in this way, a new Database Connection object is being created. Database Connection Objects are really memory hungry and they tend to leak memory, so working with a huge number of Database Connection might damage your server resource and hence it is not acceptable at all in the production environment.

---

For the above reasons, if you want to have an efficient and robust design of Database Connection Management, then you can think of Connection Pool concept, where you can create a pool of a fixed number of database connections coming out of the DataSource , thus limiting the impact of the memory leakage in your application. When you will create a database connection, a connection will be provided from the pool and when you will close the connection, it will go back to the pool. In this way, you can ensure the recycling of a user connection. In this paradigm, as no connections are being created at the source every time, the effect of memory leakage is restricted.

Now the question is how you can manage all the functionalities of a ConnectionPool. Luckily enough Java (from Java 1.5 onwards) provides a solution for this through ConnectionPoolManager . But this ConnectionPoolManager is really resource-heavy as, along with the basic connection pool management routine activities, it provides quite a good number of sophisticated connection pool management features which are generally not required for simple to moderate applications. So to perform routine connection pool management jobs, using a ConnectionPoolManager is like using a canon to kill mosquitoes.

---

But there is a fantastic lightweight MiniConnectionPoolManager developed by Christian d'Heureuse, which can be readily used in web applications with almost any prominent database. You can find the details of how you can use MiniConnectionPoolManager class for different databases here.

In this tutorial, we will guide you on how can you design efficient database connection management for your web applications with the help of MiniConnectionPoolManager class. Here we will be developing a small tiny web application called DBConnectionManagement using Embedded Derby database. In case you are planning to work with a different database, you need to change the code accordingly. Instructions are already given to change the codes as comments within the codes. Before we present the real codes, please follow the procedure mentioned below.

---

Step 1: Create a dynamic web application DBConnectionManagement with the following application folder structure. Please note that you are required to introduce derby.jar and servlet-api.jar into the lib folder within WebContent . You can get the derby.jar from here and you can get servlet-api.jar from lib folder within your downloaded Tomcat Server. Here we have used Derby 10.12.1.1 and Tomcat 7.0.82 versions.

Fig1: Required Application Folder Structure

Step 2: Next, download MiniConnectionPoolManager.java from Christian d'Heureuse's homepage and put it in the database package . Don't forget to change the package declaration in the code. We will strongly recommend not changing the author declaration and other copyrighted materials there. If everything goes fine, you will have the state of your Eclipse IDE like following

Fig2: MiniConnectionPolManager.java added to the application

Now having completed the above two steps, the following codes are to be developed so that you get the folder structure below

Fig3: Complete Folder Structure of the application

That means you have to write Constants.java in core package , DatabaseFactory.java in database package and InitDatabaseServ.java in the servlets package . Please note that there is one index.html in WebContent for initial view generation of the application.

Following are the codes which are discussed in detail later.

---

Code Constants.java

//Constants.java package core; public class Constants { public static final String DATABASE_PATH = "E:\\SampleDB"; public static final int MAX_NO_OF_CONNECTIONS = 5; }

Code DatabaseFactory.java

package database; import java.sql.Connection; import java.sql.SQLException; import org.apache.derby.jdbc.EmbeddedConnectionPoolDataSource; import core.Constants; public class DatabaseFactory { public static MiniConnectionPoolManager _POOLMANAGER = null; /** * Creates the database */ public static void createDatabase() { //In case, you want to use any other database other than Derby Database //Change the following three lines accordingly as shown in MiniConnectionPoolManager home at: //http://www.source-code.biz/miniconnectionpoolmanager/ EmbeddedConnectionPoolDataSource dataSource = new EmbeddedConnectionPoolDataSource(); dataSource.setDatabaseName(Constants.DATABASE_PATH); dataSource.setCreateDatabase("create"); _POOLMANAGER = new MiniConnectionPoolManager(dataSource, Constants.MAX_NO_OF_CONNECTIONS); } /** * Gets you the database connection */ @SuppressWarnings("finally") public static Connection getConnection() { Connection conn = null; try { conn = _POOLMANAGER.getConnection(); } catch (SQLException e) { e.printStackTrace(); } finally { System.out.println("Connection obtained:: " + conn); return conn; } } }

Code InitDatabaseServ.java

package servlets; import java.io.IOException; import java.sql.Connection; import java.sql.SQLException; import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import database.DatabaseFactory; @WebServlet(urlPatterns = "/InitDatabaseServ", loadOnStartup = 1) public class InitDatabaseServ extends HttpServlet { private static final long serialVersionUID = 1L; /** * @see HttpServlet#HttpServlet() */ public void init() { DatabaseFactory.createDatabase(); Connection conn = DatabaseFactory.getConnection(); // do your required DB operations try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } public InitDatabaseServ() { super(); } /** * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse * response) */ protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { } /** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse * response) */ protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doGet(request, response); } }

Code index.html

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"> <title>DBConnection Management With MiniConnectionPolManager</title> </head> <body> <H2>Welcome to DBConnectionManagement Application - Sample App from www.bunksallowed.com</H2> <div> As you are watching this web page, the application has already created the derby database and obtained database connection through MiniConnectionPoolManager class authored by <a href="http://www.source-code.biz/miniconnectionpoolmanager/"><b>Christian d'Heureuse</b></a>. </div> <br> <hr> <b>Please check your console to see that you have indeed received the DB connection from the pool.</b> <hr> <br> <p>Now you can use this connection to do you required database operations.</p> <br> </body> </html>

---

Code Explanations

The Constants.java file hosts the constants used in the applications. It contains DATABASE_PATH to represent the path to the database (here in this case it is SampleDB within E: drive, you can freely change it according to your requirement) and MAX_NO_OF_CONNECTIONS to represent the maximum number of database connections to be there in the Connection Pool (Here it is 5, please note that you are always free to change it with a caution that increasing value of this variable will increase the memory leakage). Please note that DATABASE_PATH is required to set the database path in the DataSource object and MAX_NO_OF_CONNECTIONS is required to call the constructor of MiniConnectionPoolManager both within DatabaseFactory.java .
DatabaseFactory.java has a static instance of MiniConnectionPoolManager as _POOLMANAGER initialized with null . This class has a static createDatabase() method which will act as the entry point to the class and a non static getConnection() method.
Whereas createDatabase() creates the dataSource, sets the physical database path, and then constructs the MiniConnectionPoolManager object to be stored within _POOLMANAGER , getConnection() method returns a database connection from the connection pool by _POOLMANAGER .

NOTE 1: This DatabaseFactory.java class will obviously run as shown in the code examples, but it is not optimized for the production environment, please recheck the code for any possible improvement. We guarantee there are many, take it as an assignment and reach us with your suggestions.

NOTE2: Please pay proper attention to the comments made within createDatabase() . This will help you in changing the database from Derby to any other.

InitDatabaseServ.java is a servlet with loadOnStartup value 1 which ensures that init() method of this servlet will be called at the very first whenever this sample web application will be loaded into the container. In case you do not have any idea on loadOnStartup , please refer to this tutorial for complete knowledge.

Within init() method, createDatabase() of DatabaseFactory class has been called statically so that MiniConnectionPoolManager object (within DatabaseFactory class) _POOLMANAGER gets instantiated. Next, static call to getConnection() of DatabaseFactory class is made to get the database connection. Once getConnection() has been accessed, it writes the connection object obtained into the console as output. Finally, connection is closed.
So when the application is run on the server, first InitDatabaseServ is hit. The init() creates the database, gets the database connection, and ultimately closes the connection. In the meantime, while getConnection() was accessed, it threw the output to the console. After all these the index.html gets opened in the browser. Here index.html has got no value except working as a landing page.

Running of the application and Output

Once you run the application, you will get the output in the console as follows

Fig4: The output is shown in the console

The browser will render the index.html as following

Fig5: The output shown in the browser

Happy Exploring!