CODEWITHSUNDEEP
jdbc
Viraj Dhamal
Viraj Dhamal

Reputation: 5325

Closing ResultSet is must or not?

I have written a query as given below-

    Connection dbConnection = null;
    PreparedStatement preparedStatement = null;
    ResultSet rs = null;
    try {

        String fetchOneSQL = "select p.NAME from PAPER p where  p.PAPERID="+paperId;
        dbConnection = icrudResultAnalysis.getConnection();
        preparedStatement = dbConnection.prepareStatement(fetchOneSQL);
        rs = preparedStatement.executeQuery();

        while (rs.next()) {
            Paper paper=new Paper();                
            paper.setName(rs.getString(NAME));              
        }

        // get new records list
        preparedStatement=null;
        rs=null;

        String getListSql="select ib.NAME from ITEMBANK ib  where ib.ITEMBANKID="+itemBankId;
        preparedStatement = dbConnection.prepareStatement(getListSql);
        rs = preparedStatement.executeQuery();

        while (rs.next()) {
            ItemBank itemBankObj=new ItemBank();
            itemBankObj.setName(rs.getString(NAME));
            listItemBanks.add(itemBankObj);
        }

        rs.close();
        preparedStatement.close();
        dbConnection.close();

    } catch (Exception e) {
        LOGGER.error("Exception Occured while fetching All record: "
                + e.getMessage());
    } finally {

        try{
            if (rs!=null){
                rs.close();
            }
        }catch(SQLException e)
        {
            LOGGER.error(RESULTSETCLOSEEXCEPTION    + e.getMessage());
        }

        try {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
        } catch (SQLException e) {
            LOGGER.error(STATEMENTCLOSEEXCEPTION
                    + e.getMessage());
        }
        try {
            if (dbConnection != null) {
                dbConnection.close();
            }
        } catch (SQLException e) {
            LOGGER.error(CONNECTIONCLOSEEXCEPTION
                    + e.getMessage());
        }
    }

In above code i have used single resultset for two select statement by creating ResulSet rs =null . Is it good practice? Or i have to close ResultSet each time? What is difference between closing ResultSet and making ResultSet null?

Upvotes: 0

Views: 242

Answers (2)

Oomph Fortuity
Oomph Fortuity

Reputation: 6118

When you are re-using resources(resultSet,PrepareStatement),they must be closed first...Instead of setting prepare statement to NULL.You must close it and it will automatically close the result set.There is no need to explicitly close the result set.

Upvotes: 0

Luca Basso Ricci
Luca Basso Ricci

Reputation: 18403

All resources MUST BE CLOSED after use using .close() method! And resultSet is not an exception, except in this case (from ResultSet javadoc):

A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.

In your case you have to manual .close() the first opened resultset, but not necessary the second used; making a resultSet = null only set reference to variable resultSet equals null, no more no less.
If you are using Java7, Resultset is implementing AutoCloseable and you can use this feature to rewrite your code in cleaner way (look Oracle doc)

Upvotes: 4

Related Questions