Software Engineering Stack Exchange is a question and answer site for professionals, academics, and students working within the systems development life cycle. It only takes a minute to sign up.
Sign up to join this community
Leave a open database connection throughout the execution of the aplication, or for each time a operation will be executed a new connection will be created?
Open throughout the execution: Open aplication -> Open connection; Operations -> use the opened connection -> commit; Close aplication -> close connection;
For each time a operation will be executed: Open aplication -> ...; Operations -> create connection, execute operation, commit, close connection; Close aplication -> ...;
The best practice for database connections is the same regardless of the language used.
You have a pool of open connections at the start of your application. You take a connection from the pool as late as possible. Return it to the pool as soon as possible.
Never hold a connection out of the pool unless it is being used. Don't have Connections as member variables in a class. I've seen lots of horrible data layer wrappers that do nothing more than duplicate the API interface while introducing unnecessary state with the Connection.
The API you program against may hide the pooling implementation. But you will still be responsible for upholding the take late, return soon rule.
You should code in a style that guarantees the connection is returned to the pool in the event of an exception. This could be achieved with try/finally constructs in Java. "using" blocks in C#. RAII in C++.
