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