How many times should I close the connection In SQL?

Question

I have lots of method in my database class that all of them have one statement, when should I close the connection? In each method or at the end of database class?

Answer 1

You should close the connection when you are finished your transaction. Since we don't know the contents of the class or its usage, it's impossible to tell when your access of the connection begins or ends.

Actually, that may not even be true if the connection is dedicated for a specific usage and not in a pool. You may want to keep it open for the duration of your application.

Answer 2

As a best practice, you should close the connection in the logical place after you are done - right after all of database activity for that task is done.

Answer 3

Generally, you should close a connection in the same method that opens it. Closing and opening connections isn't an arduous task, since modern DB servers keep even closed connections on "hot standby", so they are quickly accessed through a connection pool. Leaving them open though...that can get you in trouble and can be a nightmare to debug.

Answer 4

It depends when and how repeatedly you are using these methods. If they are sequential you should close the connection only in the end, instead of open and close often

Answer 5

This largely depends on what your Database class does. If your methods are called individually at various times then the methods should be responsible for opening and closing the connection. However, if the class does some kind of big processing operation that calls many methods then you may want to open and close the connection outside of the individual methods.

The most important thing is that wherever you open the connection, you also close the connection. Otherwise you get into the business of making assumptions about the state of the connection which can get you into trouble.

Answer 6

We've found that the best policy is to get a connection from the connection pool, execute a single transaction, and then put the connection back into the pool immediately. This way you don't have a connection being held onto for long blocks of logic, thus preventing other threads from using it - which is an issue for scalability.

Answer 7

Close the connection (and statement and resultset!) in the same method block as you've acquired it, in the finally block of the try block where they are opened.

The general idiom is:

public void doSomething() throws SQLException {
    Connection connection = null;
    try {
        connection = database.getConnection();
    } finally {
        if (connection != null) try { connection.close(); } catch (SQLException logOrIgnore) {}
    }
}

Closing of connection, statement and resultset should happen in reversed order as they're opened.

If your actual concern is performance, then consider using a connection pool to improve connecting performance, for example C3P0. This by the way does not change the general idiom. Just continue writing the same idiom, the connection pooling implementation will worry itself further under the hoods.

Also see this article for more practices/examples.

Answer 8

Always close the connection when you finish executing your transaction. It is a good practice to obtain and close your connection in the same method. If you have separate transactions that are tightly coupled you might execute them with the same connection, but for best practices, I try to execute one transaction per connection.

Answer 9

If it is an option, don't explicitly mess with connections at all. Use a full blown orm framework like hibernate or use something significantly more lightweight like spring jdbc templates.

Answer 10

use lombok and it will handle both try/catch and conn.close() for you

public void doSomething() throws SQLException {

@Cleanup Connection connection = database.getConnection();
}

lombok

Answer 11

It depends somewhat on the context your application operates in. If it's a web app you need to be careful to open your connection, do whatever work is needed, and close the connection quickly. However, in a C/S or batch environment it may be better to acquire the connection and hold onto it as long as the user is interacting "frequently" (for whatever value of "frequently" you choose), especially if the user has expectations of rapid response time and it's expensive (in terms of time or resources) to acquire the connection to your particular variety of database.

I like to set a timer every time the user has the app go to the database. If/when the timer expires, close the connection, then re-open the next time he/she/it wants to hit the database again. Timer expiration can be somewhere between 1 and 20 minutes. Just so long as it's less than the database's "inactivity disconnect" time.

Share and enjoy.