Path: csiph.com!aioe.org!.POSTED!not-for-mail
From: =?UTF-8?Q?Arne_Vajh=c3=b8j?= <arne@vajhoej.dk>
Newsgroups: comp.lang.java.programmer
Subject: Re: Not closing connections and statements
Date: Fri, 12 Aug 2016 09:15:37 -0400
Organization: Aioe.org NNTP Server
Lines: 76
Message-ID: <noki5t$1bkc$1@gioia.aioe.org>
References: <87shuakxjq.fsf@Equus.decebal.nl>
NNTP-Posting-Host: 7SK6yTFfqwlIPNBLl3l7Qg.user.gioia.aioe.org
Mime-Version: 1.0
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit
X-Complaints-To: abuse@aioe.org
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:45.0) Gecko/20100101 Thunderbird/45.2.0
X-Notice: Filtered by postfilter v. 0.8.2
Xref: csiph.com comp.lang.java.programmer:35270

On 8/12/2016 7:59 AM, Cecil Westerhof wrote:
> As I understood it you should close your SQL connections and
> statements before quitting a program. How bad is this when that does
> not happen?
>
> At the moment I am writing a general function to log memory usage to a
> SQLite database to be used by different applications. How bad would it
> be if a program would exit without cleanly closing the connection and
> statement?

Not closing the connection is a very bad practice.

In a long running program then it can cause your application/database
to run out of connections without needing to.

In a standalone program that exits quickly, then the practical
impact may be little, but it is a very bad habit.

And when people get that habit for standalone programs that exits
quickly then they sometimes carry it over to long running programs.

Don't do it!

Not closing statements is not so bad. I believe that somewhere
in the JDBC spec it promise that all statements associated with
a connection get closed when the connection is closed.

I still recommend closing statements though. Just in case
the JDBC driver implementor forgot this little quirk.

> The beginning of my code is at the moment:
>     public static boolean freeMemoryThread(final String className, final String database) {
>         final long                     divideValue  = 1024 * 1024;
>         final Runtime                  rt           = Runtime.getRuntime();
>         final ScheduledExecutorService ses          = Executors.newSingleThreadScheduledExecutor();
>
>         Connection  conn = null;
>         ResultSet   rs   = null;
>         Statement   stmt = null;
>
>         logMessage(className + ", " + database);
>         try {
>             Class.forName("org.sqlite.JDBC");
>             conn    = DriverManager.getConnection("jdbc:sqlite:" + database);
>             stmt    = conn.createStatement();
>             rs      = stmt.executeQuery("SELECT SQLITE_VERSION() AS SQLiteVersion;");
>             rs.next();
>             logMessage(String.format("Using SQLite %s", rs.getString("SQLiteVersion")));
>             rs      = null;
>         } catch (Exception e) {
>             logMessage("Could not connect with the database: " + e.getMessage());
>             return false;
>         }
>         ses.scheduleAtFixedRate(new Runnable() {

With a ScheduledExecutor then I assume that it may not be exiting
that quickly.

(but on the good side then SQLite is an embedded database, so the
resource leak may be less critical)

Anyway - why not just close?

And if you are on Java 1.7 or later then use the AutoCloseable
feature:

try(Connection con = DriverManager.getConnection(conurl)) {
     // use con and don't worry about closing
}

You do not even need to call close anywhere (normal flow,
exception flow).

Arne