Caching ResultSet

Question

Its more like subjective question, The main objective of the question is to Cache java.sql.ResultSet. But I know it is not preferred mechanism as it is tightly coupled with Connection, and data may get flushed when the connection is closed. To address this issue, I am using CachedRowSet. The instance of CachedRowSet will be cached using third party caching tool, which will help me to reduce db calls.

Code snippet of my implementation is given below. The method executeQuery(String) is implemented in an abstract class, which all sub classes would use to execute the query. There could be customer subclasses as well using this method to fetch data from our system.

public final ResultSet executeQuery(String query){
        try {
        // return data if it is available in cache, else execute and store in cache
            CachedRowSet cachedRowSet=getDataFromCache(query);
            if(cachedRowSet!=null) {
                return cachedRowSet;   
            }
            PreparedStatement statement=getStatment();
            ResultSet rs= statement.executeQuery(query);
            CachedRowSet cachedRowSet=new CachedRowSetImpl();
            cachedRowSet.populate(rs);
                    cachedData(cachedRowSet);
            return cachedRowSet;
        } catch (Exception e) {
            return null;
        }
    }

Now, I am bit confused with below points

1. Instead of ResultSet interface, I will return instance of CachedResultSet. Would be that correct replacement for resultSet. Driver JAR,DB classes can be varying across customer environment. Customer would write custom classes and expecting a resultSet from Abstract class. Would that cause any issue? Some thing like below

   public class CustomerXX extends BaseClass {

   public void process(String query){
   
       ResultSet rs = executeQuery(query);
   
       //process rs to fetch data
   
   }
   

   }

2. Risk involved in this kind of operation( Caching CachedRowSet, data correctness)

3. Performance of creating CachedRowSet

4. Compatibility with all ResultSet operations ( ResultSet.getString(), ResultSet.get..()). If at all Driver expect/produces different subclass of ResultSet ( Say jdbcResultSet,BaseResultSet, etc)

I have similar kind of many other question is my mind, I am just writing few of them that I feel valid and has higher priority.

Not sure my question is so vague, does have enough clarity of my requirements.

Any ideas, thoughts, suggestions are highly appreciated, and many thanks in advance

Answer 1

Implementing custom CachedRowSet could be painful since you have implement to all methods exposed by ResultSet interface.

I'd recommend not to cache on jdbc level but cache some value objects on data access layer instead.

For example if you user table with id, name and email columns you can have following value object

class User {
    Long id;
    String name;
    String email;
} 

next you can introduce data access layer

interface UserRepository {
    List<User> retrieveUsers();
}

with default JdbcUserRepository which loads data from the database.

Caching could be implemented using proxy pattern:

class CachingUserRepository implements UserRepository {
    private Cache cache;
    private UserRepository delegate;

    List<User> retrieveUsers() {
        List<User> result = cache.get(USERS_KEY);
        if (result == null) {
            result = delegate.retrieveUsers();
        }

        return result;
    }

}

Implementing the cache is the most challenging part. You have to worry about:

1. Concurrency - several threads will be accessing the cache
2. Memory - cache could become too big and there might occur OutOfMemoryException.

I'd recommend to use some existing caching solution instead of coding your own solution. I found google guava to be stable and easy to use.