java定时执行sql语句_spring中使用quartz动态添加定时任务执行sql

最新推荐文章于 2023-09-22 19:02:52 发布

feifeiniano

最新推荐文章于 2023-09-22 19:02:52 发布

阅读量685

点赞数

文章标签： java定时执行sql语句

本文链接：https://blog.csdn.net/weixin_33304092/article/details/114110590

版权

系统用来每天插入视图数据。。。

一、数据库表设计

1、接口配置表(t_m_db_interface_config)

2、接口日志表(t_m_db_interface_log)

3、前端配置页面

查询页面：

新增及修改页面：

第一个sql一般用来删除原有数据，第二个sql一般用来插入新数据，多条sql可以写在一起，代码中做了批处理，用分号隔开(英文的分号)。

不配置临界时间点时sql示例：delete from table_ where BUSSINESS_DATE>=DATE_FORMAT(NOW(),'%Y-%m-%d');

配置临界时间点sql示例：delete from table_ where BUSSINESS_DATE>=DATE_FORMAT(?,'%Y-%m-%d');

时间条件可自己修改，注意的是如果sql中有？号则说明配置了临界时间点，代码中会根据设置的临界时间点来确定是今天还是昨天。

4、单表类

上述两张表的字段设计可根据实际需求灵活调整。

按mvc的开发模式，写出上述两个表对应的对码。也可以用Mybatis工具自动分包生成。

分别位于com.dbs.dmsmdm.(controller/service/mapper/bean).simple路径下。

xml映射文件位于resources/mybatis+同上路径下。

这里只贴出bean层代码，Controllor、Service、dao、mapper就不贴出来了。

前面的@ApiModelProperty注解为自定义的注解，请忽略。。。

1 packagecom.dbs.dmsmdm.bean.simple;2

3 importcom.dbs.dms.uibase.bean.BaseBean;4 importjava.util.Date;5

6 importorg.springframework.format.annotation.DateTimeFormat;7 importcom.fasterxml.jackson.databind.annotation.JsonSerialize;8 importcom.fasterxml.jackson.annotation.JsonFormat;9 importcom.dbs.dms.uibase.annotations.ApiModel;10 importcom.dbs.dms.uibase.annotations.ApiModelProperty;11 importcom.dbs.dms.translate.TranslateBean;12 importcom.dbs.dms.translate.TranslateBeanSerializer;13 /**

14 * 接口配置表15 * 实体类对应的数据表为： t_m_db_interface_config16 *@author$Author17 * @date Tue Apr 17 11:21:18 GMT+08:00 201818 */

19 @ApiModel(value ="DbInterfaceConfigBean")20 public class DbInterfaceConfigBean extendsBaseBean {21 public static final String ATTR_INTERFACE_CONFIG_ID = "interfaceConfigId";22 public static final String ATTR_INTERFACE_CODE = "interfaceCode";23 public static final String ATTR_INTERFACE_NAME = "interfaceName";24 public static final String ATTR_FROM_SYSTEM = "fromSystem";25 public static final String ATTR_TO_SYSTEM = "toSystem";26 public static final String ATTR_FREQENCY = "freqency";27 public static final String ATTR_FREQENCY_TYPE = "freqencyType";28 public static final String ATTR_BEGIN_RUN_TIME = "beginRunTime";29 public static final String ATTR_NEXT_RUN_TIME = "nextRunTime";30 public static final String ATTR_TIME_RUN_YESTERDAY = "timeRunYesterday";31 public static final String ATTR_BEFORE_SQL = "beforeSql";32 public static final String ATTR_RUN_SQL = "runSql";33 public static final String ATTR_AFTER_SQL = "afterSql";34

35 @ApiModelProperty(value = "INTERFACE_CONFIG_ID",label = "接口配置",dataType="varchar(36)",length="36",primary=true,required=true)36 privateString interfaceConfigId;37

38 @ApiModelProperty(value = "INTERFACE_CODE",label = "接口编码",dataType="varchar(50)",length="50")39 privateString interfaceCode;40

41 @ApiModelProperty(value = "INTERFACE_NAME",label = "接口名称",dataType="varchar(200)",length="200")42 privateString interfaceName;43

44 @ApiModelProperty(value = "FROM_SYSTEM",label = "源系统：DB0081",dataType="varchar(20)",length="20")45 privateString fromSystem;46

47 @ApiModelProperty(value = "TO_SYSTEM",label = "目标系统：DB0081",dataType="varchar(20)",length="20")48 privateString toSystem;49

50 @ApiModelProperty(value = "FREQENCY",label = "接口频率",dataType="integer",length="0")51 privateInteger freqency;52

53 @ApiModelProperty(value = "FREQENCY_TYPE",label = "频率类别：DB0082",dataType="varchar(2)",length="2")54 privateString freqencyType;55

56 @ApiModelProperty(value = "BEGIN_RUN_TIME",label = "开始运行时间",dataType="datetime",length="0")57 @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")58 privateDate beginRunTime;59

60 @ApiModelProperty(value = "NEXT_RUN_TIME",label = "下次运行时间",dataType="datetime",length="0")61 @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")62 privateDate nextRunTime;63

64 @ApiModelProperty(value = "TIME_RUN_YESTERDAY",label = "N点之前执行前一天数据",dataType="integer",length="0")65 privateInteger timeRunYesterday;66

67 @ApiModelProperty(value = "CREATOR",label = "创建人",dataType="varchar(50)",length="50",comment="创建人")68 privateString creator;69

70 @ApiModelProperty(value = "CREATED_DATE",label = "创建时间",dataType="datetime",length="0",comment="创建时间")71 @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")72 privateDate createdDate;73

74 @ApiModelProperty(value = "MODIFIER",label = "最后更新人员",dataType="varchar(50)",length="50",comment="最后更新人员")75 privateString modifier;76

77 @ApiModelProperty(value = "LAST_UPDATED_DATE",label = "最后更新时间",dataType="timestamp",length="0",comment="最后更新时间")78 @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")79 privateDate lastUpdatedDate;80

81 @ApiModelProperty(value = "IS_ENABLE",label = "是否可用",dataType="varchar(2)",length="2",comment="是否可用 1启用,0禁用,2删除")82 privateString isEnable;83

84 @ApiModelProperty(value = "UPDATE_CONTROL_ID",label = "并发控制字段",dataType="varchar(36)",length="36",comment="并发控制字段")85 privateString updateControlId;86

87 publicString getInterfaceConfigId() {88 returninterfaceConfigId;89 }90

91 public voidsetInterfaceConfigId(String interfaceConfigId) {92 this.interfaceConfigId =interfaceConfigId;93 }94

95 publicString getInterfaceCode() {96 returninterfaceCode;97 }98

99 public voidsetInterfaceCode(String interfaceCode) {100 this.interfaceCode =interfaceCode;101 }102

103 publicString getInterfaceName() {104 returninterfaceName;105 }106

107 public voidsetInterfaceName(String interfaceName) {108 this.interfaceName =interfaceName;109 }110

111 publicString getFromSystem() {112 returnfromSystem;113 }114

115 public voidsetFromSystem(String fromSystem) {116 this.fromSystem =fromSystem;117 }118

119 publicString getToSystem() {120 returntoSystem;121 }122

123 public voidsetToSystem(String toSystem) {124 this.toSystem =toSystem;125 }126

127 publicInteger getFreqency() {128 returnfreqency;129 }130

131 public voidsetFreqency(Integer freqency) {132 this.freqency =freqency;133 }134

135 publicString getFreqencyType() {136 returnfreqencyType;137 }138

139 public voidsetFreqencyType(String freqencyType) {140 this.freqencyType =freqencyType;141 }142

143 @JsonFormat(locale = "zh", timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")144 publicDate getBeginRunTime() {145 returnbeginRunTime;146 }147

148 public voidsetBeginRunTime(Date beginRunTime) {149 this.beginRunTime =beginRunTime;150 }151

152 @JsonFormat(locale = "zh", timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")153 publicDate getNextRunTime() {154 returnnextRunTime;155 }156

157 public voidsetNextRunTime(Date nextRunTime) {158 this.nextRunTime =nextRunTime;159 }160

161 publicInteger getTimeRunYesterday() {162 returntimeRunYesterday;163 }164

165 public voidsetTimeRunYesterday(Integer timeRunYesterday) {166 this.timeRunYesterday =timeRunYesterday;167 }168

169 publicString getCreator() {170 returncreator;171 }172

173 public voidsetCreator(String creator) {174 this.creator =creator;175 }176

177 @JsonFormat(locale = "zh", timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")178 publicDate getCreatedDate() {179 returncreatedDate;180 }181

182 public voidsetCreatedDate(Date createdDate) {183 this.createdDate =createdDate;184 }185

186 publicString getModifier() {187 returnmodifier;188 }189

190 public voidsetModifier(String modifier) {191 this.modifier =modifier;192 }193

194 @JsonFormat(locale = "zh", timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")195 publicDate getLastUpdatedDate() {196 returnlastUpdatedDate;197 }198

199 public voidsetLastUpdatedDate(Date lastUpdatedDate) {200 this.lastUpdatedDate =lastUpdatedDate;201 }202

203 publicString getIsEnable() {204 returnisEnable;205 }206

207 public voidsetIsEnable(String isEnable) {208 this.isEnable =isEnable;209 }210

211 publicString getUpdateControlId() {212 returnupdateControlId;213 }214

215 public voidsetUpdateControlId(String updateControlId) {216 this.updateControlId =updateControlId;217 }218 }

DbInterfaceConfigBean

1 packagecom.dbs.dmsmdm.bean.simple;2

3 importcom.dbs.dms.uibase.bean.BaseBean;4 importjava.util.Date;5

14 * 接口运行日志15 * 实体类对应的数据表为： t_m_db_interface_log16 *@author$Author17 * @date Tue Apr 17 11:21:18 GMT+08:00 201818 */

19 @ApiModel(value ="DbInterfaceLogBean")20 public class DbInterfaceLogBean extendsBaseBean {21 public static final String ATTR_IFTERFACE_LOG_ID = "ifterfaceLogId";22 public static final String ATTR_INTERFACE_CODE = "interfaceCode";23 public static final String ATTR_INTERFACE_NAME = "interfaceName";24 public static final String ATTR_FROM_SYSTEM = "fromSystem";25 public static final String ATTR_TO_SYSTEM = "toSystem";26 public static final String ATTR_ERROR_TIME = "errorTime";27 public static final String ATTR_ERROR_MSG = "errorMsg";28

29 @ApiModelProperty(value = "IFTERFACE_LOG_ID",label = "IFTERFACE_LOG_ID",dataType="varchar(36)",length="36",primary=true,required=true,comment="ID")30 privateString ifterfaceLogId;31

32 @ApiModelProperty(value = "INTERFACE_CODE",label = "接口编码",dataType="varchar(50)",length="50",comment="接口编码")33 privateString interfaceCode;34

35 @ApiModelProperty(value = "INTERFACE_NAME",label = "接口名称",dataType="varchar(200)",length="200",comment="接口名称")36 privateString interfaceName;37

38 @ApiModelProperty(value = "FROM_SYSTEM",label = "源系统",dataType="varchar(20)",length="20",comment="源系统：DB0081")39 privateString fromSystem;40

41 @ApiModelProperty(value = "TO_SYSTEM",label = "目标系统",dataType="varchar(20)",length="20",comment="目标系统：DB0081")42 privateString toSystem;43

44 @ApiModelProperty(value = "ERROR_TIME",label = "错误时间",dataType="datetime",length="0",comment="错误时间")45 @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")46 privateDate errorTime;47

48 @ApiModelProperty(value = "CREATOR",label = "创建人",dataType="varchar(50)",length="50",comment="创建人")49 privateString creator;50

51 @ApiModelProperty(value = "CREATED_DATE",label = "创建时间",dataType="datetime",length="0",comment="创建时间")52 @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")53 privateDate createdDate;54

55 @ApiModelProperty(value = "MODIFIER",label = "最后更新人员",dataType="varchar(50)",length="50",comment="最后更新人员")56 privateString modifier;57

58 @ApiModelProperty(value = "LAST_UPDATED_DATE",label = "最后更新时间",dataType="timestamp",length="0",comment="最后更新时间")59 @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")60 privateDate lastUpdatedDate;61

62 @ApiModelProperty(value = "IS_ENABLE",label = "是否可用",dataType="varchar(2)",length="2",comment="是否可用 1启用,0禁用")63 privateString isEnable;64

65 @ApiModelProperty(value = "UPDATE_CONTROL_ID",label = "并发控制字段",dataType="varchar(36)",length="36",comment="并发控制字段")66 privateString updateControlId;67

68 @ApiModelProperty(value = "ERROR_MSG",label = "错误信息",dataType="text",length="0",comment="记录数据库报错信息")69 privateString errorMsg;70

71 publicString getIfterfaceLogId() {72 returnifterfaceLogId;73 }74

75 public voidsetIfterfaceLogId(String ifterfaceLogId) {76 this.ifterfaceLogId =ifterfaceLogId;77 }78

79 publicString getInterfaceCode() {80 returninterfaceCode;81 }82

83 public voidsetInterfaceCode(String interfaceCode) {84 this.interfaceCode =interfaceCode;85 }86

87 publicString getInterfaceName() {88 returninterfaceName;89 }90

91 public voidsetInterfaceName(String interfaceName) {92 this.interfaceName =interfaceName;93 }94

95 publicString getFromSystem() {96 returnfromSystem;97 }98

99 public voidsetFromSystem(String fromSystem) {100 this.fromSystem =fromSystem;101 }102

103 publicString getToSystem() {104 returntoSystem;105 }106

107 public voidsetToSystem(String toSystem) {108 this.toSystem =toSystem;109 }110

111 @JsonFormat(locale = "zh", timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")112 publicDate getErrorTime() {113 returnerrorTime;114 }115

116 public voidsetErrorTime(Date errorTime) {117 this.errorTime =errorTime;118 }119

120 publicString getCreator() {121 returncreator;122 }123

124 public voidsetCreator(String creator) {125 this.creator =creator;126 }127

128 @JsonFormat(locale = "zh", timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")129 publicDate getCreatedDate() {130 returncreatedDate;131 }132

133 public voidsetCreatedDate(Date createdDate) {134 this.createdDate =createdDate;135 }136

137 publicString getModifier() {138 returnmodifier;139 }140

141 public voidsetModifier(String modifier) {142 this.modifier =modifier;143 }144

145 @JsonFormat(locale = "zh", timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")146 publicDate getLastUpdatedDate() {147 returnlastUpdatedDate;148 }149

150 public voidsetLastUpdatedDate(Date lastUpdatedDate) {151 this.lastUpdatedDate =lastUpdatedDate;152 }153

154 publicString getIsEnable() {155 returnisEnable;156 }157

158 public voidsetIsEnable(String isEnable) {159 this.isEnable =isEnable;160 }161

162 publicString getUpdateControlId() {163 returnupdateControlId;164 }165

166 public voidsetUpdateControlId(String updateControlId) {167 this.updateControlId =updateControlId;168 }169

170 publicString getErrorMsg() {171 returnerrorMsg;172 }173

174 public voidsetErrorMsg(String errorMsg) {175 this.errorMsg =errorMsg;176 }177 }

DbInterfaceLogBean

二、系统定时任务类(InitQuartzJob)

参考地址：https://blog.csdn.net/u014723529/article/details/51291289

1、Quartz工具版本说明

spring3.1以下的版本必须使用quartz1.x系列，3.1以上的版本才支持quartz 2.x，不然会出错。

原因：spring对于quartz的支持实现，org.springframework.scheduling.quartz.CronTriggerBean继承了org.quartz.CronTrigger。在quartz1.x系列中org.quartz.CronTrigger是个类，而在quartz2.x系列中org.quartz.CronTrigger变成了接口，从而造成无法用spring的方式配置quartz的触发器(trigger)。

开发所选版本：spring5.x系列，quartz以及quartz-jobs版本2.3.0。

2、开发流程

①、类实现ApplicationContextAware接口，重写方法，获取上下文application，写一个init()方法初始化；

②、在初始化方法中通过上下文获取调度工厂SchedulerFactoryBean；

③、通过调度工厂生产调度Scheduler；

④、获取数据库资源；

⑤、通过单表类的服务层访问接口配置表，得到集合；

⑥、遍历接口配置集合，忽略掉null的对象和状态为不可用的对象(非1)；

⑦、根据接口对象的接口编码得到触发器键TriggerKey;

⑧、然后用调度构建出触发器CronTrigger，相当于在spring配置文件中定义的bean id=”myTrigger”;

⑨、再根据接口类型、接口频率、运行日期、运行时间生成Cron表达式；

⑩、当触发器不存在时，将执行任务的类绑定给JobDetail，并将需要传递的参数放入到JobDetail的Map中；

⑩①、用表达式调度构建器生成新的触发器，将JobDetail和触发器添加到调度中，到此已完成动态调度任务的添加；

⑩②、如果触发器已经存在，则只需要用表达式调度构建器生成新的触发器，将新触发器添加到调度中。

代码如下：

1 packagecom.dbs.stat.quartz;2

3 importjava.util.Calendar;4 importjava.util.HashMap;5 importjava.util.List;6 importjava.util.Map;7

8 importjavax.sql.DataSource;9

10 importorg.quartz.CronScheduleBuilder;11 importorg.quartz.CronTrigger;12 importorg.quartz.JobBuilder;13 importorg.quartz.JobDataMap;14 importorg.quartz.JobDetail;15 importorg.quartz.Scheduler;16 importorg.quartz.SchedulerException;17 importorg.quartz.TriggerBuilder;18 importorg.quartz.TriggerKey;19 importorg.slf4j.Logger;20 importorg.slf4j.LoggerFactory;21 importorg.springframework.beans.BeansException;22 importorg.springframework.beans.factory.annotation.Autowired;23 importorg.springframework.context.ApplicationContext;24 importorg.springframework.context.ApplicationContextAware;25 importorg.springframework.scheduling.quartz.SchedulerFactoryBean;26 importorg.springframework.stereotype.Component;27

28 importcom.dbs.dmsmdm.bean.simple.DbInterfaceConfigBeanWithBLOBs;29 importcom.dbs.dmsmdm.service.simple.DbInterfaceConfigBeanService;30 importcom.dbs.dmsmdm.service.simple.DbInterfaceLogBeanService;31

32 /**

33 * 时间调度类，动态添加定时任务34 *35 *@authoryeting36 *37 */

38 @Component39 public class InitQuartzJob implementsApplicationContextAware {40 private static final Logger logger = LoggerFactory.getLogger(InitQuartzJob.class);//日志

41 private static ApplicationContext applicationContext;//上下文

42 public static SchedulerFactoryBean schedulerFactoryBean = null;//调度工厂

44 @Autowired45 DbInterfaceConfigBeanService dbInterfaceConfigBeanService;//注入接口配置表的服务层

47 @Autowired48 DbInterfaceLogBeanService dbInterfaceLogBeanService;//注入接口运行日志表的服务层

50 @Override51 public void setApplicationContext(ApplicationContext applicationContext) throwsBeansException {52 if (null == this.applicationContext) {53 this.applicationContext = applicationContext;//获取上下文

54 }55

56 }57

58 /**

59 * 初始化方法60 */

61 public voidinit() {62 DataSource dataSource = (DataSource)applicationContext.getBean("dataSource");//连接数据库的资源

63 schedulerFactoryBean = (SchedulerFactoryBean) applicationContext.getBean(SchedulerFactoryBean.class);//通过上下文获取调度工厂

64 Scheduler scheduler = schedulerFactoryBean.getScheduler();//通过调度工厂获取Scheduler

65 try{66 logger.info(scheduler.getSchedulerName());//输出日志信息

67 } catch(SchedulerException e1) {68 e1.printStackTrace();69 }70

71 List interfaceslist = dbInterfaceConfigBeanService.selectAllBean();//查询接口配置表

72 for (DbInterfaceConfigBeanWithBLOBs interfaceConfigBean : interfaceslist) {//遍历集合

73 if (null ==interfaceConfigBean) {74 continue;//空对象时进入下一轮循环

75 }76 if (!"1".equals(interfaceConfigBean.getIsEnable())){77 continue;//状态为不可用时进入下一轮循环

78 }79

80 logger.debug(scheduler + "...........................................add");//输出日志信息

81 TriggerKey triggerKey = TriggerKey.triggerKey(interfaceConfigBean.getInterfaceConfigId());//根据id得到触发器名

82 CronTrigger trigger=null;83 try{84 trigger = (CronTrigger) scheduler.getTrigger(triggerKey);//获取trigger，即在spring配置文件中定义的 bean id="myTrigger"

85 } catch(SchedulerException e1) {86 e1.printStackTrace();87 }88

89 Map map=new HashMap<>();//将需要传递给执行任务类的数据放入map中

90 map.put("interfaceConfigBean", interfaceConfigBean);91 map.put("dbInterfaceConfigBeanService", dbInterfaceConfigBeanService);92 map.put("dbInterfaceLogBeanService", dbInterfaceLogBeanService);93 map.put("dataSource", dataSource);94

95 //利用工具类根据接口属性得到表达式

96 String CronExpression=QuartzUtil.getCronExpression(interfaceConfigBean.getFreqencyType(),97 interfaceConfigBean.getFreqency(),98 QuartzUtil.getFormatTime(interfaceConfigBean.getBeginRunTime()));99

100 if (null == trigger) {//如果触发器不存在，就创建一个

101 Class clazz =QuartzJobFactory.class;//执行计划任务的类

102

103 JobDetail jobDetail =JobBuilder.newJob(clazz)104 .withIdentity(interfaceConfigBean.getInterfaceCode())105 .usingJobData(newJobDataMap(map))106 .build();//任务执行类,任务名，数据数组

107

108 CronScheduleBuilder scheduleBuilder = CronScheduleBuilder.cronSchedule(CronExpression);//表达式调度构建器

109 trigger = TriggerBuilder.newTrigger()//构建新的trigger

110 .withIdentity(interfaceConfigBean.getInterfaceConfigId())111 .withSchedule(scheduleBuilder)112 .build();113 try{114 scheduler.scheduleJob(jobDetail, trigger);//设置调度任务

115 } catch(SchedulerException e) {116 e.printStackTrace();117 }118 } else {//如果触发器已存在，那么更新相应的定时设置;

119 CronScheduleBuilder scheduleBuilder =CronScheduleBuilder.cronSchedule(CronExpression);120 trigger =trigger.getTriggerBuilder().withIdentity(triggerKey)121 .usingJobData(newJobDataMap(map))122 .withSchedule(scheduleBuilder).build();//按新的cronExpression表达式重新构建trigger;

123 try{124 scheduler.rescheduleJob(triggerKey, trigger);//按新的trigger重新设置job执行

125 } catch(SchedulerException e) {126 e.printStackTrace();127 }128 }129

130 interfaceConfigBean.setNextRunTime(QuartzUtil.getBeginRunTime(Calendar.getInstance(),interfaceConfigBean.getFreqencyType(),131 interfaceConfigBean.getFreqency(),QuartzUtil.getFormatTime(interfaceConfigBean.getBeginRunTime())));132 if(dbInterfaceConfigBeanService.updateOneBean(interfaceConfigBean)==1){//更新可运行接口的下次执行时间

133 logger.info("{}接口的下次执行时间为：{}",interfaceConfigBean.getInterfaceCode(),134 interfaceConfigBean.getNextRunTime());135 }136 }137 }138 }

InitQuartzJob

三、计划任务执行类(QuartzJobFactory)

1、开发流程

①、类实现Job接口，写一个execute方法，定时任务启动时会执行该方法；

②、在方法中接收JobDetail传过来的参数，包括数据库资源、接口配置对象信息等；

③、用数据库资源得到数据库连接，用JDBC技术执行sql语句，并将异常信息写入接口日志表；

④、更新数据库中对应接口对象的下次执行时间，关闭资源；

2、执行说明

①、可用状态不为‘1’的接口不会执行，且其下次执行时间会为null；

②、使用trim()方法去掉sql语句前后空格，程序只能识别以‘create’、‘insert’、‘update’、‘delete’开头的sql语句。

③、sql识别通不过的接口或报异常的接口的可用状态会被修改为‘0’，下次执行时间会被修改为null。

代码如下：

1 packagecom.dbs.stat.quartz;2

3 importjava.sql.Connection;4 importjava.sql.ResultSet;5 importjava.sql.SQLException;6 importjava.sql.Statement;7 importjava.util.Calendar;8 importjava.util.Date;9

10 importjavax.sql.DataSource;11

12 importorg.apache.commons.lang3.StringUtils;13 importorg.quartz.Job;14 importorg.quartz.JobDataMap;15 importorg.quartz.JobExecutionContext;16 importorg.quartz.JobExecutionException;17 importorg.slf4j.Logger;18 importorg.slf4j.LoggerFactory;19 importorg.springframework.stereotype.Component;20

21 importcom.dbs.dmsmdm.bean.simple.DbInterfaceConfigBeanWithBLOBs;22 importcom.dbs.dmsmdm.bean.simple.DbInterfaceLogBean;23 importcom.dbs.dmsmdm.service.simple.DbInterfaceConfigBeanService;24 importcom.dbs.dmsmdm.service.simple.DbInterfaceLogBeanService;25

26 /**

27 * 计划任务执行类28 *29 *@authoryeting30 *31 */

32 @Component33 public class QuartzJobFactory implementsJob{34 public static final Logger logger = LoggerFactory.getLogger(QuartzJobFactory.class);35

36 /**

37 * 任务执行方法38 */

39 public void execute(JobExecutionContext context) throwsJobExecutionException {40 Connection conn = null;41 Statement st=null;42 ResultSet rs=null;43 JobDataMap jobDataMap=context.getJobDetail().getJobDataMap();44 DataSource dataSource = (DataSource) jobDataMap.get("dataSource");//接收数据库连接资源

45 DbInterfaceConfigBeanService dbInterfaceConfigBeanService=(DbInterfaceConfigBeanService) jobDataMap.get("dbInterfaceConfigBeanService");46 DbInterfaceLogBeanService dbInterfaceLogBeanService=(DbInterfaceLogBeanService) jobDataMap.get("dbInterfaceLogBeanService");47 DbInterfaceConfigBeanWithBLOBs interfaceConfigBean = (DbInterfaceConfigBeanWithBLOBs) jobDataMap.get("interfaceConfigBean");48 DbInterfaceLogBean interfaceLogBean=(DbInterfaceLogBean) QuartzUtil.getInitializedObject(newDbInterfaceLogBean(),49 interfaceConfigBean, 1);//初始化日志对象

50 String beforeSql=interfaceConfigBean.getBeforeSql();51 String runSql=interfaceConfigBean.getRunSql();52 String afterSql=interfaceConfigBean.getAfterSql();53 Calendar calendar=Calendar.getInstance();//任务启动时间日历类

55 logger.info("{}任务启动中。。。",interfaceConfigBean.getInterfaceCode());56 if(!"1".equals(interfaceConfigBean.getIsEnable())){57 return;//状态不可用就结束方法

58 }59 logger.info("{}接口已启动",interfaceConfigBean.getInterfaceCode());60

61 boolean flag=true;62 try{63 conn=dataSource.getConnection();64 conn.setAutoCommit(false);//打开事务边界，就是取消自动提交，改为手动

65 st=conn.createStatement();66

67 /*st.addBatch("sqls[i]");68 st.executeBatch();*/

70 if(StringUtils.isNotBlank(beforeSql)){71 beforeSql=beforeSql.trim();72 if(QuartzUtil.getBooleanByCheckSql(beforeSql)){73 String sqls[]=beforeSql.split(";");74 for(int i=0;i

88 if(StringUtils.isNotBlank(runSql)){//判断语句是否为空

89 runSql=runSql.trim();//去掉前后空格

90 if(QuartzUtil.getBooleanByCheckSql(runSql)){//判断语句是否可用

91 String sqls[]=runSql.split(";");//将多条语句按；切分开

92 for(int i=0;i

95 }96 logger.debug("{}接口 runSql执行中,共{}条",interfaceConfigBean.getInterfaceCode(),sqls.length);//输出日志信息

97 st.executeBatch();//一次执行多条SQL语句

98 }else{99 flag=false;100 interfaceLogBean.setErrorMsg("runSql ERROR");101 dbInterfaceLogBeanService.insertSystemLog(interfaceLogBean);//将错误信息插入到日志表

102 return;103 }104 }105

106 if(StringUtils.isNotBlank(afterSql)){107 afterSql=afterSql.trim();108 if(QuartzUtil.getBooleanByCheckSql(afterSql)){109 String sqls[]=afterSql.split(";");110 for(int i=0;i

124 if(flag==true){125 interfaceLogBean.setErrorMsg("SUCCESS");126 dbInterfaceLogBeanService.insertSystemLog(interfaceLogBean);//将成功信息插入到日志表

127 }128

129 conn.commit();//提交事务，正常结束

130 } catch (Exception e) {//捕获所有异常

131 e.printStackTrace();132 try{133 conn.rollback();//有异常发生就回滚事务，是为了保证释放锁

134 } catch(SQLException e1) {135 e1.printStackTrace();136 }137

138 flag=false;139 interfaceLogBean.setErrorMsg("ERROR："+e.getMessage());140 dbInterfaceLogBeanService.insertSystemLog(interfaceLogBean);//将错误信息插入到日志表

141 } finally{142 if(flag==true){//利用工具类根据接口属性,设置接口的下次运行时间

143 interfaceConfigBean.setNextRunTime(QuartzUtil.getNextRunTime(calendar,144 interfaceConfigBean.getFreqencyType(),interfaceConfigBean.getFreqency(),145 QuartzUtil.getFormatTime(interfaceConfigBean.getBeginRunTime())));146 }else{147 interfaceConfigBean.setIsEnable("0");//将接口状态设置为不可用

148 interfaceConfigBean.setNextRunTime(null);//接口的下次运行时间为null

149 }150

151 if(dbInterfaceConfigBeanService.updateOneBean(interfaceConfigBean)==1){//更新接口对象

152 logger.debug("{}接口信息已更新,下次执行时间：{}",interfaceConfigBean.getInterfaceCode(),153 interfaceConfigBean.getNextRunTime());154 }else{155 logger.debug("{}接口信息更新失败",interfaceConfigBean.getInterfaceCode());156 }157

158 QuartzUtil.closeAll(conn, st, rs);//关闭数据库资源

159 logger.info("{}接口已关闭",interfaceConfigBean.getInterfaceCode());160 }161 }162 }

QuartzJobFactory

四、工具类(QuartzUtil)

本人表示写得很烂，工具类代码里面涉及到业务的代码请忽略。。。

1 packagecom.dbs.stat.quartz;2

3 importjava.sql.Connection;4 importjava.sql.ResultSet;5 importjava.sql.SQLException;6 importjava.sql.Statement;7 importjava.text.SimpleDateFormat;8 importjava.util.Calendar;9 importjava.util.Date;10

11 importcom.dbs.dmsmdm.bean.simple.DbInterfaceConfigBeanWithBLOBs;12 importcom.dbs.dmsmdm.bean.simple.DbInterfaceLogBean;13

14 /**

15 * 工具类，优化代码结构16 *@authoryeting17 *18 */

19 public classQuartzUtil {20

21 /**

22 * 将日期对象转换为日时分秒数组23 *@authoryeting24 *@parambeginRunTime 日期对象25 *@return返回日时分秒数组26 */

27 public static int[] getFormatTime(Date beginRunTime){28 int runtime[]=null;29 String Hms="";30 if(null !=beginRunTime){31 SimpleDateFormat sdf=new SimpleDateFormat("dd HH:mm:ss");32 Hms=sdf.format(beginRunTime);33

34 runtime=new int[4];35 runtime[0]=Integer.valueOf(Hms.substring(0, 2));36 runtime[1]=Integer.valueOf(Hms.substring(3, 5));37 runtime[2]=Integer.valueOf(Hms.substring(6, 8));38 runtime[3]=Integer.valueOf(Hms.substring(9));39 }else{40 runtime=new int[]{1,0,0,0};//默认值

41 }42 returnruntime;43 }44

45 /**

46 * 根据接口属性生成表达式47 *@authoryeting48 *@paramfreqencyType 频率类型49 *@paramfreqency 频率值50 *@paramrunTime 日时分秒数组51 *@return返回表达式52 */

53 public static String getCronExpression(String freqencyType,int freqency,int[] runTime){54 String CronExpression="";//根据接口的执行频率写表达式

55 switch(freqencyType){56 case "1"://从某分某秒开始，之后每几分钟/次

57 CronExpression=runTime[3]+" "+runTime[2]+"/"+(freqency%60!=0?freqency%60:59)+" * * * ?";58 break;59 case "2"://从某时某分某秒开始，之后每几小时/次

60 CronExpression=runTime[3]+" "+runTime[2]+" "+runTime[1]+"/"+(freqency%24!=0?freqency%24:23)+" * * ?";61 break;62 case "3"://某时某分某秒固定执行，每几天/次

63 CronExpression=runTime[3]+" "+runTime[2]+" "+runTime[1]+" */"+(freqency%31!=0?freqency%31:31)+" * ?";64 break;65 case "4"://某天某时某分某秒固定执行，每几月/次

66 CronExpression=runTime[3]+" "+runTime[2]+" "+runTime[1]+" "+runTime[0]+" */"+(freqency%12!=0?freqency%12:12)+" ?";67 break;68 default://默认每月1号凌晨00:00:00执行

69 CronExpression="0 0 0 1 * ?";70 break;71 }72 returnCronExpression;73 }74

75 /**

76 * 计算任务的第一次运行时间77 *@authoryeting78 *@paramcalendar 当前时间日历类79 *@paramfreqencyType 频率类型80 *@paramfreqency 频率81 *@paramrunTime 日时分秒数组82 *@return返回日期对象83 */

84 public static Date getBeginRunTime(Calendar calendar,String freqencyType,int freqency,int[] runTime){85 switch(freqencyType) {86 case "1"://分钟/次

87 freqency=(freqency%60!=0?freqency%60:59);88 for(int i=0;i<59;i++){89 if(runTime[2]+freqency*i<60){90 if(runTime[2]+freqency*i>=calendar.get(Calendar.MINUTE)){//如果现在分钟小于下次运行分钟，那么取下次运行分钟

91 calendar.set(Calendar.MINUTE, runTime[2]+freqency*i);92 break;93 }94 if(runTime[2]+freqency*i==calendar.get(Calendar.MINUTE)){//如果现在分钟等于下次运行分钟

95 if(runTime[3]>=calendar.get(Calendar.SECOND)){//如果现在秒钟小于等于开始运行秒钟，那么取开始运行分钟

96 calendar.set(Calendar.MINUTE, runTime[2]+freqency*i);97 break;98 }99 }100 }else{//否则就到下一个小时

101 calendar.set(Calendar.MINUTE, runTime[2]);102 calendar.add(Calendar.HOUR_OF_DAY, 1);103 break;104 }105 }106 calendar.set(Calendar.SECOND, runTime[3]);107 break;108 case "2"://小时/次

109 freqency=(freqency%24!=0?freqency%24:23);110 for(int i=0;i<24;i++){111 if(runTime[1]+freqency*i<24){112 if(runTime[1]+freqency*i>calendar.get(Calendar.HOUR_OF_DAY)){113 calendar.set(Calendar.HOUR_OF_DAY, runTime[1]+freqency*i);114 break;115 }116 if(runTime[1]+freqency*i==calendar.get(Calendar.HOUR_OF_DAY)){117 if(runTime[2]>calendar.get(Calendar.MINUTE)){118 calendar.set(Calendar.HOUR_OF_DAY, runTime[1]+freqency*i);119 break;120 }121 if(runTime[2]==calendar.get(Calendar.MINUTE)){122 if(runTime[3]>=calendar.get(Calendar.SECOND)){123 calendar.set(Calendar.HOUR_OF_DAY, runTime[1]+freqency*i);124 break;125 }126 }127 }128 }else{129 calendar.set(Calendar.HOUR_OF_DAY, runTime[1]);130 calendar.add(Calendar.DAY_OF_MONTH, 1);131 break;132 }133 }134 calendar.set(Calendar.SECOND, runTime[3]);135 calendar.set(Calendar.MINUTE, runTime[2]);136 break;137 case "3"://天/次

138 Boolean b=false;139 if(runTime[1]>calendar.get(Calendar.HOUR_OF_DAY)){140 b=true;141 }142 if(runTime[1]==calendar.get(Calendar.HOUR_OF_DAY)){143 if(runTime[2]>calendar.get(Calendar.MINUTE)){144 b=true;145 }146 if(runTime[2]==calendar.get(Calendar.MINUTE)){147 if(runTime[3]>=calendar.get(Calendar.SECOND)){148 b=true;149 }150 }151 }152 if(b==false){153 calendar.add(Calendar.DAY_OF_MONTH,1);154 }155 calendar.set(Calendar.HOUR_OF_DAY, runTime[1]);156 calendar.set(Calendar.MINUTE, runTime[2]);157 calendar.set(Calendar.SECOND, runTime[3]);158 break;159 case "4"://月/次

160 Boolean bb=false;161 if(runTime[0]>calendar.get(Calendar.DAY_OF_MONTH)){162 bb=true;163 }164 if(runTime[0]==calendar.get(Calendar.DAY_OF_MONTH)){165 if(runTime[1]>calendar.get(Calendar.HOUR_OF_DAY)){166 bb=true;167 }168 if(runTime[1]==calendar.get(Calendar.HOUR_OF_DAY)){169 if(runTime[2]>calendar.get(Calendar.MINUTE)){170 bb=true;171 }172 if(runTime[2]==calendar.get(Calendar.MINUTE)){173 if(runTime[3]>=calendar.get(Calendar.SECOND)){174 bb=true;175 }176 }177 }178 }179 if(bb==false){180 calendar.add(Calendar.MONTH,1);181 }182 calendar.set(Calendar.DAY_OF_MONTH,runTime[0]);183 calendar.set(Calendar.HOUR_OF_DAY, runTime[1]);184 calendar.set(Calendar.MINUTE, runTime[2]);185 calendar.set(Calendar.SECOND, runTime[3]);186 break;187 default:188 calendar.add(Calendar.MONTH,1);189 break;190 }191

192 Date date=calendar.getTime();193 returndate;194 }195

196 /**

197 * 任务启动后计算下次运行时间198 *@authoryeting199 *@paramCalendar 任务启动时的时间日历类200 *@paramfreqencyType 频率类型201 *@paramfreqency 频率202 *@paramrunTime 日时分秒数组203 *@return返回日期对象204 */

205 public static Date getNextRunTime(Calendar calendar,String freqencyType,int freqency,int[] runTime){206 switch(freqencyType) {207 case "1"://分钟/次

208 freqency=(freqency%60!=0?freqency%60:59);209 if(calendar.get(Calendar.MINUTE)+freqency>59){//超过59分钟，就重新从某分开始

210 calendar.add(Calendar.HOUR_OF_DAY, 1);211 calendar.set(Calendar.MINUTE, runTime[2]);212 }else{//否则分钟加上频率值

213 calendar.add(Calendar.MINUTE,freqency);214 }215 break;216 case "2"://小时/次

217 freqency=(freqency%24!=0?freqency%24:23);218 if(calendar.get(Calendar.HOUR_OF_DAY)+freqency>23){//超过23小时，就重新从某小时开始

219 calendar.add(Calendar.DAY_OF_MONTH, 1);220 calendar.set(Calendar.HOUR_OF_DAY, runTime[1]);221 }else{//否则小时加上频率值

222 calendar.add(Calendar.HOUR_OF_DAY,freqency);223 }224 break;225 case "3"://天/次

226 calendar.add(Calendar.DAY_OF_MONTH,(freqency%31!=0?freqency%31:31));227 break;228 case "4"://月/次

229 calendar.add(Calendar.MONTH,(freqency%12!=0?freqency%12:12));230 break;231 default:232 calendar.add(Calendar.MONTH,1);233 break;234 }235

236 Date date=calendar.getTime();237 returndate;238 }239

240 /**

241 * 初始化对象242 *@paraminterfaceLogBean 日志对象243 *@paraminterfaceConfigBean 接口对象244 *@paramtype 类型为1时初始化日志对象245 *@return返回对象246 */

247 public staticObject getInitializedObject(DbInterfaceLogBean interfaceLogBean,248 DbInterfaceConfigBeanWithBLOBs interfaceConfigBean,inttype){249 Object obj=null;250 switch(type){251 case 1:252 interfaceLogBean.setInterfaceCode(interfaceConfigBean.getInterfaceCode());253 interfaceLogBean.setInterfaceName(interfaceConfigBean.getInterfaceName());254 interfaceLogBean.setFromSystem(interfaceConfigBean.getFromSystem());255 interfaceLogBean.setToSystem(interfaceConfigBean.getToSystem());256 interfaceLogBean.setErrorTime(newDate());257 interfaceLogBean.setCreatedDate(newDate());258 interfaceLogBean.setCreator("System");259 interfaceLogBean.setModifier("System");260 interfaceLogBean.setLastUpdatedDate(newDate());261 interfaceLogBean.setIsEnable("1");262 obj=interfaceLogBean;263 break;264 }265 returnobj;266 }267

268 /**

269 * 判断sql语句是否可用270 *@paramsql 语句271 *@return可用返回true,不可用返回false272 */

273 public static booleangetBooleanByCheckSql(String sql){274 boolean flag=false;275 if(sql.length()>6){276 if("create".equalsIgnoreCase(sql.substring(0, 6))||"insert".equalsIgnoreCase(sql.substring(0, 6))277 ||"update".equalsIgnoreCase(sql.substring(0, 6))||"delete".equalsIgnoreCase(sql.substring(0, 6))){278 flag=true;279 }else{280 flag=false;281 }282 }283 returnflag;284 }285

286 /**

287 * 根据临界时间点修改sql语句的时间字段(？)288 *@paramsql 语句289 *@paramtimeRunYesterday 临界时间点290 *@return返回原sql或替换时间条件后的sql291 */

292 public static String getSqlByTimeRunYesterday(String sql,inttimeRunYesterday){293 Calendar calendar=Calendar.getInstance();294 String newTime="NOW()";295

296 if(timeRunYesterday>0 && timeRunYesterday<=23){297 int nowHour=calendar.get(Calendar.HOUR_OF_DAY);298 if(nowHour

300 SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");301 newTime="'"+sdf.format(calendar.getTime())+"'";302 }303 }304

305 //替换时间条件

306 if(0

313 /**

314 * 关闭数据库资源315 *@paramconn 连接316 *@paramst 火箭车317 *@paramrs 结果集318 */

319 public static voidcloseAll(Connection conn,Statement st,ResultSet rs){320 if(rs!=null){321 try{322 rs.close();323 } catch(SQLException e) {324 e.printStackTrace();325 }326 }327 if(st!=null){328 try{329 st.close();330 } catch(SQLException e) {331 e.printStackTrace();332 }333 }334 if(conn!=null){335 try{336 conn.close();337 } catch(SQLException e) {338 e.printStackTrace();339 }340 }341 }342 }

QuartzUtil

五、配置文件

在applicationContext.xml文件中注册上述(二+三)两个类，如下：

3 class="org.springframework.scheduling.quartz.SchedulerFactoryBean" />

6 init-method="init" lazy-init="false" />

配置文件

六、其他说明

1、页面输入设定的规则说明

(？代表执行频率)

①、？分钟/次：指定每？分钟执行一次。

详细解释：第一次启动时间为距离当前时间最近的下一分钟整，以后每隔？分钟启动一次；

②、？小时/次：指定每？小时执行一次。

详细解释: 第一次启动时间为距离当前时间最近的下一小时整，以后每隔？小时启动一次；

③、？天/次：指定每？天执行一次。

详细解释: 需要指定某个时间点(时分秒)。

第一次启动时间为距离当前时间最近的下一个时间点，以后每隔？天启动一次；

④、？月/次：指定每？月执行一次。

详细解释: 需要指定某个时间点(天时分秒)，目前只能指定1-28号执行，如果指定其他时间则默认为一个月的最后一天执行。

第一次启动时间为距离当前时间最近的下一个时间点，以后每隔？月启动一次；

⑤、？星期/次：暂未指定。

2、其他说明

如遇到复杂的定时任务，可在表中加一个Cron_Expression字段，直接写入表达式，在程序中直接获取该字段。

至此已完成服务启动添加————————————————————————————————————————————————————

接下来是实现手动添加————————————————————————————————————————————————————

七、实现手动添加的代码

控制层：

1 packagecom.dbs.stat.controller.quartzmanager;2

3 importjava.util.Calendar;4 importjava.util.HashMap;5 importjava.util.Map;6

7 importjavax.sql.DataSource;8

9 importorg.quartz.CronTrigger;10 importorg.quartz.Scheduler;11 importorg.quartz.SchedulerException;12 importorg.quartz.SchedulerFactory;13 importorg.quartz.TriggerKey;14 importorg.quartz.impl.StdSchedulerFactory;15 importorg.springframework.beans.factory.annotation.Autowired;16 importorg.springframework.context.ApplicationContext;17 importorg.springframework.context.support.ClassPathXmlApplicationContext;18 importorg.springframework.scheduling.quartz.SchedulerFactoryBean;19 importorg.springframework.stereotype.Controller;20 importorg.springframework.web.bind.annotation.RequestMapping;21 importorg.springframework.web.bind.annotation.ResponseBody;22

23 importcom.dbs.dms.config.AppUtil;24 importcom.dbs.dms.uibase.Result;25 importcom.dbs.dms.uibase.controller.BaseController;26 importcom.dbs.dmsmdm.bean.simple.DbInterfaceConfigBeanWithBLOBs;27 importcom.dbs.dmsmdm.service.simple.DbInterfaceConfigBeanService;28 importcom.dbs.dmsmdm.service.simple.DbInterfaceLogBeanService;29 importcom.dbs.stat.quartz.QuartzJobFactory;30 importcom.dbs.stat.quartz.QuartzUtil;31 importcom.dbs.stat.service.quartzmanager.QuartzManagerService;32

34 /**

35 * 手动管理定时任务36 *37 *@authoryeting38 *39 */

40 @Controller41 @RequestMapping("/quartz/Manager")42 public class QuartzManagerController extendsBaseController{43

44 @Autowired45 AppUtil appUtil;46

47 @Autowired48 DbInterfaceConfigBeanService dbInterfaceConfigBeanService;//注入接口配置表的服务层

50 @Autowired51 DbInterfaceLogBeanService dbInterfaceLogBeanService;//注入接口运行日志表的服务层

53 @Autowired54 QuartzManagerService quartzManagerService;//注入管理服务层

56 /**

57 * 修改定时任务58 */

59 @RequestMapping("/change")60 @ResponseBody61 publicResult changeJob(DbInterfaceConfigBeanWithBLOBs interfaceConfigBean) {62 DataSource dataSource = (DataSource)appUtil.getBean("dataSource");;//获取数据库连接资源

63 SchedulerFactoryBean schedulerFactoryBean=(SchedulerFactoryBean)appUtil.getBean(SchedulerFactoryBean.class);64 Map map=new HashMap<>();//将需要传递给执行任务类的数据放入map中

65 map.put("interfaceConfigBean", interfaceConfigBean);66 map.put("dbInterfaceConfigBeanService", dbInterfaceConfigBeanService);67 map.put("dbInterfaceLogBeanService", dbInterfaceLogBeanService);68 map.put("dataSource", dataSource);69

70 Boolean b=false;71 CronTrigger trigger=null;72 Scheduler scheduler=null;73 int effect=0;74 try{75 scheduler=schedulerFactoryBean.getScheduler();76 TriggerKey triggerKey = TriggerKey.triggerKey(interfaceConfigBean.getInterfaceConfigId());//id为触发器名

77 trigger =(CronTrigger) scheduler .getTrigger(triggerKey);78 } catch(SchedulerException e) {79 e.printStackTrace();80 }81

83 switch(interfaceConfigBean.getIsEnable()){84 case "0"://启用操作

85 interfaceConfigBean.setIsEnable("1");//将状态改为启用

86 interfaceConfigBean.setNextRunTime(QuartzUtil.getBeginRunTime(Calendar.getInstance(),interfaceConfigBean.getFreqencyType(),87 interfaceConfigBean.getFreqency(),QuartzUtil.getFormatTime(interfaceConfigBean.getBeginRunTime())));88 map.put("interfaceConfigBean", interfaceConfigBean);//将最新的对象传递过去！！很重要

89 if (trigger == null) {90 b=quartzManagerService.addJob(interfaceConfigBean, QuartzJobFactory.class, map,scheduler);//新增

91 }else{92 b=quartzManagerService.removeJob(interfaceConfigBean,scheduler);//停用

93 b=quartzManagerService.addJob(interfaceConfigBean, QuartzJobFactory.class, map,scheduler);//新增

94 }95 break;96 case "1"://禁用操作

97 if (trigger != null) {98 interfaceConfigBean.setIsEnable("0");//将状态改为停用

99 interfaceConfigBean.setNextRunTime(null);100 b=quartzManagerService.removeJob(interfaceConfigBean,scheduler);//停用

101 }102 break;103 }104

105 if(b==false){//操作失败

106 effect=0;107 }else{108 effect=dbInterfaceConfigBeanService.updateOneBean(interfaceConfigBean);//操作成功更新运行接口

109 }110 return buildDbResult(effect, null);111 }112 }

QuartzManagerController

服务层：

1 packagecom.dbs.stat.service.quartzmanager;2

3 importjava.util.Map;4

5 importorg.quartz.CronScheduleBuilder;6 importorg.quartz.CronTrigger;7 importorg.quartz.JobBuilder;8 importorg.quartz.JobDataMap;9 importorg.quartz.JobDetail;10 importorg.quartz.JobKey;11 importorg.quartz.Scheduler;12 importorg.quartz.SchedulerException;13 importorg.quartz.SchedulerFactory;14 importorg.quartz.Trigger;15 importorg.quartz.TriggerBuilder;16 importorg.quartz.TriggerKey;17 importorg.quartz.impl.StdSchedulerFactory;18 importorg.slf4j.Logger;19 importorg.slf4j.LoggerFactory;20 importorg.springframework.stereotype.Service;21

22 importcom.dbs.dmsmdm.bean.simple.DbInterfaceConfigBeanWithBLOBs;23 importcom.dbs.stat.controller.quartzmanager.QuartzManagerController;24 importcom.dbs.stat.quartz.QuartzJobFactory;25 importcom.dbs.stat.quartz.QuartzUtil;26

27 @Service28 public classQuartzManagerService {29 private static final Logger logger = LoggerFactory.getLogger(QuartzManagerController.class);//日志

31 /**

32 * 新增一个运行任务33 *@paraminterfaceConfigBean 接口对象34 *@paramclazz 执行任务的类35 *@parammap 传递给执行任务对象的数据数组36 *@paramscheduler 调度对象37 *@return成功返回true,异常返回false38 */

39 public Boolean addJob(DbInterfaceConfigBeanWithBLOBs interfaceConfigBean,Class clazz,Mapmap,Scheduler scheduler){40 Boolean b=true;41 try{42 JobDetail jobDetail= JobBuilder.newJob(QuartzJobFactory.class)43 .withIdentity(interfaceConfigBean.getInterfaceCode())44 .setJobData(newJobDataMap(map))45 .build();//任务执行类,任务名，任务组，数据数组

47 String CronExpression=QuartzUtil.getCronExpression(interfaceConfigBean.getFreqencyType(),48 interfaceConfigBean.getFreqency(),49 QuartzUtil.getFormatTime(interfaceConfigBean.getBeginRunTime()));50 CronScheduleBuilder scheduleBuilder = CronScheduleBuilder.cronSchedule(CronExpression);//表达式调度构建器

52 TriggerBuilder triggerBuilder = TriggerBuilder.newTrigger();//触发器

53 triggerBuilder.withIdentity(interfaceConfigBean.getInterfaceConfigId());//触发器名

54 triggerBuilder.startNow();55 triggerBuilder.withSchedule(scheduleBuilder);//触发器时间设定

57 CronTrigger trigger = (CronTrigger) triggerBuilder.build();//创建Trigger对象

58 scheduler.scheduleJob(jobDetail, trigger);//调度容器设置JobDetail和Trigger

59 if (!scheduler.isShutdown()) { //启动

60 scheduler.start();61 }62 logger.debug("{}...........................运行任务添加成功",interfaceConfigBean.getInterfaceCode());//输出日志信息

63 } catch(SchedulerException e) {64 b=false;65 e.printStackTrace();66 logger.info("{}...........................运行任务添加失败",interfaceConfigBean.getInterfaceCode());//输出日志信息

67 }68 returnb;69 }70

71 /**

72 * 修改一个运行任务(不可用)73 *@paraminterfaceConfigBean 接口对象74 *@paramtrigger CronTrigger对象75 *@parammap 数据集合76 *@paramscheduler 调度对象77 *@return成功返回true,异常返回false78 */

79 public Boolean modifyJob(DbInterfaceConfigBeanWithBLOBs interfaceConfigBean,CronTrigger trigger,Mapmap,Scheduler scheduler){80 Boolean b=true;81 try{82 TriggerKey triggerKey = TriggerKey.triggerKey(interfaceConfigBean.getInterfaceConfigId());//根据id得到触发器名

84 String CronExpression=QuartzUtil.getCronExpression(interfaceConfigBean.getFreqencyType(),85 interfaceConfigBean.getFreqency(),86 QuartzUtil.getFormatTime(interfaceConfigBean.getBeginRunTime()));87 CronScheduleBuilder scheduleBuilder = CronScheduleBuilder.cronSchedule(CronExpression);//表达式调度构建器

89 /*trigger = trigger.getTriggerBuilder().withIdentity(triggerKey)90 .usingJobData(new JobDataMap(map))91 .withSchedule(scheduleBuilder).build();//按新的cronExpression表达式重新构建trigger;92 */

93 JobKey jobKey =trigger.getJobKey();94 JobDetail detail =scheduler.getJobDetail(jobKey);95 detail.getJobDataMap().put("interfaceConfigBean", interfaceConfigBean);//重新给JobDetail中的数据赋值

97 scheduler.rescheduleJob(triggerKey, trigger);//修改

98 logger.debug("{}...........................运行任务修改成功",interfaceConfigBean.getInterfaceCode());//输出日志信息

99 } catch(SchedulerException e) {100 b=false;101 e.printStackTrace();102 logger.info("{}...........................运行任务修改失败",interfaceConfigBean.getInterfaceCode());//输出日志信息

103 }104 returnb;105 }106

107 /**

108 * 移除一个运行任务109 *@paraminterfaceConfigBean 接口对象110 *@paramscheduler 调度对象111 *@return成功返回true,异常返回false112 */

113 publicBoolean removeJob(DbInterfaceConfigBeanWithBLOBs interfaceConfigBean,Scheduler scheduler){114 Boolean b=true;115 try{116 TriggerKey triggerKey = TriggerKey.triggerKey(interfaceConfigBean.getInterfaceConfigId());//根据id得到触发器名

117 scheduler.pauseTrigger(triggerKey);//停止触发器

118 scheduler.unscheduleJob(triggerKey);//移除触发器

119 scheduler.deleteJob(JobKey.jobKey(interfaceConfigBean.getInterfaceCode()));//删除任务

120 logger.debug("{}...........................运行任务移除成功",interfaceConfigBean.getInterfaceCode());//输出日志信息

121 } catch(SchedulerException e) {122 b=false;123 e.printStackTrace();124 logger.info("{}...........................运行任务移除失败",interfaceConfigBean.getInterfaceCode());//输出日志信息

125 }126 returnb;127 }128

129 /**

130 *@paramscheduler 调度对象131 * 移除所有任务132 */

133 public voidshutdownJobs(Scheduler scheduler) {134 try{135 if (!scheduler.isShutdown()) {136 scheduler.shutdown();137 }138 } catch(Exception e) {139 throw newRuntimeException(e);140 }141 }142 }

QuartzManagerService

服务层中的修改定时任务不知为啥用不了，所以在控制层中要修改任务时就直接先删除再新增。。。

有一个BUG当系统中的该条任务未添加但数据库中存的却是已启用时，此时会禁用失败。。。

以上为本人根据实际项目经验总结并参考其他博主的文章写成，如有错误内容请指正。。。