时间：2021-07-01 10:21:17 帮助过：7人阅读
更换成Druid连接池
DruidConnectionProvider.java
package com.hmc.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文件，因为quartz默认使用的是c3p0数据库连接池，我们要使用Druid
# #============================================================================ # 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.hmc.quartz02.util.DruidConnectionProvider org.quartz.dataSource.qzDS.driver: com.mysql.jdbc.Driver org.quartz.dataSource.qzDS.URL: jdbc:mysql://localhost:3306/book?useUnicode=true&characterEncoding=utf8 org.quartz.dataSource.qzDS.user: root org.quartz.dataSource.qzDS.password: 123 org.quartz.dataSource.qzDS.maxConnection: 10
自定义的业务表
– 注意：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)
);
QuartzConfiguration.java（quartz调度框架与spring框架整合的配置类，主要是要将org.quartz.Scheduler交给spring进行管理）
package com.hmc.quartz02.quartz;
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;
/**
* 创建配置类
*/
@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();
}
}
application.yml
server: port: 8080 servlet: context-path: / spring: datasource: #1.JDBC type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3306/book?useUnicode=true&characterEncoding=utf8 username: root password: 123 druid: #2.连接池配置 #初始化连接池的连接数量 大小，最小，最大 initial-size: 5 min-idle: 5 max-active: 20 #配置获取连接等待超时的时间 max-wait: 60000 #配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 time-between-eviction-runs-millis: 60000 # 配置一个连接在池中最小生存的时间，单位是毫秒 min-evictable-idle-time-millis: 30000 validation-query: SELECT 1 FROM DUAL test-while-idle: true test-on-borrow: true test-on-return: false # 是否缓存preparedStatement，也就是PSCache 官方建议MySQL下建议关闭 个人建议如果想用SQL防火墙 建议打开 pool-prepared-statements: true max-pool-prepared-statement-per-connection-size: 20 # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，‘wall‘用于防火墙 filter: stat: merge-sql: true slow-sql-millis: 5000 #3.基础监控配置 web-stat-filter: enabled: true url-pattern: /* #设置不统计哪些URL exclusions: "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*" session-stat-enable: true session-stat-max-count: 100 stat-view-servlet: enabled: true url-pattern: /druid/* reset-enable: true #设置监控页面的登录名和密码 login-username: admin login-password: admin allow: 127.0.0.1 #mybatis配置 mybatis: #配置SQL映射文件路径 mapper-locations: classpath:mapper/*.xml #驼峰命名 configuration: map-underscore-to-camel-case: true logging: level: #???????sql??? com.hmc.quartz02.mapper: debug #deny: 192.168.1.100
Quartz02Application.java
package com.hmc.quartz02;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.cache.annotation.EnableCaching;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import org.springframework.web.cors.CorsConfiguration;
import org.springframework.web.cors.CorsConfiguration;
import org.springframework.web.cors.UrlBasedCorsConfigurationSource;
import org.springframework.web.filter.CorsFilter;
@SpringBootApplication
@MapperScan("com.hmc.quartz02.mapper")
@EnableTransactionManagement
@EnableScheduling
public class Quartz02Application {
public static void main(String[] args) {
SpringApplication.run(Quartz02Application.class, args);
}
}
mapper层
ScheduleTriggerParamMapper
package com.hmc.quartz02.mapper; import com.hmc.quartz02.model.ScheduleTriggerParam; import org.springframework.stereotype.Repository; import org.springframework.web.bind.annotation.ResponseBody; import java.util.List; @Repository public interface ScheduleTriggerParamMapper { /** * 根据定时任务id获取任务对应的执行参数信息 */ List<ScheduleTriggerParam> queryScheduleParams(Integer id); }
ScheduleTriggerParamMapper.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > <mapper namespace="com.hmc.quartz02.mapper.ScheduleTriggerParamMapper" > <resultMap id="BaseResultMap" type="com.hmc.quartz02.model.ScheduleTriggerParam" > <constructor > <idArg column="param_id" jdbcType="INTEGER" javaType="java.lang.Integer" /> <arg column="name" jdbcType="VARCHAR" javaType="java.lang.String" /> <arg column="value" jdbcType="VARCHAR" javaType="java.lang.String" /> <arg column="schedule_trigger_id" jdbcType="INTEGER" javaType="java.lang.Integer" /> </constructor> </resultMap> <sql id="Base_Column_List" > param_id, name, value, schedule_trigger_id </sql> <select id="queryScheduleParams" resultType="com.hmc.quartz02.model.ScheduleTriggerParam"> SELECT <include refid="Base_Column_List"/> FROM t_schedule_trigger_param where schedule_trigger_id=#{id} </select> </mapper>
ScheduleTtriggerMapper
package com.hmc.quartz02.mapper; import com.hmc.quartz02.model.ScheduleTtrigger; import org.springframework.stereotype.Repository; import java.util.List; @Repository public interface ScheduleTtriggerMapper { /** * 获取所有的定时调度任务信息t_schedule_trigger */ List<ScheduleTtrigger> queryScheduleTtrigger(); }
ScheduleTtriggerMapper.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > <mapper namespace="com.hmc.quartz02.mapper.ScheduleTtriggerMapper" > <resultMap id="BaseResultMap" type="com.hmc.quartz02.model.ScheduleTtrigger" > <constructor > <idArg column="id" jdbcType="INTEGER" javaType="java.lang.Integer" /> <arg column="cron" jdbcType="VARCHAR" javaType="java.lang.String" /> <arg column="status" jdbcType="CHAR" javaType="java.lang.String" /> <arg column="job_name" jdbcType="VARCHAR" javaType="java.lang.String" /> <arg column="job_group" jdbcType="VARCHAR" javaType="java.lang.String" /> </constructor> </resultMap> <sql id="Base_Column_List" > id, cron, status, job_name, job_group </sql> <select id="queryScheduleTtrigger" resultType="com.hmc.quartz02.model.ScheduleTtrigger"> SELECT <include refid="Base_Column_List"/> FROM t_schedule_trigger </select> </mapper>
定时任务作业类
IScheduleTriggerParamServiceImp
package com.hmc.quartz02.service.impl; import com.hmc.quartz02.mapper.ScheduleTriggerParamMapper; import com.hmc.quartz02.mapper.ScheduleTtriggerMapper; import com.hmc.quartz02.model.ScheduleTriggerParam; import com.hmc.quartz02.model.ScheduleTtrigger; import com.hmc.quartz02.service.IScheduleTriggerParamService; import org.quartz.*; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.scheduling.annotation.Scheduled; import org.springframework.stereotype.Component; import org.springframework.stereotype.Service; import java.util.List; /** * @author胡明财 * @site www.xiaomage.com * @company xxx公司 * @create 2019-11-30 02:39 */ @Service public class IScheduleTriggerParamServiceImpl { //获取调度器实例 @Autowired private Scheduler scheduler; @Autowired private ScheduleTtriggerMapper sheduleTtriggerMapper; @Autowired private ScheduleTriggerParamMapper scheduleTriggerParamMapper; @Scheduled(cron = "*/10 * * * * ?") public void reflushScheduler(){ try { //1.循环获取数据库里面的所有定时任务信息 List<ScheduleTtrigger> list = sheduleTtriggerMapper.queryScheduleTtrigger(); //2.循环遍历 for (ScheduleTtrigger scheduleTtrigger : list) { //获取cron表达式 String cron=scheduleTtrigger.getCron(); //获取Jobname,job任务类的全路径名 String jobname=scheduleTtrigger.getJobName(); //获取JobGroup String jobGroup=scheduleTtrigger.getJobGroup(); //获取Status运行状态 String status=scheduleTtrigger.getStatus(); //3.根据Jobname和JobGroup生成TriggerKey TriggerKey triggerKey = TriggerKey.triggerKey(jobname, jobGroup); //4根据TriggerKey获取Scheduler调度器中的触发器，并判断是否为null，不为null即存在 CronTrigger trigger =(CronTrigger) scheduler.getTrigger(triggerKey); //5判断是否为null if(null==trigger){ //6.判断当前kob任务是否可用 if(status.equals("0")) continue; //7.创建JobDetail JobDetail jobDetail = JobBuilder.newJob((Class<? extends Job>) Class.forName(jobname)) .withIdentity(jobname,jobGroup) .build(); //8.传递参数到job任中 JobDataMap jobDataMap=jobDetail.getJobDataMap(); //获取调度器任务id Integer scheduleid=scheduleTtrigger.getId(); List<ScheduleTriggerParam> params = scheduleTriggerParamMapper.queryScheduleParams(scheduleid); for (ScheduleTriggerParam param : params) { jobDataMap.put(param.getName(),param.getValue()); } //9.创建cron表达式调度器 CronScheduleBuilder cronScheduleBuilder = CronScheduleBuilder.cronSchedule(cron); //10 创建cron表达式触发器 trigger=TriggerBuilder.newTrigger() .withIdentity(jobname,jobGroup) .withSchedule(cronScheduleBuilder) .build(); //11.将jobDetail和Trigger注入到调度器中 scheduler.scheduleJob(jobDetail,trigger); } else{ System.out.println("Quartz调度器实例已经存在"); if(status.equals("0")){ //删除调度任务 JobKey jobKey=JobKey.jobKey(jobname,jobGroup); scheduler.deleteJob(jobKey); continue; } //获取调度器中的表达式 String cronExpression = trigger.getCronExpression(); //将调度器中的cron与数据库中的job任务进行比对 if(!cron.equals(cronExpression)){ //重构表达式调度器 CronScheduleBuilder newCronSchedule = CronScheduleBuilder.cronSchedule(cron); //重构CronTrigger trigger=TriggerBuilder.newTrigger() .withIdentity(triggerKey) .withSchedule(newCronSchedule) .build(); //刷新调度器实例 scheduler.rescheduleJob(triggerKey,trigger); } } } } catch (Exception e) { e.printStackTrace(); } } }
1表示可用，0表示不可用
MyJob1任务 测试
package com.hmc.quartz02.quartz;
import lombok.extern.slf4j.Slf4j;
import org.quartz.Job;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;
import org.springframework.stereotype.Component;
import java.util.Date;
/**
* @author胡明财
* @site www.xiaomage.com
* @company xxx公司
* @create 2019-11-30 01:57
*/
@Component
@Slf4j
public class MyJob1 implements Job{
//获取spring 上下文的Bean
@Override
public void execute(JobExecutionContext jobExecutionContext) throws JobExecutionException {
System.out.println("这是一个简单的Job任务调度实例"+ new Date().toLocaleString());
}
}
MyJob2任务 测试 带参数的
package com.hmc.quartz02.quartz;
import lombok.extern.slf4j.Slf4j;
import org.quartz.*;
import org.springframework.stereotype.Component;
import java.util.Date;
/**
* @author胡明财
* @site www.xiaomage.com
* @company xxx公司
* @create 2019-11-30 01:57
*/
@Component
@Slf4j
public class MyJob2 implements Job{
//获取spring 上下文的Bean
@Override
public void execute(JobExecutionContext jobExecutionContext) throws JobExecutionException {
System.out.println("这是一个有参数的Job任务调度实例，用于接收外部参数"+new Date().toLocaleString());
JobDetail jobDetail=jobExecutionContext.getJobDetail();
JobDataMap jobDataMap=jobDetail.getJobDataMap();
System.out.println("name="+jobDataMap.get("name")+",age="+jobDataMap.get("age")
);
}
}
MyJob3任务 测试
package com.hmc.quartz02.quartz; import com.hmc.quartz02.model.ScheduleTriggerParam; import com.hmc.quartz02.service.IScheduleTriggerParamService; import lombok.extern.slf4j.Slf4j; import org.quartz.*; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Component; import java.util.Date; import java.util.List; /** * @author胡明财 * @site www.xiaomage.com * @company xxx公司 * @create 2019-11-30 01:57 */ @Component @Slf4j public class MyJob3 implements Job{ @Autowired private IScheduleTriggerParamService IScheduleTriggerParamService; //获取spring 上下文的Bean @Override public void execute(JobExecutionContext jobExecutionContext) throws JobExecutionException { System.out.println("这是一个获取sprig中Bean的job任务"+new Date().toLocaleString()); List<ScheduleTriggerParam> scheduleTriggerParams = IScheduleTriggerParamService.queryScheduleParams(2); System.out.println("参数数量"+scheduleTriggerParams.size()); } }
Springbot 结合Vue实现页面版
这里需要配置跨越
@CrossOrigin(origins = {"*","null"}) 放在类上面即可
效果图
quartz.vue
<template> <div> <!-- 搜索筛选 --> <div style="margin-left: 30px;margin-top: 20px;"> <el-form :inline="true" :model="formInline" class="user-search"> <el-form-item label="搜索："> <el-input size="small" v-model="formInline.uname" placeholder="输入用户名称"></el-input> </el-form-item> <el-form-item> <el-button size="small" type="primary" icon="el-icon-search" @click="douser(formInline.uname)">搜索</el-button> <el-button size="small" type="primary" icon="el-icon-plus" @click="userAdd()">添加</el-button> </el-form-item> </el-form> </div> <div> <el-table size="small" :data="listData" border element-loading-text="拼命加载中" style="width: 100%;"> <el-table-column sortable prop="id" align="center" label="任务id" min-width="1"> </el-table-column> <el-table-column sortable prop="cron" label="任务表达式" min-width="1"> </el-table-column>