closing the Database connections in java

Question

I have written the following java code to connect the Oracle Express Edition database.....I have read a lot about closing the connection, statement and result but there are some questions about this issue...

1- I usually test my code several time so If I Don not close the connection and etc. will I face with any problem?!!(because the connection is made on the same variable or object in my code!!!!)

2-how can I understand how many connections exist now?(after testing the code for several time)

3- Should I place CLOSE methods in finally block with a new TRY CATCH block or add THROWS SQLException after main method?!!!! what is the difference between these to implementation?!!!

package mainAssignment;

import java.sql.*;
import java.text.MessageFormat;
import java.math.*;
import java.util.*;
//import java.text.MessageFormat;

public class Conn {

/**
 * @param args
 */
public static void main(String[] args)throws SQLException{
    // TODO Auto-generated method stub

    String jdbcURL = "jdbc:oracle:thin:@localhost:1521:xe";
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    String user = "hr";
    String passwd = "hr";
    Scanner input = new Scanner(System.in);
    try {

        Class.forName("oracle.jdbc.driver.OracleDriver");
        conn = DriverManager.getConnection(jdbcURL, user, passwd);
        System.out.println("ok");
        System.out.println("Please enter the desired employee ID: ");
        Object[] arg={new String(input.nextLine())};
        String query = MessageFormat.format(
            "select * from EMPLOYEES where employee_id={0}", arg);
        //System.out.println(query);
        stmt = conn.createStatement();
        stmt.executeQuery(query);
        rs = stmt.getResultSet();
        while (rs.next()) {
                BigDecimal empid = rs.getBigDecimal(1);
                String firstname = rs.getString("FIRST_NAME");
                System.out.println("employee ID " + empid
                        + "  first name is " + firstname);
            }

    } catch (SQLException e) {
        e.printStackTrace();
        // TODO
    } catch (ClassNotFoundException e) {
        // TODO
        e.printStackTrace();
    } finally {
        conn.close();
        rs.close();
    if (conn.isClosed())
        System.out.println("no connection any more");
    else
        System.out.println("connection exists");
    }
   }
 }

See http://stackoverflow.com/questions/1335812/try-catch-finally-and-then-again-a-try-catch — Reimeus, Jul 12 '12 at 17:33

score 3 · Accepted Answer · edited May 23 '17 at 10:34

3

1) You'll be left with many idle (hanging) connections after you finish the execution. To prevent that check whether the connection is null. If it is - establish a new one, if it's not null - use existing.

2) You can check it on the sql server. Example for ms sql server: How to determine total number of open/active connections in ms sql server 2005

3) Putting close methods in finally block is a good practice. Don't change that. Sometimes finally block also requires its own inner try-catch-finally block.

Side note: if you create methods that throw exceptions you're not solving any issues. it's just pushing the need to maintain the exception to the person who'll use your methods. Sometimes it's a good approach but more often it is not.

In real life applications you should also check whether the database is available, the connection was established and the query result is not null.

Appendix 1: How to check the number of active connections in oracle express:

How to list active / open connections in Oracle?

http://dbalink.wordpress.com/2008/06/08/find-active-sessions-in-oracle-database/

edited May 23 '17 at 10:34

Community

1
1

answered Jul 12 '12 at 17:36

Andrzej Bobak

2,106
3
28
36

2

Just to add, OP should check if con!= null – Luiggi Mendoza Jul 12 '12 at 17:38
1- even if the connection is made on the same object in every execution? 2-how? 3- I have put it there but there is an ERROR telling that I should put throws in my main method or adding try catch for each CLOSE methods!!! which one is better? – msc87 Jul 12 '12 at 17:38
1

And do `if(rs != null)` because `rs` might be unreached more often. – nkr Jul 12 '12 at 17:39
2

At the beginning of the code: `Connection conn = null;`, so yes, I guess OP should check `if (conn != null)` before closing it, the same with ResultSet and Statement, and the order is ResultSet, Statement (Callable, Prepared or simple) and finally Connection. – Luiggi Mendoza Jul 12 '12 at 17:42
@msc87 1) yes, 3) try/catch would be better in this case. – nkr Jul 12 '12 at 17:43
1

nkr: try { code } catch { exceptions } finally { release resources }, of course you'll end up with another try-catch inside finally ... – Andrzej Bobak Jul 12 '12 at 17:45
msc87: just check whether the connection is null and if it is establish a new one, if not, use existing – Andrzej Bobak Jul 12 '12 at 17:52
@ Andrzej Bobak & Luiggi Mendoza: Thanks for your replies.....I want to know how to check the number of connections in oracle express edition also.... – msc87 Jul 12 '12 at 21:42
1

msc87: I added two links in my answer. These are the server side methods. – Andrzej Bobak Jul 12 '12 at 21:59
I am working on my code to improve and more understand about other methods...I used isClosed method in IF-ELSE section but it does not work and returns ERROR...why? my new code is:"try{ rs.close(); if (rs.isClosed()) System.out.println("ResultSet closed"); }", and the error is "Exception in thread "main" java.lang.AbstractMethodError: oracle.jdbc.driver.OracleResultSetImpl.isClosed()Z". – msc87 Jul 12 '12 at 22:18
can you update your question and the source code attached to it? it will be a lot easier to answer :) – Andrzej Bobak Jul 13 '12 at 07:10

Alper Akture · Answer 2 · 2016-09-21T19:20:04.377

1

You may want to use a connection pool. Take a look at C3P0 or DBCP. You still need to close your result set, and statement, but when you call close on the connection it just returns it back to the pool. Update: we'be been using Hikari for connection pooling for a few years, and have been really happy with it.

edited Sep 21 '16 at 19:20

answered Jul 13 '12 at 16:06

Alper Akture

2,445
1
30
44

closing the Database connections in java

2 Answers2