Close connection

[Tom Joiner]

Lets suppose I create a database connection...

   
    connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/database01", "Login", "password");
 
 
 

Now I have an open connection. Do I need to close it explicitly?

If I do not close it explicitly, will the database run out of its pool of connections?

Thanks for any info

[Gregg Bolinger]

Moving to the JDBC forum...

[Ulf Dittmer]

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.

[Tom Joiner]

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?

[Ulf Dittmer]

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.

[Tom Joiner]

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?

[Remko Strating]

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.

Here the code for closing the connection. The class has to implement the
HttpSessionBindingListener

public void valueUnbound(HttpSessionBindingEvent event) {
   
try {
con.close(); 
  } 
  catch (SQLException ex) { 
  Logfile.makeLog(className, " Closing a database connection has faild, because " + ex);  
  }
  } 
 
    

Until now I'm not a fan of connection pooling within Tomcat, because I've to set this which every new installation.

Now I can use a simple textfile for reading the different connection parameters which I and my customers can change easily.

Are there good advantages for connection pooling which will outweight this disadvantage.

[Ulf Dittmer]

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.