How to overcome "java.sql.SQLException: Too many connections" exception?

Question

I have a simple web application written in java which has servlets accessing the Mysql Database to generate reports. The report generation happens very frequently.

I am using the apache commons DBCP to get connections to the DB. I also close the connection explicity in the finally block always. But i do not explicitly close the Statements and ResultSets i create.

I'm forced to restart the tomcat instance everytime i get the Exception which says "java.sql.SQLException: Too many connections".

How do i overcome this.... do i need to increase the maxconnections in Mysql ?.

Any help is appreciated.

Answer 1

Use connection pooling and always close (return to pool) the connection once work is done.

http://dev.mysql.com/tech-resources/articles/connection_pooling_with_connectorj.html

Answer 2

• Yes you should close your Statements / ResultSets - These will typically reference the raw JDBC Connection rather than the PooledConnection proxy Connection returned by the DBCP PooledDataSource.
• Consider using C3P0 rather than DBCP.
• Consider using Spring to manage your JDBC operations and you will never have to worry about this kind of thing.