Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.lang.java.programmer > #35278
| From | Cecil Westerhof <Cecil@decebal.nl> |
|---|---|
| Newsgroups | comp.lang.java.programmer |
| Subject | Re: Not closing connections and statements |
| Date | 2016-08-12 23:55 +0200 |
| Organization | Decebal Computing |
| Message-ID | <87k2fllkjf.fsf@Equus.decebal.nl> (permalink) |
| References | <87shuakxjq.fsf@Equus.decebal.nl> <nol6er$gf5$1@dont-email.me> |
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
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