Connection Pooling : What, Why, How ?

September 24, 2012 at 4:43 pm 2 comments

What ?

Connection, is a way to interact with the Database from our programming language. Mostly all of us will come across the word “Database connection is always costly“.

We also accept that statement without knowing what it really means. So just a gist of what it really makes it [ if you already knew this, please skip it and move to how it works ]

snippet of connection creation

String connUrl = "jdbc:mysql://your.database.domain/yourDBname";
 Class.forName("com.mysql.jdbc.Driver");
 Connection con = DriverManager.getConnection (connUrl);

When we create a Connection Object, internally what it means it

  •  The “DriverManager” checks for the registered Driver,
  •  Since we have registered our driver as “com.mysql.jdbc.Driver”, it will call “connect(url…)” method on this Driver class.
  • com.mysql.jdbc.Driver’s connect method inturn creates a “socket connection”[with default port 3306 specified with IP (your.database.domain)] to the database we are requesting in “connUrl”.
  • The created Socket connection will be used to exchange the queries from our program to the db and what we end up is called a Resultset back to program.

Why ?

So now lets talk about the word “costly”….

If we look at the time consumption in creating a connection, socket creation takes lot of time when compared to actual query execution[which is the operation we do].

So we say that “a database connection is always costly” since its creating a socket connection everytime to talk to the DB and this socket resource is limited one.

In overcoming the above defaults, we are going for Connection Pooling.

Connection Pooling means creating the connections[while initializing] and reusing them from the pool of connections rather than creating new one every time.

How ?

Let’s see how this works and how its able to manage or reuse the existing connections ?

When we use the Connection Pool Provider, it has an internal Connection Pool Manager, when it gets initialized

  • It creates connections with default pool size(for eg.5) specified [ it means creating 5 connection objects and putting it in any collection or array with status "AVAILABLE"]

eg snippet :

...
 String connUrl = "jdbc:mysql://your.database.domain/yourDBname";
 String driver = "com.mysql.jdbc.Driver";
 private Map<java.sql.Connection, String> connectionPool = null;

private void initPool() {
 try {
 connectionPool = new HashMap<java.sql.Connection, String>();
 Class.forName(driver);
 java.sql.Connection con = DriverManager.getConnection(dbUrl);
 for (int poolInd = poolSize; poolInd < 0; poolInd++) {
 connectionPool.put(con, "AVAILABLE");
 }

}
 ...

  • when we give connectionProvider.getConnection(), then it will fetch a connection from collections. and change the status to “NOT AVAILABLE”

eg snippet :

...
 public java.sql.Connection getConnection() throws ClassNotFoundException, SQLException
 {
 boolean isConnectionAvailable = true;
 for (Entry<java.sql.Connection, String> entry : connectionPool
 .entrySet()) {
 synchronized (entry) {
 if (entry.getValue()=="AVAILABLE") {
 entry.setValue("NOTAVAILABLE");
 return (java.sql.Connection) entry.getKey();
 }
 isConnectionAvailable = false;
 }
 }
 if (!isConnectionAvailable) {
 Class.forName(driver);
 java.sql.Connection con = DriverManager.getConnection(connUrl);
 connectionPool.put(con, "NOTAVAILABLE");
 return con;
 }
 return null;
 }
 ...

  • When we close the connection we got, ConnectionProvider wont actually close the connection. Rather, it change the status to “AVAILABLE”

eg snippet :

...
 public void closeConnection(java.sql.Connection connection)
 throws ClassNotFoundException, SQLException {
 for (Entry<java.sql.Connection, String> entry : connectionPool
 .entrySet()) {
 synchronized (entry) {
 if (entry.getKey().equals(connection)) {
 //Getting Back the conncetion to Pool
 entry.setValue("AVAILABLE");
 }
 }
 }
 }
 ...

This is how a connection pool actually works [but may use diff approach] basically.

Now you may have a question, can we create our own connection Pool mechanism ?

My recommendation is to use already existing Connection Pool mechanisms available like c3p0, dbcp etc.,

About these ads

Entry filed under: ConnectionPooling. Tags: , , , , , , .

HelloWorld In JVM’s View – How Java Program executed internally in JVM Java HTTP/HTTPs Client Example – Ignore SSL

2 Comments Add your own

  • 1. joe123  |  September 28, 2012 at 11:48 am

    Very useful to get the basic concept of a connection pool.
    Thank you.

    Reply
  • 2. Aslam Rajper (@Aslam_Rajper)  |  November 15, 2012 at 10:46 am

    Good one…!

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Trackback this post  |  Subscribe to the comments via RSS Feed


Blog Stats

  • 17,817 hits

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: