Re: Tomcat JDBC Connection Pool interfaces to PostgreSQL...

https://github.com/davecramer/testunwrap for a test that works with DBCP

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

On 17 May 2016 at 17:04, Eric Neron <eneron(at)e-djuster(dot)ca> wrote:

> Thanks Dave for your quick response...
>
> You are cheating a little bit since you are using the
> DBSimpleDataSource... (which according to the Doc "Simple DataSource
> which does not perform connection pooling").
>
> As I explained in my email I am using the
>
> org.apache.tomcat.jdbc.pool.DataSource
>
> and this one (again according to the Doc "The *JDBC Connection
> Pool org.apache.tomcat.jdbc.pool* is a replacement or an alternative to
> the Apache Commons DBCP <http://commons.apache.org/dbcp/> connection
> pool").
>
> This is from this one that I do not seem to be able to access the
> PGConnection object...
>
> So does your answer is some sort of "It is on the Tomcat side"?
>
> Granted that the issue sits either on the PostgreSQL, Tomcat, or my side.
> I am just surprised that it is so difficult to make this work properly,
> given that I use Tomcat (a fairly widely used Application Server),
> PostgreSQL (a fairly widely used DB engine), the combination of the two
> (which I also suspect is fairly widely used) and that I seem to be the only
> one having this issue! It is like saying that printf does not work on this
> particular C compiler... I must not be the only one trying to use the
> combination of Tomcat Connection pooling with a PostgreSQL DB engine; this
> simply cannot be the case!
>
> Strangely, when using the Netbeans debugger to examine the Connection that
> is returned by the Tomcat Connection Pooling engine, I see:
>
> poolConn = (com.sun.proxy.$Proxy77)
> ProxyConnection[PooledConnection[org(dot)postgresql(dot)jdbc(dot)PgConnection(at)1b712b5e
> ]]
>
> So the PgConnection is there buried somewhere underneath the
> ProxyConnection/PooledConnection objects. If I could find a way to access
> it, my problems would be solved, since according to the Doc, it implements
> all the Interfaces I need to have access to (PGConnection & BaseConnection):
>
>
> - java.lang.Object
> <http://docs.oracle.com/javase/8/docs/api/java/lang/Object.html?is-external=true>
> -
> - org.postgresql.jdbc.PgConnection
>
>
> - All Implemented Interfaces:AutoCloseable
> <http://docs.oracle.com/javase/8/docs/api/java/lang/AutoCloseable.html?is-external=true>
> , Connection
> <http://docs.oracle.com/javase/8/docs/api/java/sql/Connection.html?is-external=true>
> , Wrapper
> <http://docs.oracle.com/javase/8/docs/api/java/sql/Wrapper.html?is-external=true>
> , BaseConnection
> <https://jdbc.postgresql.org/development/privateapi/org/postgresql/core/BaseConnection.html>
> , PGConnection
> <https://jdbc.postgresql.org/development/privateapi/org/postgresql/PGConnection.html>
> I just cannot get access to it!Anyway, thanks for you time & quick
> response!
> Eric
>
>
>
> On Tue, May 17, 2016 at 4:17 PM, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:
>
>> it would appear that tomcat is not returning the PgConnection object.
>>
>> I just wrote this as a test:
>>
>> public void testConnectionUnwrapPGDataSource() throws SQLException {
>> PGSimpleDataSource dataSource = new PGSimpleDataSource();
>> assertNotNull(dataSource);
>> dataSource.setDatabaseName("test");
>> dataSource.setServerName("localhost");
>> dataSource.setPortNumber(5432);
>> Connection connection = dataSource.getConnection("test","test");
>> assertNotNull(connection);
>> Object v=connection.unwrap(PGConnection.class);
>> assertNotNull(v);
>> assertTrue(v instanceof PGConnection);
>>
>> }
>>
>> and it works fine.
>>
>>
>> Dave Cramer
>>
>> davec(at)postgresintl(dot)com
>> www.postgresintl.com
>>
>> On 17 May 2016 at 14:40, Eric Neron <eneron(at)e-djuster(dot)ca> wrote:
>>
>>> I am currently working using the Tomcat JDBC Connection Pool (
>>> http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html) which we plan
>>> to use in our Java Web application in order to streamline server resources
>>> usage associated to supporting our PostgreSQL connections (i.e.,
>>> minimize the number or connections required).
>>>
>>> I am using the following jar files to support my work:
>>>
>>> tomcat-jdbc.jar
>>> postgresql-9.4.1208.jre7.jar
>>>
>>> Although most of it seems to be working fine so far, I am hitting issues
>>> when I try to "cast" the connection the pool is returning to me (a
>>> ProxyConnection) into either a PGConnection or BaseConnection. I need
>>> those in order to access either of the following PostgreSQL
>>> "advanced/internal" methods:
>>>
>>> - addDataType () (PGConnection)
>>> - CopyManager () (BaseConnection).
>>>
>>> The Tomcat documentation suggests to extract the native connection by
>>> using the following method:
>>>
>>> Connection pgConnection = ((javax.sql.PooledConnection) conn).
>>> getConnection ( );
>>>
>>> Although this does not fail, using a debugger, I can see that
>>> pgConnection is really an
>>> org.postgresql.jdbc.PgConnection (not an org.postgresql.PGConnection),
>>> and there is not way it seems that I can bridge between the two... even by
>>> using:
>>>
>>> pgConnection.unwrap ( PGConnection.class );
>>>
>>> I am just wondering if this issue is caused by the fact that I use the
>>>
>>> org.apache.tomcat.jdbc.pool.DataSource to instantiate my Connection
>>> Pool.
>>>
>>> Browsing through the list of choice given to me to determine where
>>> the DataSource class is imported in my Java code, I can see:
>>>
>>> javax.sql.DataSource (which I have seen can give one access to
>>> PGConnection from one of my research on the Web - http://stackoverflow
>>> .com/questions/27898632/how-to-cast-jdbc4connection-to-pgconnection),
>>> though if I select it, it tells me that this is an abstract Class that I
>>> cannot instantiate from!
>>>
>>> Similarly, another StackOverlflow page (http://stackoverflow
>>> .com/questions/36986653/cast-java-sql-connection-to-pgconnection)
>>> suggests using the following code:
>>>
>>> PGConnection pgConnection = dataSource.getConnection().unwrap(PGConnection.class);
>>>
>>> but again, if I try this, I get the following SQLException: "Not a wrapper of org.postgresql.PGConnection"
>>>
>>> (in fact, it turns out that this is exactly the same solution as the "Tomcat solution" I explained above).
>>>
>>> I am running out of alternatives... Any ideas?
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>