无奈自己写了一个简单的JDBC查询缓存，分享一下

正在做一个项目，因为不是一个人做的，所以很多地方还得用最原始的JDBC技术，享受不了Hibernate的查询缓存，，而且我们用的是Oracle 10G，NND上网查，人家MySQL早就有查询缓存了，Oracle非得11G才支持，无奈自己写了一个简单的基于SQL语句和CachedRowSet名值对的查询缓存。测试了一下性能还不错，大概10:1（其实这是废话，从内存里读当然要比从数据库中查询快了）。

因为简单，没有过多考虑清除缓存的策略，简单地只要有修改就清空缓存。所以应用场合有两个限制：
1、SQL语句的可能性不能无限多（例如如果语句中包含可变的时间日期就不行）；
2、读操作远远多于写操作。

我用在权限管理中，肯定是很合适的。

下面是代码：

[code=Java]package oas.common.cache;

import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Map.Entry;
import java.util.logging.Logger;

/**
 * 通用缓存类
 * @author shajunxing
 */
public class Cache<T> {

    private final static Logger logger = Logger.getLogger(Cache.class.getName());
    /**
     * 缓存
     */
    private Map<String, T> cache;
    /**
     * 访问计数器
     */
    private long visitedCount;
    /**
     * 命中计数器
     */
    private long hitCount;

    /**
     * 构造函数
     * @param maxSize 缓存最大容量
     */
    public Cache(final int maxSize) {
        cache = new LinkedHashMap<String, T>(1, 0.75F, true) {

            @Override
            protected boolean removeEldestEntry(Entry<String, T> eldest) {
                return size() > maxSize;
            }
        };
        clear();
    }

    /**
     * 取出一个值
     * @param key 键
     * @return 值/空
     */
    public T get(String key) {
        if (key == null) {
            return null;
        }

        visitedCount++;
        if (cache.containsKey(key)) {
            hitCount++;
            return cache.get(key);
        } else {
            return null;
        }
    }

    /**
     * 存入一个值
     * @param key 键
     * @param value 值
     */
    public synchronized void put(String key, T value) {
        if ((key == null) || (value == null)) {
            return;
        }

        cache.put(key, value);
    }

    /**
     * 清除缓存，复位计数器
     */
    public void clear() {
        cache.clear();
        visitedCount = 0;
        hitCount = 0;
    }

    /**
     * 获取命中计数
     * @return 命中计数
     */
    public long getHitCount() {
        return hitCount;
    }

    /**
     * 获取访问计数
     * @return 访问计数
     */
    public long getVisitedCount() {
        return visitedCount;
    }

    /**
     * 执行一个任务，如果在缓存中有对应的值，那么直接返回，否则执行任务并把输出保存入缓存
     * @param task 任务
     * @return 任务返回值
     */
    public T doCachedTask(CachedTask<T> task) {
        String key = task.getKey();
        T value = get(key);
        if (value == null) {
            value = task.run();
            put(key, value);
        }
        return value;
    }

    /**
     * 返回缓存内容
     * @return 缓存内容
     */
    @Override
    public String toString() {
        StringBuilder str = new StringBuilder("/n");
        for (Map.Entry<String, T> entry : cache.entrySet()) {
            str.append("Key: " + entry.getKey() + "/n");
            str.append("Value: " + entry.getValue() + "/n");
        }
        return str.toString();
    }
}
[/code]

[code=Java]package oas.common.cache;

/**
 * 通用缓存任务类，执行一个简单的缓存任务
 * @author shajunxing
 */
public abstract class CachedTask<T> {

    private String key;

    public String getKey() {
        return key;
    }

    public CachedTask(String key) {
        this.key = key;
    }

    public abstract T run();
}
[/code]

[code=Java]package oas.common;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;
import javax.sql.rowset.CachedRowSet;
import oas.common.cache.Cache;
import oas.common.cache.CachedTask;

/**
 * JDBC助手类
 * @author shajunxing
 */
public class JdbcHelper {

    private final static Logger logger = Logger.getLogger(JdbcHelper.class.getName());
    private static ComboPooledDataSource cpds;
    private static ThreadLocal<Connection> threadLocal = null;
    private static Cache<CachedRowSet> queryCache = new Cache<CachedRowSet>(10000);


    static {
        try {
            Class.forName(Utility.getProperty("hibernate.connection.driver_class"));
            // 初始化C3P0
            cpds = new ComboPooledDataSource();
            cpds.setDriverClass(Utility.getProperty("hibernate.connection.driver_class"));
            cpds.setJdbcUrl(Utility.getProperty("hibernate.connection.url"));
            cpds.setUser(Utility.getProperty("hibernate.connection.username"));
            cpds.setPassword(Utility.getProperty("hibernate.connection.password"));
            logger.info("连接池大小：" + cpds.getMaxPoolSize());
            cpds.setMaxPoolSize(100);
            cpds.setMaxStatements(10);
            // 初始化线程局部变量
            threadLocal = new ThreadLocal<Connection>();
        } catch (ClassNotFoundException ex) {
            logger.severe("JDBC驱动加载失败：" + ex);
            cpds = null;
            System.exit(-1);
        } catch (PropertyVetoException ex) {
            logger.severe("C3P0初始化失败：" + ex);
            cpds = null;
            System.exit(-1);
        }
    }

    /**
     * 获取连接
     * @return 可用的连接
     * @throws java.sql.SQLException
     */
    public static Connection getConnection() throws SQLException {
        Connection conn = threadLocal.get();
        if (conn == null) {
            conn = cpds.getConnection();
//            conn = DriverManager.getConnection(
//                    Utility.getProperty("hibernate.connection.url"),
//                    Utility.getProperty("hibernate.connection.username"),
//                    Utility.getProperty("hibernate.connection.password"));
            conn.setAutoCommit(false);
            threadLocal.set(conn);
        }
        return conn;
    }

    /**
     * 关闭连接
     * @param conn 连接
     */
    public static void closeConnection() {
        Connection conn = threadLocal.get();
        threadLocal.set(null);
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException ex) {
                logger.severe("关闭Connection失败：" + ex);
            }
        }
    }

    /**
     * 提交事务
     * @throws java.sql.SQLException
     */
    public static void commit() throws SQLException {
        getConnection().commit();
    }

    /**
     * 回滚事务
     */
    public static void rollback() {
        try {
            getConnection().rollback();
        } catch (SQLException ex) {
            logger.severe("事务回滚异常：" + ex);
        }
    }

    /**
     * 关闭声明
     * @param stmt 声明
     */
    public static void close(Statement stmt) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException ex) {
                logger.severe("关闭Statement失败：" + ex);
            }
        }
    }

    /**
     * 关闭结果集
     * @param rs 结果集
     */
    public static void close(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException ex) {
                logger.severe("关闭ResultSet失败：" + ex);
            }
        }
    }

    /**
     * 获取一个缓存行集对象
     * @return 缓存行集对象
     */
    private static CachedRowSet getCachedRowSet() {
        CachedRowSet ret = null;
        try {
            ret = (CachedRowSet) Class.forName("com.sun.rowset.CachedRowSetImpl").newInstance();
        } catch (InstantiationException ex) {
            logger.severe(ex.toString());
        } catch (IllegalAccessException ex) {
            logger.severe(ex.toString());
        } finally {
            return ret;
        }
    }

    /**
     * 查询
     * @param sql SQL语句
     * @return 查询结果
     */
    public static CachedRowSet query(String sql) {
        CachedRowSet crs = getCachedRowSet();
        if (crs == null) {
            return null;
        }

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            crs.populate(rs);
            return crs;
        } catch (SQLException ex) {
            logger.severe(ex.toString());
            return null;
        } finally {
            close(rs);
            close(stmt);
            closeConnection();
        }
    }

    /**
     * 缓存查询
     * @param sql SQL语句
     * @return 查询结果
     */
    public static CachedRowSet cachedQuery(final String sql) {
        return queryCache.doCachedTask(new CachedTask<CachedRowSet>(sql) {

            @Override
            public CachedRowSet run() {
                return query(sql);
            }
        });
    }

    public static void clearCache() {
        queryCache.clear();
    }

    public static long getCacheHitCount() {
        return queryCache.getHitCount();
    }

    public static long getCacheVisitedCount() {
        return queryCache.getVisitedCount();
    }

    public static void main(String[] args) throws SQLException {
        for (int i = 0; i < 1000; i++) {
            Thread th = new Thread(new Runnable() {

                @Override
                public void run() {
                    CachedRowSet rs = JdbcHelper.query("select classid from mta_class order by classid");
                    if (rs != null) {
                        try {
                            List<String> ids = new ArrayList<String>();
                            while (rs.next()) {
                                ids.add(rs.getString("classid"));
                            }
                            logger.info(ids.toString());
                        } catch (SQLException ex) {
                            logger.severe(ex.toString());
                        }
                    }
                }
            });
            th.start();
        }
    }
}
[/code]