Pooling PreparedStatements

Discussion in 'Java' started by Brzezi, Jun 13, 2005.

  1. Brzezi

    Brzezi Guest

    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
    --
    [ E-mail: ][ MP3Blaster is playing now: ]
    [ Ekg: #3781111 ][ Title: Happy Home, Artist: 2Pac ]
    [ LinuxUser: #249916 ][ Album: Until The End Of Time, Year: 2001 ]
    [ Bitrate: 192kbit/s, Length: 3:56 ]
     
    Brzezi, Jun 13, 2005
    #1
    1. Advertising

  2. Brzezi

    jonck Guest

    > 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
     
    jonck, Jun 14, 2005
    #2
    1. Advertising

  3. Brzezi

    Adam Maass Guest

    "Brzezi" <> wrote :
    > 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
     
    Adam Maass, Jun 14, 2005
    #3
  4. Brzezi

    JScoobyCed Guest

    jonck wrote:
    >>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
    >


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

    --
    JSC
     
    JScoobyCed, Jun 14, 2005
    #4
  5. Brzezi

    jonck Guest

    > 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
     
    jonck, Jun 14, 2005
    #5
  6. Brzezi

    JScoobyCed Guest

    jonck wrote:
    >>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
    >


    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

    --
    JSC
     
    JScoobyCed, Jun 15, 2005
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Ike
    Replies:
    1
    Views:
    340
    FundablePlans.com
    Sep 23, 2003
  2. Robert Brown
    Replies:
    7
    Views:
    7,525
    Yakov
    Sep 21, 2004
  3. Jaba
    Replies:
    0
    Views:
    512
  4. Replies:
    23
    Views:
    1,007
    Are Nybakk
    Nov 14, 2007
  5. nico
    Replies:
    0
    Views:
    310
Loading...

Share This Page