commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Shaan" <sh...@em-t.com>
Subject [dbcp] removing idle connections.
Date Sun, 10 Oct 2004 08:34:57 GMT

 Hi,
         My problem is not new. But i need your suggestion. 

  I have java based application which is connecting to oracle database.
There is firewall in between Application and Database. Physical distance
between 2 servers also considerable more. So it takes around 3-4 seconds to
initiate any connection. I was using Oracle thin driver before. 3-4 seconds
time gap forced me to use Oracle's connection caching. which worked well but
that does not have any mechanism to remove connections from pool which are
closed by firewall after some certain idle timeout. This firewall behavior
is now creating problem for my application. Oracle driver has solution for
this but that works only with Oracle 10g. And i am using Oracle 9i. I cant
change it to 10g.

 Well then i found DBCP. I made it working on my test samples but i have few
queries as I am new to it.
 
1) What settings i have to bring in for DBCP to remove these types of
connections?

2) Can i set some time interval for DBCP to remove it automatically from
pool before firewall close them? 

3) Can DBCP remove broken connections by itself? As  connections can be
broken by any reason by  any party at any time. 


 I gone through parameters of DBCP but i became confused,  which one is
useful in my case and how to use them in following my sample code (copied
from ManualPoolingDriverExample :p )? 


thanks 

regards,
Shahnaz Ali.




======================== Sample Code ========================


 
 public class ManualPoolingDriverExample 
  {

    public static void main(String[] args) throws Exception 
     {

        System.out.println("Loading underlying JDBC driver.");
       
        try 
         {
            Class.forName("oracle.jdbc.driver.OracleDriver");
         }
        catch (ClassNotFoundException e) 
         {
            e.printStackTrace();
         }


        System.out.println("Setting up driver.");
        
        try 
         {
            setupDriver(  "jdbc:oracle:thin:ET002/ET002@10.0.0
6:1521:EngApps" );
         }
        catch (Exception e) 
         {
            e.printStackTrace();
         }


  for (int j=1; j<=10 ; j++)
   {

   System.out.println ( j + "
===================================================== " ) ;
   for (int i=1; i<=10 ; i++)
    {
    new NeedData().start() ;
    }
   Thread.sleep ( 15000) ;
   }
  System.out.println ( " over " ) ;

       try 
        {
            shutdownDriver();
        }
       catch (Exception e) 
        {
            e.printStackTrace();
        }
    }

    public static void setupDriver(String connectURI) throws Exception 
     {
 
   ObjectPool connectionPool = new GenericObjectPool(null);
        ConnectionFactory connectionFactory = new
DriverManagerConnectionFactory(connectURI,null);
        PoolableConnectionFactory poolableConnectionFactory = new
PoolableConnectionFactory(connectionFactory,connectionPool,null,null,false
true);

        Class.forName("org.apache.commons.dbcp.PoolingDriver");
        PoolingDriver driver = (PoolingDriver) DriverManager.getDriver(
jdbc:apache:commons:dbcp:");

        driver.registerPool("example",connectionPool);

    }

    public static void printDriverStats() throws Exception 
    {
        PoolingDriver driver = (PoolingDriver) DriverManager.getDriver(
jdbc:apache:commons:dbcp:");
        ObjectPool connectionPool = driver.getConnectionPool("example");

        System.out.println("NumActive: " + connectionPool.getNumActive());
        System.out.println("NumIdle: " + connectionPool.getNumIdle());
    }

    public static void shutdownDriver() throws Exception 
    {
        PoolingDriver driver = (PoolingDriver) DriverManager.getDriver(
jdbc:apache:commons:dbcp:");
        driver.closePool("example");
    }
}



  class NeedData extends Thread
   {

    public NeedData()
     {
     }

    public void run ()
     {
      try
       {
       getTableNameList(   ) ;
       }
      catch (Exception e)
       {
      e.printStackTrace() ;
       }
     }


      public ArrayList getTableNameList(  ) throws Exception
       {

   ArrayList prodList = new ArrayList();

          Connection conn = null;
          Statement stmt = null;
          ResultSet rset = null;

         try
          {
              // Get Connection from the Cache
              java.util.Date d1 = new java.util.Date () ;

              conn = DriverManager.getConnection(
jdbc:apache:commons:dbcp:example");
              stmt = conn.createStatement();
              rset = stmt.executeQuery(" SELECT * from tab ");

         java.util.Date d2 = new java.util.Date () ;
         System.out.println ( "Time taken :" + (d2.getTime() - d1.getTime())
);

          }
          catch (SQLException ex)
          {
              throw new Exception("SQL Error : \n" + ex.toString());
          }
          finally
           {
              try
              {
                  rset.close();
                  stmt.close();
                  conn.close();
              }
              catch (Exception ex)
              { 
                  throw new Exception("SQL Error while closing objects = " +
ex.toString());
              }
          }
         return prodList;
          
      }

 }







============================================================


---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org


Mime
View raw message