quartz（三）任务持久化-jdbc篇（一看就会）

迈不过友情╰ 2022-11-14 10:29 114阅读 0赞

# 一、背景 #

如果任务执行到一半突然宕机怎么办？能否重新执行任务？任务执行的数据能够以恢复吗？带着这些问题，我发现了几种持久化策略，先讲最简单，也是我们最为喜闻乐见的mysql吧！说干就干，我们来写一个最简单的吧。

# 二、代码实现 #

其实实现起来也很简单，主要步骤如下：

1.  在数据库控制台执行如下sql脚本

#
    # Quartz seems to work best with the driver mm.mysql-2.0.7-bin.jar
    #
    # PLEASE consider using mysql with innodb tables to avoid locking issues
    #
    # In your Quartz properties file, you'll need to set 
    # org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate
    #
    
    DROP TABLE IF EXISTS QRTZ_FIRED_TRIGGERS;
    DROP TABLE IF EXISTS QRTZ_PAUSED_TRIGGER_GRPS;
    DROP TABLE IF EXISTS QRTZ_SCHEDULER_STATE;
    DROP TABLE IF EXISTS QRTZ_LOCKS;
    DROP TABLE IF EXISTS QRTZ_SIMPLE_TRIGGERS;
    DROP TABLE IF EXISTS QRTZ_SIMPROP_TRIGGERS;
    DROP TABLE IF EXISTS QRTZ_CRON_TRIGGERS;
    DROP TABLE IF EXISTS QRTZ_BLOB_TRIGGERS;
    DROP TABLE IF EXISTS QRTZ_TRIGGERS;
    DROP TABLE IF EXISTS QRTZ_JOB_DETAILS;
    DROP TABLE IF EXISTS QRTZ_CALENDARS;
    
    
    CREATE TABLE QRTZ_JOB_DETAILS
      (
        SCHED_NAME VARCHAR(120) NOT NULL,
        JOB_NAME  VARCHAR(200) NOT NULL,
        JOB_GROUP VARCHAR(200) NOT NULL,
        DESCRIPTION VARCHAR(250) NULL,
        JOB_CLASS_NAME   VARCHAR(250) NOT NULL,
        IS_DURABLE VARCHAR(1) NOT NULL,
        IS_NONCONCURRENT VARCHAR(1) NOT NULL,
        IS_UPDATE_DATA VARCHAR(1) NOT NULL,
        REQUESTS_RECOVERY VARCHAR(1) NOT NULL,
        JOB_DATA BLOB NULL,
        PRIMARY KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)
    );
    
    CREATE TABLE QRTZ_TRIGGERS
      (
        SCHED_NAME VARCHAR(120) NOT NULL,
        TRIGGER_NAME VARCHAR(200) NOT NULL,
        TRIGGER_GROUP VARCHAR(200) NOT NULL,
        JOB_NAME  VARCHAR(200) NOT NULL,
        JOB_GROUP VARCHAR(200) NOT NULL,
        DESCRIPTION VARCHAR(250) NULL,
        NEXT_FIRE_TIME BIGINT(13) NULL,
        PREV_FIRE_TIME BIGINT(13) NULL,
        PRIORITY INTEGER NULL,
        TRIGGER_STATE VARCHAR(16) NOT NULL,
        TRIGGER_TYPE VARCHAR(8) NOT NULL,
        START_TIME BIGINT(13) NOT NULL,
        END_TIME BIGINT(13) NULL,
        CALENDAR_NAME VARCHAR(200) NULL,
        MISFIRE_INSTR SMALLINT(2) NULL,
        JOB_DATA BLOB NULL,
        PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
        FOREIGN KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)
            REFERENCES QRTZ_JOB_DETAILS(SCHED_NAME,JOB_NAME,JOB_GROUP)
    );
    
    CREATE TABLE QRTZ_SIMPLE_TRIGGERS
      (
        SCHED_NAME VARCHAR(120) NOT NULL,
        TRIGGER_NAME VARCHAR(200) NOT NULL,
        TRIGGER_GROUP VARCHAR(200) NOT NULL,
        REPEAT_COUNT BIGINT(7) NOT NULL,
        REPEAT_INTERVAL BIGINT(12) NOT NULL,
        TIMES_TRIGGERED BIGINT(10) NOT NULL,
        PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
        FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
            REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
    );
    
    CREATE TABLE QRTZ_CRON_TRIGGERS
      (
        SCHED_NAME VARCHAR(120) NOT NULL,
        TRIGGER_NAME VARCHAR(200) NOT NULL,
        TRIGGER_GROUP VARCHAR(200) NOT NULL,
        CRON_EXPRESSION VARCHAR(200) NOT NULL,
        TIME_ZONE_ID VARCHAR(80),
        PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
        FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
            REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
    );
    
    CREATE TABLE QRTZ_SIMPROP_TRIGGERS
      (          
        SCHED_NAME VARCHAR(120) NOT NULL,
        TRIGGER_NAME VARCHAR(200) NOT NULL,
        TRIGGER_GROUP VARCHAR(200) NOT NULL,
        STR_PROP_1 VARCHAR(512) NULL,
        STR_PROP_2 VARCHAR(512) NULL,
        STR_PROP_3 VARCHAR(512) NULL,
        INT_PROP_1 INT NULL,
        INT_PROP_2 INT NULL,
        LONG_PROP_1 BIGINT NULL,
        LONG_PROP_2 BIGINT NULL,
        DEC_PROP_1 NUMERIC(13,4) NULL,
        DEC_PROP_2 NUMERIC(13,4) NULL,
        BOOL_PROP_1 VARCHAR(1) NULL,
        BOOL_PROP_2 VARCHAR(1) NULL,
        PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
        FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) 
        REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
    );
    
    CREATE TABLE QRTZ_BLOB_TRIGGERS
      (
        SCHED_NAME VARCHAR(120) NOT NULL,
        TRIGGER_NAME VARCHAR(200) NOT NULL,
        TRIGGER_GROUP VARCHAR(200) NOT NULL,
        BLOB_DATA BLOB NULL,
        PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
        FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
            REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
    );
    
    CREATE TABLE QRTZ_CALENDARS
      (
        SCHED_NAME VARCHAR(120) NOT NULL,
        CALENDAR_NAME  VARCHAR(200) NOT NULL,
        CALENDAR BLOB NOT NULL,
        PRIMARY KEY (SCHED_NAME,CALENDAR_NAME)
    );
    
    CREATE TABLE QRTZ_PAUSED_TRIGGER_GRPS
      (
        SCHED_NAME VARCHAR(120) NOT NULL,
        TRIGGER_GROUP  VARCHAR(200) NOT NULL, 
        PRIMARY KEY (SCHED_NAME,TRIGGER_GROUP)
    );
    
    CREATE TABLE QRTZ_FIRED_TRIGGERS
      (
        SCHED_NAME VARCHAR(120) NOT NULL,
        ENTRY_ID VARCHAR(95) NOT NULL,
        TRIGGER_NAME VARCHAR(200) NOT NULL,
        TRIGGER_GROUP VARCHAR(200) NOT NULL,
        INSTANCE_NAME VARCHAR(200) NOT NULL,
        FIRED_TIME BIGINT(13) NOT NULL,
        SCHED_TIME BIGINT(13) NOT NULL,
        PRIORITY INTEGER NOT NULL,
        STATE VARCHAR(16) NOT NULL,
        JOB_NAME VARCHAR(200) NULL,
        JOB_GROUP VARCHAR(200) NULL,
        IS_NONCONCURRENT VARCHAR(1) NULL,
        REQUESTS_RECOVERY VARCHAR(1) NULL,
        PRIMARY KEY (SCHED_NAME,ENTRY_ID)
    );
    
    CREATE TABLE QRTZ_SCHEDULER_STATE
      (
        SCHED_NAME VARCHAR(120) NOT NULL,
        INSTANCE_NAME VARCHAR(200) NOT NULL,
        LAST_CHECKIN_TIME BIGINT(13) NOT NULL,
        CHECKIN_INTERVAL BIGINT(13) NOT NULL,
        PRIMARY KEY (SCHED_NAME,INSTANCE_NAME)
    );
    
    CREATE TABLE QRTZ_LOCKS
      (
        SCHED_NAME VARCHAR(120) NOT NULL,
        LOCK_NAME  VARCHAR(40) NOT NULL, 
        PRIMARY KEY (SCHED_NAME,LOCK_NAME)
    );
    
    
    commit;

1.  pom配置延用[quartz（一）基础篇][quartz]
2.  quartz.properties配置

# 存储类型有三种：1. 内存存储，org.quartz.simpl.RAMJobStore，性能最好，执行最快
    # 2. 数据库存储，org.quartz.impl.jdbcjobstore.JobStoreTX，性能最差，但是能够持久化，可恢复等
    # 3. TerracottaJobStore服务器，性能优于数据库一个数量级，性能居中，可持久化，可恢复
    org.quartz.jobStore.class=org.quartz.impl.jdbcjobstore.JobStoreTX
    # 表名前缀
    org.quartz.jobStore.tablePrefix=QRTZ_
    # 设置数据源名称,可随便配置，建议不改，直接qzDS
    org.quartz.jobStore.dataSource=qzDS
    # 数据库连接信息
    org.quartz.dataSource.qzDS.driver=com.mysql.cj.jdbc.Driver
    org.quartz.dataSource.qzDS.URL=jdbc:mysql://localhost:3306/db?useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true
    org.quartz.dataSource.qzDS.user=root
    org.quartz.dataSource.qzDS.password=123456
    org.quartz.dataSource.qzDS.maxConnections=10
    org.quartz.datasource.qzDS.validateOnCheckout=true
    org.quartz.datasource.qzDS.validationQuery=select 1
    org.quartz.dataSource.qzDS.discardIdleConnectionsSeconds=60
    org.quartz.jobStore.acquireTriggersWithinLock=true
    # quartz连接池设置
    org.quartz.threadPool.class=org.quartz.simpl.SimpleThreadPool
    org.quartz.threadPool.threadCount=5
    org.quartz.threadPool.threadPriority=5
    org.quartz.threadPool.threadsInheritContextClassLoaderOfInitializingThread=true
    org.quartz.jobStore.misfireThreshold=5000

本文是基于[quartz（二）动态增删改查停止启用job][quartz_job]的基础上进行扩展的，有兴趣的可以去看看。

# 三、测试结果 #

## 1、通过接口创建一个job ##

1.  swagger执行接口调用  
    ![image][]
2.  执行日志

2020-12-29 21:37:49.621  INFO 10040 --- [io-8088-exec-10] org.quartz.core.QuartzScheduler          : Scheduler meta-data: Quartz Scheduler (v2.2.1) 'QuartzScheduler' with instanceId 'NON_CLUSTERED'
      Scheduler class: 'org.quartz.core.QuartzScheduler' - running locally.
      NOT STARTED.
      Currently in standby mode.
      Number of jobs executed: 0
      Using thread pool 'org.quartz.simpl.SimpleThreadPool' - with 5 threads.
      Using job-store 'org.quartz.impl.jdbcjobstore.JobStoreTX' - which supports persistence. and is not clustered.
    
    2020-12-29 21:37:49.621  INFO 10040 --- [io-8088-exec-10] org.quartz.impl.StdSchedulerFactory      : Quartz scheduler 'QuartzScheduler' initialized from default resource file in Quartz package: 'quartz.properties'
    2020-12-29 21:37:49.621  INFO 10040 --- [io-8088-exec-10] org.quartz.impl.StdSchedulerFactory      : Quartz scheduler version: 2.2.1
    2020-12-29 21:37:49.651  INFO 10040 --- [io-8088-exec-10] c.m.v.c.i.AbstractPoolBackedDataSource   : Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> 1hge0zqae1ch7y8hlzte6w|2c4e7ebd, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.cj.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 1hge0zqae1ch7y8hlzte6w|2c4e7ebd, idleConnectionTestPeriod -> 0, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://121.36.24.8:3306/db?useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true, lastAcquisitionFailureDefaultUser -> null, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 60, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 10, maxStatements -> 0, maxStatementsPerConnection -> 120, minPoolSize -> 1, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]
    2020-12-29 21:37:50.305  INFO 10040 --- [io-8088-exec-10] org.quartz.impl.jdbcjobstore.JobStoreTX  : Freed 1 triggers from 'acquired' / 'blocked' state.
    2020-12-29 21:37:50.330  INFO 10040 --- [io-8088-exec-10] org.quartz.impl.jdbcjobstore.JobStoreTX  : Handling 1 trigger(s) that missed their scheduled fire-time.
    2020-12-29 21:37:50.569  INFO 10040 --- [io-8088-exec-10] org.quartz.impl.jdbcjobstore.JobStoreTX  : Recovering 0 jobs that were in-progress at the time of the last shut-down.
    2020-12-29 21:37:50.569  INFO 10040 --- [io-8088-exec-10] org.quartz.impl.jdbcjobstore.JobStoreTX  : Recovery complete.
    2020-12-29 21:37:50.594  INFO 10040 --- [io-8088-exec-10] org.quartz.impl.jdbcjobstore.JobStoreTX  : Removed 0 'complete' triggers.
    2020-12-29 21:37:50.619  INFO 10040 --- [io-8088-exec-10] org.quartz.impl.jdbcjobstore.JobStoreTX  : Removed 1 stale fired job entries.
    2020-12-29 21:37:50.670  INFO 10040 --- [io-8088-exec-10] org.quartz.core.QuartzScheduler          : Scheduler QuartzScheduler_$_NON_CLUSTERED started.
    2020-12-29 21:37:50.670  INFO 10040 --- [io-8088-exec-10] c.e.d.service.impl.JobServiceImpl        : 成功创建重复任务!!!
    {name:'ljl'}
    hello world!!!
    {name:'ljl'}
    hello world!!!
    {name:'ljl'}
    hello world!!!
    {name:'ljl'}
    hello world!!!

1.  数据库持久化数据  
    ![jobDetail数据][jobDetail]  
    当然还有trigger的数据，这里就不一一展示了。

[quartz]: https://mp.weixin.qq.com/s/woXJauXj7Q6eJne1GOuAQA
[quartz_job]: https://mp.weixin.qq.com/s/kJKQIAqk4yBc2ot6XzFaaw
[image]: /images/20221022/1013155af8c645f1977637ef4b3cf68f.png
[jobDetail]: /images/20221022/2770eb3d9c704cf88d30b9ab6c341123.png