JDBC Connection close

Question

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!

suppose you can find the solution referring to this [post](http://stackoverflow.com/questions/2225221/closing-database-connections-in-java) — Rajith Pemabandu, May 20 '17 at 01:32
like I said... I already saw this post and tried it, but I get the exception I mentioned. — Leonard Michalas, May 20 '17 at 01:33
Also, do I have to close the connection in the connection class or in the Controller class? — Leonard Michalas, May 20 '17 at 01:34
The `Connection` object in the constructor goes out of scope at the end of the constructor. This closes the connection. `Statement`s created from this connection are then closed as well. The `Connection` object would need to be a member variable just like the `Statement` object. — Glenn, May 20 '17 at 01:58

Stephen C · Accepted Answer · 2017-05-20T09:25:25.290

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:

The close() method is the way that the application tells the DBConnection class "I have finished".
Declaring DBConnection as `AutoCloaseable means that we can use try-with-resources to manage the closure ... which is simpler and more robust.
The instance variables are private as per good OO design principles.
The class name is corrected per the Java conventions.
We still needed to be careful to ensure that we don't leak a connection if an exception occurs in the constructor itself.
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.

May as well change the `Statement` to a `PreparedStatement`, prepare it in the constructor, and re-use in `addSubject` while at it... — Glenn, May 20 '17 at 02:02
There is no need to add a `finalize` method, JDBC drivers will do that cleanup if necessary; adding `finalize` here will only hinder garbage collection. — Mark Rotteveel, May 20 '17 at 07:45
Good point. Fixed. However, that does depend on the specific driver. AFAIK, the JDBC spec doesn't require a driver to implement finalizers. — Stephen C, May 20 '17 at 09:25

score 1 · Answer 2 · answered May 20 '17 at 02:16

When you get it going following Stephen's guidance, take a look at PreparedStatement as well...

Declare PreparedStatement rather than Statement:

    private PreparedStatement pStmt;

Create the PreparedStatement:

     pStmt = con.prepareStatement("INSERT INTO `Subject` VALUES (?)");

Reuse the PreparedStatement:

public void addSubject(String subject) throws SQLException {
    pStmt.setString(1, subject);
    pStmt.executeUpdate();
    pStmt.clearParameters();
}

Allows re-use and some protection from injection.

There is no need to call `clearParameters` in this case. – Mark Rotteveel May 20 '17 at 07:47 — Mark Rotteveel, May 20 '17 at 07:47

score 0 · Answer 3 · answered May 20 '17 at 02:15

0

Don't hold connections etc. as member variables at all. You should make them all (Connection, Statement/PreparedStatement, ResultSet) local variables and use the try-with-resources idiom.

answered May 20 '17 at 02:15

user207421

305,947
44
307
483

Hmm, this is a bit more questionable. Ideally, use a connection pool. Creating the `DB_Connection` object, re-using multiple times, then "close"ing the object to clean resources is not unreasonable depending on use case. Creating the connection can easily be orders of magnitude more expensive than executing the statement (e.g. 1.5sec vs 30ms). So very use case dependent. But in general, good advice: do not hold on to resources longer than you need. – Glenn May 20 '17 at 11:34

score 0 · Answer 4 · answered May 20 '17 at 02:17

0

Don't close conn object in the Connection class, Better to close in DAO class only or in the Main method or in the Method itself.

for example:

public void addSubject(String subject) throws SQLException {
    ........
    ........//**some operations**
    // close here after some operation done
   if (conn != null) {
    try {
        conn.close();
    } catch (SQLException e) { sysout(e);}
} 
}

answered May 20 '17 at 02:17

Py-Coder

2,024
1
22
28

by mayking it autocloseable and put the "Connection conn = new Connetion();" in a try {}, I also close it in the Controller aka. Dao Class right? :) – Leonard Michalas May 20 '17 at 11:02
u can't close in Controler, Only DAO class u can – Py-Coder May 20 '17 at 15:24

JDBC Connection close

4 Answers4