定时任务框架Quartz(二)之数据库储存

目录

一、数据库解析

        1、先创建一个springBoot项目，勾选quartz scheduler​

        2、进入官网下载quartz内置表，进行测试的表

        3、找到table_mysql的脚本在idea里面运行

​        4、运行sql脚本，新生成两个数据库表

        5、新增模拟数据，进行测试

二、工具类分析

       1、导入pom依赖

       2、导入使用数据库存储的配置文件：

       3、导入相关类：

三、准备工作

         1、先创建实体类：

        2、新建mapper类：

        3、service层：

四、数据库存储开发

        创建一个QuartzTask类：

---

今天的任务是继Quartz01内存存储之后的数据库存储

一、数据库解析

        1、先创建一个springBoot项目，勾选quartz scheduler

        2、进入官网下载quartz内置表，进行测试的表

将以下压缩包进行解压，然后将数据库导入到项目中即可

        3、找到table_mysql的脚本在idea里面运行

运行之后的数据库： 

        4、运行sql脚本，新生成两个数据库表

这两个是额外生成，不是运行以上文件就有的

create table t_schedule_trigger
(
    id          bigint primary key auto_increment comment '触发器编号',
    cron        varchar(200) not null comment '触发器表达式',
    status      char(1)          not null comment '触发器状态: 禁用 启用',
    job_name    varchar(200) not null comment '任务名称: 存放的任务类的全路径',
    job_group   varchar(200) not null comment '任务所处分组',
    job_description varchar(200) not null comment '任务描述',
    trigger_description varchar(200) not null comment '触发器描述',
    unique index (job_name, job_group) comment '通过jobName和jobGroup来确定trigger的唯一性，所以这两列为联合唯一索引'
);

create table t_schedule_trigger_data
(
    id         bigint primary key auto_increment comment '数据编号',
    name       varchar(200) not null comment '对应的数据名称',
    value      varchar(512) comment '对应的数据值',
    trigger_id bigint       not null comment '外键: 引用t_schedule_trigger(id)',
    foreign key (trigger_id) references t_schedule_trigger (id)
);
 

这是新生成的两个表 

        5、新增模拟数据，进行测试

 t_schedule_trigger：

t_schedule_trigger_data：

注意这里的两张表的关系是：一对多的关系，其中与我们这次做测试有关的数据库表是以下这些

 以上三张是quartz内部中的数据库表，下面是调度器相关表 

二、工具类分析

          在这次开发中，由于Quartz不与spring框架相容，必须导入工具类

       1、导入pom依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.ZJ</groupId>
    <artifactId>code</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>Quartz_02</name>
    <description>Quartz_02</description>

    <properties>
        <java.version>1.8</java.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <spring-boot.version>2.4.1</spring-boot.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-quartz</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-freemarker</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.8</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>tk.mybatis</groupId>
            <artifactId>mapper-spring-boot-starter</artifactId>
            <version>2.0.2</version>
        </dependency>
        <dependency>
            <groupId>javax.persistence</groupId>
            <artifactId>persistence-api</artifactId>
            <version>1.0</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
    </dependencies>

    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-dependencies</artifactId>
                <version>${spring-boot.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <version>2.4.1</version>
                <configuration>
                    <mainClass>com.zj.code.Quartz02Application</mainClass>
                </configuration>
                <executions>
                    <execution>
                        <id>repackage</id>
                        <goals>
                            <goal>repackage</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

</project>

       2、导入使用数据库存储的配置文件：

quartz.properties:

#
#============================================================================
# Configure Main Scheduler Properties \u8C03\u5EA6\u5668\u5C5E\u6027
#============================================================================
org.quartz.scheduler.instanceName:DefaultQuartzScheduler
org.quartz.scheduler.instanceId=AUTO
org.quartz.scheduler.rmi.export:false
org.quartz.scheduler.rmi.proxy:false
org.quartz.scheduler.wrapJobExecutionInUserTransaction:false
org.quartz.threadPool.class:org.quartz.simpl.SimpleThreadPool
org.quartz.threadPool.threadCount=10
org.quartz.threadPool.threadPriority:5
org.quartz.threadPool.threadsInheritContextClassLoaderOfInitializingThread:true
org.quartz.jobStore.misfireThreshold:60000
#============================================================================
# Configure JobStore
#============================================================================
#\u5B58\u50A8\u65B9\u5F0F\u4F7F\u7528JobStoreTX,\u4E5F\u5C31\u662F\u6570\u636E\u5E93
org.quartz.jobStore.class:org.quartz.impl.jdbcjobstore.JobStoreTX
org.quartz.jobStore.driverDelegateClass:org.quartz.impl.jdbcjobstore.StdJDBCDelegate
#\u4F7F\u7528\u81EA\u5DF1,\u7684\u914D\u7F6E\u6587\u4EF6
org.quartz.jobStore.useProperties:true
#\u6570\u636E\u5E93\u4E2Dquartz\u8868\u7684\u8868\u540D\u524D\u7F00
org.quartz.jobStore.tablePrefix:qrtz_
org.quartz.jobStore.dataSource:qzDS
#\u662F\u5426\u4F7F\u7528\u96C6\u7FA4(\u5982\u679C\u9879\u76EE\u53EA\u90E8\u7F72\u5230 \u4E00\u53F0\u670D\u52A1\u5668,\u5C31\u4E0D\u7528\u4E86)
org.quartz.jobStore.isClustered=true
#============================================================================
# Configure Datasources
#============================================================================
#\u914D\u7F6E\u6570\u636E\u5E93\u6E90\uFF08org.quartz.dataSource.qzDS.maxConnections: c3p0\u914D\u7F6E\u7684\u662F\u6709s\u7684,druid\u6570\u636E\u6E90\u6CA1\u6709s\uFF09
org.quartz.dataSource.qzDS.connectionProvider.class:com.yk.code.util.DruidConnectionProvider
org.quartz.dataSource.qzDS.driver:com.mysql.cj.jdbc.Driver
org.quartz.dataSource.qzDS.URL:jdbc:mysql://127.0.0.1:3306/boot?useUnicode=true&serverTimezone=Asia/Shanghai&useSSL=false&characterEncoding=utf-8
org.quartz.dataSource.qzDS.user:root
org.quartz.dataSource.qzDS.password:root123
org.quartz.dataSource.qzDS.maxConnection:10

       3、导入相关类：

DruidConnectionProvider：

作用：帮助quartz创建druid连接池

package com.zj.code.util;

import com.alibaba.druid.pool.DruidDataSource;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.SneakyThrows;
import org.quartz.utils.ConnectionProvider;

import java.sql.Connection;

/*
#============================================================================
# JDBC
#============================================================================
org.quartz.jobqzDS.connectionProvider.class:com.zking.q03.quartz.DruidConnectionProvider
org.quartz.dataSourStore.driverDelegateClass:org.quartz.impl.jdbcjobstore.StdJDBCDelegate
org.quartz.jobStore.useProperties:false
org.quartz.jobStore.dataSource:qzDS
#org.quartz.dataSource.qzDS.connectionProvider.class:org.quartz.utils.PoolingConnectionProvider
org.quartz.dataSource.ce.qzDS.driver:com.mysql.jdbc.Driver
org.quartz.dataSource.qzDS.URL:jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8
org.quartz.dataSource.qzDS.user:root
org.quartz.dataSource.qzDS.password:root
org.quartz.dataSource.qzDS.maxConnections:30
org.quartz.dataSource.qzDS.validationQuery: select 0
*/

/**
 * Druid连接池的Quartz扩展类
 *
 * @author hgh
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
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;

    @Override
    @SneakyThrows
    public Connection getConnection() {
        return datasource.getConnection();
    }

    @Override
    public void shutdown() {
        datasource.close();
    }

    @Override
    @SneakyThrows
    public void initialize() {
        assert this.URL != null : "DB URL cannot be null";
        assert this.driver != null : "DB driver class name cannot be null!";
        assert this.maxConnection > 0 : "Max connections must be greater than zero!";

        datasource = new DruidDataSource();
        datasource.setDriverClassName(this.driver);
        datasource.setUrl(this.URL);
        datasource.setUsername(this.user);
        datasource.setPassword(this.password);
        datasource.setMaxActive(this.maxConnection);
        datasource.setMinIdle(1);
        datasource.setMaxWait(0);
        datasource.setMaxPoolPreparedStatementPerConnectionSize(DruidConnectionProvider.DEFAULT_DB_MAX_CACHED_STATEMENTS_PER_CONNECTION);
        if (this.validationQuery != null) {
            datasource.setValidationQuery(this.validationQuery);
            if (!this.validateOnCheckout) {
                datasource.setTestOnReturn(true);
            } else {
                datasource.setTestOnBorrow(true);
            }
            datasource.setValidationQueryTimeout(this.idleConnectionValidationSeconds);
        }
    }

}

MyJobFactory：

作用：使quartz具备与spring交互的能力，

package com.zj.code.util;

import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.quartz.spi.TriggerFiredBundle;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.config.AutowireCapableBeanFactory;
import org.springframework.scheduling.quartz.AdaptableJobFactory;
import org.springframework.stereotype.Component;

/**
 * @author hgh
 */
@Component
@Slf4j
public class MyJobFactory extends AdaptableJobFactory {

    private final AutowireCapableBeanFactory autowireCapableBeanFactory;

    @Autowired
    public MyJobFactory(AutowireCapableBeanFactory autowireCapableBeanFactory) {
        this.autowireCapableBeanFactory = autowireCapableBeanFactory;
    }

    /**
     * 重写创建Job任务的实例方法,解决Job任务无法使用Spring中的Bean问题
     */
    @Override
    @SneakyThrows
    protected Object createJobInstance(TriggerFiredBundle bundle) {
        Object jobInstance = super.createJobInstance(bundle);
        autowireCapableBeanFactory.autowireBean(jobInstance);
        return super.createJobInstance(bundle);
    }

}

QuartzConfiguration： 

作用：将调度器交给spring进行管理，以后在别的类中需要调度器，只需要注入就可以了

package com.zj.code.conf;

import com.zj.code.util.MyJobFactory;
import lombok.SneakyThrows;
import org.quartz.Scheduler;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.config.PropertiesFactoryBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.ClassPathResource;
import org.springframework.scheduling.quartz.SchedulerFactoryBean;

import java.util.Properties;

/**
 * @author hgh
 */
@Configuration
public class QuartzConfiguration {

    private final MyJobFactory myJobFactory;

    @Autowired
    public QuartzConfiguration(MyJobFactory myJobFactory) {
        this.myJobFactory = myJobFactory;
    }

    /**
     * 创建调度器工厂
     */
    @Bean
    public SchedulerFactoryBean schedulerFactoryBean() {
        //1.创建SchedulerFactoryBean
        SchedulerFactoryBean factoryBean = new SchedulerFactoryBean();
        //2.加载自定义的quartz.properties配置文件
        factoryBean.setQuartzProperties(quartzProperties());
        //3.设置MyJobFactory
        factoryBean.setJobFactory(myJobFactory);
        return factoryBean;
    }

    @Bean
    @SneakyThrows
    public Properties quartzProperties() {
        PropertiesFactoryBean propertiesFactoryBean = new PropertiesFactoryBean();
        propertiesFactoryBean.setLocation(new ClassPathResource("/quartz.properties"));
        propertiesFactoryBean.afterPropertiesSet();
        return propertiesFactoryBean.getObject();
    }

    @Bean
    public Scheduler scheduler() {
        return schedulerFactoryBean().getScheduler();
    }

}

三、准备工作

         1、先创建实体类：

Sched        uleTrigger：

package com.zj.code.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;
import tk.mybatis.mapper.annotation.KeySql;

import javax.persistence.Column;
import javax.persistence.Id;
import javax.persistence.Table;

/**
 * @author hgh
 */
@AllArgsConstructor
@NoArgsConstructor
@Data
@Accessors(chain = true)
@Table(name = "t_schedule_trigger")
public class ScheduleTrigger {

    @Id
    @KeySql(useGeneratedKeys = true)
    @Column(name = "id")
    private Long id;

    @Column(name = "cron")
    private String cron;

    @Column(name = "status")
    private String status;

    @Column(name = "job_name")
    private String jobName;

    @Column(name = "job_group")
    private String jobGroup;

    @Column(name = "job_description")
    private String jobDescription;


    @Column(name = "trigger_description")
    private String triggerDescription;

}

ScheduleTriggerData：

package com.zj.code.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;
import tk.mybatis.mapper.annotation.KeySql;

import javax.persistence.Column;
import javax.persistence.Id;
import javax.persistence.Table;

/**
 * @author hgh
 */
@AllArgsConstructor
@NoArgsConstructor
@Data
@Accessors(chain = true)
@Table(name = "t_schedule_trigger")
public class ScheduleTrigger {

    @Id
    @KeySql(useGeneratedKeys = true)
    @Column(name = "id")
    private Long id;

    @Column(name = "cron")
    private String cron;

    @Column(name = "status")
    private String status;

    @Column(name = "job_name")
    private String jobName;

    @Column(name = "job_group")
    private String jobGroup;

    @Column(name = "job_description")
    private String jobDescription;


    @Column(name = "trigger_description")
    private String triggerDescription;

}

        2、新建mapper类：

ScheduleTriggerMapper：

package com.zj.code.mapper;

import com.zj.code.pojo.ScheduleTrigger;
import org.springframework.stereotype.Repository;
import tk.mybatis.mapper.common.Mapper;


@Repository
public interface ScheduleTriggerMapper extends Mapper<ScheduleTrigger> {



}

ScheduleTriggerDataMapper：

package com.zj.code.mapper;

import com.zj.code.pojo.ScheduleTrigger;
import com.zj.code.pojo.ScheduleTriggerData;
import org.springframework.stereotype.Repository;
import tk.mybatis.mapper.common.Mapper;


@Repository
public interface ScheduleTriggerDataMapper extends Mapper<ScheduleTriggerData> {
}

        3、service层：

ScheduleTriggerDataService：

package com.zj.code.service;

import com.zj.code.pojo.ScheduleTriggerData;
import org.apache.catalina.LifecycleState;

import java.util.List;

public interface ScheduleTriggerDataService {


    List<ScheduleTriggerData> find(Long triggerId);



}

ScheduleTriggerDataServiceImpl：

package com.zj.code.service.impl;

import com.zj.code.mapper.ScheduleTriggerDataMapper;
import com.zj.code.pojo.ScheduleTriggerData;
import com.zj.code.service.ScheduleTriggerDataService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import tk.mybatis.mapper.entity.Example;

import java.util.List;



@Service
public class ScheduleTriggerDataServiceImpl implements ScheduleTriggerDataService {

    @Autowired
    private ScheduleTriggerDataMapper mapper;


    @Override
    public List<ScheduleTriggerData> find(Long triggerId) {
        Example example=new Example(ScheduleTriggerData.class);
        example.createCriteria().andEqualTo("triggerId",triggerId);
        return mapper.selectByExample(triggerId);
    }
}

这里要注意的是：没有根据外键查询的方法所以就得用example


ScheduleTriggerService：

package com.zj.code.service;

import com.zj.code.pojo.ScheduleTrigger;

import java.util.List;

public interface ScheduleTriggerService {

    List<ScheduleTrigger> find();


}

ScheduleTriggerServiceImpl：

package com.zj.code.service.impl;

import com.zj.code.mapper.ScheduleTriggerMapper;
import com.zj.code.pojo.ScheduleTrigger;
import com.zj.code.service.ScheduleTriggerService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;


@Service
public class ScheduleTriggerServiceImpl implements ScheduleTriggerService {


    @Autowired
    private ScheduleTriggerMapper mapper;


    @Override
    public List<ScheduleTrigger> find() {
        return mapper.selectAll();
    }
}

四、数据库存储开发

        创建一个QuartzTask类：

目的：将schdule中的数据写入到quartz表中去：

package com.zj.code.util;


import com.zj.code.pojo.ScheduleTrigger;
import com.zj.code.pojo.ScheduleTriggerData;
import com.zj.code.service.ScheduleTriggerDataService;
import com.zj.code.service.ScheduleTriggerService;
import lombok.SneakyThrows;
import org.quartz.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

import java.util.Date;

import static org.quartz.JobBuilder.newJob;

@Component
public class QuartzTask {


    private ScheduleTriggerService triggerService;
    private ScheduleTriggerDataService dataService;
    private Scheduler scheduler;

    public QuartzTask(ScheduleTriggerService triggerService, ScheduleTriggerDataService dataService, Scheduler scheduler) {
        this.triggerService = triggerService;
        this.dataService = dataService;
        this.scheduler = scheduler;
    }

    @Autowired


    @Scheduled(cron = "0/10 * * * * ?")
    @SneakyThrows
    public void task() {
        //遍历数据库中所有的任务
        for (ScheduleTrigger t : triggerService.find()) {
            String jobName = t.getJobName();
            String jobGroup = t.getJobGroup();
            String triggerDescription = t.getTriggerDescription();
            String jobDescription = t.getJobDescription();
            String cron = t.getCron();
            CronTrigger trigger = (CronTrigger) scheduler.getTrigger(TriggerKey.triggerKey(jobName, jobGroup));

            //没有被quartz管理
            if (trigger == null) {

                //被禁用
                if ("0".equals(t.getStatus())) {
                    continue;
                }
                //新建出来的，将任务放到quartz中
                JobDetail jobDetail = newJob((Class<? extends Job>) Class.forName(jobName))
                        .withDescription(jobDescription)
                        .withIdentity(jobName, jobGroup)
                        .build();
                //读取任务所需要的数据
                for (ScheduleTriggerData data : dataService.find(t.getId())) {
                    //将任务数据放到任务中
                    jobDetail.getJobDataMap().put(data.getName(), data.getValue());
                }
                //创建触发器
                trigger = TriggerBuilder.newTrigger()
                        .withDescription(triggerDescription)
                        .withIdentity(jobName, jobGroup)
                        .startAt(new Date())
                        .withSchedule(
                                CronScheduleBuilder.cronSchedule(cron)
                        )
                        .build();
                scheduler.scheduleJob(jobDetail, trigger);
                continue;
            }

            //存在于quartz中但是禁用了
            if ("0".equals(t.getStatus())) {
                scheduler.deleteJob(JobKey.jobKey(jobName, jobGroup));
                continue;
            }
            //是否被修改  假设cron表达式被修改
            if (!trigger.getCronExpression().equals(cron)) {
                trigger = TriggerBuilder.newTrigger()
                        .withDescription(triggerDescription)
                        .withIdentity(jobName, jobGroup)
                        .startAt(new Date())
                        .withSchedule(
                                CronScheduleBuilder.cronSchedule(cron)
                        )
                        .build();
                //让调度器更换触发器
                scheduler.rescheduleJob(TriggerKey.triggerKey(jobName, jobGroup), trigger);
            }

        }


    }


}

今天主要是从内存方式的存储转变为数据库存储，今天的知识就分享到这了，希望能够帮助到你