java 缓存结果集

Caching ResultSet

提问by Satheesh Cheveri

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.

它更像是主观问题，问题的主要目标是缓存 java.sql.ResultSet。但我知道这不是首选机制，因为它与 Connection 紧密耦合，并且在连接关闭时数据可能会被刷新。为了解决这个问题，我使用了 CachedRowSet。CachedRowSet 的实例将使用第三方缓存工具进行缓存，这将帮助我减少数据库调用。

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.

下面给出了我的实现的代码片段。该方法executeQuery(String)在一个抽象类中实现，所有子类都将使用该抽象类来执行查询。也可能有客户子类使用此方法从我们的系统中获取数据。

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 ResultSetoperations ( ResultSet.getString(), ResultSet.get..()). If at all Driver expect/produces different subclass of ResultSet( Say jdbcResultSet,BaseResultSet, etc)

1. 我将返回 CachedResultSet 的实例而不是 ResultSet 接口。将是 resultSet 的正确替代品。驱动程序 JAR、DB 类可能因客户环境而异。客户将编写自定义类并期望来自抽象类的结果集。这会导致任何问题吗？像下面这样的东西

   公共类 CustomerXX 扩展了 BaseClass {

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

   }

2. 此类操作涉及的风险（缓存CachedRowSet、数据正确性）

3. 创造的表现 CachedRowSet

4. 与所有ResultSet操作（ResultSet.getString()、ResultSet.get..()）的兼容性。如果 Driver 期望/产生ResultSet( Say jdbcResultSet, BaseResultSet, 等) 的不同子类

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

任何想法，想法，建议都受到高度赞赏，并在此先感谢

采纳答案by Mairbek Khadikov

Implementing custom CachedRowSetcould be painful since you have implement to all methods exposed by ResultSetinterface.

实现自定义CachedRowSet可能会很痛苦，因为您已经实现了ResultSet接口公开的所有方法。

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

我建议不要在 jdbc 级别缓存，而是在数据访问层缓存一些值对象。

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

例如，如果您使用带有 id、name 和 email 列的用户表，您可以拥有以下值对象

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.

使用默认的 JdbcUserRepository 从数据库加载数据。

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.

1. 并发 - 多个线程将访问缓存
2. 内存 - 缓存可能变得太大，并且可能发生 OutOfMemoryException。

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

我建议使用一些现有的缓存解决方案，而不是编写自己的解决方案。我发现谷歌番石榴稳定且易于使用。