Quartz使用切换数据库源运行定时任务以及注意事项

最新推荐文章于 2024-05-21 11:50:19 发布

梦寒君

最新推荐文章于 2024-05-21 11:50:19 发布

阅读量1.4k

点赞数 1

文章标签：数据库 java

本文链接：https://blog.csdn.net/m0_54595829/article/details/122891936

版权

我使用的是SpringBoot项目。

引入的依赖：（使用了Mybatis记得不要忘记引入生成代码的插件）

        <!--JDBC-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <!--web-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!--Mybatis-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.1</version>
        </dependency>
        <!--MySql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.44</version>
            <scope>runtime</scope>
        </dependency>
        <!--lombok组件-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <!--druid连接池依赖-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

我这里用的是druid连接池，需要一个额外的扩展类

package com.zking.quartz02.util;

import com.alibaba.druid.pool.DruidDataSource;
import org.quartz.SchedulerException;
import org.quartz.utils.ConnectionProvider;

import java.sql.Connection;
import java.sql.SQLException;

/*
#============================================================================
# JDBC
#============================================================================
org.quartz.jobStore.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.qzDS.connectionProvider.class:com.zking.q03.quartz.DruidConnectionProvider
org.quartz.dataSource.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扩展类]
 */
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;

    /*
    * ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    *
    * 接口实现
    *
    * ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    */
    public Connection getConnection() throws SQLException {
        return datasource.getConnection();
    }

    public void shutdown() throws SQLException {
        datasource.close();
    }
    public void initialize() throws SQLException{
        if (this.URL == null) {
            throw new SQLException("DBPool could not be created: DB URL cannot be null");
        }

        if (this.driver == null) {
            throw new SQLException("DBPool driver could not be created: DB driver class name cannot be null!");
        }

        if (this.maxConnection < 0) {
            throw new SQLException("DBPool maxConnectins could not be created: Max connections 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);
            } 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(this.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);
        }
    }

    /*
    * ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    *
    * 提供get set方法
    *
    * ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    */
    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;
    }

    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.idleConnectionValidationSeconds = idleConnectionValidationSeconds;
    }

    public DruidDataSource getDatasource() {
        return datasource;
    }

    public void setDatasource(DruidDataSource datasource) {
        this.datasource = datasource;
    }
}

在项目中添加quartz.properties文件(这样就不会加载自带的properties文件)

#
#============================================================================
# Configure Main Scheduler Properties 调度器属性
#============================================================================
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
#============================================================================
#存储方式使用JobStoreTX，也就是数据库
org.quartz.jobStore.class: org.quartz.impl.jdbcjobstore.JobStoreTX
org.quartz.jobStore.driverDelegateClass:org.quartz.impl.jdbcjobstore.StdJDBCDelegate
#使用自己的配置文件
org.quartz.jobStore.useProperties:true
#数据库中quartz表的表名前缀
org.quartz.jobStore.tablePrefix:qrtz_
org.quartz.jobStore.dataSource:qzDS
#是否使用集群（如果项目只部署到 一台服务器，就不用了）
#org.quartz.jobStore.isClustered = true
#============================================================================
# Configure Datasources
#============================================================================
#配置数据库源
org.quartz.dataSource.qzDS.connectionProvider.class: com.zking.quartz02.util.DruidConnectionProvider
org.quartz.dataSource.qzDS.driver: com.mysql.jdbc.Driver
org.quartz.dataSource.qzDS.URL: jdbc:mysql://localhost:3306/数据库名称?useUnicode=true&characterEncoding=utf8&useSSL=false
org.quartz.dataSource.qzDS.user: 数据库账号
org.quartz.dataSource.qzDS.password: 数据库密码
org.quartz.dataSource.qzDS.maxConnection: 10

在数据库中创建quartz相关的表
进入quartz的官网http://www.quartz-scheduler.org/，点击Downloads，
下载后在目录\docs\dbTables下有常用数据库创建quartz表的脚本，例如：“tables_mysql.sql”

自定义MyJobFactory，解决spring不能在quartz中注入bean的问题

package com.zking.quartz02.util;

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;


/**
 * 自定义MyJobFactory，解决Job中拿不到Spring上下文中的bean的问题
 */
@Component
public class MyJobFactory extends AdaptableJobFactory {

    //这个对象Spring会帮我们自动注入进来
    @Autowired
    private AutowireCapableBeanFactory autowireCapableBeanFactory;

    //重写创建Job任务的实例方法
    @Override
    protected Object createJobInstance(TriggerFiredBundle bundle) throws Exception {
        Object jobInstance = super.createJobInstance(bundle);
        //通过以下方式，解决Job任务无法使用Spring中的Bean问题
        autowireCapableBeanFactory.autowireBean(jobInstance);
        return jobInstance;
    }
}

创建调度器schedule

package com.zking.quartz02.config;

import com.zking.quartz02.util.MyJobFactory;
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.io.IOException;
import java.util.Properties;

/**
 * Quartz自定义配置类，用于加载quartz.properties配置文件，使其支持JDBCStore
 */
@Configuration
public class QuartzConfiguration {

    @Autowired
    private MyJobFactory myJobFactory;

    @Bean
    public SchedulerFactoryBean schedulerFactoryBean(){
        SchedulerFactoryBean sc=new SchedulerFactoryBean();
        try {
            //加载自定义的quartz.properties文件
            sc.setQuartzProperties(quartzProperties());
            //设置自定义的JobFactory工程类，解决Spring不能再Quartz框架中Bean的注入问题
            sc.setJobFactory(myJobFactory);
            return sc;
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    //指定quartz.properties
    @Bean
    public Properties quartzProperties() throws IOException {
        //创建Properties属性工厂Bean类
        PropertiesFactoryBean propertiesFactoryBean = new PropertiesFactoryBean();
        //加载自定义的quartz.properties
        propertiesFactoryBean.setLocation(new ClassPathResource("/quartz.properties"));
        propertiesFactoryBean.afterPropertiesSet();
        return propertiesFactoryBean.getObject();
    }

    //创建schedule
    @Bean(name = "scheduler")
    public Scheduler scheduler() {
        //从调度器工厂Bean中获取调取度实例对象
        return schedulerFactoryBean().getScheduler();
    }
}

以上配置完成，可以创建定时任务了

继承org.quartz.Job或org.springframework.scheduling.quartz.QuartzJobBea创建任务，可通过JobExecutionContext传参

package com.zking.quartz02.job;

import org.quartz.Job;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;

import java.util.Date;

public class MyJob implements Job {
    @Override
    public void execute(JobExecutionContext jobExecutionContext) throws JobExecutionException {
        System.out.println("这是一个简单地调度器任务："+ new Date().toLocaleString());
    }
}

这里在数据库自定义了两张表：一张是创建的定时任务表，一张是定时任务的参数表

-- 注意：job_name存放的任务类的全路径，在quartz中通过jobName和jobGroup来确定trigger的唯一性，所以这两列为联合唯一索引
create table t_schedule_trigger
(
  id int primary key auto_increment,                                -- ID
  cron varchar(200) not null,                                       -- 时间表达式
  status char(1) not null,                                          -- 使用状态 0：禁用   1：启用
  job_name varchar(200) not null,                                   -- 任务名称
  job_group varchar(200) not null,                                  -- 任务分组  
  unique index(job_name,job_group)
);

-- 额外添加到任务中的参数
create table t_schedule_trigger_param
(
  param_id int primary key auto_increment,                                -- ID
  name varchar(200) not null,                                             -- 参数名
  value varchar(512),                                                     -- 参数值
 
  schedule_trigger_id int not null,                                       -- 外键：引用t_schedule_trigger(id)
  foreign key(schedule_trigger_id) references t_schedule_trigger(id)
);

这里省去用Mybatis生成代码的操作，直接到实现类进行代码编写

package com.zking.quartz02.service;

import com.zking.quartz02.mapper.ScheduleTriggerMapper;
import com.zking.quartz02.mapper.ScheduleTriggerParamMapper;
import com.zking.quartz02.model.ScheduleTrigger;
import org.quartz.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class ScheduleServiceImpl {

    @Autowired
    private Scheduler scheduler;

    @Autowired
    private ScheduleTriggerMapper scheduleTriggerMapper;

    @Autowired
    private ScheduleTriggerParamMapper scheduleTriggerParamMapper;

    //实现：spring自带的定时任务+quartz任务
    //最外层由Spring自带的定时任务每隔十秒扫描一次，读取数据库的定时任务
    @Scheduled(cron = "*/5 * * * * ?")
    public void reflush(){
        System.out.println("启动");
        try {
            //1.每隔十秒获取一次数据库中的定时任务
            List<ScheduleTrigger> scheduleTriggers=scheduleTriggerMapper.queryScheduleTriggerAll();
            //2.循环所有的定时任务
            for (ScheduleTrigger s : scheduleTriggers) {
                //获取定时任务的ID
                Integer scheduleId = s.getId();
                //获取定时任务的cron表达式
                String cron = s.getCron();
                //获取定时任务的状态status=1 可用  status=0不可用
                String status = s.getStatus();
                String jobName = s.getJobName();
                String jobGroup = s.getJobGroup();
                //根据jobName和jobGroup获取触发器的TriggerKey
                TriggerKey triggerKey = TriggerKey.triggerKey(jobName, jobGroup);
                //根据TriggerKey获取对应的Trigger(触发器)
                //quartz框架：select * from qrtz_triggers where jobname=? and jobgroup=?
                Trigger trigger = scheduler.getTrigger(triggerKey);
                //判断trigger是否为空
                if(null==trigger){
                    if("".equals(status))
                        continue;
                    System.out.println("正在创建quartz的定时调度任务");
                    //创建JobDetaill
                    JobDetail jobDetail = JobBuilder.newJob((Class<? extends Job>) Class.forName(jobName))
                            .withIdentity(jobName, jobGroup)
                            .build();
                    //创建触发器
                    CronScheduleBuilder cronScheduleBuilder = CronScheduleBuilder.cronSchedule(cron);
                    trigger=TriggerBuilder.newTrigger()
                            .withIdentity(triggerKey)
                            .withSchedule(cronScheduleBuilder)
                            .build();
                    //8.将JobDetail和trigger注入到scheduler调度器中
                    //quartz：往qrtz_cron_triggers等等表中插入定时任务信息
                    scheduler.scheduleJob(jobDetail, trigger);
                }else{
                    System.out.println("quertz中的定时调度任务已经存在");

                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

因为用了SpringBoot自带的定时任务，所以要在启动类上加入@EnableScheduling注解

package com.zking.quartz02;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.scheduling.annotation.EnableScheduling;

/**
 * 启动定时任务
 */
@EnableScheduling
@SpringBootApplication
@MapperScan({"com.zking.quartz02.mapper"})
public class Quartz02Application {

    public static void main(String[] args) {
        SpringApplication.run(Quartz02Application.class, args);
    }

}

这里要确保一件事，就是启动类要与util、model等包同级，否则运行没有效果，也不会报错！