Quartz数据库存储

最新推荐文章于 2024-05-29 22:00:47 发布

葱姜麻麻

最新推荐文章于 2024-05-29 22:00:47 发布

阅读量1.7k

点赞数

文章标签：数据库 database spring boot spring mysql

本文链接：https://blog.csdn.net/weixin_52639224/article/details/122883868

版权

一、数据库解析

1.新建spring项目，勾选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.模拟数据

注意：job_name属性内容应该是任务类的路径

二、工具类解析

1.导入配置文件（使用数据库存储）

#
#============================================================================
# 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

2.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.sjy</groupId>
    <artifactId>demo3</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>demo3</name>
    <description>Demo project for Spring Boot</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.sjy.demo3.Demo3Application</mainClass>
                </configuration>
                <executions>
                    <execution>
                        <id>repackage</id>
                        <goals>
                            <goal>repackage</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

</project>

3.导入拓展类DruidConnectionProvider

作用：帮助quartz创建连接池

package com.sjy.demo3.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扩展类
 */
@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);
        }
    }

}

4.导入MyJobFactory类

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

package com.sjy.demo3.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;


@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);
    }

}

5.导入QuartzConfiguration 类

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

package com.sjy.demo3.conf;

import com.sjy.demo3.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 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.实体类

package com.sjy.demo3.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;

}

package com.sjy.demo3.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_data")
public class ScheduleTriggerData {

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

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

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

    @Column(name = "trigger_id")
    private Long triggerId;

}

2.mapper层

继承通用mapper

3.service层

ScheduleTrigger查询全部的方法

ScheduleTriggerData通过ScheduleTrigger的id查询

4.实现类

由于没有根据外键查询的方法，所以要用到example

5.创建类quartzTask

为了检查我的数据库表是否变化，然后同步到quartz表中去

package com.sjy.demo3.util;

import com.sjy.demo3.MyJob;
import com.sjy.demo3.pojo.ScheduleTrigger;
import com.sjy.demo3.pojo.ScheduleTriggerData;
import com.sjy.demo3.service.ScheduleTriggerDataService;
import com.sjy.demo3.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;

/**
 * @author 小宝的宝
 */
@Component
public class QuartzTask {

    private ScheduleTriggerService scheduleTriggerService;
    private ScheduleTriggerDataService scheduleTriggerDataService;
    private Scheduler scheduler;

    @Autowired
    public QuartzTask(ScheduleTriggerService scheduleTriggerService, ScheduleTriggerDataService scheduleTriggerDataService, Scheduler scheduler) {
        this.scheduleTriggerService = scheduleTriggerService;
        this.scheduleTriggerDataService = scheduleTriggerDataService;
        this.scheduler = scheduler;
    }

    @Scheduled(cron = "0/10 * * * * ?")
    @SneakyThrows
    public void task(){
        //遍历数据库中所有的任务
        for (ScheduleTrigger t : scheduleTriggerService.selectAll()) {
            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 : scheduleTriggerDataService.selectbyid(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);
            }

        }


    }
}

6.运行结果

quartz表

葱姜麻麻

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Quartz数据库存储

一、数据库解析1.新建spring项目，勾选quartz scheduler2.进入官网下载quartz内置表3.找到table_mysql的脚本在idea里面运行4.运行sql脚本create table t_schedule_trigger( id bigint primary key auto_increment comment '触发器编号', cron varchar(200) not null comm...
复制链接

扫一扫