02.Quartz 环境搭建-基于jdbc-mysql

左手的ㄟ右手 2022-02-20 04:35 409阅读 0赞

笔者前面介绍了quartz 基于内存的环境搭建, 接下来在前面的基础上, 将项目修改为基于jdbc存储的quartz环境. quartz 2.x 共有11张表, 需要事先导入数据库中. quartz 针对常见的数据库均提供了初始化脚本, 位于docs/dbTables目录中.

## 1. quartz 相关表 ##

quartz 默认表前缀为QRTZ\_, quartz 支持修改表前缀. 但是由于quartz默认表名比较长, 因此修改后可能会导致标识符过长的问题. 通常情况下, 我们并不用关注quartz表数据, 更不需要私自操作quartz 表数据. 笔者这里也只介绍几张核心表:

*  QRTZ\_TRIGGERS: 存储所有定时任务的信息
 *  QRTZ\_SIMPLE\_TRIGGERS: 存储SimpleTrigger 触发器的信息
 *  QRTZ\_CRON\_TRIGGERS: 存储CronTrigger 触发器的信息
 *  QRTZ\_JOB\_DETAILS: 存储定时任务的相关信息
 *  QRTZ\_FIRED\_TRIGGERS: 存储已触发的触发器信息
 *  QRTZ\_PAUSED\_TRIGGER\_GRPS: 存储暂停的触发器组信息
 *  QRTZ\_BLOB\_TRIGGERS: 存储二进制触发器信息

## 2. 修改项目 ##

### 2.1 添加数据库驱动依赖 ###

<dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.38</version>
    </dependency>

### 2.2 创建配置文件:quartz-jdbc.properties ###

*  如果修改了默认的quartz表前缀, 那么需要修改配置:org.quartz.jobStore.tablePrefix
 *   *  测试时将测试类中配置文件名称修改为 quartz-jdbc.properties 即可

org.quartz.scheduler.instanceName = MyScheduler
    
    #============================================================================
    # Configure ThreadPool
    #============================================================================
    
    org.quartz.threadPool.class = org.quartz.simpl.SimpleThreadPool
    org.quartz.threadPool.threadCount = 5
    org.quartz.threadPool.threadPriority = 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.driver=com.mysql.jdbc.Driver
    org.quartz.dataSource.myDS.URL=jdbc:mysql://localhost:3306/learn-quartz?serverTimezone=UTC
    org.quartz.dataSource.myDS.user=root
    org.quartz.dataSource.myDS.password=root
    
    org.quartz.scheduler.dbFailureRetryInterval=1

## 3. mysql 初始化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;