db connection

[nikil shar]

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]

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]

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

      Context c = new InitialContext();
        Context envContext = (Context) c.lookup("java:comp/env");
        (DataSource) envContext.lookup("jdbc/myDB");

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]

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]

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]

i like the idea of a utilities class. Also do you know if i need to synchronise my prepared statements i.e

synchronized (ps){
   ps.execute("some sql");
}

or does the servlet container handle that as well ??

[Raghavan Muthu]

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]

i was planning on using the same prepared statement to handle all db queries from my servlet. so have delcared a

  private PreparedStatement ps = null;

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


ta.

[Saravanan Mahalingam]

What is the purpose of declaring 'ps' as private ?

I would suggest to delcare it as local variable inside doPost() or doGet()

[nikil shar]

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.