数据库连接池从c3p0迁移到druid

Git: https://github.com/alibaba/druid  

Druid.wiki: https://github.com/alibaba/druid/wiki

FAQ: https://github.com/alibaba/druid/wiki/FAQ

1. 加入druid.jar

<!-- alibaba database connection pool druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.5</version>
        </dependency>

附：http://mvnrepository.com/artifact/com.alibaba/druid 

       http://repo1.maven.org/maven2/com/alibaba/druid/

2. 配置druid

方式一：使用spring

   <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
    ... ...
	<!-- https://github.com/alibaba/druid/wiki/DruidDataSource%E9%85%8D%E7%BD%AE -->
  </bean>

方式二：自己动手

由于多个项目共同使用一个jdbc，这里加入的是兼容c3p0的基于静态代理模式修改的一种设计模式，此种方式下可以不用修改原有的数据源连接，只需增加新的配置文件即可

原有的jdbc是通过Database.class获取连接ConnectionManager

public class Database {
	// ........
	private static ConnectionManager conManager = ConnectionManager.getInstance();
	
	public static Connection getConnection() throws SQLException {
		Connection localConnection = b.getConnection();
		localConnection.setAutoCommit(false);
		return localConnection;
	}
	// ........
}

并且在spring的配置

<bean id="connectionManager" class="com.longdai.data.ConnectionManager" />
	<bean id="baseService" class="com.gozap.base.BaseService" abstract="true">
		<property name="connectionManager" ref="connectionManager" />
	</bean>

以上的spring配置是多个项目中均使用的（虽然所有项目公用jdbc，但目前并不是所有的项目均使用druid连接池，固需要兼容c3p0）。这里可以把ConnectionManager修改成Factory, 只需修改ConnectionManager.getInstance() 的实现即可

加入Connection接口 ConnectionManagerInterface.java

public interface ConnectionManagerInterface {
    public Connection getConnection() throws SQLException;
}

c3p0和druid的connect均实现ConnectionManagerInterface的接口，利用ConnectionManager代理获取链接

ConnectionManagerC3P0.java

public class ConnectionManagerC3P0 implements ConnectionManagerInterface {

	private static ConnectionManagerC3P0 instance;
	private ComboPooledDataSource b = new ComboPooledDataSource();
	
	public ConnectionManagerC3P0() {}
	
	public static final ConnectionManagerC3P0 getInstance() {
        if (instance == null)
            try {
                instance = new ConnectionManagerC3P0();
            } catch (Exception e) {
                e.printStackTrace();
                log.error(e);
            }
        return instance;
    }
	
	public final synchronized Connection getConnection() throws SQLException {
		int i = 0;
		Connection connection = null;
		while (i < 3 && connection == null) {
			try {
				connection = this.b.getConnection();
				return connection;
			} catch (SQLException e) {
				e.printStackTrace();
				log.error(e);
				if (i == 2) {
					throw e;
				}
			}
			i++;
		}
		return connection;
	}
	
	protected void finalize() throws Throwable {
		DataSources.destroy(this.b);
		super.finalize();
	}

}

ConnectionManagerDruid.java  

public class ConnectionManagerDruid implements ConnectionManagerInterface {

	public static final Log log = LogFactory.getLog(ConnectionManagerDruid.class);
    private static String DRUID_CONFIG = "druid.xml";  // druid.properties
	private static ConnectionManagerDruid instance;

    private static DruidDataSource dataSource = new DruidDataSource();
    static {

    }

    public ConnectionManagerDruid(){

        try {
            String resource = Thread.currentThread().getContextClassLoader().getResource("").toString().substring(6)
                    .replace("%20", " ")
                    + DRUID_CONFIG;
            String resourceName = URLDecoder.decode(resource, "utf-8");

            File pFile = new File(resourceName);
            if(!pFile.exists()){
                throw new FileNotFoundException("can not find druid properties file, please check : "+resourceName);
            }
            InputStream is = new FileInputStream(pFile);
            Properties prop = new Properties();
            prop.loadFromXML(is);

            log.info("ConnectionManagerDruid()  loaded properties @ " + resourceName);

            DruidDataSourceFactory.config(dataSource, prop);
            //log.info("username: "+dataSource.getUsername());

        } catch (Exception e) {
            e.printStackTrace();
            log.error(e);
        }
    }

    public static final ConnectionManagerDruid getInstance() {
        if (instance == null)
            try {
                instance = new ConnectionManagerDruid();
            } catch (Exception e) {
                e.printStackTrace();
                log.error(e);
            }
        return instance;
    }

	public final synchronized Connection getConnection() throws SQLException {
		int i = 0;
		Connection connection = null;
		while (i < 3 && connection == null) {
			try {
				connection = this.dataSource.getConnection();
				return connection;
			} catch (SQLException e) {
				e.printStackTrace();
				log.error(e);
				if (i == 2) {
					throw e;
				}
			}
			i++;
		}
		return connection;

	}

	protected void finalize() throws Throwable {
		super.finalize();
	}

	public String getConnectionUser() {
		return this.dataSource.getUsername();
	}

}

接下来利用ConnectionManager制造产品

public class ConnectionManager  implements  ConnectionManagerInterface{

	public static final Log log = LogFactory.getLog(ConnectionManager.class);
    public static ConnectionManager instance = null;
    public static ConnectionManagerInterface comInstance = null;
    public static String owner = "";
    public static boolean outputStatementToLogger = false;
    public static String jdbcUrl = "";

    private static String DRUID_CONFIG = "druid.xml";  // druid.properties

    private  ConnectionManager(){}

    public static final ConnectionManager getInstance()
    {
        if(instance==null){
            instance = new ConnectionManager();
        }
        if (comInstance == null)
            try {
                String resource = Thread.currentThread().getContextClassLoader().getResource("").toString().substring(6)
                        .replace("%20", " ")
                        + DRUID_CONFIG;
                String resourceName = URLDecoder.decode(resource, "utf-8");

                File pFile = new File(resourceName);
                if(!pFile.exists()){
					//druid的配置文件不存在，使用原有的c3p0
                    comInstance = new ConnectionManagerC3P0();// c3p0 database connection pool
                }else{
                    try{
                        InputStream is = new FileInputStream(pFile);
                        Properties prop = new Properties();
                        prop.loadFromXML(is);
                        String value = prop.getProperty("enable", "false");
                        if(value!=null && value.trim().equals("true")){
                            comInstance = new ConnectionManagerDruid(); // Druid database connection pool
                        }
                    }catch(Exception e){
                        e.printStackTrace();
                    }
                }
                //use default ConnnectionManager
                if(comInstance==null){
                    comInstance = new ConnectionManagerC3P0();
                }
                log.info("ConnectionManager using :" +comInstance.getClass());

            } catch (Exception e) {
                e.printStackTrace();
                log.error(e);
            }
        return instance;
    }

    public Connection getConnection() throws SQLException{
        return comInstance.getConnection();
    }

}

附：

druid.xml

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
    <!-- https://github.com/alibaba/druid  -->
    <entry key="enable">true</entry>

    <entry key="url">${env.mysql.url}</entry>
    <entry key="username">${env.mysql.username}</entry>
    <entry key="password">${env.mysql.password}</entry>
    <!-- #初始化时建立物理连接的个数。初始化发生在显示调用init方法，或者第一次getConnection时,default=0 -->
    <entry key="initialSize">3</entry>
    <!-- #最大连接池数量,default=8 -->
    <entry key="maxActive">100</entry>
    <!-- #最小连接池数量 -->
    <entry key="minIdle">3</entry>
    <!-- #获取连接时最大等待时间，单位毫秒。配置了maxWait之后，缺省启用公平锁，并发效率会有所下降，如果需要可以通过配置useUnfairLock属性为true使用非公平锁 -->
    <entry key="maxWait">30000</entry>
    <!-- #是否缓存preparedStatement，也就是PSCache。PSCache对支持游标的数据库性能提升巨大，比如说oracle。在mysql下建议关闭,default=false -->
    <entry key="poolPreparedStatements">false</entry>
    <!-- #要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为true。在Druid中，不会存在Oracle下PSCache占用内存过多的问题，可以把这个数值配置大一些，比如说100 -->
    <entry key="maxOpenPreparedStatements">-1</entry>
    <!-- #属性类型是字符串，通过别名的方式配置扩展插件，常用的插件有：
         # 监控统计用的filter:stat 日志用的filter:log4j 防御sql注入的filter:wall -->
    <entry key="filters">stat,log4j</entry>
</properties>

至此使用完成了druid的配置，在响应的项目中就可以使用了。同时，原有的一些就项目可以原封不动的继续使用c3p0

另外：使用druid的监控功能

1.在配置文件中加入druid.xml

<entry key="filters">stat,log4j</entry>

或者druid.properties

filters=stat,log4j

web.xml

    <servlet-mapping>
        <servlet-name>DruidStatView</servlet-name>
        <url-pattern>/druid.stat/*</url-pattern>
    </servlet-mapping>

访问: http://ip:port/druid.stat/index.html ， 如果druid.stat没有被struts2拦截掉，就可以正常显示统计页面了，

更多的统计配置详看： https://github.com/alibaba/druid/wiki/FAQ

 本文原始地址： http://blog.csdn.net/lanmo555/article/details/39992423