It's good practice to close it if you no longer need it, since it consumes limited system resources (namely, a port). If the "connection" object goes out of scope and is garbage-collected, that will have the same effect -eventually- as closing the connection explicitly, but that's not good style at all.
In many cases you need the connection more than once, so you keep it around. That's especially the case with web applications, where many users may access it. Then the use of a connection pool would be indicated.
You'd only run out of DB connections if you open new connections all the time and keep the previous in scope somehow. But that would be a really bad implementation anyway.
Then the use of a connection pool would be indicated.
This I don't quit understand. Are you suggesting we create our own connection pool, independent of the connection pool the database runs for itself?
Is this not reinventing the wheel. Wouldn't it be better coding practice to open a connection when you want it, then close it, and let the database handle having a pool?
Joined: Mar 22, 2005
The database does not keep a connection pool - the application server (or web server) that connects to it does. So, no, this would not be duplication of something.
Joined: Sep 19, 2006
I am using Tomcat. Therefore, as I understand it, Tomcat keeps a connection pool. When I open a database connection, it is using this pool.
Now if I wanted to, I could create another connection pool- one that is owned by my application perhaps, in a servlet that keeps a set of connections. But this would be duplicating what Tomcat (I assume) already provides. Is this correct?
Joined: Dec 28, 2006
I'm creating connections to the database in which every session has it's own connection. Thereby I'm using the HttpSessionBindingListener for closing the connection if the session ends.
I do this, because I want to follow each user unique on the database en I've created a class for that, where I store his username for the different databases.
I am using Tomcat. Therefore, as I understand it, Tomcat keeps a connection pool.
Not automatically - only if you tell it do so, and how to do it. If you have done so, then indeed you wouldn't want to create another one. But you also wouldn't close the connections - you would return them to the pool after you're done with them.