• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

db connection

 
nikil shar
Ranch Hand
Posts: 116
  • Mark post as helpful
  • send pies
  • 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.
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 64720
86
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • 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
  • 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
Author and ninkuma
Marshal
Pie
Posts: 64720
86
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • 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?
 
Raghavan Muthu
Ranch Hand
Posts: 3381
Mac MySQL Database Tomcat Server
  • Mark post as helpful
  • send pies
  • 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
  • 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: 3381
Mac MySQL Database Tomcat Server
  • Mark post as helpful
  • send pies
  • 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
  • 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.
 
Saravanan Mahalingam
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • 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
  • 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.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic