When to close connection in JDBC/SQL

Question

I have a program that uses an SQL database and I had a question regarding when to close the connection of the program and the database. Should I connect at the beginning of a method that uses the database and then close the connection at the end of that method? Or should I keep the connection going until the user closes the program?

I recommend you `close` the `Connection` with a [`try-with-resources` statement](http://docs.oracle.com/javase/tutorial/essential/exceptions/tryResourceClose.html) (which is to say `close` it in the method that opens it), and also that you use a [Connection Pool](http://docs.oracle.com/javase/jndi/tutorial/ldap/connect/pool.html). — Elliott Frisch, Jun 12 '15 at 05:04
Depends on whether your connection is local variable or global variable. Sometimes you might want to keep it open across methods . — Anindya Dutta, Jun 12 '15 at 05:04

score 2 · Answer 1 · edited May 23 '17 at 12:01

2

You should never keep your connection open. Instead open it whenever required and close immediately after its use.

Normally we prefer this

// declare reference to connection, statement, recordset
try {
 // open connection, statement, recordset, execute queries
} finally {
    // close recordset, statement, connection.
}

Note: You can refer this blog by @BalusC, it is really good stuff to start with.

edited May 23 '17 at 12:01

Community

1
1

answered Jun 12 '15 at 05:02

Naman Gala

4,670
1
21
55

@noobProgrammer, at least have look at the link of blog. – Naman Gala Jun 12 '15 at 05:17
2

Better yet: use try-with-resources. – Mark Rotteveel Jun 12 '15 at 10:45
@MarkRotteveel, can we log the exception thrown while auto closing the connection/statement in try-with-resources? – Naman Gala Jun 12 '15 at 11:04
Yes, just add a catch-block, see the Java tutorial [The try-with-resources Statement](http://docs.oracle.com/javase/tutorial/essential/exceptions/tryResourceClose.html): _"In a try-with-resources statement, any catch or finally block is run after the resources declared have been closed."_ If the try-block throws an exception, then any exceptions of the resource close is 'suppressed'. So if there is an exception in the block and on close, then you need to call `getSuppressed` on the exception to log exceptions from the close. – Mark Rotteveel Jun 12 '15 at 11:12
@MarkRotteveel, thanks for the information about it. Will surely try it. – Naman Gala Jun 12 '15 at 11:27

score 1 · Accepted Answer · answered Jun 12 '15 at 05:08

The 1st approach is correct, It's good programming practice to explicitly close things that you open once you've finished with them.

Generally you should Close the connection in a finally block. All other JDBC resource depend on this so are freed up implicitly...

When to close connection in JDBC/SQL

2 Answers2