Skip site navigation (1) Skip section navigation (2)

Re: COPY using Hibernate

From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
To: Dave Cramer <pg(at)fastcrypt(dot)com>
Cc: Vaibhav Patil <infovaibhav(at)yahoo(dot)com>, pgsql-jdbc(at)postgresql(dot)org
Subject: Re: COPY using Hibernate
Date: 2010-01-15 12:59:29
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-jdbc
On 15/01/2010 8:00 PM, Dave Cramer wrote:
> Hi Vaibhav
> C3p0 provides a mechanism to get at the underlying connection and
> statement. search for c3P0 underlying connection

According to the C3P0 docs:

"JDBC drivers sometimes define vendor-specific, non-standard API on 
Connection and Statement implementations. C3P0 wraps these Objects 
behind a proxies, so you cannot cast C3P0-returned Connections or 
Statements to the vendor-specific implementation classes. C3P0 does not 
provide any means of accessing the raw Connections and Statements 
directly, because C3P0 needs to keep track of Statements and ResultSets 
created in order to prevent resource leaks and pool corruption."

... so, you can't just get the connection from your Hibernate Session 
and use that. Instead, you have to do it reflectively via C3P0 methods:

"C3P0 does provide an API that allows you to invoke non-standard methods
reflectively on an underlying Connection. To use it, first cast the
returned Connection to a C3P0ProxyConnection. Then call the method
rawConnectionOperation, supplying the java.lang.reflect.Method object
for the non-standard method you wish to call as an argument. The Method
you supply will be invoked on the target you provide on the second
argument (null for static methods), and using the arguments you supply
in the third argument to that function. For the target, and for any of
the method arguments, you can supply the special token
C3P0ProxyConnection.RAW_CONNECTION, which will be replaced with the
underlying vendor-specific Connection object before the Method is invoked."


Permit me to say "argh!". It's highly frustrating that you can't just 
"check out" a connection, unwrapping it and taking responsibility for 
any statements and result sets you create while it's unwrapped.

( C3P0's documentation is really preachy about this, and likes to
   explain to you how you shouldn't want to do "legacy" things like
   that since it breaks "database independence" which is apparently
   something it's unthinkable not to care about for your particular
   app ... sigh. )

In my J2SE app I only need one connection for the app - and in fact it's 
strongly preferable to limit the app to one connection. I also needed 
direct access to that connection to use listen/notify via PgConnection. 
Hibernate wants you to use a connection pool, and jealously guards the 
connections it obtains via the pool - in fact, if you're using Hibernate 
via JPA2 you can't access the underlying JDBC connection *at* *all*.

Thankfully Hibernate provides a clean and simple abstraction for its 
access to connection pools, so I landed up writing my own 
SingleConnectionProvider to give Hibernate its "pool" of one connection. 
The provider blocks on any getConnection(...) requests issued while the 
connection is checked out to someone else, so I can just check the 
connection out of the pool directly if I want to do PostgreSQL-specific 
things with it ( like using listen/notify or COPY ) and use Hibernate 
the rest of the time. It works great. If it'd be of any use to you, let 
me know.

Craig Ringer

In response to


pgsql-jdbc by date

Next:From: Steve WaldmanDate: 2010-01-15 14:26:21
Subject: Re: COPY using Hibernate
Previous:From: Dave CramerDate: 2010-01-15 12:00:44
Subject: Re: COPY using Hibernate

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group