Oracle Connection Pooling

[George Stoianov]

Hello guys,

I am trying to setup Oracle Connection Pooling for a webapp I am working on. I am using the following code:

  
...
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.Properties;
import javax.sql.PooledConnection;
import oracle.jdbc.pool.OracleConnectionCacheManager;
import oracle.jdbc.pool.OracleConnectionPoolDataSource;
import oracle.jdbc.pool.OracleDataSource;
import oracle.jdbc.pool.OracleOCIConnectionPool;
import org.apache.log4j.Logger
...
 
    /**
     * Test the Oracle pool connections and how many logical can ride on a
     * single physical connection.
     */
    public static void makeConnection4() {
        try {
            
            OracleConnectionPoolDataSource ocpds =
                    new OracleConnectionPoolDataSource();
            ocpds.setURL("jdbc:oracle:thin:@<host>:<port>:<SID>");
            ocpds.setUser("user");
            ocpds.setPassword("password");
            
            log.debug("Created a pooled connection ...");
            
            PooledConnection pooledConnection = ocpds.getPooledConnection();
            
            log.debug("Get a connections from the pooled connection.");
            
            ArrayList connList = new ArrayList();
            
            for (int i = 0; i < 10; i++) {
                Connection temp = ocpds.getConnection();
                System.out.println("getting logical connection "
                        + i + ": " + temp);
                connList.add(temp);
            }
            
            //close them
            Iterator iter = connList.iterator();
            
            /**
            while(iter.hasNext()) {
                Connection temp = (Connection) iter.next();
                log.debug("Closing connection: " + temp);
                temp.close();
            }
            **/
            
            
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
 

I am going through R.M. Menon's book and I am a little confused between the pool and cache concepts I guess. What I mean is in the above code I am using a single pooled connection and getting logical connections from it, 10 in this case, what I am wondering is how many logical connections (Connection objects) can ride on a single PooledConnection??? I mean why have multiple physical if one can handle many logical once, what is the point of caching the physical connections? Why do we need OCI then? When am I supposed to open a new physical from the cache???

I know lots of questions but I thought I can try my luck and see what you guys have to say. I am using Java 1.5 and Oracle 10g backend with the oracle 10g jdbc driver, I need/wish to use that.

Thanks,
George

[ December 11, 2007: Message edited by: George Stoianov ]
[ December 11, 2007: Message edited by: George Stoianov ]