springboot调度sqlsever存储过程 springboot任务调度

一、quartz介绍

1.简介

起初是OpenSymphony组织提供的一个功能丰富的开源作业调度框架

官网：http://www.quartz-scheduler.org/

纯java编写，用法简单而功能强大

2.特点

1、强大的作业调度功能

作为spring默认的调度框架，极易与spring无缝整合，实现灵活可配置的调度功能，quartz提供了调 度运行环境持久化机制，可以保存并恢复调度现场，即使系统因故障关闭也不会造成调度现场的数据丢失。

2、 灵活的应用方式

允许我们灵活定义触发器的调度时间规则，并可以将触发器与任务进行关联绑定，quartz提供了主键式的监听器，各种插件以及线程池等功能，支持任务和调度的多重组合，支持调度数据的多种存储方式。

3、 分布式和集群能力

Quartz 集群中的每个节点是一个独立的 Quartz 应用，需要对每个节点分别启动或停止。不像许多应用服务器的集群，独立的 Quartz 节点并不与其他的节点或是管理节点通信。Quartz 应用是通过数据库表来感知到另一应用的。

3.核心结构

job（或task）是实现定时功能的接口：如定时推送消息等。

trigger是触发执行定时任务的触发器，基本设置有：指定job的运行时间，时间间隔，运行次数等。

schedule是调度指定的trigger去执行指定的job。

二、集成与使用步骤

1.springboot中的pom.xml

<?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>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.5.RELEASE</version>
        <relativePath/>
    </parent>

    <groupId>com.ultra</groupId>
    <artifactId>smartlog</artifactId>
    <version>6.0.0-SNAPSHOT</version>
    <name>smartlog</name>
    <packaging>jar</packaging>
    <description>Demo project for Spring Boot</description>

    <properties>
        <lombok.version>1.18.12</lombok.version>
        <fastjson.version>1.2.68</fastjson.version>
        <commons-lang3.version>3.10</commons-lang3.version>
        <hikaricp.version>3.4.2</hikaricp.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
        <!-- 打包跳过测试模块 -->
        <skipTests>true</skipTests>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!--quartz-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-quartz</artifactId>
        </dependency>
        <!--使用hikari数据源实现job持久化-->
        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
            <version>${hikaricp.version}</version>
        </dependency>
        <!--lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>${lombok.version}</version>
            <scope>provided</scope>
        </dependency>
        <!--fastjson-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>${fastjson.version}</version>
        </dependency>
        <!--commons-lang3-->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>${commons-lang3.version}</version>
        </dependency>
        <!--test-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

2.application.yaml

spring:
  application:
    name: ultra-smartlog-schedule
  quartz: #quartz config
    job-store-type: jdbc
    properties:
      org:
        quartz:
          scheduler:
            instanceName: smartlogClusteredScheduler #调度器实例名称
            instanceId: AUTO #调度器实例编号自动生成
          jobStore:
            dataSource: smartlog
            class: org.quartz.impl.jdbcjobstore.JobStoreTX #持久化方式配置
            driverDelegateClass: org.quartz.impl.jdbcjobstore.StdJDBCDelegate #持久化方式配置数据驱动，MySQL数据库
            tablePrefix: QRTZ_ #数据库持久化表前缀
            isClustered: true #开启分布式部署
            clusterCheckinInterval: 10000 #分布式节点有效性检查时间间隔，单位：毫秒
            useProperties: false #配置是否使用
          threadPool:
            class: org.quartz.simpl.SimpleThreadPool #线程池实现类
            threadCount: 10 #执行最大并发线程数量
            threadPriority: 5 #线程优先级
            threadsInheritContextClassLoaderOfInitializingThread: true  #配置是否启动自动加载数据库内的定时任务，默认true
          dataSource:
            smartlog:
              provider: hikaricp #使用hikari(默认使用c3p0)
              URL: jdbc:mysql://192.168.194.221:3306/smartlog?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
              driver: com.mysql.cj.jdbc.Driver
              user: smartlog
              password: smartlog

3.定义任务类（实现Job接口，重写execute方法）

import lombok.extern.slf4j.Slf4j;
import org.quartz.Job;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;
import org.springframework.stereotype.Component;

/**
 * @Deacription 定时任务逻辑实现类
 * @Author wenyt
 * @Date 2020/4/8 18:21
 * @Version 1.0
 **/
@Component
@Slf4j
public class TestJob implements Job {

    @Override
    public void execute(JobExecutionContext jobExecutionContext) throws JobExecutionException {
        //todo 实现任务逻辑
    }
}

4.定义配置类

import com.ultra.smartlog.shcedule.job.CreateIndexJob;
import org.quartz.*;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * @Deacription 创建索引的定时任务配置类
 * @Author wenyt
 * @Date 2020/4/1 17:23
 * @Version 1.0
 **/
@Configuration
public class CreateIndexJobConfig {

    @Bean("tesstJobDetail")
    public JobDetail createIndexJobDetail() {
        return JobBuilder.newJob(TestJob .class)
                .withIdentity("tesstJobDetail", "tesstJobDetailGroup").storeDurably()
                .withDescription("测试").build();
    }

    @Bean("tesstJobTrigger")
    public Trigger tesstJobTrigger() {
        return TriggerBuilder.newTrigger().forJob(tesstJobDetail())
                .withIdentity("tesstJobTrigger", "tesstJobTriggerGroup")
                .withSchedule(CronScheduleBuilder.cronSchedule("0/2 * * * * ?")//两秒执行一次
                        .withMisfireHandlingInstructionDoNothing()).build();
    }
}

5.初始化Quartz使用的数据库表

每个quartz的版本引入的数据库表数量应该是略有差异的。本文只以自己项目中引入的为准。建库脚本可以在http://www.quartz-scheduler.org/downloads/ 下载对应版本的tar.gz，对应脚本文件在\src\org\quartz\impl\jdbcjobstore目录下可以找到，选择自己数据库的脚本，本项目用到的是mysql数据库，如下所示：

drop table qrtz_calendars;

drop table qrtz_fired_triggers;

drop table qrtz_blob_triggers;

drop table qrtz_cron_triggers;

drop table qrtz_simple_triggers;

drop table qrtz_simprop_triggers;

drop table qrtz_triggers;

drop table qrtz_job_details;

drop table qrtz_paused_trigger_grps;

drop table qrtz_locks;

drop table qrtz_scheduler_state;

 

 

CREATE TABLE qrtz_job_details

  (

    SCHED_NAME VARCHAR2(120) NOT NULL,

    JOB_NAME  VARCHAR2(200) NOT NULL,

    JOB_GROUP VARCHAR2(200) NOT NULL,

    DESCRIPTION VARCHAR2(250) NULL,

    JOB_CLASS_NAME   VARCHAR2(250) NOT NULL, 

    IS_DURABLE VARCHAR2(1) NOT NULL,

    IS_NONCONCURRENT VARCHAR2(1) NOT NULL,

    IS_UPDATE_DATA VARCHAR2(1) NOT NULL,

    REQUESTS_RECOVERY VARCHAR2(1) NOT NULL,

    JOB_DATA BLOB NULL,

    CONSTRAINT QRTZ_JOB_DETAILS_PK PRIMARY KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)

);

CREATE TABLE qrtz_triggers

  (

    SCHED_NAME VARCHAR2(120) NOT NULL,

    TRIGGER_NAME VARCHAR2(200) NOT NULL,

    TRIGGER_GROUP VARCHAR2(200) NOT NULL,

    JOB_NAME  VARCHAR2(200) NOT NULL, 

    JOB_GROUP VARCHAR2(200) NOT NULL,

    DESCRIPTION VARCHAR2(250) NULL,

    NEXT_FIRE_TIME NUMBER(13) NULL,

    PREV_FIRE_TIME NUMBER(13) NULL,

    PRIORITY NUMBER(13) NULL,

    TRIGGER_STATE VARCHAR2(16) NOT NULL,

    TRIGGER_TYPE VARCHAR2(8) NOT NULL,

    START_TIME NUMBER(13) NOT NULL,

    END_TIME NUMBER(13) NULL,

    CALENDAR_NAME VARCHAR2(200) NULL,

    MISFIRE_INSTR NUMBER(2) NULL,

    JOB_DATA BLOB NULL,

    CONSTRAINT QRTZ_TRIGGERS_PK PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),

    CONSTRAINT QRTZ_TRIGGER_TO_JOBS_FK 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 VARCHAR2(120) NOT NULL,

    TRIGGER_NAME VARCHAR2(200) NOT NULL,

    TRIGGER_GROUP VARCHAR2(200) NOT NULL,

    REPEAT_COUNT NUMBER(7) NOT NULL,

    REPEAT_INTERVAL NUMBER(12) NOT NULL,

    TIMES_TRIGGERED NUMBER(10) NOT NULL,

    CONSTRAINT QRTZ_SIMPLE_TRIG_PK PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),

    CONSTRAINT QRTZ_SIMPLE_TRIG_TO_TRIG_FK FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) 

   REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)

);

CREATE TABLE qrtz_cron_triggers

  (

    SCHED_NAME VARCHAR2(120) NOT NULL,

    TRIGGER_NAME VARCHAR2(200) NOT NULL,

    TRIGGER_GROUP VARCHAR2(200) NOT NULL,

    CRON_EXPRESSION VARCHAR2(120) NOT NULL,

    TIME_ZONE_ID VARCHAR2(80),

    CONSTRAINT QRTZ_CRON_TRIG_PK PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),

    CONSTRAINT QRTZ_CRON_TRIG_TO_TRIG_FK FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) 

      REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)

);

CREATE TABLE qrtz_simprop_triggers

  (          

    SCHED_NAME VARCHAR2(120) NOT NULL,

    TRIGGER_NAME VARCHAR2(200) NOT NULL,

    TRIGGER_GROUP VARCHAR2(200) NOT NULL,

    STR_PROP_1 VARCHAR2(512) NULL,

    STR_PROP_2 VARCHAR2(512) NULL,

    STR_PROP_3 VARCHAR2(512) NULL,

    INT_PROP_1 NUMBER(10) NULL,

    INT_PROP_2 NUMBER(10) NULL,

    LONG_PROP_1 NUMBER(13) NULL,

    LONG_PROP_2 NUMBER(13) NULL,

    DEC_PROP_1 NUMERIC(13,4) NULL,

    DEC_PROP_2 NUMERIC(13,4) NULL,

    BOOL_PROP_1 VARCHAR2(1) NULL,

    BOOL_PROP_2 VARCHAR2(1) NULL,

    CONSTRAINT QRTZ_SIMPROP_TRIG_PK PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),

    CONSTRAINT QRTZ_SIMPROP_TRIG_TO_TRIG_FK FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) 

      REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)

);

CREATE TABLE qrtz_blob_triggers

  (

    SCHED_NAME VARCHAR2(120) NOT NULL,

    TRIGGER_NAME VARCHAR2(200) NOT NULL,

    TRIGGER_GROUP VARCHAR2(200) NOT NULL,

    BLOB_DATA BLOB NULL,

    CONSTRAINT QRTZ_BLOB_TRIG_PK PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),

    CONSTRAINT QRTZ_BLOB_TRIG_TO_TRIG_FK FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) 

        REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)

);

CREATE TABLE qrtz_calendars

  (

    SCHED_NAME VARCHAR2(120) NOT NULL,

    CALENDAR_NAME  VARCHAR2(200) NOT NULL, 

    CALENDAR BLOB NOT NULL,

    CONSTRAINT QRTZ_CALENDARS_PK PRIMARY KEY (SCHED_NAME,CALENDAR_NAME)

);

CREATE TABLE qrtz_paused_trigger_grps

  (

    SCHED_NAME VARCHAR2(120) NOT NULL,

    TRIGGER_GROUP  VARCHAR2(200) NOT NULL, 

    CONSTRAINT QRTZ_PAUSED_TRIG_GRPS_PK PRIMARY KEY (SCHED_NAME,TRIGGER_GROUP)

);

CREATE TABLE qrtz_fired_triggers 

  (

    SCHED_NAME VARCHAR2(120) NOT NULL,

    ENTRY_ID VARCHAR2(95) NOT NULL,

    TRIGGER_NAME VARCHAR2(200) NOT NULL,

    TRIGGER_GROUP VARCHAR2(200) NOT NULL,

    INSTANCE_NAME VARCHAR2(200) NOT NULL,

    FIRED_TIME NUMBER(13) NOT NULL,

    SCHED_TIME NUMBER(13) NOT NULL,

    PRIORITY NUMBER(13) NOT NULL,

    STATE VARCHAR2(16) NOT NULL,

    JOB_NAME VARCHAR2(200) NULL,

    JOB_GROUP VARCHAR2(200) NULL,

    IS_NONCONCURRENT VARCHAR2(1) NULL,

    REQUESTS_RECOVERY VARCHAR2(1) NULL,

    CONSTRAINT QRTZ_FIRED_TRIGGER_PK PRIMARY KEY (SCHED_NAME,ENTRY_ID)

);

CREATE TABLE qrtz_scheduler_state 

  (

    SCHED_NAME VARCHAR2(120) NOT NULL,

    INSTANCE_NAME VARCHAR2(200) NOT NULL,

    LAST_CHECKIN_TIME NUMBER(13) NOT NULL,

    CHECKIN_INTERVAL NUMBER(13) NOT NULL,

    CONSTRAINT QRTZ_SCHEDULER_STATE_PK PRIMARY KEY (SCHED_NAME,INSTANCE_NAME)

);

CREATE TABLE qrtz_locks

  (

    SCHED_NAME VARCHAR2(120) NOT NULL,

    LOCK_NAME  VARCHAR2(40) NOT NULL, 

    CONSTRAINT QRTZ_LOCKS_PK PRIMARY KEY (SCHED_NAME,LOCK_NAME)

);

 

create index idx_qrtz_j_req_recovery on qrtz_job_details(SCHED_NAME,REQUESTS_RECOVERY);

create index idx_qrtz_j_grp on qrtz_job_details(SCHED_NAME,JOB_GROUP);

 

create index idx_qrtz_t_j on qrtz_triggers(SCHED_NAME,JOB_NAME,JOB_GROUP);

create index idx_qrtz_t_jg on qrtz_triggers(SCHED_NAME,JOB_GROUP);

create index idx_qrtz_t_c on qrtz_triggers(SCHED_NAME,CALENDAR_NAME);

create index idx_qrtz_t_g on qrtz_triggers(SCHED_NAME,TRIGGER_GROUP);

create index idx_qrtz_t_state on qrtz_triggers(SCHED_NAME,TRIGGER_STATE);

create index idx_qrtz_t_n_state on qrtz_triggers(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP,TRIGGER_STATE);

create index idx_qrtz_t_n_g_state on qrtz_triggers(SCHED_NAME,TRIGGER_GROUP,TRIGGER_STATE);

create index idx_qrtz_t_next_fire_time on qrtz_triggers(SCHED_NAME,NEXT_FIRE_TIME);

create index idx_qrtz_t_nft_st on qrtz_triggers(SCHED_NAME,TRIGGER_STATE,NEXT_FIRE_TIME);

create index idx_qrtz_t_nft_misfire on qrtz_triggers(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME);

create index idx_qrtz_t_nft_st_misfire on qrtz_triggers(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME,TRIGGER_STATE);

create index idx_qrtz_t_nft_st_misfire_grp on qrtz_triggers(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME,TRIGGER_GROUP,TRIGGER_STATE);

 

create index idx_qrtz_ft_trig_inst_name on qrtz_fired_triggers(SCHED_NAME,INSTANCE_NAME);

create index idx_qrtz_ft_inst_job_req_rcvry on qrtz_fired_triggers(SCHED_NAME,INSTANCE_NAME,REQUESTS_RECOVERY);

create index idx_qrtz_ft_j_g on qrtz_fired_triggers(SCHED_NAME,JOB_NAME,JOB_GROUP);

create index idx_qrtz_ft_jg on qrtz_fired_triggers(SCHED_NAME,JOB_GROUP);

create index idx_qrtz_ft_t_g on qrtz_fired_triggers(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP);

create index idx_qrtz_ft_tg on qrtz_fired_triggers(SCHED_NAME,TRIGGER_GROUP);

启动工程，任务执行成功，发现数据库已经持久化了job的相关信息。