• 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

db connection

 
Ranch Hand
Posts: 116
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
hi all,
was wondering whats the best practise when opening and closing db connections from a servlet ? initialise the connection in init() and close it in destroy() ?? Am using glassfish v2.1 app server and was also wondering if i should leave the connection pooling to the app server ??


many thanks in advance.
 
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Best practice is to NOT handle the connections in the servlets. Either use container-managed connection pooling, or let an ORM tool handle the connections.
 
nikil shar
Ranch Hand
Posts: 116
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
thanks for the reply. i have set up a connection pool in the app server and am looking it up in my servlet by using its JNDI name i.e



I wanted to know if its a good idea to establish connection

1) only when its needed. in my case it will be in the doPost(); so the container is responsible for establishing and closing connections.

OR

2) to open and hold a connection i.e in the init() (in order to save time asking the container to establish db connection everytime its needed). And disconnect in destroy() ??


thanks again.

 
Bear Bibeault
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Only when its needed. Otherwise you are holding open a connection needlessly for the duration of the servlet lifetime (which is likely the lifetime of the application itself). Why bother to use connection pooling if you're never going to return the connection to the pool?
 
Ranch Hand
Posts: 3389
Mac MySQL Database Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Overall, it is always best to use a utility class (call it as ConnectionUtil or DBUtil) and let it do the stuff. You just call it from your servlet.

The time duration to which the connection is kept open should still be minimized. Say for example you make a call to the utility/helper from your servlet and that method takes care of opening and closing the connections as and when it is done with the job! This would be ideal and better choice!
 
nikil shar
Ranch Hand
Posts: 116
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
i like the idea of a utilities class. Also do you know if i need to synchronise my prepared statements i.e




or does the servlet container handle that as well ??
 
Raghavan Muthu
Ranch Hand
Posts: 3389
Mac MySQL Database Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I think you are very confused.

Why do you synchronize the prepared statement? It is NOT at all necessary until you are going to use ONLY ONE prepared statement for the entire application and it is going to be shared by many threads!
 
nikil shar
Ranch Hand
Posts: 116
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
i was planning on using the same prepared statement to handle all db queries from my servlet. so have delcared a



and was planning on using 'ps' for this particualr servlet for all db queries.


ta.
 
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
What is the purpose of declaring 'ps' as private ?

I would suggest to delcare it as local variable inside doPost() or doGet()
 
nikil shar
Ranch Hand
Posts: 116
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
the only reason i declared it private is so i could reuse it at different places within the servlet since i am making multiple db queries. Please correct me if this doesnt sound like the right way to go.

ta.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic