Best Place to Close Database Connection

Closing database connections in Java

When you are done with using your Connection, you need to explicitly close it by calling its close() method in order to release any other database resources (cursors, handles, etc.) the connection may be holding on to.

Actually, the safe pattern in Java is to close your ResultSet, Statement, and Connection (in that order) in a finally block when you are done with them. Something 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 into (to avoid the null check):

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

But, still, this is extremely verbose so you generally end up using an helper class to close the objects in null-safe helper methods and the finally block becomes something like this:

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

And, actually, the Apache Commons DbUtils has a DbUtils class which is precisely doing that, so there isn't any need to write your own.

What is the suitable way to close the database connection in Java?

Those methods only close the ResultSet. You still have to close all Statement and Connection instances. I recommend doing so in a finally block. Something like,

Connection conn = null;
Statement stmt = null'
ResultSet rs = null;
try {
  conn = getConnection();
  stmt = conn.prepareStatement(sql);
  stmt.setString(1, "Hello"); 
  rs = stmt.executeQuery();
  while (rs.next()) {
    // ...
  }
} catch (SQLException se) {
  se.printStackTrace();
} finally {
  if (rs != null) {
    try {
      rs.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
  if (stmt != null) {
    try {
      stmt.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
  if (conn != null) {
    try {
      conn.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
}

If you use my Close utility the finally block could be,

} finally {
  Close.close(rs, stmt, conn);
}

How can I close the database connection in this approach?

You can change your code to something like:

public class DatabaseConnection {

    static final String URL = "jdbc:mysql://localhost:3306/rubyrail?useSSL=false";
    static final String Username = "root";
    static final String Password = "root";

    private static Connection createConnection() {
        return DriverManager.getConnection(URL, Username, Password);
    }

    public static void create() {
        try (Connection connection = createConnection()) {
            // do something with the connection
        } catch (SQLException e) {
            e.printStackTrace();
            // or something else to handle the error
        }
    }

    // same for the rest of your methods

    public static void main (String args[]) {
        int choice= 0;
        while (choice < 6) {
            System.out.println("\n1. Create");
            System.out.println("\n2. Read");
            System.out.println("\n3. Update");
            System.out.println("\n4. Delete");
            System.out.println("\n5. Close");

            choice = scanner.nextInt();
            switch(choice) {
            case 1: 
                create();
                break;
            // other cases
            }
        }
    }
}

This will create a connection for each method invocation, which may be less efficient, but will simplify resource management. If performance is of real importance, you should consider using a data source that provides connection pooling (eg HikariCP, Apache DBCP, etc). Using a connection pool will allow reuse of connections without your code having to worry about it beyond setting up the data source configuration.

Alternatively, create the connection once in your main, and pass it to each method you want to call:

public static void create(Connection connection) {
    try {
        // do something with connection
    } catch (SQLException e) {
        e.printStackTrace();
        // or something else to handle the error
    }
}
public static void main (String args[]) {
    try (Connection connection = createConnection()) {
        int choice= 0;
        while (choice < 6) {
            System.out.println("\n1. Create");
            System.out.println("\n2. Read");
            System.out.println("\n3. Update");
            System.out.println("\n4. Delete");
            System.out.println("\n5. Close");

            choice = scanner.nextInt();
            switch(choice) {
            case 1: 
                create(connection);
                break;
            // other cases
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
        // or something else to handle the error
    }
}

Closing Database Connections in Java Swing

If you are using Java 7 and above, I would recommend using try with resources.

The try-with-resources statement ensures that each resource is closed at the end of the statement. Any object that implements java.lang.AutoCloseable, which includes all objects which implement java.io.Closeable, can be used as a resource.

In your case:

        try (PreparedStatement pst = conn.prepareStatement(sql))//use try with resources
            {
            pst.setString(1, loginField.getText());
            pst.setString(2, passwordField.getText());
            ResultSet rs = pst.executeQuery();
            int countUsr = 0;
            while(rs.next()){
                countUsr++;
            }
            if(countUsr == 1){
                loginFrame.dispose();
                AdminFrame adminFrame = new AdminFrame();
                adminFrame.setVisible(true);
            }else  if(countUsr > 1){
                JOptionPane.showMessageDialog(null, "ERR");
            }else{
                JOptionPane.showMessageDialog(null, "ERR");
                passwordField.setText("");
            }
        //removed rst closing, no need to close if your PreparedStatement is being closed.
        //No need to explicitly close our PreparedStatement since we are using try with resources
        }catch(Exception e){
            JOptionPane.showMessageDialog(null, "ERR: "+e.getMessage());
        }
    }

You should also note that you don't need to close your ResultSet if you are closing your PreparedStatement. (See this answer)

Where to Close MySQL Connection on PHP

You could change this part

$result = mysql_query($sql);
$title = mysql_result($result, 0,0);
echo trim($title);

to

$result = mysql_query($sql) or some_exception_function("ERROR IN QUERY:".$sql."<br>".mysql_error()); // here you can send an email with error, or whatever you want, if some error occurs
$row = mysql_fetch_array($result);
$title = $row['title']; // so you always fetch desired column by it's name
echo trim($title);

and like @fred-ii said, there is no need to close mysql connection

How to close existing connections to a DB

This should disconnect everyone else, and leave you as the only user:

alter database YourDb set single_user with rollback immediate

Note: Don't forget

alter database YourDb set MULTI_USER

after you're done!

Should I open/close database connection after every query/insert?

Don't close your connection unless you are exiting your app, and then make sure you do. Ensure that you are using the same connection when you come back to do more I/O. Database connections are resource-intensive and should be established as few times as possible and shared as much as possible. You can also get middleware problems with connections like ODBC if you don't pick up existing connections and get weird errors like connection pools running out. Get to know your connector and how to use it most effectively, it will be a rewarding activity :-)

What is the best time to open and close a connection to database?

ASP.NET / Web API / (...)

• Usually tied to the unit of work pattern, a connection is created for an incoming request then closed when the request has been processed
  
  
  • deviations may occur when you have long running background tasks

WPF / Window Forms

• In this case I would suggest that it's more on a per action basis, even when polling for changes - if that scenario exists
  
  
  • Open, Query, Close, repeat

---

Related Topics