3 Replies Latest reply on Feb 2, 2009 3:23 AM by alekoc

    PooledConnection question

    alekoc

      Could somebody explain why I can not get few Connections from the pool at once?

      Connection con1 = null; Connection con2 = null; PooledConnection pc = apo.getPooledConnection(); try {   con1 = pc.getConnection();   con2 = pc.getConnection(); } catch (Exception ex) {   log.error("Connection error", ex); } finally {   if (con1 != null) {     con1.close();     con1 = null;   }   if (con2 != null) {     con2.close();     con2 = null;   } }

      When I get second Connection (con2), the first Connection immediately became “closed”.
      This is simplified example. I want to use few procedural depth methods, where each one will do some process with DB access (get Connection, use it and close). Also is very strange that sometime I was able to get two Connections, but never more than two. For my projects I need 4-6 simultaneously open Connections.
      Here is a method for PooledConnection creation

      private void buildConPool() throws Exception {   OracleConnectionPoolDataSource ds = null;   PooledConnection pc = null;   try {     ds = new OracleConnectionPoolDataSource();     ds.setDescription(serID + appName);     ds.setURL(dbPath);            ds.setDriverType(dbDriver);     ds.setUser(dbUser);            ds.setPassword(dbPassword);     pc = ds.getPooledConnection();   } catch (Exception ex) {     log.error("PooledConnection error", ex);     try {       if (pc != null) {         pc.close();         pc = null;       }     } catch (Exception ign) {     }     try {       if (ds != null) {         ds.close();         ds = null;       }     } catch (Exception ign) {     }   }   return; }
        • 1. Re: PooledConnection question
          dvohra21
          Create a OracleConnectionCacheImpl object and set minumum and maximum limit on the numbe rof connections in the connection pool.

          try {
          ds = new OracleConnectionPoolDataSource();
          ds.setDescription(serID + appName);
          ds.setURL(dbPath);
          ds.setDriverType(dbDriver);
          ds.setUser(dbUser);
          ds.setPassword(dbPassword);
          //Use Oracle Connection Cache implementation
          OracleConnectionCacheImpl ocacheimpl = new OracleConnectionCacheImpl(ds);
          //Set the maximum number of connections
          ocacheimpl.setMinLimit (3);
          ocacheimpl.setMaxLimit (10);

          Connection conn = ocacheimpl.getConnection();
          } catch (Exception ex) {                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
          • 2. Re: PooledConnection question
            dvohra21
            By setting the minimum/maximum limit do more connections become available?
            • 3. Re: PooledConnection question
              alekoc
              Thank you dvohra. I found another decision. Looks like I should get every Connection through the PooledConnection and don’t keep PooledConnection for long time. The only DataSource can be created and saved for long time. So, next code works great
              Connection con1 = null;
              Connection con2 = null;
              try {
                  con1 = apo.getDataSource().getPooledConnection().getConnection();
                  con2 = apo.getDataSource().getPooledConnection().getConnection();
              } catch (Exception ex) {
                  apo.getLogger().error("Connection error", ex);
              } finally {
                  if (con1 != null) {
                      con1.close();
                      con1 = null;
                  }
                  if (con2 != null) {
                      con2.close();
                      con2 = null;
                  }
              }