CODEWITHSUNDEEP
javasql
spiotr12
spiotr12

Reputation: 79

java, how to close statement which is used in different method?

I have two static methods in two classes: Account and FunnyDB. in FunnyDB I have a method which is used in Account to retrieve object from database. I am wondering if it is possible to somehow close statement (stmt) in the FunnyDB from Account method. Also, if it is not really possible, what are the consequences of leaving statement not closed? These are my methods:

FunnyDB:

/**
 * Returns ResultSet from query to get data to create object. Identufy data by id
 *
 * @param tableName Name of he table to search. Use getClass().getSimpleName().
 * @param id
 * @return
 */
public static ResultSet getObjectResultSetById(String tableName, int id) {
    ResultSet rs = null;
    try {
        // gets object's details from database
        Statement stmt = con.createStatement(); //<-- I WANT TO CLOSE THIS PARAMETER
        String sql = "SELECT * \n"
                + "FROM " + tableName + " \n"
                + "WHERE " + tableName.toLowerCase() + "_id = " + id;
        rs = stmt.executeQuery(sql);
        //TODO: Closing rs and stmt? .close().
    } catch (SQLException ex) {
        Logger.getLogger(FunnyDB.class.getName()).log(Level.SEVERE, null, ex);
        System.out.println(ex.getMessage());
    }
    return rs;
}

Account:

/**
 * Returns object found by given ID.
 *
 * @param id Object ID.
 * @return Object from database.
 */
public static Account getAccountFromDatabaseById(int id) {
    Account account = null;
    try {
        ResultSet rs = FunnyDB.getObjectResultSetById("Account", id);
        // create object to return
        while (rs.next()) {
            account = new Account(rs.getInt("account_id"), rs.getString("account_name"), rs.getString("account_type"), Currency.getCurrencyFromDatabaseById(rs.getInt("currency_id")), rs.getDouble("start_amount"), rs.getDouble("balance"));
        }
        rs.close();
//close stmt?????
    } catch (SQLException ex) {
        Logger.getLogger(Currency.class.getName()).log(Level.SEVERE, null, ex);
        System.out.println(ex.getMessage());
    }
    return account;
}

Thank you for help

Upvotes: 1

Views: 164

Answers (2)

rgettman
rgettman

Reputation: 178263

Making a class responsible for closing a Statement that is different than the one that created it is a bad idea. What if the calling code forgets to close it?

The FunnyDB class should be responsible for closing the Statement; it created it. To do this, it must read the ResultSet itself, creating the Account, so that it's completely done with the ResultSet. This way, there is no problem closing the ResultSet and the Statement before it returns.

Move the code that creates the Account with calls to the ResultSet into FunnyDB's getObjectResultSetById method. Have that method return the Account instead of the ResultSet. Close the ResultSet and Statement there. You might want to rename it getAccountById. You may also want to use a "try with resources" statement if you're using Java 7+. You can have the ResultSet and Statement closed automatically. If you are stuck with Java 6 or below, use a finally block to close them, to ensure that everything is closed before returning.

The getAccountFromDatabaseById in Account doesn't need to manipulate the ResultSet at all; it just needs to use the Account returned by FunnyDB.

Upvotes: 3

antonio
antonio

Reputation: 18242

You can pass the ResultSet as a parameter to getObjectResultSetById, so getAccountFromDatabaseById can close it.

Never EVER leave a resource open. When talking about databases, leaving a resource open could mean that at one point or another you will get a 'cursor limit exceeded' or something like that and this is hard to debug.

Also, as a general rule, always close your resources in a finally block. This way, you ensure that your resources will be closed even if your method throws an exception.

Upvotes: 0

Related Questions