This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes problem in connectionpooling Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Reply locked New topic

problem in connectionpooling

sreenath reddy
Ranch Hand

Joined: Sep 21, 2003
Posts: 415
Hi everybody

Here with i am using a connection pool of size of 10 for MYSQL database.The problem is that aftersometime the databse server says "TOO MANY COnnections" and exception will be thrown.At the database server if i give "netstat"(in windows to look at the processes) it shows many connections (more than 100) .This shouldnt happen bcoz i am releasing the connection after getting connection and the connections should be only 10.
Here is my connection pooling class

public class ConnectionPooling implements Runnable
static Logger logger=PlasmaLogServices.initLog();
static private ConnectionPooling instance; // The single instance
static private int clients=0;
//private static String PATH="";
public String path=this.getClass().getResource("").getPath();
private Vector availableConnections, busyConnections;
private int maxConnections;
private boolean connectionPending = false;
static synchronized public ConnectionPooling getInstance() throws Exception
if (instance == null)
{"Getting the instance of Connection pool");
instance = new ConnectionPooling();
System.out.println("no of clients:"+clients);
return instance;
private ConnectionPooling()
private void init()
{"Instantiating the Connection pool");
int maxConnections=0;
//System.out.println("The url is :"+this.getClass().getResource("").getPath());

catch(Exception e){
availableConnections = new Vector(maxConnections);
busyConnections = new Vector();"Pool of size"+maxConnections);
for(int i=0; i<maxConnections; i++)


public Connection makeNewConnection()
Properties fat=new Properties();
Connection conn=null;
String driver=fat.getProperty("Driver");
String user=fat.getProperty("username");
String url=fat.getProperty("url");
conn = DriverManager.getConnection(url,user,"");
System.out.println("Driver loaded");"Driver loaded");
catch (ClassNotFoundException e){
logger.error("Exception in Pooling.makeNewConnection:" +e.getMessage());}
catch(SQLException ex){
logger.error("Exception in ConnectionPooling class:"+ex.getMessage()); }
catch (Exception ex) {
logger.error("Exception in Pooling class:"+ex.getMessage());}
return conn;
public synchronized Connection getConnection() throws SQLException

if (!availableConnections.isEmpty())
Connection existingConnection =(Connection)availableConnections.lastElement();
int lastIndex = availableConnections.size() - 1;
// If connection on available list is closed (e.g.,
// it timed out), then remove it from available list
// and repeat the process of obtaining a connection.
// Also wake up threads that were waiting for a
// connection because maxConnection limit was reached.
if (existingConnection.isClosed())
notifyAll(); // Freed up a spot for anybody waiting
System.out.println("total connections"+totalConnections());

// Three possible cases:
// 1) You haven't reached maxConnections limit. So
// establish one in the background if there isn't
// already one pending, then wait for
// the next available connection (whether or not
// it was the newly established one).
// 2) You reached maxConnections limit . Throw SQLException in such a case.
// 3) You reached maxConnections limit and waitIfBusy
// flag is true. Then do the same thing as in second
// part of step 1: wait for next available connection.

if ((totalConnections() < maxConnections) && !connectionPending)
throw new SQLException("Connection limit reached");
// Wait for either a new connection to be established
// (if you called makeBackgroundConnection) or for
// an existing connection to be freed up.
try {
} catch(InterruptedException ie) {
logger.error("Exception in Pooling.getConnection:"+ie.getMessage());
// Someone freed up a connection, so try again.

// You can't just make a new connection in the foreground
// when none are available, since this can take several
// seconds with a slow network connection. Instead,
// start a thread that establishes a new connection,
// then wait. You get woken up either when the new connection
// is established or if someone finishes with an existing
// connection.
private void makeBackgroundConnection()
connectionPending = true;
try {
Thread connectThread = new Thread(this);
} catch(OutOfMemoryError oome) {
logger.error("Exception in makeBackgroundConnection:"+oome.getMessage()); }
public void run() {
try {
Connection conn = makeNewConnection();
synchronized(this) {
connectionPending = false;
} catch(Exception e) {
System.out.println("Exception in Pooling class:"+e.getMessage());
// SQLException or OutOfMemory
// Give up on new connection and wait for existing one
// to free up.
public synchronized void free(Connection connection,ResultSet rs,PreparedStatement pst) {

catch(Exception e)
System.out.println("Close Connection in Pooling"+e.getMessage());
// Wake up threads that are waiting for a connection

public synchronized int totalConnections() {
return(availableConnections.size() +
/** Close all the connections. Use with caution:
* be sure no connections are in use before
* calling. Note that you are not <I>required</I> to
* call this when done with a ConnectionPool, since
* connections are guaranteed to be closed when
* garbage collected. But this method gives more control
* regarding when the connections are closed.
public synchronized void closeAllConnections() {
availableConnections = new Vector();
busyConnections = new Vector();
private void closeConnections(Vector connections)
for(int i=0; i<connections.size(); i++)
Connection connection =(Connection)connections.elementAt(i);
if (!connection.isClosed())
catch(SQLException sqle) {
System.out.println("Exception in Pooling class:"+sqle.getMessage());
// Ignore errors; garbage collect anyhow


and i will get connection using ConnectionPooling.getConnection() and i will be releasing connection using c,ResultSet s,PreparedStatement rs) method.

can any one help me out in finding the problem in ConnectionPooling code where its opening more connections and the driverurl,username and password are retrived from .properties file

Pls can anyone help me fast??? can u list out the changes to be made in the code that will get rid of this problem
Jessica Sant

Joined: Oct 17, 2001
Posts: 4313

closing this duplicate. Please comment in the original thread
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
subject: problem in connectionpooling
Similar Threads
how many initial connections should be opend
problem in connectionpooling
Problems with connection pooling.
Connection pooling with my own cofiguration file instead of Initial Context.
connection pool problem