How often should we create Connection Object?

[Alamu Vinai]

Is it efficient to create Connection Object for each servlet(for retreiving, updating, deleting data to DB)? Or to store the connection Object in Session variable and use it throughout the session? How you all do in coding?
Any feedback is appreciated.

[David O'Meara]

Actually, neither.
You are correct in saying that opening a new connection for each operation is inefficient, but if there was only one connection per servlet it would tend to choke the performance of busy servlets (that get hit more often).
Likewise, creating one for each session ends up assigning resources which largely remain idle (and is therefore a waste) and limits the scalability of the app. (if you want to design for 100 concurrent users, how many databases can be expected to handle this?)
The solution is that many (?) J2EE complient Application servers provide a connection pool. When the server starts up, it creates a set of connections that it maintains separate from the servlets. Each servlet can then ask for a connection from a common pool. If an Application server doesn't provide Connection Pooling it isn't too difficult to implement a simple version yourself.
Dave.

[Sophia Oscario]

I use Pool Manager to handle that ....
U can get it from http://www.codestudio.com/PoolMan/index.shtml

------------------
** Science belongs to the world **

[Jeroen Wenting]

Good reasons not to store Connections in the session:
- most database engines are limited to a fixed number of connections by their license. If a lot of sessions are open, this number gets exceeded and the server may even lock up. At the very least it will disallow future connections until some are released. When that happens depends on the session timeout interval, the working of the garbage collector as well as the connection timeout interval on the server.
- If the connection timeout interval on the server is set to a low value, it may be that the next database operation on the stored connection encounters a problem if the connection was timed out.
- Waste of resources. Storing things in the session takes up RAM as well as CPU cycles.

[Abdul Kalam]

Hi all,
continuing with the discussion i would like to add few more things
1.Session object take up resource at the server
2.If the server decides to passivate some of the objects in session bcoz the server can maintain only certain session then as the connection object is not serializable hence if it is passivated by the server then it cannot be retrieved back in the same state.
bye