Re: COPY using Hibernate

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

See:
http://www.mchange.com/projects/c3p0/index.html#raw_connection_ops

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