Thread: Tomcat JDBC Connection Pool interfaces to PostgreSQL...
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?
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.On 17 May 2016 at 14:40, Eric Neron <eneron@e-djuster.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.jarpostgresql-9.4.1208.jre7.jarAlthough 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 anorg.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 theorg.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?
This appears to be another incident of a report received a couple of weeks ago...
David J.
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.On 17 May 2016 at 14:40, Eric Neron <eneron@e-djuster.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.jarpostgresql-9.4.1208.jre7.jarAlthough 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 anorg.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 theorg.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?
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 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.postgresql.jdbc.PgConnection@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
- org.postgresql.jdbc.PgConnection
- All Implemented Interfaces:
- AutoCloseable, Connection, Wrapper, BaseConnection, PGConnection
- 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@fastcrypt.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.On 17 May 2016 at 14:40, Eric Neron <eneron@e-djuster.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.jarpostgresql-9.4.1208.jre7.jarAlthough 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 anorg.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 theorg.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?
Eric,
Yes, a bit. Can you create a self contained test using DBCP and I'll try to find some time to look at it?
Dave
On 17 May 2016 at 17:04, Eric Neron <eneron@e-djuster.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 theorg.apache.tomcat.jdbc.pool.DataSourceand this one (again according to the Doc "The JDBC Connection Poolorg.apache.tomcat.jdbc.poolis a replacement or an alternative to the Apache Commons 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.postgresql.jdbc.PgConnection@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
- org.postgresql.jdbc.PgConnection
- All Implemented Interfaces:
- AutoCloseable, Connection, Wrapper, BaseConnection, PGConnection
- 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@fastcrypt.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.On 17 May 2016 at 14:40, Eric Neron <eneron@e-djuster.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.jarpostgresql-9.4.1208.jre7.jarAlthough 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 anorg.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 theorg.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?
https://github.com/davecramer/testunwrap for a test that works with DBCP
On 17 May 2016 at 17:04, Eric Neron <eneron@e-djuster.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 theorg.apache.tomcat.jdbc.pool.DataSourceand this one (again according to the Doc "The JDBC Connection Poolorg.apache.tomcat.jdbc.poolis a replacement or an alternative to the Apache Commons 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.postgresql.jdbc.PgConnection@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
- org.postgresql.jdbc.PgConnection
- All Implemented Interfaces:
- AutoCloseable, Connection, Wrapper, BaseConnection, PGConnection
- 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@fastcrypt.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.On 17 May 2016 at 14:40, Eric Neron <eneron@e-djuster.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.jarpostgresql-9.4.1208.jre7.jarAlthough 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 anorg.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 theorg.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?
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 anorg.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:
If you get this I'm not sure where the issue is...
org.postgresql.jdbc.PgConnection implements org.postgresql.core.BaseConnect which extends
org.postgresql.PGConnection which is the interface that you desire.
pgConnection.unwrap ( PGConnection.class );
Not sure...
David J.
Hi Dave,
Strangely, when building my "bare metal" test case, the issue disappears... I can send you the code if you wish (but as I said, it does not demonstrate the issue that I am having since this issue has vanished). I have to investigate which layer of my application is causing me this issue...
But to comfort the other people looking over your shoulder, let me just say that the following code (which demonstrates the gist of it) appears to work fine:
// Set-up ConnectionPool...
dataSource = initTomcatConnectionPool ( );
// Get a connection from the Pool
// TODO: Put your breakpoint here to examine the connection that is returned from the Tomcat pool...
connection = borrowConnection ( );
// TODO: This is where I am having problem casting the above variable into a PgConnection or a BaseConnection
PgConnection pgConnection = connection.unwrap ( PgConnection.class );
CopyManager cm = new CopyManager ( (BaseConnection) pgConnection );
PGClassAndType pgClassAndType = new PGClassAndType ( );
pgConnection.addDataType( pgClassAndType.getType ( ), PGClassAndType.class );
PreparedStatement stmt = pgConnection.prepareStatement ( "SELECT 1" );
FYI, this is the borrowConnection () code:
public static Connection borrowConnection ( ) throws SQLException, InterruptedException {
Connection conn = ( dataSource == null ) ? null : dataSource.getConnection ( );
return conn;
}
and dataSource is defined as follows:
import org.apache.tomcat.jdbc.pool.DataSource;
private static DataSource dataSource = null;
Thanks anyway for making me delve into my own code... I think I have made some progress at least telling me there should be a way to make this work!
Regards,
Eric
On Tue, May 17, 2016 at 5:06 PM, Dave Cramer <pg@fastcrypt.com> wrote:
Eric,Yes, a bit. Can you create a self contained test using DBCP and I'll try to find some time to look at it?DaveOn 17 May 2016 at 17:04, Eric Neron <eneron@e-djuster.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 theorg.apache.tomcat.jdbc.pool.DataSourceand this one (again according to the Doc "The JDBC Connection Poolorg.apache.tomcat.jdbc.poolis a replacement or an alternative to the Apache Commons 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.postgresql.jdbc.PgConnection@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
- org.postgresql.jdbc.PgConnection
- All Implemented Interfaces:
- AutoCloseable, Connection, Wrapper, BaseConnection, PGConnection
- 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@fastcrypt.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.On 17 May 2016 at 14:40, Eric Neron <eneron@e-djuster.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.jarpostgresql-9.4.1208.jre7.jarAlthough 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 anorg.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 theorg.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?
Ya I think tc does something unique like instantiate the concrete class
On May 17, 2016 6:16 PM, "Eric Neron" <eneron@e-djuster.ca> wrote:
Hi Dave,Strangely, when building my "bare metal" test case, the issue disappears... I can send you the code if you wish (but as I said, it does not demonstrate the issue that I am having since this issue has vanished). I have to investigate which layer of my application is causing me this issue...But to comfort the other people looking over your shoulder, let me just say that the following code (which demonstrates the gist of it) appears to work fine:// Set-up ConnectionPool...dataSource = initTomcatConnectionPool ( );// Get a connection from the Pool// TODO: Put your breakpoint here to examine the connection that is returned from the Tomcat pool...connection = borrowConnection ( );// TODO: This is where I am having problem casting the above variable into a PgConnection or a BaseConnectionPgConnection pgConnection = connection.unwrap ( PgConnection.class );CopyManager cm = new CopyManager ( (BaseConnection) pgConnection );PGClassAndType pgClassAndType = new PGClassAndType ( );pgConnection.addDataType( pgClassAndType.getType ( ), PGClassAndType.class );PreparedStatement stmt = pgConnection.prepareStatement ( "SELECT 1" );FYI, this is the borrowConnection () code:public static Connection borrowConnection ( ) throws SQLException, InterruptedException {Connection conn = ( dataSource == null ) ? null : dataSource.getConnection ( );return conn;}and dataSource is defined as follows:import org.apache.tomcat.jdbc.pool.DataSource;private static DataSource dataSource = null;Thanks anyway for making me delve into my own code... I think I have made some progress at least telling me there should be a way to make this work!Regards,EricOn Tue, May 17, 2016 at 5:06 PM, Dave Cramer <pg@fastcrypt.com> wrote:Eric,Yes, a bit. Can you create a self contained test using DBCP and I'll try to find some time to look at it?DaveOn 17 May 2016 at 17:04, Eric Neron <eneron@e-djuster.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 theorg.apache.tomcat.jdbc.pool.DataSourceand this one (again according to the Doc "The JDBC Connection Poolorg.apache.tomcat.jdbc.poolis a replacement or an alternative to the Apache Commons 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.postgresql.jdbc.PgConnection@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
- org.postgresql.jdbc.PgConnection
- All Implemented Interfaces:
- AutoCloseable, Connection, Wrapper, BaseConnection, PGConnection
- 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@fastcrypt.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.On 17 May 2016 at 14:40, Eric Neron <eneron@e-djuster.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.jarpostgresql-9.4.1208.jre7.jarAlthough 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 anorg.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 theorg.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?
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.On 17 May 2016 at 14:40, Eric Neron <eneron@e-djuster.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.jarpostgresql-9.4.1208.jre7.jarAlthough 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 anorg.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 theorg.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?
This appears to be another incident of a report received a couple of weeks ago...
David J.
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.On 17 May 2016 at 14:40, Eric Neron <eneron@e-djuster.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.jarpostgresql-9.4.1208.jre7.jarAlthough 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 anorg.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 theorg.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?
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 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.postgresql.jdbc.PgConnection@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
- org.postgresql.jdbc.PgConnection
- All Implemented Interfaces:
- AutoCloseable, Connection, Wrapper, BaseConnection, PGConnection
- 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@fastcrypt.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.On 17 May 2016 at 14:40, Eric Neron <eneron@e-djuster.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.jarpostgresql-9.4.1208.jre7.jarAlthough 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 anorg.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 theorg.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?
Eric,
Yes, a bit. Can you create a self contained test using DBCP and I'll try to find some time to look at it?
Dave
On 17 May 2016 at 17:04, Eric Neron <eneron@e-djuster.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 theorg.apache.tomcat.jdbc.pool.DataSourceand this one (again according to the Doc "The JDBC Connection Poolorg.apache.tomcat.jdbc.poolis a replacement or an alternative to the Apache Commons 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.postgresql.jdbc.PgConnection@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
- org.postgresql.jdbc.PgConnection
- All Implemented Interfaces:
- AutoCloseable, Connection, Wrapper, BaseConnection, PGConnection
- 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@fastcrypt.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.On 17 May 2016 at 14:40, Eric Neron <eneron@e-djuster.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.jarpostgresql-9.4.1208.jre7.jarAlthough 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 anorg.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 theorg.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?
https://github.com/davecramer/testunwrap for a test that works with DBCP
On 17 May 2016 at 17:04, Eric Neron <eneron@e-djuster.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 theorg.apache.tomcat.jdbc.pool.DataSourceand this one (again according to the Doc "The JDBC Connection Poolorg.apache.tomcat.jdbc.poolis a replacement or an alternative to the Apache Commons 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.postgresql.jdbc.PgConnection@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
- org.postgresql.jdbc.PgConnection
- All Implemented Interfaces:
- AutoCloseable, Connection, Wrapper, BaseConnection, PGConnection
- 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@fastcrypt.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.On 17 May 2016 at 14:40, Eric Neron <eneron@e-djuster.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.jarpostgresql-9.4.1208.jre7.jarAlthough 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 anorg.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 theorg.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?
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 anorg.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:
If you get this I'm not sure where the issue is...
org.postgresql.jdbc.PgConnection implements org.postgresql.core.BaseConnect which extends
org.postgresql.PGConnection which is the interface that you desire.
pgConnection.unwrap ( PGConnection.class );
Not sure...
David J.
Hi Dave,
Strangely, when building my "bare metal" test case, the issue disappears... I can send you the code if you wish (but as I said, it does not demonstrate the issue that I am having since this issue has vanished). I have to investigate which layer of my application is causing me this issue...
But to comfort the other people looking over your shoulder, let me just say that the following code (which demonstrates the gist of it) appears to work fine:
// Set-up ConnectionPool...
dataSource = initTomcatConnectionPool ( );
// Get a connection from the Pool
// TODO: Put your breakpoint here to examine the connection that is returned from the Tomcat pool...
connection = borrowConnection ( );
// TODO: This is where I am having problem casting the above variable into a PgConnection or a BaseConnection
PgConnection pgConnection = connection.unwrap ( PgConnection.class );
CopyManager cm = new CopyManager ( (BaseConnection) pgConnection );
PGClassAndType pgClassAndType = new PGClassAndType ( );
pgConnection.addDataType( pgClassAndType.getType ( ), PGClassAndType.class );
PreparedStatement stmt = pgConnection.prepareStatement ( "SELECT 1" );
FYI, this is the borrowConnection () code:
public static Connection borrowConnection ( ) throws SQLException, InterruptedException {
Connection conn = ( dataSource == null ) ? null : dataSource.getConnection ( );
return conn;
}
and dataSource is defined as follows:
import org.apache.tomcat.jdbc.pool.DataSource;
private static DataSource dataSource = null;
Thanks anyway for making me delve into my own code... I think I have made some progress at least telling me there should be a way to make this work!
Regards,
Eric
On Tue, May 17, 2016 at 5:06 PM, Dave Cramer <pg@fastcrypt.com> wrote:
Eric,Yes, a bit. Can you create a self contained test using DBCP and I'll try to find some time to look at it?DaveOn 17 May 2016 at 17:04, Eric Neron <eneron@e-djuster.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 theorg.apache.tomcat.jdbc.pool.DataSourceand this one (again according to the Doc "The JDBC Connection Poolorg.apache.tomcat.jdbc.poolis a replacement or an alternative to the Apache Commons 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.postgresql.jdbc.PgConnection@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
- org.postgresql.jdbc.PgConnection
- All Implemented Interfaces:
- AutoCloseable, Connection, Wrapper, BaseConnection, PGConnection
- 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@fastcrypt.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.On 17 May 2016 at 14:40, Eric Neron <eneron@e-djuster.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.jarpostgresql-9.4.1208.jre7.jarAlthough 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 anorg.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 theorg.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?
Ya I think tc does something unique like instantiate the concrete class
On May 17, 2016 6:16 PM, "Eric Neron" <eneron@e-djuster.ca> wrote:
Hi Dave,Strangely, when building my "bare metal" test case, the issue disappears... I can send you the code if you wish (but as I said, it does not demonstrate the issue that I am having since this issue has vanished). I have to investigate which layer of my application is causing me this issue...But to comfort the other people looking over your shoulder, let me just say that the following code (which demonstrates the gist of it) appears to work fine:// Set-up ConnectionPool...dataSource = initTomcatConnectionPool ( );// Get a connection from the Pool// TODO: Put your breakpoint here to examine the connection that is returned from the Tomcat pool...connection = borrowConnection ( );// TODO: This is where I am having problem casting the above variable into a PgConnection or a BaseConnectionPgConnection pgConnection = connection.unwrap ( PgConnection.class );CopyManager cm = new CopyManager ( (BaseConnection) pgConnection );PGClassAndType pgClassAndType = new PGClassAndType ( );pgConnection.addDataType( pgClassAndType.getType ( ), PGClassAndType.class );PreparedStatement stmt = pgConnection.prepareStatement ( "SELECT 1" );FYI, this is the borrowConnection () code:public static Connection borrowConnection ( ) throws SQLException, InterruptedException {Connection conn = ( dataSource == null ) ? null : dataSource.getConnection ( );return conn;}and dataSource is defined as follows:import org.apache.tomcat.jdbc.pool.DataSource;private static DataSource dataSource = null;Thanks anyway for making me delve into my own code... I think I have made some progress at least telling me there should be a way to make this work!Regards,EricOn Tue, May 17, 2016 at 5:06 PM, Dave Cramer <pg@fastcrypt.com> wrote:Eric,Yes, a bit. Can you create a self contained test using DBCP and I'll try to find some time to look at it?DaveOn 17 May 2016 at 17:04, Eric Neron <eneron@e-djuster.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 theorg.apache.tomcat.jdbc.pool.DataSourceand this one (again according to the Doc "The JDBC Connection Poolorg.apache.tomcat.jdbc.poolis a replacement or an alternative to the Apache Commons 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.postgresql.jdbc.PgConnection@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
- org.postgresql.jdbc.PgConnection
- All Implemented Interfaces:
- AutoCloseable, Connection, Wrapper, BaseConnection, PGConnection
- 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@fastcrypt.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.On 17 May 2016 at 14:40, Eric Neron <eneron@e-djuster.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.jarpostgresql-9.4.1208.jre7.jarAlthough 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 anorg.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 theorg.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?
Note:
Vladimir
org.postgresql.jdbc.PgConnection, org.postgresql.core.BaseConnection, and org.postgresql.copy.CopyManager(BaseConnection connection) are NOT part of pgjdbc public API.
Those connection classes and CopyManager constructor should NOT be used in application code.
The proper interface is org.postgresql.PGConnection.
The proper way to get CopyManager is to call org.postgresql.PGConnection#getCopyAPI.
When using a connection pool, PGConnection pgConnection = connection.unwrap ( PGConnection.class ); should work.
Vladimir
Note:
Vladimir
org.postgresql.jdbc.PgConnection, org.postgresql.core.BaseConnection, and org.postgresql.copy.CopyManager(BaseConnection connection) are NOT part of pgjdbc public API.
Those connection classes and CopyManager constructor should NOT be used in application code.
The proper interface is org.postgresql.PGConnection.
The proper way to get CopyManager is to call org.postgresql.PGConnection#getCopyAPI.
When using a connection pool, PGConnection pgConnection = connection.unwrap ( PGConnection.class ); should work.
Vladimir