Is it a good programming practice to constantly connect to an sql database?

Question

so i have a web app in jsp, and is it good/bad to connect to the db on page load and disconnect on exit?

Answer 1

I concur with Chris G. Even if you're fetching multiple rows, you want to do that in one connection, and fetch the rows to an array, which you would then loop through to display your data.

Databases can handle lots of open connections, but it's always best for performance to close the connection when you're through with it.

Answer 2

I don't know about jsp but I know in php if you do database connections after the first connection it makes no difference until you disconnect. I bet it's the same thing with jsp so it's no big deal but you might need to re-think your approach and write more object orientated and organised, logical code.

Yes, it's normal to do different queries in other methods, even if you attempt to make a connection for each one, but most people open the connection in the constructor of the class right at the beginning, only once for the class.

Answer 3

You should only open connections to the DB on page load and disconnect once your data has been retrieved/changed. This ensures that your database does not run out of resources to service all the connections to it and eventually slow down performance.

Answer 4

I would think that best practice is to connect to the database only when you need to connect to a database.

Write a class or function which allows you to easily connect to a database, so when you want to pull data you can open the connection -> get the data -> and close the connection

Feel free, anyone, to correct me if I am wrong.