how to write java close connection?

Question

I have more than 2 classes that implemented the connection separately using try-catch, however when I tried to close the connection in the main it didn't work since it has a scope in the try-catch. My question is how do I close the connection while using the try-catch block?

Here is my code for one of my classes

String query="INSERT INTO branch (branch_Num , Street, Nieghbourhood,city) VALUES (?, ?,?, ?)";

try{
    PreparedStatement ps;
    String f="jdbc:derby://localhost:1527/coffeeZone";
    connection = DriverManager.getConnection(f, "coffee", "1234"); 
    ps=connection.prepareStatement(query);
    ps.setString(1, bno);
    ps.setString(2, strt);
    ps.setString(3, nbh);
    ps.setString(4, ci);
    if (ps.executeUpdate()>0){
        JOptionPane.showMessageDialog(null, "Complete! a new branch is added !");
    }else
    {
        JOptionPane.showMessageDialog(null, "");
    }       
 } catch (SQLException ex) {
    JOptionPane.showMessageDialog(null,ex);
 }

Why won't you close it inside the `try` block ? – Dominik Wosiński Apr 08 '19 at 09:03 — Dominik Wosiński, Apr 08 '19 at 09:03

score 4 · Answer 1 · answered Apr 08 '19 at 09:06

You need to do this in finally block, so that it's executed no matter if the operation was successful or not (an exception was thrown):

connection.close();

Or easier - use try-with-resources. Then writing:

try (Connection connection = DriverManager.getConnection(f, "coffee", "1234")) {
    ...
}

Then you won't have to worry about closing the resource.

score 3 · Answer 2 · answered Apr 08 '19 at 09:15

Since java version 1.7 you can use try-with-resources as following:

    String f = ...
    try (Connection connection = DriverManager.getConnection(f, "coffee", "1234")) {
        ...
    } catch (SQLException ex) {
        ...
    }

and it will be closed automatically by java.

score 1 · Answer 3 · answered Apr 08 '19 at 09:17

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 that:

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 */}
    }
}

For more info check this link

how to write java close connection?

3 Answers3