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?

Check this Q&A for more info: http://stackoverflow.com/questions/1458693/jdbc-connection-pooling-using-c3p0 — Boris Pavlović, Apr 18 '12 at 07:29
Your question is answered here: http://stackoverflow.com/questions/2835090/jdbc-connection-pooling — Michael Fernando, Apr 18 '12 at 07:32
Don't do this `catch(Exception e) { }`, it makes debugging impossible. — artbristol, Apr 18 '12 at 07:55
@artbristol ya but how should i write `catch statement`? please help me little bit — sujit, Apr 18 '12 at 08:03
Checked exceptions are a pain. I usually just do `catch(Exception e) { throw new RuntimeException(e); }`, but it depends. — artbristol, Apr 18 '12 at 08:12

eshwar · Answer 1 · 2013-03-06T06:39:26.387

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();

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. — Andrew Barber, Mar 05 '13 at 10:14
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. — eshwar, Mar 06 '13 at 04:05
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. — Andrew Barber, Mar 06 '13 at 04:10

score 3 · Accepted Answer · edited Apr 28 '12 at 09:55

3

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:

edited Apr 28 '12 at 09:55

Arjan Tijms

37,782
12
108
140

answered Apr 18 '12 at 07:30

paulsm4

114,292
17
138
190

score 1 · Answer 3 · answered Apr 18 '12 at 07:31

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

how to do connection pooling in java?

3 Answers3

Linked