jdbc数据库连接丢失或未释放情况跟踪

忙碌的工作让我很久没有写过博客了，另外技术上也没有明显的进步，也缺乏兴奋点。项目正式上线很长时间，随要访问压力的增大，每天PV差不多有500万。这时出现了一些问题，主要的是访问响应慢，程序中未发现异常。错误日志有数据库连接的错误，怀疑是数据库连接丢失或有某些请求会锁表。因此写一段代码跟踪数据库连接获取和释放的情况。
原理很简单，拦截DataSource的getConnnection方法，把当前Connection和调用堆栈保存到连接列表；拦截Connection对象close方法，把Connection从连接列表中删除。
直接上代码：

package com.emagsoftware;

import java.lang.reflect.Method;
import java.sql.Connection;
import javax.sql.DataSource;

import net.sf.cglib.proxy.Enhancer;
import net.sf.cglib.proxy.MethodInterceptor;
import net.sf.cglib.proxy.MethodProxy;
import org.springframework.beans.factory.FactoryBean;
import org.springframework.beans.factory.config.BeanPostProcessor;

import org.apache.log4j.Logger;

import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

/**
 * DataSource connection监控处理
 *
 * @author huzl
 * @date 2010-11-26 9:42:17
 */
class DataSourceBeanPostProcessor implements BeanPostProcessor {
    public static Map connections = new ConcurrentHashMap();
    public static Map connectionTime = new ConcurrentHashMap();
    Logger log = Logger.getLogger(DataSourceBeanPostProcessor.class);

    public Object postProcessBeforeInitialization(Object object, String name) {
        return object;
    }

    //创建DataSource或DataSource工厂的代理
    public Object postProcessAfterInitialization(Object object, String name) throws org.springframework.beans.BeansException {
        if (!"dataSource".equals(name)) return object;
        System.out.println("****************DataSource postProcessAfterInitialization success ");
        if (object instanceof FactoryBean)
            return createDataSourceFactoryProxy((FactoryBean) object);
        else
            return createDataSourceProxy((DataSource) object);

    }

    private FactoryBean createDataSourceFactoryProxy(final FactoryBean factoryBean) {
        if (Enhancer.isEnhanced(factoryBean.getClass())) return factoryBean;
        MethodInterceptor factoryInterceptor = new MethodInterceptor() {
            public java.lang.Object intercept(Object obj, Method method, Object[] args, MethodProxy proxy) throws java.lang.Throwable {
                Object result = method.invoke(factoryBean, args);
                if ("getObject" != method.getName()) return result;
                return createDataSourceProxy((DataSource) result);
            }
        };
        return (FactoryBean) createProxy(FactoryBean.class, factoryInterceptor);
    }
    //拦截DataSource getConnection方法，记录获取的数据库连接
    private DataSource createDataSourceProxy(final DataSource dataSource) {
        if (Enhancer.isEnhanced(dataSource.getClass())) return dataSource;
        MethodInterceptor dataSourceInterceptor = new MethodInterceptor() {
            public java.lang.Object intercept(Object obj, Method method, Object[] args, MethodProxy proxy) throws java.lang.Throwable {
                Object result = method.invoke(dataSource, args);
                if ("getConnection" != method.getName()) return result;
                connections.put(result, new Exception());
                connectionTime.put(result, new java.util.Date());
                System.out.println("****************DataSource Connection  get  size = " + connections.size());
                return createConnectionProxy((Connection) result);
            }
        };
        return (DataSource) createProxy(DataSource.class, dataSourceInterceptor);
    }
    //拦截Connection close方法，清除释放的数据库连接
    private Connection createConnectionProxy(final Connection conn) {
        if (Enhancer.isEnhanced(conn.getClass())) return conn;
        MethodInterceptor connectionProxy = new MethodInterceptor() {
            public java.lang.Object intercept(Object obj, Method method, Object[] args, MethodProxy proxy) throws java.lang.Throwable {
                if ("close" == method.getName()) {
                    connections.remove(conn);
                    connectionTime.remove(conn);
                    System.out.println("****************DataSource Connection close size = " + connections.size());
                }
                return method.invoke(conn, args);
            }
        };
        return (Connection) createProxy(Connection.class, connectionProxy);
    }

    private Object createProxy(Class targetInterfaceClass, MethodInterceptor interceptor) {
        Enhancer enhancer = new Enhancer();
        enhancer.setInterfaces(new Class[]{targetInterfaceClass});
        enhancer.setCallback(interceptor);
        return enhancer.create();
    }

}

spring配置文件

<bean class="com.emagsoftware.DataSourceBeanPostProcessor"/>

写一个页面，检查connections和connectionTime中的对象即可

<%@ page import="org.apache.commons.lang.exception.ExceptionUtils" %>
<%@ page import="java.util.Iterator" %>
<%@ page import="com.emagsoftware.DataSourceBeanPostProcessor" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="zh-CN" dir="ltr">

<head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
    <title>连接池查询</title>
    <style type="text/css">
        .hidden{
            display: none;
        }
    </style>

</head>
<body>
    <h1>连接池(<%=DataSourceBeanPostProcessor.connections.size()%>)</h1>
    <%
        if(DataSourceBeanPostProcessor.connections.size()>0){
            out.println("时间:" + DataSourceBeanPostProcessor.connectionTime.values() + "
");
            Iterator iterator = DataSourceBeanPostProcessor.connections.values().iterator();
            while(iterator.hasNext())
            {
                Exception ex = (Exception) iterator.next();
                out.println("<pre>" + ExceptionUtils.getFullStackTrace(ex) + "</pre>
");
            }
        }
    %>
</body>
</html>