• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • Liutauras Vilda
  • Jeanne Boyarsky
  • paul wheaton
Sheriffs:
  • Ron McLeod
  • Devaka Cooray
  • Henry Wong
Saloon Keepers:
  • Tim Holloway
  • Stephan van Hulst
  • Carey Brown
  • Tim Moores
  • Mikalai Zaikin
Bartenders:
  • Frits Walraven

problem in connectionpooling

 
Ranch Hand
Posts: 415
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
author
Posts: 3252
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Everybody's invited. Even this tiny ad:
Gift giving made easy with the permaculture playing cards
https://coderanch.com/t/777758/Gift-giving-easy-permaculture-playing
reply
    Bookmark Topic Watch Topic
  • New Topic