Artikel Terbaru

Wednesday, 29 May 2013

2 ways to create Connection Pool in java with examples

Java Connection Pooling


Usually applications are hosted on application server, and talk to database using database connection. data
base connection is plain HTTP socket connection to database machine.
Whenever request comes to application, application create a plain socket connection to database, once the request is completed, connection is timeout/closed, so like this for every request, connection is established and performances of application is decreased.So connection is expensive operation, so for every request, creating connection and closing connection is expensive operation. creating and closing connections need to be handled by applications.
To improve the performance of connections mechanism, connection pool is introduced.

Connection pool is a mechanism/concept used by software applications to connect to backend databases with a pre created group connections and reuses the connections whenever required,
Once a request is completed, connections are placed in connection pool

Connection pool is pool of group of pre created connections managed by application server or webserver,

Advantages of Connection Pool

Reduce network calls, thus improve network latency
Improve the application performances by reusing the connections
No need of opening or closing connections as group of connections already in pool and reuse and placed in or out from pool.

Disadvantages of Connection Pool

Need to do configure connection pooling carefully, otherwise database reject/close connections.
Connections are like a resource and there might be resource memory leakage issues

How to create Connection Pooling in java

Below are the examples to create various ways to create a connection pool.
Connection Pool can be implemented in various ways by using Custom java code connection pooling using LIFO stacks or FIFO Queues or different frameworks provided by apache and others.
Connection pool can be created in either in Standalone or web container/application Servers like jboss, tomcat , weblogic and websphere.

JDBC Connection Pooling example

Connection pool can also be created usind jdbc using JNDI name or Datasource.
JNDI name is a name configured mapped to connection pools using websphere, weblogic
Datasource is interface on which implementation can be done by different vendors like apache DBCP and c3po

Connection Pool settings:-

For any connection pool, Below number of parameters are required. For having better performance in Production systems, settings need to be tuned.

Total number of connections in Connection Pool:-This parameter specifies the total number of connection to be created at start up. Basically in web application, these connections are created at application start up and destroyed when application stops.

Maximum Connections :- tells maximum connections to be created in connection pool

Minimum connections: - specifies minimum connection to be create a pool

Connection Increment: - if total request are more than maximum connections, container increment the connection count

Connection pool Idle timeout :- if connection is established and idle for some time, then container return that connection to pool, later other request can be reuse it. This is basically to free the database connection resource.

Apache DBCP Connection pool tutorial:-


Apache DBCP is database connection pooling concept to create/manage group of connections. This can be used to create a java database connection pool to improve the performance of an java applications. Apache tomcat is internally using DBCP connection pool framework


connection pool using Apache DBCP with example

Datasource object is required for getting Connection object.
To get Datasource object, we need to create a following code
import java.sql.*;
import org.apache.commons.dbcp.*;

public class C3POPConnectionExample {
 public static void main(String args[]) throws Exception {
  BasicDataSource dataSource = new BasicDataSource();
  dataSource.setDriverClassName("com.mysql.jdbc.Driver");
  dataSource.setUrl("jdbc:mysql://localhost:3306/mydatabase");
  dataSource.setUsername("root");
  dataSource.setPassword("");
  dataSource.setInitialSize(1);
  Connection con = dataSource.getConnection();
  System.out.println("Connection Object information : " + con);

 }
}
To execute/compile the above dbcp connection pool example, dbcp jar should be in classpath

Java C3PO connection pool

C3PO connection pool is one of powerful and stable open source connection pool, performance is also good compared to apache dbcp.

Below is the example for C4PO code. Before that c3po jar should be in classpath.

import java.sql.*;
import com.mchange.v2.c3p0.ComboPooledDataSource;

public class DBCPConnectionExample {
 public static void main(String args[]) throws Exception {
  ComboPooledDataSource connectionPoolDatasource = new ComboPooledDataSource();
  connectionPoolDatasource.setDriverClass("com.mysql.jdbc.Driver");
  connectionPoolDatasource.setJdbcUrl("jdbc:mysql://localhost:3306/mydatabase");
  connectionPoolDatasource.setUser("root");
  connectionPoolDatasource.setPassword("");
  connectionPoolDatasource.setMinPoolSize(1);
  connectionPoolDatasource.setAcquireIncrement(5);
  connectionPoolDatasource.setMaxPoolSize(20);
  Connection con = connectionPoolDatasource.getConnection();
  System.out.println("Connection Object information : " + con);
 }
}

that's it my learnings/understanding on connection pooling in java.

Please click +1 if you like this post.