JDBC connection pooling closing connections probelm

Hi i am still trying to get this to work...

I am using some connection pooling code to try and avoid the open and close network overhead when i query a mysql database from a client machine.

The pooling code is working EXCEPT in the closing of the connections part, i have tried to get this to work a number of ways, but i can't see the problem,

The class JDCConnection implements Connection and so overwrites its close() method, using a
JDCConnection pool...

The orginal code didn't close anything its just removed items from the vector i am trying to close
the Connection , but am confused as to how ?  any ideas would be appreciated
 the code is below

ERROR

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection,  message from server: "Too many connections"
      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

________________________________________________________________________
class ConnectionReaper extends Thread {

    private JDCConnectionPool pool;
    private final long delay=300000;

    ConnectionReaper(JDCConnectionPool pool) {
        this.pool=pool;
    }

    public void run() {
        while(true) {
           try {
              sleep(delay);
           } catch( InterruptedException e) { }
           pool.reapConnections();
        }
    }
}

public class JDCConnectionPool {

   private Vector connections;
   private String url, user, password;
   final private long timeout=600000000;
   private ConnectionReaper reaper;
   final private int poolsize=10;

   public JDCConnectionPool(String url, String user, String password) {
      this.url = url;
      this.user = user;
      this.password = password;
      connections = new Vector(poolsize);
      reaper = new ConnectionReaper(this);
      reaper.start();
   }

   public synchronized void reapConnections() {

      long stale = System.currentTimeMillis() - timeout;
      Enumeration connlist = connections.elements();
    
      while((connlist != null) && (connlist.hasMoreElements())) {
          JDCConnection conn = (JDCConnection)connlist.nextElement();

          if((conn.inUse()) && (stale >conn.getLastUse()) && 
                                            (!conn.validate())) {
 	      
 	      try {
			conn.close();
			removeConnection(conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
         }
      }
   }

   public synchronized void closeConnections() {
        
      Enumeration connlist = connections.elements();

      while((connlist != null) && (connlist.hasMoreElements())) {
          JDCConnection conn = (JDCConnection)connlist.nextElement();
          try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
          removeConnection(conn);
      }
   }

   private synchronized void removeConnection(JDCConnection conn) {
	   try {
		conn.close();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
       connections.removeElement(conn);
   }


   public synchronized Connection getConnection() throws SQLException {

       JDCConnection c;
       
       try
       {
       for(int i = 0; i < connections.size(); i++) {
           c = (JDCConnection)connections.elementAt(i);
           if (c.lease()) {
              return c;
           }
       }

       }
       catch(Exception t)
       {
    	   
    	   System.out.println(" t " + t.getMessage());
    	   
    	   
       }
       System.out.println(" attempting to create new connection ");
       
       try
       {
    	   Connection conn = DriverManager.getConnection(url, user, password);
           c = new JDCConnection(conn, this);
           c.lease();
           connections.addElement(c);
           return c;
    	   
       }
       catch(Exception e)
       {
    	   System.out.println(" unable to open new connection ");
    	   e.printStackTrace();
       }
	return null;
     
  } 

   public synchronized void returnConnection(JDCConnection conn) {
      conn.expireLease();
   }
}
__________________________________________________________

package data;
import java.sql.*;
import java.util.*;
import java.io.*;

public class JDCConnection implements Connection {

    private JDCConnectionPool pool;
    private Connection conn;
    private boolean inuse;
    private long timestamp;


    public JDCConnection(Connection conn, JDCConnectionPool pool) {
        this.conn=conn;
        this.pool=pool;
        this.inuse=false;
        this.timestamp=0;
    }

    public synchronized boolean lease() {
       if(inuse)  {
           return false;
       } else {
          inuse=true;
          timestamp=System.currentTimeMillis();
          return true;
       }
    }
    public boolean validate() {
	try {
            conn.getMetaData();
        }catch (Exception e) {
	    return false;
	}
	return true;
    }

    public boolean inUse() {
        return inuse;
    }

    public long getLastUse() {
        return timestamp;
    }

    public void close() throws SQLException {
       pool.returnConnection(this);
    }

    protected void expireLease() {
        inuse=false;
    }

    protected Connection getConnection() {
        return conn;
    }

    public PreparedStatement prepareStatement(String sql) throws SQLException {
        return conn.prepareStatement(sql);
    }

    public CallableStatement prepareCall(String sql) throws SQLException {
        return conn.prepareCall(sql);
    }

    public Statement createStatement() throws SQLException {
        return conn.createStatement();
    }

    public String nativeSQL(String sql) throws SQLException {
        return conn.nativeSQL(sql);
    }

    public void setAutoCommit(boolean autoCommit) throws SQLException {
        conn.setAutoCommit(autoCommit);
    }

    public boolean getAutoCommit() throws SQLException {
        return conn.getAutoCommit();
    }

    public void commit() throws SQLException {
        conn.commit();
    }

    public void rollback() throws SQLException {
        conn.rollback();
    }

    public boolean isClosed() throws SQLException {
        return conn.isClosed();
    }

    public DatabaseMetaData getMetaData() throws SQLException {
        return conn.getMetaData();
    }

    public void setReadOnly(boolean readOnly) throws SQLException {
        conn.setReadOnly(readOnly);
    }
  
    public boolean isReadOnly() throws SQLException {
        return conn.isReadOnly();
    }

    public void setCatalog(String catalog) throws SQLException {
        conn.setCatalog(catalog);
    }

    public String getCatalog() throws SQLException {
        return conn.getCatalog();
    }

    public void setTransactionIsolation(int level) throws SQLException {
        conn.setTransactionIsolation(level);
    }

    public int getTransactionIsolation() throws SQLException {
        return conn.getTransactionIsolation();
    }

    public SQLWarning getWarnings() throws SQLException {
        return conn.getWarnings();
    }

    public void clearWarnings() throws SQLException {
        conn.clearWarnings();
    }

	@Override
	public Array createArrayOf(String arg0, Object[] arg1) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Blob createBlob() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Clob createClob() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public NClob createNClob() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public SQLXML createSQLXML() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Statement createStatement(int arg0, int arg1) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Statement createStatement(int arg0, int arg1, int arg2)
			throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Struct createStruct(String arg0, Object[] arg1) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Properties getClientInfo() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public String getClientInfo(String arg0) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public int getHoldability() throws SQLException {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public Map<String, Class<?>> getTypeMap() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public boolean isValid(int arg0) throws SQLException {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public CallableStatement prepareCall(String arg0, int arg1, int arg2)
			throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public CallableStatement prepareCall(String arg0, int arg1, int arg2,
			int arg3) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public PreparedStatement prepareStatement(String arg0, int arg1)
			throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public PreparedStatement prepareStatement(String arg0, int[] arg1)
			throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public PreparedStatement prepareStatement(String arg0, String[] arg1)
			throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public PreparedStatement prepareStatement(String arg0, int arg1, int arg2)
			throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public PreparedStatement prepareStatement(String arg0, int arg1, int arg2,
			int arg3) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void releaseSavepoint(Savepoint arg0) throws SQLException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void rollback(Savepoint arg0) throws SQLException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void setClientInfo(Properties arg0) throws SQLClientInfoException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void setClientInfo(String arg0, String arg1)
			throws SQLClientInfoException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void setHoldability(int arg0) throws SQLException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public Savepoint setSavepoint() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Savepoint setSavepoint(String arg0) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void setTypeMap(Map<String, Class<?>> arg0) throws SQLException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public boolean isWrapperFor(Class<?> arg0) throws SQLException {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public <T> T unwrap(Class<T> arg0) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}
}

Open in new window

SYMONAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mick BarryJava DeveloperCommented:
> Data source rejected establishment of connection,  message from server: "Too many connections"

you need to configure the size of the pool to match the max number of connections your db allows to be opened
0
Mick BarryJava DeveloperCommented:
closing a connection should just return it to the pool (leaving it open)
the pool can then close it after a specified amount of time if its not needed
0
SYMONAuthor Commented:
yeah i've tried increasing the pool size, but i'm not sure about the reaper part.. i think that each time this is called that its creating and running a new thread with a new "pool" of connections and that's why i eventually run out of connections..

so when you just remove a connection from the Vector does that mean that the connection is still live on the server side ?
0
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Mick BarryJava DeveloperCommented:
no the reaper is what actually closes the connections (after they have been idle for a period of time)

and if you're running out of connections then you should be decreasing the size of the pool, not increasing it
0
SYMONAuthor Commented:
tried that reduced pool size to one, still eventually get the same problem after i run about 50 queries..?
0
Mick BarryJava DeveloperCommented:
actually that pool doesn't limit the numbers of connections it will open
any reason you're using it and not one of the various others available such as dbcp and c3po

are you closing connections once you have finished with them?
0
SYMONAuthor Commented:
No specific reason, just want to try and start using connection pooling,

>such as dbcp and c3po

i don't know what these are ?

>are you closing connections once you have finished with them?

No, that's the problem i can't work out when/how to close the connections properly.. this is why i am
running out of connections....

i am open to alternatives ;)


0
Mick BarryJava DeveloperCommented:
you close the connection the same way you usually close a connection :)

connection.close();
0
SYMONAuthor Commented:

>connection.close()

yeah but in this case JDCConnection implments Connection and  overwrites close...

public void close() throws SQLException {
       pool.returnConnection(this);
    }

which calls JDCConnectionPool

public synchronized void returnConnection(JDCConnection conn) {
      conn.expireLease();
   }

which calls in JDCConnection

protected void expireLease() {
        inuse=false;
    }

.. the part that is confusing me is that all of this stuff is acting on a JDCConnection pool ref in JDCConnection..

http://commons.apache.org/dbcp/
http://sourceforge.net/projects/c3p0/

i need something that i can use for mysql ?
0
Mick BarryJava DeveloperCommented:
> yeah but in this case JDCConnection implments Connection and  overwrites close...

yes, thats what it should do
you still need to ensure your application code calls close() once done, otherwise the connection never gets returned to the pool

> .. the part that is confusing me is that all of this stuff is acting on a JDCConnection pool ref in JDCConnection..

thats the actual pool that the connection came from (and is returned to)
0
SYMONAuthor Commented:
yeah so what should be happening is that i grab a connection from the pool and use it, when its finished it goees back into an "open" state and gets returned to the vector... this is what the reaper does ...

but that's not what's happening when i keep calling
pool.getConnection();

i am continuing to make new connections ??

0
Mick BarryJava DeveloperCommented:
> i am continuing to make new connections ??

yes that will happen if you aren't calling close()
0
SYMONAuthor Commented:
but i am calling close... well the version.. i found out that it's trying to create new connections all the time
and that the program crashes when the number of attempts gets to 100...

so it looks as if the whole .. leasing connections part ain't working ?
public synchronized Connection getConnection() throws SQLException {

       JDCConnection c;
       
       try
       {
       for(int i = 0; i < connections.size(); i++) {
           c = (JDCConnection)connections.elementAt(i);
           if (c.lease()) {
              return c;
           }
       }

       }
       catch(Exception t)
       {
    	   
    	   System.out.println(" t " + t.getMessage());
    	   
    	   
       }
       System.out.println(" attempting to create new connection ");
       numberOfAttempts++;
       System.out.println(" \n\n\n\n numberOfAttempts " + numberOfAttempts);
       
       
       
       
       try
       {
    	   Connection conn = DriverManager.getConnection(url, user, password);
           c = new JDCConnection(conn, this);
           c.lease();
           connections.addElement(c);
           return c;
    	   
       }
       catch(Exception e)
       {
    	   System.out.println(" unable to open new connection ");
    	   e.printStackTrace();
       }
	return null;
     
  } 

Open in new window

0
Mick BarryJava DeveloperCommented:
I mean closing the connection in your application code, if you don't do that the connnection can never get reused
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SYMONAuthor Commented:
very helpful advice for a non trivial problem :)

thanks again :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.