Re: COPY using Hibernate

---

From: Craig Ringer <craig@postnewspapers.com.au>
To: Dave Cramer <pg@fastcrypt.com>
Cc: Vaibhav Patil <infovaibhav@yahoo.com>; pgsql-jdbc@postgresql.org
Sent: Fri, 15 January, 2010 6:29:29 PM
Subject: Re: [JDBC] 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