how to do connection pooling in java?

Question

I am trying to understand connection pooling in java, i am using jsp, servlet and tomcat 6 server in my application. I have written the following code in a java class dbconnection.java:

I am using type 4 jdbc connection with oracle 10g EE in windows Xp OS

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

    public class dbconnection {   
     public Connection con = null;    
      public Connection getConnection() throws Exception, SQLException
       {
         try
           {
          Class.forName("oracle.jdbc.driver.OracleDriver");
           con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:abc","abc", "abc");           
           }
           catch(Exception e)
           {          
           }
        return con;
        }
    public void removeConnection() throws SQLException
  {
    con.close();
  }
 }

Then i am retriving connection in servlet as follows:

try{
 dbconnection db= new dbconnection();

 Connection con=db.getConnection();
 }
 catch(Exception e){
 } 
finally{
 db.removeConnection();//removes connection
}

Is it connection pooling or some configuration is required in tomcat server or something else?

Solution 1

You can get a third-party library, or you can use the connection pooling your Java EE container (for example, JBoss or WebSphere) provides for you.

To do this, you configure and use a JNDI datasource.

Here are details for Tomcat:

• http://people.apache.org/~fhanik/jdbc-pool/jdbc-pool.html

• http://www.tomcatexpert.com/blog/2012/01/24/using-tomcat-7-jdbc-connection-pool-production

Solution 2

A connection pool operates by performing the work of creating connections ahead of time. In the case of a JDBC connection pool, a pool of Connection objects is created at the time the application server starts. The client can access the connection object in connection pool and return the object to pool once the db work is completed.

Context.xml

   <Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource" 
maxActive="100" maxIdle="30" maxWait="10000" username="root" password="" 
driverClassName="com.mysql.jdbc.Driver"               
url="jdbc:mysql://localhost:3306/cdcol"/>

//This should be added in the servers context,xml file. For example if you are using apache server then the context.xml will be found in C:\apache-tomcat-6.0.26\conf\Context.xml

web.xml

  <resource-ref>
      <description>DB Connection</description>
      <res-ref-name>jdbc/TestDB</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
  </resource-ref>

//This should be added in the web.xml of the local project. (Not in server's web.xml).

Context ctx=new InitialContext();
          Context envContext = (Context)ctx.lookup("java:comp/env");
          DataSource ds=(DataSource)envContext.lookup("jdbc/TestDB");//TestDB is the Database Name
          con=ds.getConnection();
          stmt = con.createStatement();

Solution 3

Connection pooling is the feature available in all major web and application servers. You can find the simple example on configuring with Tomcat. Tomcat Connection Pooling

But if you would like to write your own connection pooling then there are libraries available to write. Apache DBCP

Comments

[Andrew Barber]

I have notified you before about the requirement to disclose when linking to your own site. Additionally, your blog post does not address the question asked above.... and nor does your text here.

[eshwar]

The question is about how to do connection pooling and the person have asked a example. So i pointed it to the site in which connection pooling is explained and also implementation example is given.

[Andrew Barber]

You have failed to disclose it is your site, and your text here just says what a connection pool is. That is not an answer.

[eshwar]

Edited the answer. Is this ok now?