• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Proper way to use a connection pool

 
Ranch Hand
Posts: 282
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
What is the proper way to use a database connection pool? My understanding is that, when I wish to execute an SQL statement, I should 1) request a Connection from the pool, 2) execute the SQL, and 3) return the Connection to the pool.

Some of my fellow developers, however, believe that when a user request is received, a Connection should be obtained at the beginning of the request and held onto until the request is completed.

I understand that, if I obtain connections as described in the first paragraph, I may have to request multiple connections during a single request. However, I believe that obtaining a single Connection for the duration of a request is wasteful, particularly for our poorly written application, which often takes several seconds to complete a single page request.

Comments please.
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Jay,
You are correct. The connection pool is responsible for managing the connections. And it can't do its job if developers don't return the connections as soon as possible.

The overhead of returning a connection to the pool is extremely low. So there is no harm in doing that. By comparison, not returning connections to the pool diminishes the number of connections available to other clients.
 
Ranch Hand
Posts: 118
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I think there are a lots of questions related to Connection Pooling floating about. I want to know whether there is any write up on this. I have googled and found only how to Create a connection pool. . But never have found how to get connection from a Pool and when to release it, Whats the best and worst practice, and things like that.
 
reply
    Bookmark Topic Watch Topic
  • New Topic