Subic Project Summary (iii)-Modify quartz default connection pool

Quartz The default is to use the C3P0 connection pool, and remember an article that tested C3P0, Proxool, druid, Tomcat Jdbc Pool, the performance of the four connection pools, roughly given the test data for Druid >tomcat JDBC pool > C3p0 >proxool, above for reference only.

Recently, the project always complains, it is annoying, specific error log:

The last packet successfully received from the server is 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.NativeConstructorAccessorI Mpl.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.J Dbc.
        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.ja va:2615) at Com.mysql.jdbc.MysqlIO.sqlQueryDiRect (mysqlio.java:2776) at Com.mysql.jdbc.ConnectionImpl.execSQL (connectionimpl.java:2832) at COM.MYSQL.JD Bc. 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. $Proxy 89.setAutoCommit (Unknown Source) At Org.quartz.impl.jdbcjobstore.JobStoreSupport.getConnection (jobstoresupport.java:799) at ORG.QUARTZ.IMPL.JD Bcjobstore. Jobstoretx.getnonmanagedtxconnection (jobstoretx.java:71) at Org.quartz.impl.jdbcjobstore.JobStoreSupport.executeInNonManagedTXLock (jobstoresupport.java:3796) ... 2 more

From the error log can be seen, the underlying error is due to quartz, resulting in the database link exception, the first thought is that the database connection is not closed, so the quartz.properties configured automatic shutdown as follows, but after the restart or error.

Org.quartz.datasource.myds.autocommitonclose=true

Tried n methods and changed a lot of configuration parameters, but none worked. This kind of problem does not often appear, occasionally appear, before the restart is good, but today how can not.

Considering the unity and stability of the dispatching center and the platform system, it is necessary to investigate the internal application technology of the data source connection pool and expand the Druid connection pool.

1.Quartz database Connection Pool Technical update of each version
Quartz 2.0 before DBCP
Quartz 2.0 after C3P0 (inclusive 2.0)

2. This project uses Quartz2.2.2, the database connection pool default configuration is as follows
Configuration items in the Quartz.properties file:
Org.quartz.dataSource.myDS (data source name). connectionProvider.class:org.quartz.utils.PoolingConnectionProvider

3. Extended Druid Database Connection pool configuration is adjusted as follows
Org.quartz.dataSource.myDS (data source name). Connectionprovider.class = XXXXX (custom connectionprovider)

4. Custom Druid database connection pool, need to implement Org.quartz.utils.ConnectionProvider interface, and introduce Druid related jar package, code as follows:

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 Connection Pool Quartz extension class * Creator Zhang Zhibong * Create Time July 26, 2016 */public class Druidconnectionprovider implements Ctionprovider {/* * ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ * * Constant with
     , the key to the Quartz.properties file is consistent (remove the prefix), the set method is provided, and the quartz frame is automatically injected with the value. * * ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~///JDBC Drive public String
    Driver
    JDBC Connection string public string URL;
    Database user name public String user;
    Database user password public String password;
    Database maximum connection number public int maxconnection;
    The database SQL query returns each connection to the connection pool to ensure that it is still valid.
    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 Connection pool private Druiddatasource datasource; * * * ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ * * Interface Implementation * * ~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ * * Public Connection getconnection () throws SQLExc
    eption {return datasource.getconnection ();
    public void shutdown () throws SQLException {datasource.close (); The public void Initialize () throws sqlexception{if (this.
        URL = = null) {throw new SQLException ("Dbpool could not being created:db URL cannot be null"); } if (this.driver = = null) {throw new SQLException ("Dbpool driver could not be created:db CLA SS Name cannot be null! ");} if (This.maxconnection < 0) {throw new SQLException ("Dbpool maxconnectins could not be Created:max Connec
        tions 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); The 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);
    }} * * ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ * * provides get Set method
        * * ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~/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;
    The 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.idleconnectionval
    Idationseconds = 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.discardidleconnect
    Ionsseconds = Discardidleconnectionsseconds; }
}

Quartz.properties configuration:

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