Connection Pooling

What is Connection Pooling:-

Opening/Closing database connections is an expensive process and hence connection pools improve the performance of execution of commands on a database for which we maintain connection objects in the pool.

Every time a client request is received, the pool is searched for an available connection object and it's highly likely that it gets a free connection object.

                                     

Need of Connection Pooling:

The biggest performance problem when dealing with remote databases is the time taken to establish a connection.
            In the case of very short operations like selecting a single attribute from a single row, the connection time might be orders of magnitude longer, such as a fraction of a second for the operation, but over a second to connect.
            Connection Pooling gets rid of this by establishing connections before they are required and holding them until someone asks for one.

Other advantages to Connection Pooling are:

It can reuse connections if they are returned, reducing work on the database to keep creating connections.
            They can be made to expand and contract as more connections are needed, which protects database resources from being wasted.
            And it can also have an upper limit of connections, to protect too many connections from choking the database.

Connection Pooling Example in Apache Tomcat+JSP Servlet+Hibernate+Mysql

Steps:

Step 1:

Create Context.xml  file  as below mentioned in Meta-INF Folder of project

Where you have to mention  configuration with you database .

<?xml version="1.0" encoding="UTF-8"?>

<Context antiJARLocking="true" path="/coral" debug="1" reloadable="true">

    <Resource name="jdbc/coraljndi"

              type="javax.sql.DataSource"

              factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"

              driverClassName="com.mysql.jdbc.Driver"

              url="jdbc:mysql://localhost:3306/coralDB"

              username="root"

              password=""

              maxActive="15"

          maxIdle="3"

          maxWait="5000"

          removeAbandoned="true"

          removeAbandonedTimeout="20"

          logAbandoned="true"

          validationQuery="select 1"

          minEvictableIdleTimeMillis="3600000"

          timeBetweenEvictionRunsMillis="1800000"

          numTestsPerEvictionRun="10"

          testWhileIdle="true"

          testOnBorrow="true"

          testOnReturn="false"/>

</Context>

Fields that need to change:

url: According to database you are using.

Username:

Password:

Resource Name: It is jndi name which you have to mention in web.xml file.

Step2:

Mention your jndi in web.xml as follows:

<?xml version="1.0" encoding="UTF-8"?>

<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:javaee="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd" id="WebApp_9" version="2.4">

 

  <resource-ref>

    <description>This is a MySQL database connection</description>

    <res-ref-name>jdbc/coraljndi</res-ref-name>

    <res-type>javax.sql.DataSource</res-type>

    <res-auth>Container</res-auth>

  </resource-ref>

</web-app>

Step 3:

Mention your datasource in hibernate.cfg.xml file:

<property name="hibernate.connection.datasource">java:comp/env/jdbc/coraljndi</property>

In this way you can access your datasource.