Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.java.programmer > #35278

Re: Not closing connections and statements

Show key headers only | View raw

On Friday 12 Aug 2016 21:01 CEST, Martin Gregorie wrote:

> On Fri, 12 Aug 2016 13:59:37 +0200, 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?
>>
> If you're going to use an SQL connection, it should be opened during
> initialisation and closed as one of the last actions of the process:
> opening and closing DB connections is generally pretty slow. Once
> opened, the connection should always be closed as one of the last
> actions in the process - putting the close() inside a finally{}
> block is a good idea.

That is the case. As long as the program is running I need the
database connection. Only when the program is terminated the database
connection is not used anymore. I show the code at the end of this
message.

As I understood it the execution of finally is not guaranteed.


> Apart from that, the amount of harm caused by failure to close the
> connection depends on two things: how you configure the connection
> and how the jdbc calls that access data in the database are handled:
>
> - if the database autocommits after every SQL statement and each
> JDBC call executes a single SQL statement, then failures can't do
> much damage to the database, but your program may end up quite
> limited on what you can do with the data.

That is what I use yes. I just write one record an hour to log the
memory usage of the application. But maybe I am going to log more in
the future.


> - if the database autocommits after every SQL statement and these
> all execute stored procedures rather than SQL DML statements, then
> you can do pretty much whatever you want with the data but you may
> find you're pretty much locked you into a particular flavour of
> RDBMS.
>
> - if you disable autocommit and execute several SQL DML statements 
> and/or stored procedures in each transaction, then you *MUST* 
> structure your code so that no matter what exceptions are thrown or
> conditions are violated after you've started a transaction, your 
> code will always commit or rollback the last transaction before it
> terminates. 

I know. If I would use transactions I would take care of the commits
and/or rollbacks.


> Done correctly, which means being rigorous in the way your code
> detects and handles JDBC/SQL exceptions, following any of the above
> options will make database corruption unlikely to happen, pretty
> much regardless of what hardware or software failure occurs.
>
> Done badly, and/or with insufficiently tested code, you'd better
> hope that DB backups are done frequently and reliably *and* make
> sure that data that has been accepted but not committed to the DB is
> securely stored where it will be replayed after a restart because
> for sure both the cached uncommitted data and the backups will get
> used.

And the code:
    public static boolean freeMemoryThread(final String className, final String database) {
        final Connection                conn;
        final long                      divideValue  = 1024 * 1024;
        final String                    insertLine   =
            "INSERT INTO memUsageLine\n" +
            "(className, PID, freeAfter, freeBefore, maxMemory, totalAfter, totalBefore)\n" +
            "VALUES\n"                          +
            "(?, ?, ?, ?, ?, ?, ?);\n"                                                      +
            "";
        final String                    insertRun    =
            "INSERT INTO javaApplicationRun\n"                                              +
            "(className, PID, maxMemory)\n" +
            "VALUES\n"                                                                      +
            "(?, ?, ?);\n"                      +
            "";
        final long                      pid          =
            Long.parseLong(java.lang.management.ManagementFactory.getRuntimeMXBean().getName().split("@")[0]);
        final Runtime                   rt           = Runtime.getRuntime();
        final ScheduledExecutorService  ses          = Executors.newSingleThreadScheduledExecutor();

        logMessage(className + ", " + database);
        try {
            PreparedStatement   ps;
            ResultSet           rs;
            Statement           stmt;

            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")));
            logMessage(Long.toString(pid));
            ps      = conn.prepareStatement(insertRun);
            ps.setString(1, className);
            ps.setLong(  2, pid);
            ps.setLong(  3, rt.maxMemory() / divideValue);
            ps.executeUpdate();
        } catch (Exception e) {
            logMessage("Could not connect with the database: " + e.getMessage());
            return false;
        }
        ses.scheduleAtFixedRate(new Runnable() {
                @Override
                public void run() {
                    try {
                        PreparedStatement   ps          = conn.prepareStatement(insertLine);
                        long                freeBefore  = rt.freeMemory()  / divideValue;
                        long                totalBefore = rt.totalMemory() / divideValue;
                        System.gc();
                        long                freeAfter   = rt.freeMemory()  / divideValue;
                        long                totalAfter  = rt.totalMemory() / divideValue;

                        ps.setString(1, className);
                        ps.setLong(  2, pid);
                        ps.setLong(  3, freeAfter);
                        ps.setLong(  4, freeBefore);
                        ps.setLong(  5, rt.maxMemory() / divideValue);
                        ps.setLong(  6, totalAfter);
                        ps.setLong(  7, totalBefore);
                        ps.executeUpdate();
                    } catch (Exception e) {
                        logMessage("Could not write memory line: " + e.getMessage());
                    }
                }
            }, 15, 60, TimeUnit.MINUTES);
        return true;
    }

The only thing that could go wrong is when someone exits the program
during the ps.executeUpdate. I think that is very unlikely and only
the last log record will be lost. That is not a big problem.

I heard a few times that it was very bad when your program terminates
without closing. I thought it a little bit strange: but better save as
sorry, that is why I wanted to check.


It works great: it is much better to get you log information out of a
(SQLite) database as from different files.

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

Back to comp.lang.java.programmer | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Not closing connections and statements Cecil Westerhof <Cecil@decebal.nl> - 2016-08-12 13:59 +0200
  Re: Not closing connections and statements Arne Vajhøj <arne@vajhoej.dk> - 2016-08-12 09:15 -0400
    Re: Not closing connections and statements Arne Vajhøj <arne@vajhoej.dk> - 2016-08-12 09:39 -0400
    Re: Not closing connections and statements Marcel Mueller <news.5.maazl@spamgourmet.org> - 2016-08-12 22:31 +0200
      Re: Not closing connections and statements Arne Vajhøj <arne@vajhoej.dk> - 2016-08-12 22:04 -0400
        Re: Not closing connections and statements Marcel Mueller <news.5.maazl@spamgourmet.org> - 2016-08-13 07:55 +0200
  Re: Not closing connections and statements Martin Gregorie <martin@address-in-sig.invalid> - 2016-08-12 19:01 +0000
    Re: Not closing connections and statements Marcel Mueller <news.5.maazl@spamgourmet.org> - 2016-08-12 22:35 +0200
      Re: Not closing connections and statements Arne Vajhøj <arne@vajhoej.dk> - 2016-08-12 21:52 -0400
    Re: Not closing connections and statements Cecil Westerhof <Cecil@decebal.nl> - 2016-08-12 23:55 +0200
      Re: Not closing connections and statements Eric Sosman <esosman@comcast-dot-net.invalid> - 2016-08-12 18:16 -0400
        Re: Not closing connections and statements Cecil Westerhof <Cecil@decebal.nl> - 2016-08-13 01:19 +0200
          Re: Not closing connections and statements Martin Gregorie <martin@address-in-sig.invalid> - 2016-08-13 00:21 +0000
            Re: Not closing connections and statements Gunter Herrmann <gunter.herrmann@earthlink.net> - 2016-08-17 16:38 -0400
      Re: Not closing connections and statements Arne Vajhøj <arne@vajhoej.dk> - 2016-08-12 21:58 -0400
        Re: Not closing connections and statements Cecil Westerhof <Cecil@decebal.nl> - 2016-08-13 12:50 +0200
      Re: Not closing connections and statements Arne Vajhøj <arne@vajhoej.dk> - 2016-08-12 21:59 -0400
      Re: Not closing connections and statements Marcel Mueller <news.5.maazl@spamgourmet.org> - 2016-08-13 08:47 +0200
        Re: Not closing connections and statements Andreas Leitgeb <avl@logic.at> - 2016-08-13 08:05 +0000
          Re: Not closing connections and statements Cecil Westerhof <Cecil@decebal.nl> - 2016-08-13 12:21 +0200
          Re: Not closing connections and statements Arne Vajhøj <arne@vajhoej.dk> - 2016-08-13 09:54 -0400
        Re: Not closing connections and statements Cecil Westerhof <Cecil@decebal.nl> - 2016-08-13 12:19 +0200
        Re: Not closing connections and statements Arne Vajhøj <arne@vajhoej.dk> - 2016-08-13 10:04 -0400
          log4j RollingFileAppender (was: Not closing connections and statements) Marcel Mueller <news.5.maazl@spamgourmet.org> - 2016-08-14 22:24 +0200
            Re: log4j RollingFileAppender Arne Vajhøj <arne@vajhoej.dk> - 2016-08-14 21:01 -0400
        Re: Not closing connections and statements Arne Vajhøj <arne@vajhoej.dk> - 2016-08-13 11:58 -0400
          logging (was: Not closing connections and statements) Marcel Mueller <news.5.maazl@spamgourmet.org> - 2016-08-14 23:46 +0200
    Re: Not closing connections and statements Arne Vajhøj <arne@vajhoej.dk> - 2016-08-12 21:44 -0400
  Re: Not closing connections and statements Florian Weimer <fw@deneb.enyo.de> - 2016-08-13 16:33 +0200
    Re: Not closing connections and statements Cecil Westerhof <Cecil@decebal.nl> - 2016-08-13 17:34 +0200

csiph-web