aspose file tools*
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 EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "problem in connectionpooling" Watch "problem in connectionpooling" New topic
Author

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="plasma.properties";
public String path=this.getClass().getResource("plasma.properties").getPath();
private Vector availableConnections, busyConnections;
private int maxConnections;
private boolean connectionPending = false;
static synchronized public ConnectionPooling getInstance() throws Exception
{
if (instance == null)
{
logger.info("Getting the instance of Connection pool");
instance = new ConnectionPooling();
}
clients++;
System.out.println("no of clients:"+clients);
return instance;
}
private ConnectionPooling()
{
init();
}
private void init()
{
logger.info("Instantiating the Connection pool");
int maxConnections=0;
try{
//System.out.println("The url is :"+this.getClass().getResource("plasma.properties").getPath());
maxConnections=Integer.parseInt(PropertyProvider.getProperty(path,"maximumconnections"));

}
catch(Exception e){
System.out.println(e.getMessage());}
availableConnections = new Vector(maxConnections);
busyConnections = new Vector();
logger.info("Pool of size"+maxConnections);
for(int i=0; i<maxConnections; i++)
{

availableConnections.addElement(makeNewConnection());
}
}

public Connection makeNewConnection()
{
Properties fat=new Properties();
Connection conn=null;
try
{
fat=PropertyProvider.getPropertiesObject(path);
String driver=fat.getProperty("Driver");
Class.forName(driver).newInstance();
String user=fat.getProperty("username");
String url=fat.getProperty("url");
conn = DriverManager.getConnection(url,user,"");
System.out.println("Driver loaded");
logger.info("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;
availableConnections.removeElementAt(lastIndex);
// 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());
return(getConnection());
}
else
{
busyConnections.addElement(existingConnection);
return(existingConnection);
}
}
else
{

// 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)
{
makeBackgroundConnection();
}
else
{
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 {
wait();
} catch(InterruptedException ie) {
logger.error("Exception in Pooling.getConnection:"+ie.getMessage());
}
// Someone freed up a connection, so try again.

return(getConnection());
}
}
// 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);
connectThread.start();
} catch(OutOfMemoryError oome) {
logger.error("Exception in makeBackgroundConnection:"+oome.getMessage()); }
}
public void run() {
try {
Connection conn = makeNewConnection();
synchronized(this) {
availableConnections.addElement(conn);
connectionPending = false;
notifyAll();
}
} 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) {
try{
if(pst!=null)
pst.close();
if(rs!=null)
rs.close();

}
catch(Exception e)
{
System.out.println("Close Connection in Pooling"+e.getMessage());
}
busyConnections.removeElement(connection);
availableConnections.addElement(connection);
// Wake up threads that are waiting for a connection
notifyAll();
}

public synchronized int totalConnections() {
return(availableConnections.size() +
busyConnections.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() {
closeConnections(availableConnections);
availableConnections = new Vector();
closeConnections(busyConnections);
busyConnections = new Vector();
}
private void closeConnections(Vector connections)
{
try
{
for(int i=0; i<connections.size(); i++)
{
Connection connection =(Connection)connections.elementAt(i);
if (!connection.isClosed())
{
connection.close();
}
}
}
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 ConnectionPooling.free(Connection 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
Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
Before I spend time looking at your problem: given that every application server comes with a built in connection pool -- given that there are some decent connection pool implementations freely available in the rare cases that the app server one won't do -- why would you write your own flawed, broken and buggy connection pool?
Writing a decent connection pool seems really easy, but is in fact surprisingly hard.
- Peter
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: problem in connectionpooling
 
Similar Threads
connection pool problem
Problems with connection pooling.
how many initial connections should be opend
Connection pooling with my own cofiguration file instead of Initial Context.
problem in connectionpooling