系统用来每天插入视图数据。。。
一、数据库表设计
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
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_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;//调度工厂
43
44 @Autowired45     DbInterfaceConfigBeanService dbInterfaceConfigBeanService;//注入接口配置表的服务层
46
47 @Autowired48     DbInterfaceLogBeanService dbInterfaceLogBeanService;//注入接口运行日志表的服务层
49
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();//任务启动时间日历类
54
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();*/
69
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文件中注册上述(二+三)两个类，如下：
1 
2     
3     class="org.springframework.scheduling.quartz.SchedulerFactoryBean" />
4     
5     
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
33
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;//注入接口配置表的服务层
49
50 @Autowired51     DbInterfaceLogBeanService dbInterfaceLogBeanService;//注入接口运行日志表的服务层
52
53 @Autowired54     QuartzManagerService quartzManagerService;//注入管理服务层
55
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
82
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);//日志
30
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();//任务执行类,任务名，任务组，数据数组
46
47             String CronExpression=QuartzUtil.getCronExpression(interfaceConfigBean.getFreqencyType(),48 interfaceConfigBean.getFreqency(),49 QuartzUtil.getFormatTime(interfaceConfigBean.getBeginRunTime()));50             CronScheduleBuilder scheduleBuilder = CronScheduleBuilder.cronSchedule(CronExpression);//表达式调度构建器
51
52             TriggerBuilder triggerBuilder = TriggerBuilder.newTrigger();//触发器
53             triggerBuilder.withIdentity(interfaceConfigBean.getInterfaceConfigId());//触发器名
54 triggerBuilder.startNow();55             triggerBuilder.withSchedule(scheduleBuilder);//触发器时间设定
56
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得到触发器名
83
84             String CronExpression=QuartzUtil.getCronExpression(interfaceConfigBean.getFreqencyType(),85 interfaceConfigBean.getFreqency(),86 QuartzUtil.getFormatTime(interfaceConfigBean.getBeginRunTime()));87             CronScheduleBuilder scheduleBuilder = CronScheduleBuilder.cronSchedule(CronExpression);//表达式调度构建器
88
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中的数据赋值
96
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当系统中的该条任务未添加但数据库中存的却是已启用时，此时会禁用失败。。。
以上为本人根据实际项目经验总结并参考其他博主的文章写成，如有错误内容请指正。。。

1.主程序中添加代码，执行sql脚本


Spring 中提供了一个 jdbc 脚本执行器，使用这个工具可以非常方便的运行一个 sql 脚本文件，下面是这个方法：

ScriptUtils.executeSqlScript()

只需要传入它需要的参数即可。
下面代码运行 sql 目录中的四个脚本程序，每次运行都会删除四个数据库再重新创建，并初始化数据。
package cn.tedu.dbinit;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.support.EncodedResource;
import org.springframework.jdbc.datasource.init.ScriptUtils;

import javax.annotation.PostConstruct;
import javax.sql.DataSource;
import java.sql.SQLException;

@SpringBootApplication
public class DbInitApplication {

	@Autowired
	private DataSource dataSource;

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

	@PostConstruct
	public void init() throws SQLException {
		exec(dataSource, "sql/account.sql");
		exec(dataSource, "sql/storage.sql");
		exec(dataSource, "sql/order.sql");
		exec(dataSource, "sql/seata-server.sql");
	}

	private void exec(DataSource accountDatasource, String script) throws SQLException {
		ClassPathResource rc = new ClassPathResource(script, DbInitApplication.class.getClassLoader());
		EncodedResource er = new EncodedResource(rc, "utf-8");
		ScriptUtils.executeSqlScript(accountDatasource.getConnection(), er);
	}
}

SQL语句
分类：

数据定义语言；

数据查询语言；

数据操纵语言；

数据控制语言。

规范：

1、SQL语句可以单行书写，也可以多行书写，执行多条语句时，使用分号结尾。

2、SQL语句通常使用空格或缩进来增强代码的可读性。

3、SQL语句使用/**/或者#符号进行代码注释，增强代码可读性。

4、为规避关键字，常使用单反引号“ ` ”括住关键字完成规避。
代码示例
create database test character set utf8//创建test数据库
drop database test//删除test数据库
show databases//查询所有数据库
select database()//显示正在使用的数据库

Java 一次执行多条SQL


数据库 java sql 多条 jdbc

概要：
本实例将演示如何一次执行多条SQL语句，这些SQL语句可以是插入语句、更新语句和删除语句。本实例以myuser数据库中的staff表为例。



| |目录


1技术要点

2代码实现

3程序解读




1技术要点
一次执行多条SQL的技术要点如下：


DatabaseMetaData接口是描述有关数据库的整体综合信息，由于DatabaseMetaData是接口，所以没有构造方法，故不能使用new来创建DatabaseMetaData对象，但是可以通过Connection的getMetaData()方法创建。例如：DatabaseMetaData md=con.getMetaData()。



DatabaseMetaData类的supportsBatchUpdates方法用于判断此数据库是否支持批量更新。其返回值类型为boolean，如果此数据库支持批量更新，则返回true；否则返回false。



Statement的addBatch(String sql)方法将给定的SQL命令添加到此Statement对象的当前命令列表中，此方法可多次调用。



Statement的executeBatch()方法的作用是将一批命令提交给数据库来执行，如果全部命令执行成功，则返回更新计数组成的数组。





2代码实现


Java | 复制







1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103




package net.xsoftlab.baike;

import java.sql.Connection;

import java.sql.DatabaseMetaData;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class Batch {

    /** 判断数据库是否支持批处理 */

    public static boolean supportBatch(Connection con) {

        try {

            // 得到数据库的元数据

            DatabaseMetaData md = con.getMetaData();

            return md.supportsBatchUpdates();

        } catch (SQLException e) {

            e.printStackTrace();

        }

        return false;

    }

    /** 执行一批SQL语句 */

    public static int[] goBatch(Connection con, String[] sqls) throws Exception {

        if (sqls == null) {

            return null;

        }

        Statement sm = null;

        try {

            sm = con.createStatement();

            for (int i = 0; i < sqls.length; i++) {

                sm.addBatch(sqls[i]);// 将所有的SQL语句添加到Statement中

            }

            // 一次执行多条SQL语句

            return sm.executeBatch();

        } catch (SQLException e) {

            e.printStackTrace();

        } finally {

            sm.close();

        }

        return null;

    }

    public static void main(String[] args) throws Exception {

        System.out.println("没有执行批处理时的数据为：");

        query();

        String[] sqls = new String[3];

        sqls[0] = "UPDATE staff SET depart='Personnel' where name='mali'";

        sqls[1] = "INSERT INTO staff (name, age, sex,address, depart, worklen,wage) VALUES ('mali  ', 27, 'w', 'china','Technology','2','2300')";

        sqls[2] = "DELETE FROM staff where name='marry'";

        Connection con = null;

        try {

            con = getConnection();// 获得数据库连接

            boolean supportBatch = supportBatch(con); // 判断是否支持批处理

            System.out.println("支持批处理？ " + supportBatch);

            if (supportBatch) {

                int[] results = goBatch(con, sqls);// 执行一批SQL语句

                // 分析执行的结果

                for (int i = 0; i < sqls.length; i++) {

                    if (results[i] >= 0) {

                        System.out.println("语句: " + sqls[i] + " 执行成功，影响了"

                                + results[i] + "行数据");

                    } else if (results[i] == Statement.SUCCESS_NO_INFO) {

                        System.out.println("语句: " + sqls[i] + " 执行成功，影响的行数未知");

                    } else if (results[i] == Statement.EXECUTE_FAILED) {

                        System.out.println("语句: " + sqls[i] + " 执行失败");

                    }

                }

            }

        } catch (ClassNotFoundException e1) {

            throw e1;

        } catch (SQLException e2) {

            throw e2;

        } finally {

            con.close();// 关闭数据库连接

        }

        System.out.println("执行批处理后的数据为：");

        query();

    }

    public static Connection getConnection() {// 数据库连接

        Connection con = null;

        try {

            Class.forName("com.mysql.jdbc.Driver");// 加载Mysql数据驱动

            con = DriverManager.getConnection(

                    "jdbc:mysql://localhost:3306/myuser", "root", "123456");// 创建数据连接

        } catch (Exception e) {

            System.out.println("数据库连接失败");

        }

        return con;

    }

    public static void query() throws Exception {// 查询所有的数据

        Connection con = getConnection();

        Statement st = con.createStatement();

        ResultSet rs = st.executeQuery("select * from staff");

        while (rs.next()) {

            String name = rs.getString("name");

            int age = rs.getInt("age");

            String sex = rs.getString("sex");

            String address = rs.getString("address");

            String depart = rs.getString("depart");

            String worklen = rs.getString("worklen");

            String wage = rs.getString("wage");

            System.out.println(name + " " + age + " " + sex + " " + address

                    + " " + depart + " " + worklen + " " + wage);

        }

    }

}









3程序解读


support_Batch()方法判断数据库是否支持SQL语句的批处理。通过Connection的getMetaData方法获得数据库的元数据对象DatabaseMetaData，再调用DatabaseMetaData supportsBatchUpdates方法判断数据库是否支持批处理。



startBatch()方法执行一组SQL语句。首先创建执行SQL语句的Statement对象，通过Statement类的addBatch方法将待执行SQL语句添加到执行缓冲区中，再调用executeBatch方法将执行缓冲区中的SQL语句全部执行，返回一个整型数组，如果数组元素的值大于等于0，则表示该语句执行成功，该值表示了执行该SQL语句修改的记录的行数；如果数组元素的值等于Statement.SUCCESS_NO_INFO常量，表示该语句也执行成功，但不知道具体修改了多少条记录；如果数组元素的值等于Statement.EXECUTE_FAILED常量，表示该语句执行失败。



getConnection()方法封装了数据库的连接方式。如果在程序中需要使用数据库，直接调用此方法即可。



query()方法的作用是查询数据库，传入执行查询语句的Statement对象和待执行的SQL语句，通过Statement的executeQuery方法执行SQL语句，返回一个ResultSet对象。再调用ResultSet的next()方法，根据字段名将数据取出，并打印在控制台上。