commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Seva Popov" <seva_po...@tvworks.com>
Subject RE: [DBCP] The PerUserPoolDataSource/SharedPoolDataSource does not reuse/pool the db connections
Date Thu, 14 Jun 2007 00:06:31 GMT
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: oracle.jdbc.pool.OracleConnectionPoolDataSource@be4d44
Oracle pooled connection: oracle.jdbc.pool.OraclePooledConnection@51dee1
Underlying physical connection:
oracle.jdbc.driver.OracleConnection@ff8f2c
 

-----Original Message-----
From: Seva Popov [mailto:seva_popov@tvworks.com] 
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:
org.apache.tomcat.dbcp.dbcp.datasources.PerUserPoolDataSource@513bb5
Connection 1: oracle.jdbc.driver.OracleConnection@1bdedf8
Connection 2: oracle.jdbc.driver.OracleConnection@15daaef
Connection 3: oracle.jdbc.driver.OracleConnection@572554

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: org.apache.tomcat.dbcp.dbcp.BasicDataSource@12a4caf
Connection 1: org.apache.tomcat.dbcp.dbcp.PoolableConnection@12b7083
Connection 2: org.apache.tomcat.dbcp.dbcp.PoolableConnection@13a8045
Connection 3: org.apache.tomcat.dbcp.dbcp.PoolableConnection@13a8045

Am I missing something here?

Thanks,
Seva 

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


---------------------------------------------------------------------
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