Caching Data in HashMap (Connection, ResultSet, and Statement is opened after getting Data from it and put in the Map)

Question

In my application I need to cache data in HashMap or TreeMap it saves a lot of time because it is very time consuming task to fetch the records every time from DB server and process it.
I am also doing profiling of this application using JProfiler and I feel that when I am getting records from DataBase to put in the Map connection to the ResultSet and Statement are not closed because it is showing me these classes taking so much memory.
Is this something that I am overreacting over this or it is really a problem?
By the way I am closing the connection in finally block. I am posting the code of it.

public Map someFunction() throws SomeException{
Connection con=null;
    Statement stmt=null;
    ResultSet rs=null;
    String sql=null;
    Map <String,String> testMap=new TreeMap<String, String>();

    try {
        con=HMDBUtil.getConnection();
        if(cacheSize==0) {
            sql = "SELECT SOMETHING FROM SOMEWHERE";
        }else {
            sql = "SELECT SOMETHING FROM SOMEWHERE_ELSE where rownum<"+cacheSize;
        }
        stmt=con.createStatement();
        stmt.setFetchSize(100000);
        rs=stmt.executeQuery(sql);
        long count=0;
        while(rs.next()) {
            testMap.put(rs.getString(1).trim(), rs.getString(2));
            count++;
        }
    } catch (SQLException e) {

        log.fatal("SQLException while fetching Data",e);
        throw new SomeException("SQLException while fetching Data",e);          
    }finally {
        HMDBUtil.close(con, stmt, rs);
    }

    return testMap;
}

HMDBUtil.close() method---

public static void close(Connection con, Statement stmt, ResultSet rs)
        throws SomeException {
    if (log.isDebugEnabled())
        log.debug("Invoked");
    close(rs);
    close(stmt);
    close(con);

    if (log.isDebugEnabled())
        log.debug("Leaving");
}

All close methods that are used to close all the connections --

    public static void close(Connection con) throws SomeException {
    try {

        if (log.isDebugEnabled())
            log.debug("Invoked");
        if (con != null) {
            con.close();
            con = null;
        }

        if (log.isDebugEnabled())
            log.debug("Leaving");

    } catch (SQLException e) {
        log.fatal("SQLException while Closing connection ", e);
        throw new SomeException("SQLException while Closing connection ",
                e, false, true);
    }
}


public static void close(Statement stmt) throws SomeException {
    try {
        if (log.isDebugEnabled())
            log.debug("Invoked");

        if (stmt != null) {
            stmt.close();
            stmt = null;
        }
        if (log.isDebugEnabled())
            log.debug("Leaving");

    } catch (SQLException e) {
        // log.error("Exception while Closing statement ", e);
        log.fatal("SQLException while Closing statement ", e);
        throw new SomeException("SQLException while Closing statement ", e, false, true);

    }
}


public static void close(ResultSet rs) throws SomeException {

    try {
        if (log.isDebugEnabled())
            log.debug("Invoked");
        if (rs != null) {
            rs.close();
            rs = null;
        }
        if (log.isDebugEnabled())
            log.debug("Leaving");

    } catch (SQLException e) {
        log.fatal("SQLException while Closing rs", e);
        throw new SomeException("SQLException while Closing rs", e, false, true);
    }
}

why don't you use any caching API to do this for you in more flexible and efficient manner? — Umesh Awasthi, Feb 29 '12 at 06:13
But again question is why my profiler showing these DataBase Classes, even I've closed them. — NIVESH SENGAR, Feb 29 '12 at 06:28
Not a direct answer, but have you though about LinkedHashMap: http://docs.oracle.com/javase/1.4.2/docs/api/java/util/LinkedHashMap.html — zengr, Feb 29 '12 at 08:47
Hey I want to ask if I will nullify Connection, Statement and ResultSet after calling the close method like this finally { HMDBUtil.close(con, stmt, rs); con=null; stmt=null; rs=null;} does it help something? — NIVESH SENGAR, Feb 29 '12 at 08:53
Yes, it does help garbage collector. It can figure out what objects it could collect before method ends. However in your implementation its won't work because you are setting to null _references_ to connection and statement which where passed to _close_ method. Actual objects still can't be collected because they have referenced in _someFunction_ method. — Petro Semeniuk, Feb 29 '12 at 12:29

Petro Semeniuk · Accepted Answer · 2012-04-03T07:05:21.880

Result Set and statement won't be removed from heap straightway after you closed connection. You need to have at least one run of garbage collector for this to happen.

Also, are you sure that HMDBUtil.close() method works as expected. It could leak connection as well.

+1 for using 3rd party caching provider.

EDIT: Just to avoid confusion. Classes will be present in heap till you process ends and will reside in Permanent Generation.

Instances of classes (objects) should be garbage collected. If they not that mean that your process holds references to them somehow somewhere, or garbage collector hasn't been run.

I'm not sure about JProfiler but with YourKit you could easy navigate object graph and find which object holds references to uncollected objects. It helped me a lot with finding memory leaks in the past.

I've monitored it many times and I am sure that after GC called these classes not being GCed. and yes HMDBUtil.close() method is fine I am taking Statement, Resultset and Connection as argument and close them one by one in the method. — NIVESH SENGAR, Feb 29 '12 at 06:35

score 1 · Answer 2 · answered Feb 29 '12 at 10:23

1

Finally executes after all teh execution of the try blocl, so it's normal that when you are doing "testMap.put(rs.getString(1).trim(), rs.getString(2));" the ResultSet and Statement are open because you haven't closed them yet.

answered Feb 29 '12 at 10:23

Pau

803
1
6
12

My try block completed already. I am returning a map by the above given method and I am getting it perfectly. It means Try block is executed. – NIVESH SENGAR Feb 29 '12 at 10:27
But the problem you are reporting is when you are putting things in the map, isn't it? "I feel that when I am getting records from DataBase to put in the Map connection to the ResultSet and Statement are not closed" This is executed inside the try, and before closing. – Pau Feb 29 '12 at 10:31
I think I've described it wrongly but it was like when I finished with my DB activity then also these Object are on the heap – NIVESH SENGAR Feb 29 '12 at 10:44

Caching Data in HashMap (Connection, ResultSet, and Statement is opened after getting Data from it and put in the Map)

2 Answers2