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 Mon, 18 Jun 2007 21:07:43 GMT
Hi Phil,

Here is the config for the physical connections:

         <Resource name="jdbc/MyCPDS"
            auth="Container"
            type="oracle.jdbc.pool.OracleConnectionPoolDataSource"
            factory="oracle.jdbc.pool.OracleDataSourceFactory"
            url="jdbc:oracle:thin:@some_url:1521:some_sid"/>


Yes, I know about the differences between PerUserPoolDataSource and
SharedPoolDataSource and I agree that the test for PerUserPoolDataSource
was not valid as you've indicated and I will try it again.  

However, I've used the same configuration for the SharedPoolDataSource
with the same negative results. 

Also, in both cases when I've used ds.getConnection() I've obtained the
raw Oracle connection but I've expected to get
org.apache.tomcat.dbcp.dbcp.PoolableConnection as with the
BasicDataSource.  Note, that I am not expecting to get
OraclePooledConnection as you've indicated as this would be wrong as the
OraclePooledConnection (that implements javax.sql.PooledConnection) is
supposed to be used internally by the Pool Manager implementation. The
OraclePooledConnection does not implement java.sql.Connection.  So, what
the DBCP's Pool Manager should do is to wrap the OraclePooledConnection
with the org.apache.tomcat.dbcp.dbcp.PoolableConnection (or some other
wrapper that implements java.sql.Connection interface) and return the
latter to the client.

Is it not the case with DBCP?


-----Original Message-----
From: Phil Steitz [mailto:phil.steitz@gmail.com] 
Sent: Sunday, June 17, 2007 3:32 PM
To: Jakarta Commons Users List
Subject: Re: [DBCP] The PerUserPoolDataSource/SharedPoolDataSource does
not reuse/pool the db connections

On 6/13/07, Seva Popov <seva_popov@tvworks.com> wrote:
>
> 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"/>
>

For PerUserPoolDataSource, the parameters are specified differently,
since you can set defaults for the pool and also override them per
user.  See
http://jakarta.apache.org/commons/dbcp/api-1.2.2/org/apache/commons/dbcp
/datasources/PerUserPoolDataSource.html
for the property names and
http://wiki.apache.org/jakarta-commons/DBCP for an example config.

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

For PerUserPoolDataSource this is most likely due to maxActive,
maxIdle parameters not being recognized.

> 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.
>
Where and how do you specify the Oracle driver?  What you should get
back is a a PooledConnection.

> I've got the same results with the DBCP's SharedPoolDataSource.

The maxActive, maxIdle parameters should work for
SharedPoolDataSource.  Can you share the config that you used for this
test?

Phil

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