Thread: COPY using Hibernate

Hi Vaibhav

C3p0 provides a mechanism to get at the underlying connection and
statement. search for c3P0 underlying connection

dave

On Fri, Jan 15, 2010 at 6:45 AM, Vaibhav Patil <infovaibhav@yahoo.com> wrote:
> Hello All,
>
> I am using C3p0 for connection pooling using hibernate. The connection
> object returned by that pool does not provide the COPY from STDIN
> functionality. Have also checked with the apache dbcp pooling mechanism. I
> am using JDBC4 driver with JDK1.6.
> As I understand, to support COPY operation of postgres using JDBC, the
> connection object should be an implementation of "PGConnection" interface.
>
> I am able to perform the COPY operation if I do not use any connection
> pooling.
>
> I am curious to know, if postgres provide some sort of connection pooling
> API which I can integrate in Hibernate and then use the COPY operation ?
>
> Thanks,
> Vaibhav.
>
> ________________________________
> The INTERNET now has a personality. YOURS! See your Yahoo! Homepage.

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

you can just check out the inner Connection with c3p0's reflective
API, if you want. the preachiness is a speedbump, but nothing prevents
you from returning RAW_CONNECTION_OBJECT. just be sure that when
you're done, the you've not left the state of the Connection modified,
cuz that can lead to subtle misbehavior.

~oo~
Steve Waldman
swaldman@mchange.com


On Jan 15, 2010, at 7:59 AM, Craig Ringer
<craig@postnewspapers.com.au> wrote:

> 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
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc

Steve Waldman wrote:
> you can just check out the inner Connection with c3p0's reflective API,
> if you want. the preachiness is a speedbump, but nothing prevents you
> from returning RAW_CONNECTION_OBJECT. just be sure that when you're
> done, the you've not left the state of the Connection modified, cuz that
> can lead to subtle misbehavior.

With Hibernate's Session object, sure - you can getConnection() from the
session, then reflectively access the methods of the underlying
PGConnection via C3P0. It's ugly but works.

I was about to say that you can't get the Connection object from
EntityManager if using Hibernate, when I realised you can call
EntityManager.getDelegate() to get the Session, then getConnection() on
that. I have no idea how I missed getDelegate() before.

Sigh. Guess you *can* just use C3P0's reflective calls even if using JPA.

--
Craig Ringer

Vaibhav Patil wrote:

> The other way I am trying is the pg/plsql stored procedure. As per the
> requirement, I have to insert around a million integers in a table
> having just one column. I am not aware of how to use "COPY FROM STDIN"
> using pg/plsql. I'll pass these integers as array to stored procedure. I
> want to avoid dependency of file, otherwise it could have been done
> easily using "COPY FROM file". Looking for more information on internet
> to solve the issue. If anybody aware of the way, please help me.

It's a real pity the JDBC driver doesn't support the COPY protocol so
you could just do this via PGConnection. There have been patches around
for ages ... though I presume there are good reasons why they haven't
been merged.

Do check to make sure that the stored proc approach is actually faster
than just doing batched multi-valued INSERTs. Open a transaction, then
prepare a statement like this:

INSERT INTO sometable VALUES (?),(?),(?),(?),(?);

(but with say 20 parameters - you'll need to play around and see how
many is optimal), then add a series of invocations of the prepared
statement to a JDBC batch insert/update and execute it.

I wouldn't be surprised if that was quite a bit faster than doing it
with an array and PL/PgSQL.

--
Craig Ringer

The driver supports COPY as of 8.4-701. I finally (after much too long
a delay; I apologize again) made good on my promise to clean up Kalle
Hallivuori's patch with Truviso's internal fixes, and worked with Kris
to get it accepted last spring. The final version came out in July.

It looks like it's not there in the main documentation, but it *is* in
the javadoc. PGConection.getCopyAPI() is the entry point.
---
Maciek Sakrejda | Software Engineer | Truviso

1065 E. Hillsdale Blvd., Suite 230
Foster City, CA 94404
(650) 242-3500 Main
(650) 242-3501 F
msakrejda@truviso.com
www.truviso.com



On Fri, Jan 15, 2010 at 8:31 AM, Craig Ringer
<craig@postnewspapers.com.au> wrote:
> Vaibhav Patil wrote:
>
>> The other way I am trying is the pg/plsql stored procedure. As per the
>> requirement, I have to insert around a million integers in a table
>> having just one column. I am not aware of how to use "COPY FROM STDIN"
>> using pg/plsql. I'll pass these integers as array to stored procedure. I
>> want to avoid dependency of file, otherwise it could have been done
>> easily using "COPY FROM file". Looking for more information on internet
>> to solve the issue. If anybody aware of the way, please help me.
>
> It's a real pity the JDBC driver doesn't support the COPY protocol so
> you could just do this via PGConnection. There have been patches around
> for ages ... though I presume there are good reasons why they haven't
> been merged.
>
> Do check to make sure that the stored proc approach is actually faster
> than just doing batched multi-valued INSERTs. Open a transaction, then
> prepare a statement like this:
>
> INSERT INTO sometable VALUES (?),(?),(?),(?),(?);
>
> (but with say 20 parameters - you'll need to play around and see how
> many is optimal), then add a series of invocations of the prepared
> statement to a JDBC batch insert/update and execute it.
>
> I wouldn't be surprised if that was quite a bit faster than doing it
> with an array and PL/PgSQL.
>
> --
> Craig Ringer
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>

Maciek Sakrejda wrote:
> The driver supports COPY as of 8.4-701. I finally (after much too long
> a delay; I apologize again) made good on my promise to clean up Kalle
> Hallivuori's patch with Truviso's internal fixes, and worked with Kris
> to get it accepted last spring. The final version came out in July.

Thanks for that - that's absolutely wonderful to have.

--
Craig Ringer