Problem with not closing db connection during debugging?

Question

I have a Java application that opens a database connection at the beginning and closes it at the end. However, the program does not always end because an exception is thrown or I am debugging it and stopping it halfway.

Will this cause open connections to accumulate and slow down the database, or will it be automatically cleaned?

Answer 1

Database A connection is owned and managed by a database, the class simply gives you access to this database resource. If you do not close the connection, then the Java class may be garbage collected, but the database may not be able to say that the connection is no longer in use, which can result in wasting database resources (as long as the timeout on side of the database) or even a leak.

So, when you finish using Connection , you should be sure to explicitly close it by calling its close() method. This will allow the garbage collector to remember the memory as early as possible and more importantly , it releases any other database resources (cursors, descriptors, etc.) that the connection can connect to.

The traditional way to do this in Java is to close your ResultSet , Statement and Connection (in that order) in the finally block when you are done with them, and the safe template looks like this:

 Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; try { // Do stuff ... } catch (SQLException ex) { // Exception handling stuff ... } finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { /* ignored */} } if (ps != null) { try { ps.close(); } catch (SQLException e) { /* ignored */} } if (conn != null) { try { conn.close(); } catch (SQLException e) { /* ignored */} } } 

The finally block can be slightly improved (to avoid null checking):

 } finally { try { rs.close(); } catch (Exception e) { /* ignored */ } try { ps.close(); } catch (Exception e) { /* ignored */ } try { conn.close(); } catch (Exception e) { /* ignored */ } } 

But, nevertheless, this is very verbose, so you usually use the helper class to close objects in the methods of the helper method with zero use, and the finally block becomes something like this:

 } finally { DbUtil.closeQuietly(rs); DbUtil.closeQuietly(ps); DbUtil.closeQuietly(conn); } 

And, in fact, Apache Commons DbUtils has DbUtils that does exactly that, so there is no need to write your own.

In your case, this will solve the exception problem, but not debugging (and you will spend the database resources until the timeout appears on the database side). So, 1. do not debug your code using the production database. 2. Try to debug the session to the end.

Answer 2

Here is what Sun (err ... Oracle?) Says :

It is recommended that programmers explicitly close the connections and applications that they created when they are no longer needed.

A programmer who writes code in the Java programming language and does not use any external resources should not worry about memory management. The garbage collector automatically deletes objects when they are no longer in use, and frees the memory that they use. When memory runs low, it will recycle discarded objects, making the memory they currently occupy for quick reuse.

However, if the application uses external resources, as happens when accessing the DBMS with the JDBC API, the garbage collector has no way of knowing the status of these resources. It will still recycle discarded objects, but if there is a lot of free memory in the Java heap, it may not collect garbage very often, although the (small) amount of Java garbage keeps large, expensive database resources open. Therefore, programmers explicitly close all connections (using the Connection.close method) and statements (using the Statement.close method) as soon as they are no longer needed, thereby freeing up DBMS resources as soon as possible. This is especially true for applications that are designed to work with various DBMSs due to changes from one DBMS to another.

I would put access to the database in a try block and not close all the statements and connections in the finally block.

Answer 3

Your db server will timeout. It will close the connection and roll back any uncommitted transactions. This has been happening for decades on any db manufacturing product.

If you want to do it right, use try {.. your code ..} finally {..close connections ..}

Answer 4

Nope.

If your program continues and your connections are alive, BD simply rejected your offer.

If something happened to your connection (for example, a timeout), then BD closed this connection and did not consume resources.

If you let go of your connection and the garbage collector was called (maybe time), the connection will close before being released.

If your program terminated without closing your connection, then the whole process (operating system) will free its own resources, and between them - its own resource connected to the BD (possibly a network socket). Then the BD will receive the interrupted / closed connection and release your connection.

The only thing that can happen is that only one execution will connect to BD many times and do something very bad to keep them open, occupying all available connections. But this is not your case, I think.

Edit : In general, BD made "bad-client-behavior"