java使用druid连接池_Quartz学习笔记(四)使用druid连接池

quartz默认使用的是c3p0的连接池，记得有篇文章测试过c3p0 ,Proxool ,Druid ,Tomcat Jdbc Pool这四种连接池的性能。

大致给出的测试数据为Druid >Tomcat Jdbc Pool >c3p0 >Proxool，以上仅供参考。

最近项目老是报错，甚是让人烦恼，具体错误日志：The last packet successfully received from the server was 56,268 milliseconds ago. The last packet sent successfully to the server was 0 milliseconds ago.

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)

at java.lang.reflect.Constructor.newInstance(Constructor.java:526)

at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)

at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1127)

at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3715)

at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3604)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4155)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2832)

at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:5357)

at com.mchange.v2.c3p0.impl.NewProxyConnection.setAutoCommit(NewProxyConnection.java:756)

at org.quartz.impl.jdbcjobstore.AttributeRestoringConnectionInvocationHandler.setAutoCommit(AttributeRestoringConnectionInvocationHandler.java:98)

at org.quartz.impl.jdbcjobstore.AttributeRestoringConnectionInvocationHandler.invoke(AttributeRestoringConnectionInvocationHandler.java:66)

at com.sun.proxy.$Proxy89.setAutoCommit(Unknown Source)

at org.quartz.impl.jdbcjobstore.JobStoreSupport.getConnection(JobStoreSupport.java:799)

at org.quartz.impl.jdbcjobstore.JobStoreTX.getNonManagedTXConnection(JobStoreTX.java:71)

at org.quartz.impl.jdbcjobstore.JobStoreSupport.executeInNonManagedTXLock(JobStoreSupport.java:3796)

... 2 more

从错误日志可以看出，底层错误是由于quartz引起的，导致数据库链接异常，一开始以为是数据库连接没有关闭导致的，所以quartz.properties中配置了自动关闭如下，但是重启后还是报错。

org.quartz.dataSource.myDS.autoCommitOnClose=true

尝试了N种方法，也修改了很多配置参数，但是都不起作用。这种问题不是经常出现，偶尔出现过，以前重启就好了，但是今天怎么都搞不定。

考虑到调度中心和平台系统的统一性和稳定性，需要深入调研数据源连接池的内部应用技术，同时扩展成为Druid连接池技术。

1.Quartz各版本数据库连接池技术更新情况

Quartz 2.0 以前 DBCP

Quartz 2.0 以后 C3P0(包含2.0)

2.本项目使用Quartz2.2.2,数据库连接池默认配置如下

在quartz.properties文件中的配置项：

org.quartz.dataSource.myDS(数据源名).connectionProvider.class:org.quartz.utils.PoolingConnectionProvider

3.扩展Druid数据库连接池配置调整如下

org.quartz.dataSource.myDS(数据源名).connectionProvider.class = XXXXX(自定义的ConnectionProvider)

4.自定义Druid数据库连接池，需要实现org.quartz.utils.ConnectionProvider接口,同时引入Druid相关的jar包，代码如下：package com.acts.web.common.connection;

import com.alibaba.druid.pool.DruidDataSource;

import org.quartz.SchedulerException;

import java.sql.Connection;

import java.sql.SQLException;

import org.quartz.utils.ConnectionProvider;

/**

* Druid连接池的Quartz扩展类

* 创建者 张志朋

* 创建时间 2016年7月26日

*

*/

public class DruidConnectionProvider implements ConnectionProvider {

/*

* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

*

* 常量配置，与quartz.properties文件的key保持一致(去掉前缀)，同时提供set方法，Quartz框架自动注入值。

*

* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

*/

//JDBC驱动

public String driver;

//JDBC连接串

public String URL;

//数据库用户名

public String user;

//数据库用户密码

public String password;

//数据库最大连接数

public int maxConnection;

//数据库SQL查询每次连接返回执行到连接池，以确保它仍然是有效的。

public String validationQuery;

private boolean validateOnCheckout;

private int idleConnectionValidationSeconds;

public String maxCachedStatementsPerConnection;

private String discardIdleConnectionsSeconds;

public static final int DEFAULT_DB_MAX_CONNECTIONS = 10;

public static final int DEFAULT_DB_MAX_CACHED_STATEMENTS_PER_CONNECTION = 120;

//Druid连接池

private DruidDataSource datasource;

/*

* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

*

* 接口实现

*

* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

*/

public Connection getConnection() throws SQLException {

return datasource.getConnection();

}

public void shutdown() throws SQLException {

datasource.close();

}

public void initialize() throws SQLException{

if (this.URL == null) {

throw new SQLException("DBPool could not be created: DB URL cannot be null");

}

if (this.driver == null) {

throw new SQLException("DBPool driver could not be created: DB driver class name cannot be null!");

}

if (this.maxConnection < 0) {

throw new SQLException("DBPool maxConnectins could not be created: Max connections must be greater than zero!");

}

datasource = new DruidDataSource();

try{

datasource.setDriverClassName(this.driver);

} catch (Exception e) {

try {

throw new SchedulerException("Problem setting driver class name on datasource: " + e.getMessage(), e);

} catch (SchedulerException e1) {

}

}

datasource.setUrl(this.URL);

datasource.setUsername(this.user);

datasource.setPassword(this.password);

datasource.setMaxActive(this.maxConnection);

datasource.setMinIdle(1);

datasource.setMaxWait(0);

datasource.setMaxPoolPreparedStatementPerConnectionSize(DEFAULT_DB_MAX_CONNECTIONS);

if (this.validationQuery != null) {

datasource.setValidationQuery(this.validationQuery);

if(!this.validateOnCheckout)

datasource.setTestOnReturn(true);

else

datasource.setTestOnBorrow(true);

datasource.setValidationQueryTimeout(this.idleConnectionValidationSeconds);

}

}

/*

* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

*

* 提供get set方法

*

* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

*/

public String getDriver() {

return driver;

}

public void setDriver(String driver) {

this.driver = driver;

}

public String getURL() {

return URL;

}

public void setURL(String URL) {

this.URL = URL;

}

public String getUser() {

return user;

}

public void setUser(String user) {

this.user = user;

}

public String getPassword() {

return password;

}

public void setPassword(String password) {

this.password = password;

}

public int getMaxConnection() {

return maxConnection;

}

public void setMaxConnection(int maxConnection) {

this.maxConnection = maxConnection;

}

public String getValidationQuery() {

return validationQuery;

}

public void setValidationQuery(String validationQuery) {

this.validationQuery = validationQuery;

}

public boolean isValidateOnCheckout() {

return validateOnCheckout;

}

public void setValidateOnCheckout(boolean validateOnCheckout) {

this.validateOnCheckout = validateOnCheckout;

}

public int getIdleConnectionValidationSeconds() {

return idleConnectionValidationSeconds;

}

public void setIdleConnectionValidationSeconds(int idleConnectionValidationSeconds) {

this.idleConnectionValidationSeconds = idleConnectionValidationSeconds;

}

public DruidDataSource getDatasource() {

return datasource;

}

public void setDatasource(DruidDataSource datasource) {

this.datasource = datasource;

}

public String getDiscardIdleConnectionsSeconds() {

return discardIdleConnectionsSeconds;

}

public void setDiscardIdleConnectionsSeconds(String discardIdleConnectionsSeconds) {

this.discardIdleConnectionsSeconds = discardIdleConnectionsSeconds;

}

}

quartz.properties配置：org.quartz.scheduler.instanceName = MyScheduler

org.quartz.threadPool.threadCount = 5

org.quartz.jobStore.class = org.quartz.impl.jdbcjobstore.JobStoreTX

org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate

org.quartz.jobStore.tablePrefix = QRTZ_

org.quartz.jobStore.dataSource = myDS

org.quartz.dataSource.myDS.connectionProvider.class:com.acts.web.common.connection.DruidConnectionProvider

org.quartz.dataSource.myDS.driver = com.mysql.jdbc.Driver

org.quartz.dataSource.myDS.URL = jdbc:mysql://localhost:3306/acts_manage_alpha?characterEncoding=utf-8

org.quartz.dataSource.myDS.user = root

org.quartz.dataSource.myDS.password = 123

org.quartz.dataSource.myDS.maxConnection = 5