How many database connections I can open in jsp/servlet or in console application?

Question

I am developing software For SMS Gateway that: - do many select, insert, update and delete statements like more than one million hit on database every day. - I have some difficulties in managing resources like DB connection, so I do not know where to close the connection to minimize the number of created connections. - I use Driver Manager. - When I keep the connection unclosed I get memory leak in tomcat. - In the console app there are threads using DAOs and the have static connection so I do not close it.

1) Is it good idea not to close the connection of DB after the query done, and leave it open?

2) I have servlet running to receive SMS on http connection and insert them in DB, and I expect thousands of http connections on this servlet (maybe per minutes), shall I close and create DB connection for every request for the servlet?

3) what is the best practice for using DB Connections in this case?

Answer 1

Is it good idea not to close the connection of DB after the query done, and leave it open?

It is a good idea to use a connection pool, acquire a connection from it when you need it, and close it to release it back to the pool as soon as possible.

I have threads using static connection, so is it possible if I closed the connection the other threads gets exception?

Definitely. This is worst practice. Don't do it. See above.

I have servlet running to receive SMS on http connection and insert them in DB, and I expect thousands of http connections on this servlet (maybe per minutes), shall I close and create DB connection for every request for the servlet?

Yes, but via a connection pool, see above.

what is the best practice for using DB Connections in this case?

See above.

As for the question in your title, it depends entirely on the database configuration.