Path: csiph.com!eternal-september.org!feeder.eternal-september.org!news.eternal-september.org!.POSTED!not-for-mail
From: Cecil Westerhof <Cecil@decebal.nl>
Newsgroups: comp.lang.java.programmer
Subject: Re: Not closing a SQL(ite3) connection
Date: Fri, 11 Aug 2017 10:26:48 +0200
Organization: Decebal Computing
Lines: 103
Message-ID: <874lte1pdj.fsf@munus.decebal.nl>
References: <87a839um2q.fsf@munus.decebal.nl> <omg0la$1qe3$1@gioia.aioe.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 8bit
Injection-Info: mx02.eternal-september.org; posting-host="b5572a6689d7cc39c1ab3122a4c52947"; logging-data="18534"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX18M+6l4fcL8QFeiuOG2sXUFAyqt6ZsF3ig="
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/24.5 (gnu/linux)
Cancel-Lock: sha1:3UTWzrdrwTHnmf6Ng1CcYNhmUWM= sha1:LM7vO+3E3vUjPcHoXOMymEmvoWQ=
X-Homepage: http://www.decebal.nl/
Xref: csiph.com comp.lang.java.programmer:37131

Arne Vajhøj <arne@vajhoej.dk> writes:

> On 8/9/2017 5:28 AM, Cecil Westerhof wrote:
>> Normally I always close a connection when finished. But I now wrote my
>> own class SQLite3 to handle things in this class. It has its own
>> connection. Can there be a problem when a connection is not closed, or
>> is that not something to worry myself about?
>>
>> Would it be a good idea to write a function to close the database
>> connection?
>
> If the class implements the JDBC standard interfaces it will need
> a close method to compile.

That is a bit to much for me. ;-) It is just a wrapper around the
SQLite JDBC class.


> But even if it does not then I will recommend adding a close
> method *and* implement AutoCloseable.

I made the class AutoClosable. I use it during the whole run, but
maybe someone else will use it in a try with resources.


> It is the right way to handle database connections.
>
> In some contexts like SE app with embedded DB it is not likely
> to make a difference, but it is still a good to do things the right
> way. To maintain good habits. And to prevent disasters if the
> context suddenly change (that sometimes happens).

Completely my idea. I am often told: “That is not necessary.”, but in
my experience there is much more harm done by ‘not doing what is not
necessary’ as by ‘doing more as necessary’.


I have a strange problem. I made a class SQLite3ResultSet. There is a
lot of stuff to be removed, but that is just to find out if it is
doing what I think it is doing.

In this class I have:
    private ResultSet resultSet = null;
    private Statement stmt      = null;

The constructor is:
    public SQLite3ResultSet(Connection conn, String query) throws SQLite3Exception {
        System.out.println("SQLite3ResultSet: constructor.");
        try {
            stmt      = conn.createStatement();
            resultSet = stmt.executeQuery(query);
        } catch (SQLException e1) {
            String errorMsg = "";

            if (stmt != null) {
                try {
                    stmt.close();
                    stmt = null;
                } catch (SQLException e2) {
                    errorMsg += "Could not close Statement.";
                }
            }
            throw new SQLite3Exception(errorMsg +
                                       "Could not create a resultSet.");
        }
    }

And the close method is (for now, because I want to test it):
    @Override
    public void close() throws SQLite3Exception {
        System.out.println("SQLite3ResultSet: closing open resources.");
        String    errorMsg  = "";

        try {
            stmt.close();
            resultSet.next();
            resultSet.close();
        } catch (SQLException e) {
            errorMsg += "Could not close ResultSet.\n";
            System.out.println(errorMsg);
        }
        System.out.println("Closed resultSet.");
        try {
            stmt.close();
        } catch (SQLException e) {
            errorMsg += "Could not close Statement belonging to ResultSet.\n";
            System.out.println(errorMsg);
        }
        System.out.println("closed stmt");
        System.out.println("SQLite3ResultSet: closed open resources.");
        if (!errorMsg.isEmpty()) {
            throw new SQLite3Exception(errorMsg);
        }
    }

In the first try I close the stmt and then I do a resultSet.next(). I
would expect an exception, because I closed the stmt, but I do not get
an exception. Why not?

-- 
Cecil Westerhof
Senior Software Engineer
LinkedIn: http://www.linkedin.com/in/cecilwesterhof