Find Connection leak in Java application

Question

I have an application which starts giving me internal server error after some time, Some people I asked told me that this can be because of Connection leak in my application. I started searching and found this query to simulate connection leak.

select LAST_CALL_ET, SQL_TEXT, username, machine, to_char(logon_time, 'ddMon hh24:mi') as login, SQL_HASH_VALUE, PREV_HASH_VALUE, status from v$session, v$sql where username='USERNAME' and HASH_VALUE = PREV_HASH_VALUE order by last_call_et desc;.

I monitored my application with this query and closed all leaked connections for the query shown in this result. But now my application is starting to give same error for even less inactive sessions . Am I using the correct query to find out in active session / connection leak ? Someone told me condition HASH_VALUE = PREV_HASH_VALUE in this query is wrong, But I do not know about these columns (not much DB knowledge.)

Thank you

Answer 1

If you need to find out leaks you can use profilers like yourkit or jprofiler which is able to track socket/jdbc leaks.

To fix leaks you have to find out places where you opening connections and use try-with-resources which will do all close() stuff for you

try (Connection conection = DriverManager.getConnection(url);
     PreparedStatement statement = createPreparedStatement(conection); 
     ResultSet resultSet = statement.executeQuery()) {
     // process the resultSet here, all resources will be cleaned up
}