Pooling PreparedStatements

[Brzezi]

Hi.

I`m programing an application, which uses database (Postgresql), it`s
multithread, there is no problem yet

it`s multithread, so I`v decided to use connection`s pooling, I had been
doing it before end there is no problem too, it works well

but problem appeared because I want to use PreparedStatements to execute
queries
for pooling connections I`m using org.apache.commons.dbcp, in its
configuration (BasicDataSource) is something about pooling prepared
statements, but how to use it?

when I want to gain Connection from pool I do:
dataSource.getConnection()

but how to do it for PreparedStatement, I mean how to gain
PreparedStatement from pool?
is preparedStatement.execute*() doing it itself? and I shouldn`t matter
about it no more?

If it is possible, please for a piece of source/example how I should do
it, because I`v not found anything useful on dbcp project website nor
google :/

Thanks in advance for any helpful idea.


Pozdrawiam
Brzezi

 

[jonck]

Thanks in advance for any helpful idea.

Check out c3p0, a much easier to use connection pooling library than
dbcp with nice documentation. I have spent many futile hours with dbcp
in the past, switched to c3p0 and never looked back.

The project can be found here: http://sourceforge.net/projects/c3p0

 

[Adam Maass]

Hi.

I`m programing an application, which uses database (Postgresql), it`s
multithread, there is no problem yet

it`s multithread, so I`v decided to use connection`s pooling, I had been
doing it before end there is no problem too, it works well

but problem appeared because I want to use PreparedStatements to execute
queries
for pooling connections I`m using org.apache.commons.dbcp, in its
configuration (BasicDataSource) is something about pooling prepared
statements, but how to use it?

BasicDataSource bsd = ...
bsd.setPoolPreparedStatements(true);

But you may want a PoolingDataSource, not a BasicDataSource.

when I want to gain Connection from pool I do:
dataSource.getConnection()

but how to do it for PreparedStatement, I mean how to gain
PreparedStatement from pool?
is preparedStatement.execute*() doing it itself? and I shouldn`t matter
about it no more?

Precisely. Now a Connection.prepareStatement() actually fetches from the
pool if already created.


Some databases do not react well to pooling PreparedStatements. YMMV.


-- Adam Maass

 

[JScoobyCed]

Check out c3p0, a much easier to use connection pooling library than
dbcp with nice documentation. I have spent many futile hours with dbcp
in the past, switched to c3p0 and never looked back.

The project can be found here: http://sourceforge.net/projects/c3p0

Well, following your advice, I have used c3p0 in my benchmark plan. I
have a few comments:
1. c3p0 is not easier to use than Apache dbcp, I found it equally easy
2. execution time, memory usage, cpu: no differences between the two
libraries

I used JNDI connection in my server.xml (Tomcat).
Maybe I missed something...

 

[jonck]

I used JNDI connection in my server.xml (Tomcat).

Maybe I missed something...

The point is that C3p0 does not require JNDI, which for me was what was
the main struggle I was having with DBCP (could be that your troubles
lie elsewhere, but once I got the JNDI stuff working DBCP gave me no
more problems). Now the JNDI stuff is only difficult if you have a
standalone application (from your post I gathered that this is what
you're doing). If you're using Tomcat then DBCP works quite easy, since
Tomcat does the JNDI stuff for you.

Should you want to walk the JNDI path, a kind soul has helped me to get
this to work in the past. See
http://groups-beta.google.com/group...m/thread/6708485d8ca23fe0/d922a402dd4222b4?q=
for instructions.

Kind regards, Jonck

 

[JScoobyCed]

The point is that C3p0 does not require JNDI, which for me was what was
the main struggle I was having with DBCP (could be that your troubles
lie elsewhere, but once I got the JNDI stuff working DBCP gave me no
more problems). Now the JNDI stuff is only difficult if you have a
standalone application (from your post I gathered that this is what
you're doing). If you're using Tomcat then DBCP works quite easy, since
Tomcat does the JNDI stuff for you.

Should you want to walk the JNDI path, a kind soul has helped me to get
this to work in the past. See
http://groups-beta.google.com/group...m/thread/6708485d8ca23fe0/d922a402dd4222b4?q=
for instructions.

Kind regards, Jonck

Thanks for the reply.
I'm not sure to get what you say in:
"Now the JNDI stuff is only difficult if you have a standalone
application (from your post I gathered that this is what you're doing)"

I am working on a web application, using Tomcat as application server.

Now, I agree that if you are going to do a standalone application, it
might be more complex with DBCP than c3p0

Thanks