watchmerisxe watchmerisxe -4 years ago 167
Java Question

JDBC Connection close

can someone tell me where and how I have to close the connection here? Do I have to close the Connection in the Connection class or in the Controller class? I already tried to put followin at the end of the method in the connection class:

if (conn != null) {
try {
conn.close();
} catch (SQLException e) { /* ignored */}
}


But then I get:
"No operations allowed after statement closed."

Here is my Code:

public class DB_Connection {

String url = "XXX";

Statement statement;

public DB_Connection (){
try {

Connection con = (Connection) DriverManager.getConnection(url);
statement = (Statement) con.createStatement();

}
catch (SQLException ex){
System.out.println("Failed connection");
}
}

public void addSubject(String subject) throws SQLException {

try {
statement.executeUpdate("INSERT INTO `Subject` VALUES ('" + subject + "')" );
System.out.println("Added " + subject + "to database");
} catch(SQLException e) {
System.out.println("SQL Exception");
}
}
}


And I call it from here:

public class MenuController {

@FXML
public void SendSubject(ActionEvent e) throws IOException, SQLException {
DB_Connection connection = new DB_Connection();
connection.addSubject("English");

}

}


Thanks for your help!

Answer Source

You need to close the connection after you have finished using it. There are many ways to deal with that, but here's what I suggest you do:

public class DBConnection implements AutoCloseable {
    private String url = ...
    private Connection con;
    private Statement statement;

    public DBConnection () throws SQLException {
        try {
             con = DriverManager.getConnection(url);
             statement = con.createStatement();
        } finally {
             // Avoid leak if an exception was thrown in createStatement
             if (statement == null) {
                 con.close();
             }
        }
    }

    public void addSubject(String subject) throws SQLException {
        statement.executeUpdate("INSERT INTO `Subject` VALUES ('" + 
                                subject + "')" );
    }

    public void close() throws SQLException {
        con.close();
    }
}

Then use it like this:

try (DBConnection connection = new DBConnection()) {
     connection.addSubject("English");
}

Explanation / commentary:

  1. The close() method is the way that the application tells the DBConnection class "I have finished".
  2. Declaring DBConnection as `AutoCloaseable means that we can use try-with-resources to manage the closure ... which is simpler and more robust.
  3. The instance variables are private as per good OO design principles.
  4. The class name is corrected per the Java conventions.
  5. We still needed to be careful to ensure that we don't leak a connection if an exception occurs in the constructor itself.
  6. We allow SQLException to propagate to the caller. Those exceptions cannot be handled properly in DBConnection class itself.

The other approach is to do away with the DBConnection class entirely, and have the calling code take care of the connection object and the statements for itself. Certainly, in this small example the DBConnection abstraction adds minimal value.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download