Connection Pooling

[Srividya Shiv]

How do u achieve connection pooling thru Java. Its there in JDBC2.0, But I am not sure how to go about that.

[Jim Yingst]

There are a couple of articles here and here.

[Neetu Gupta]

You can create your own class for connection pooling. In that class keep a vector of predefined number of jdbc connections and a flag for eac connection indicating if the current connection is in use or not. Also keep methods to get and free the connection. In case of increase of load, add some more connections to the vector.

[Derek True]

Originally posted by Srividya Shiv:
How do u achieve connection pooling thru Java. Its there in JDBC2.0, But I am not sure how to go about that.

In addition to the above, you might want to go to: http://www.sys-con.com/java/archives/subscribe/0510/index.html
And check out the article,"Implementing a lightweight web server for resource pooling and scalability" on pg. 60.
Derek

[KASI VISHWANATH]

Every time u need to have a connection u have to create it at that time, it takes lot of time to create the connection. To improve the performance we go for connection pooling. Connection pooling involves creation of pool of connections in advance, it can be either JDBC connections or Thread Connections (in case of thread pooling) and when a request comes for that connection, the connection already created in the pool is assigned. These are generally stored in a vector. The pool is created anticipating the demand. A minimum of few connections are created when the server is started, a after that connections are created checking the need. When the allotment of the connection has exceed the max_connections to be created the an warning is send. This will help the performance improvance drastically and a good concept for improving the speed.
------------------
I.K.VISHWANATH

[Frank Carver]

Just for fun, here's my code for a very simple pooling system which I have used successfully on many projects over the last few years. The class "com.efsol.util.Pool" is a general purpose pooling class which can be used to build classes to pool connections, threads or anything else you fancy. The class "com.efsol.database.ConnectionPool" is a Pool for database connections. I know they don't quite match with the Java Ranch official style, but feel free to use them for whatever you like.
File Pool.java

package com.efsol.util;
import java.util.*;   // for Hashtable
 
public abstract class Pool
{
private class ObjectInfo
{
    boolean isClaimed;
    long lastReleased;
    
    public ObjectInfo(boolean claimed)
    {
        isClaimed = claimed;
        lastReleased = System.currentTimeMillis();
    }
}
 
    private Hashtable pool;
    
    public Pool()
    {
        pool = new Hashtable();
    }
 
    protected abstract Object create();
 
    protected void destroy(Object obj)
    {
    }
 
    protected abstract boolean validate(Object obj);
    
    public synchronized Object claim()
    {
        Iterator it = pool.keySet().iterator();
        while (it.hasNext())
        {
            Object obj = it.next();
            ObjectInfo info = (ObjectInfo)pool.get(obj);
            if (!info.isClaimed)
            {
                if (validate(obj))
                {
                    info.isClaimed = true;
                    return obj;
                }
                else
                {
                    pool.remove(obj);
                    destroy(obj);
                }
            }    
        }
        
        // not found, so create one
        //
        Object obj = create();
        
        if (obj != null)
        {
            pool.put(obj, new ObjectInfo(true));
        }
        else
        {
        // consider throwing an exception here, but which one?
        }
        
        return obj;
    }
    
    public synchronized void release(Object obj)
    {
        if (pool.containsKey(obj))
        {
            ((ObjectInfo)pool.get(obj)).isClaimed = false;
        }
    }
}

File ConnectionPool.java

package com.efsol.database;
 
import java.sql.*;
import java.io.*;
import java.util.Properties;
 
import com.efsol.util.Pool;
 
public class ConnectionPool extends Pool
{
    private String driver;
    private String uri;
    private String name;
    private String password;
    
    private void setup(String driver, String uri, String name, String password)
    {
        this.driver = driver;
        this.uri = uri;
        this.name = name;
        this.password = password;
        
        System.out.println("creating pool: driver='" + driver + 
            "' uri='" + uri + "' name='" + name + "' password='" + password + "'");
        
        try
        {
            Class.forName(driver);
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
    }
    
    public ConnectionPool(String driver, String uri, String name, String password)
    {
        setup(driver, uri, name, password);
    }
    
    public ConnectionPool(String specfile)
        throws IOException
    {
        Properties spec = new Properties();
        spec.load(new FileInputStream(specfile));
        
        setup(spec.getProperty("driver"), spec.getProperty("uri"),
            spec.getProperty("name", ""), spec.getProperty("password", ""));
    }
    
    public Connection claimConnection()
    {
        return (Connection)super.claim();
    }
    
    public void releaseConnection(Connection connection)
    {
        super.release(connection);
    }
    
    protected Object create()
    {
        Object ret = null;
        
        try
        {
            ret = DriverManager.getConnection(uri, name, password);
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        
        return ret;
    }
    
    protected void destroy(Object obj)
    {
        try
        {
            if (obj instanceof Connection)
            {
                ((Connection)obj).close();
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
    }
    
    protected boolean validate(Object obj)
    {
        boolean ret = false;
        
        try
        {
            ret = ((Connection)obj).isClosed();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        
        return ret;
    }
}

I hope this is useful (or at least interesting!)

[Axel Janssen]

I am thinking about that, too.
With struts there is a connection pool, but if I use this, its strongly coupled with struts and its database layer, not presentation layer.
There seems to be some activity based on the jarkarta.commons.pool package:
http://sourceforge.net/projects/jdbcpool/
But nothing yet published.
In chapter 3 or 5 or 6 of the now not yet downloadable struts-beta-book on theServerside they explained how to use an open source object-relational-maper. Will give this a try the weekend.
At least I will have Franks code if I will run into problems. Need some demo running next week.
Thank you
Axel

[Nelson David]

Hello All,
I have develped my ConnetionPooling module, but I am having some problems in long run, the problem is I am caching connections in Vector object, the database server later on closes the connection when ther is no activity on connection , leaving my Vector object holding closed connections, how do i prevent it?
I am thinking of closing all the connections in Vector object when they are no longer in use after some specified time.
but that may become performance overhead instead of performance gain, I have seen Frank Carver in same thread but I wonder Frank
will be having same problem also, the cached connection may be closed database server after some time.

Any JDBC guru comment on this ,

Nelson

[Peter den Haan]

Originally posted by Axel Janssen:
With struts there is a connection pool, but if I use this, its strongly coupled with struts and its database layer, not presentation layer.

The struts 1.1 connection pool is no more than a wrapper around the Commons DataBase Connection Pool.
Please mind that in an application server (servlet/JSP or EJB) environment, you already have a connection pool. Configure the server to make a DataSource available in the JNDI tree. That DataSource will support connection pooling.
- Peter

[Peter den Haan]

Originally posted by Nelson David:
I have develped my ConnetionPooling module

Unless it's done as a learning exercise, I would really discourage you from doing this. There are plenty of good connection pools around (including your favourite application server and the Apache Jakarta DBCP mentioned above) and the world doesn't need another one. Also, a solid connection pool is not trivial to develop. Doing your own thing is reinventing a wheel that's likely to be inferior.

[...] I am caching connections in Vector object

Forget about Vector! It's obsolete, superfluous, has a bloated API, and its synchronization usually buys you nothing but trouble. Use a List implementation from the Collections framework. The equivalent of Vector would be an ArrayList, but for a connection pool a LinkedList would actually be more appropriate. I will leave the reasons why as an exercise for the reader A hint follows immediately below.

the database server later on closes the connection when ther is no activity on connection, leaving my Vector object holding closed connections, how do i prevent it?

Ensure each connection gets a fair amount of use (i.e. use a FIFO list rather than a LIFO list). Use a test query to test each connection before giving it to the client code, and/or actively expire connections after a certain time if inactivity, and/or use a reaper thread to periodically test connections. Those are the naive approaches. Much better would be to use the connection pool support that is probably offered by the database driver -- i.e. PooledDataSource and friends. This has an event model which can notify your pool when a connection is knocked out due to some fatal error.
Do you see what I mean by "a solid connection pool is not trivial to develop"?
- Peter
[ April 28, 2003: Message edited by: Peter den Haan ]

[Jon Strayer]

Originally posted by Srividya Shiv:
How do u achieve connection pooling thru Java. Its there in JDBC2.0, But I am not sure how to go about that.

There is no portable way to do it in J2SE. In J2EE you can get a DataSource from a JNDI server.
If you are using Oracle you can go here http://technet.oracle.com/sample_code/tech/java/sqlj_jdbc/files/jdbc20/jdbc20.htm
and look at the second example.

[Bruno Arantes]

Jakarta's Commons Project develops a connection pool, the Commons Database Connection Pool. You can take a look at Jakarta's web site: http://jakarta.apache.org/.
Bruno.