I thought you might be wondering whether my OracleConnectionPoolDataSource is somehow configured incorrectly as the physical connection to the database.
To show that it's just fine I've added the following to my test JSP page: DataSource cpds = (DataSource) envContext.lookup("jdbc/MyCPDS "); System.out.println("DataSource: " + cpds); oracle.jdbc.pool.OracleConnectionPoolDataSource ocpdc = (oracle.jdbc.pool.OracleConnectionPoolDataSource) cpds; javax.sql.PooledConnection pc = ocpdc.getPooledConnection("user", "psw "); System.out.println("Oracle pooled connection: " + pc); Connection conn8 = pc.getConnection(); System.out.println("Underlying physical connection: " + conn8); And the output looks good: DataSource: [EMAIL PROTECTED] Oracle pooled connection: [EMAIL PROTECTED] Underlying physical connection: [EMAIL PROTECTED] -----Original Message----- From: Seva Popov [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 13, 2007 4:04 PM To: commons-user@jakarta.apache.org Subject: [DBCP] The PerUserPoolDataSource/SharedPoolDataSource does not reuse/pool the db connections Hi, I am using Tomcat 5.5.9 and have configured OracleConnectionPoolDataSource as the physical connection to the database and DBCP's PerUserPoolDataSource as the pool of logical connections to the same database (i.e. using the mentioned OracleConnectionPoolDataSource). Here is the configuration of my PerUserPoolDataSource (note the pool size): <Resource name="jdbc/MyPool" auth="Container" type="org.apache.tomcat.dbcp.dbcp.datasources.PerUserPoolDataSource" factory="org.apache.tomcat.dbcp.dbcp.datasources.PerUserPoolDataSourceFa ctory" dataSourceName="java:comp/env/jdbc/MyCPDS" maxActive="2" maxIdle="2" maxWait="5"/> Now, I am using the below test JSP page to see that the connections are reused from the pool: <jsp:directive.page import="javax.naming.Context" /> <jsp:directive.page import="javax.naming.InitialContext" /> <jsp:directive.page import="javax.sql.DataSource" /> <jsp:directive.page import="java.sql.Connection" /> <html> Hello <% Context initContext = new InitialContext(); Context envContext = (Context) initContext.lookup("java:/comp/env"); DataSource ds = (DataSource) envContext.lookup("jdbc/MyPool"); System.out.println("DataSource: " + ds); Connection conn1 = ds.getConnection("user", "psw "); System.out.println("Connection 1: " + conn1); Connection conn2 = ds.getConnection("user", "psw "); System.out.println("Connection 2: " + conn2); conn2.close(); // closing this connection to return it to the pool Connection conn3 = ds.getConnection("user", "psw "); System.out.println("Connection 3: " + conn3); Conn1.close(); conn3.close(); %> </html> Here is the output (and as you see all connections are different objects): DataSource: [EMAIL PROTECTED] Connection 1: [EMAIL PROTECTED] Connection 2: [EMAIL PROTECTED] Connection 3: [EMAIL PROTECTED] My questions: 1. Firstly, I've expected to get the same connection object (as for the connection #2) when I've obtained the 3thd connection because the pool max size is "2", the connection #1 is still active at the moment, and the connection #2 is closed and supposed to be returned to the pool. 2. Secondly, I've expected to get the DBCP's logical org.apache.tomcat.dbcp.dbcp.PoolableConnection but I've got the physical Oracle connection instead. I've got the same results with the DBCP's SharedPoolDataSource. So, for me it seems that neither SharedPoolDataSource nor PerUserPoolDataSource are really keeping and reusing the connections in the pool of database connections. Just to be complete with my testing I've verified that the Tomcat's default DBCP's BasicDataSource does reusing the connections from the pool. I.e. when I've configured BasicDataSource and used the similar test JSP page I saw the output as expected like this (the PoolableConnections #2 and #3 are the same i.e. they are reused): DataSource: [EMAIL PROTECTED] Connection 1: [EMAIL PROTECTED] Connection 2: [EMAIL PROTECTED] Connection 3: [EMAIL PROTECTED] Am I missing something here? Thanks, Seva --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]