Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Running out of database connections using JSP

 
Tomas Nilson
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hej!

I am running Tomcat and MySQL. I have some JSP pages which communicates with the database using Java Beans. The problem is that the database connections do not get closed...

So if I type "show processlist;" in MySQL I get a list of sleeping connections.. When the number reaches 100 I cannot use my site anymore as that is the maximum number of connections.

So the questions is: Is the problem that I don't close the connections that my beans create?

Every JSP that use a Java Bean creates a new connection... Should I use a connection pool instead or how is this normally solved?

Thanks!

Tom
 
Kareem Qureshi
Ranch Hand
Posts: 102
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Tom,
I think you can achieve it by
1. Closing the connections whenever you open them is good. Whereever you are openning a connection write a finally block and close the connections in that block so that the connections gets closed even if there is any exception.

for example

somemethod(){
Connection conn = null;
try{
...
conn = getConnection();//however you get the connection
...code
}
catch(SQLException e){
.... do what you need in case of exception
}
finally{
close the resultSet
close the Statement or PreparedStatement or Callable Statement
conn.close();
}
}

Hope this helps. You can also try connection pools

Thanks
Kareem
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Tom,
You should do both. A connection pool will make getting connections less expensive. You still need to call close() as Kareem described to tell the connection to return itself to the pool.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic