quartz 相关表的创建语句

建表同时建索引:
– 存储每一个已配置的 Job 的详细信息

	CREATE TABLE `QRTZ_JOB_DETAILS` (
	  `SCHED_NAME` varchar(120) NOT NULL,
	  `JOB_NAME` varchar(200) NOT NULL,
	  `JOB_GROUP` varchar(200) NOT NULL,
	  `DESCRIPTION` varchar(250) DEFAULT NULL,
	  `JOB_CLASS_NAME` varchar(250) NOT NULL,
	  `IS_DURABLE` varchar(1) NOT NULL,
	  `IS_NONCONCURRENT` varchar(1) NOT NULL,
	  `IS_UPDATE_DATA` varchar(1) NOT NULL,
	  `REQUESTS_RECOVERY` varchar(1) NOT NULL,
	  `JOB_DATA` blob,
	  PRIMARY KEY (`SCHED_NAME`,`JOB_NAME`,`JOB_GROUP`),
	  KEY `IDX_QRTZ_J_REQ_RECOVERY` (`SCHED_NAME`,`REQUESTS_RECOVERY`),
	  KEY `IDX_QRTZ_J_GRP` (`SCHED_NAME`,`JOB_GROUP`)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='存储每一个已配置的 Job 的详细信息';

– 存储已配置的 Trigger 的信息

CREATE TABLE  IF NOT EXISTS QRTZ_TRIGGERS (  
SCHED_NAME VARCHAR(120) NOT NULL,  
TRIGGER_NAME VARCHAR(200) NOT NULL,  
TRIGGER_GROUP VARCHAR(200) NOT NULL,  
JOB_NAME VARCHAR(200) NOT NULL,  
JOB_GROUP VARCHAR(200) NOT NULL,  
DESCRIPTION VARCHAR(250) NULL,  
NEXT_FIRE_TIME BIGINT(13) NULL,  
PREV_FIRE_TIME BIGINT(13) NULL,  
PRIORITY INTEGER NULL,  
TRIGGER_STATE VARCHAR(16) NOT NULL,  
TRIGGER_TYPE VARCHAR(8) NOT NULL,  
START_TIME BIGINT(13) NOT NULL,  
END_TIME BIGINT(13) NULL,  
CALENDAR_NAME VARCHAR(200) NULL,  
MISFIRE_INSTR SMALLINT(2) NULL,  
JOB_DATA BLOB NULL,  
PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),  
KEY `IDX_QRTZ_T_J` (SCHED_NAME,JOB_NAME,JOB_GROUP), 
KEY `IDX_QRTZ_T_JG` (SCHED_NAME,JOB_GROUP) ,
KEY `IDX_QRTZ_T_C` (SCHED_NAME,CALENDAR_NAME), 
KEY `IDX_QRTZ_T_G` (SCHED_NAME,TRIGGER_GROUP) ,
KEY `IDX_QRTZ_T_STATE` (SCHED_NAME,TRIGGER_STATE), 
KEY `IDX_QRTZ_T_N_G_STATE` (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP,TRIGGER_STATE) ,
KEY `IDX_QRTZ_T_NEXT_FIRE_TIME` (SCHED_NAME,NEXT_FIRE_TIME), 
KEY `IDX_QRTZ_T_NFT_ST` (SCHED_NAME,TRIGGER_STATE,NEXT_FIRE_TIME) ,
KEY `IDX_QRTZ_T_NFT_MISFIRE` (SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME) ,
KEY `IDX_QRTZ_T_NFT_ST_MISFIRE` (SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME,TRIGGER_STATE), 
KEY `IDX_QRTZ_T_NFT_ST_MISFIRE_GRP` (SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME,TRIGGER_GROUP,TRIGGER_STATE) ,
FOREIGN KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)  
REFERENCES QRTZ_JOB_DETAILS(SCHED_NAME,JOB_NAME,JOB_GROUP))  
ENGINE=InnoDB COMMENT='存储已配置的 Trigger 的信息';   

– 存储简单的 Trigger,包括重复次数,间隔,以及已触的次数

CREATE TABLE  IF NOT EXISTS QRTZ_SIMPLE_TRIGGERS (  
SCHED_NAME VARCHAR(120) NOT NULL,  
TRIGGER_NAME VARCHAR(200) NOT NULL,  
TRIGGER_GROUP VARCHAR(200) NOT NULL,  
REPEAT_COUNT BIGINT(7) NOT NULL,  
REPEAT_INTERVAL BIGINT(12) NOT NULL,  
TIMES_TRIGGERED BIGINT(10) NOT NULL,  
PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),  
FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)  
REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP))  
ENGINE=InnoDB COMMENT='存储简单的 Trigger,包括重复次数,间隔,以及已触的次数';   

– 存储 Cron Trigger,包括 Cron 表达式和时区信息

CREATE TABLE  IF NOT EXISTS  QRTZ_CRON_TRIGGERS (  
SCHED_NAME VARCHAR(120) NOT NULL,  
TRIGGER_NAME VARCHAR(200) NOT NULL,  
TRIGGER_GROUP VARCHAR(200) NOT NULL,  
CRON_EXPRESSION VARCHAR(120) NOT NULL,  
TIME_ZONE_ID VARCHAR(80),  
PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),  
FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)  
REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP))  
ENGINE=InnoDB COMMENT='存储 Cron Trigger,包括 Cron 表达式和时区信息';   




CREATE TABLE  IF NOT EXISTS  QRTZ_SIMPROP_TRIGGERS (            
SCHED_NAME VARCHAR(120) NOT NULL,  
TRIGGER_NAME VARCHAR(200) NOT NULL,  
TRIGGER_GROUP VARCHAR(200) NOT NULL,  
STR_PROP_1 VARCHAR(512) NULL,  
STR_PROP_2 VARCHAR(512) NULL,  
STR_PROP_3 VARCHAR(512) NULL,  
INT_PROP_1 INT NULL,  
INT_PROP_2 INT NULL,  
LONG_PROP_1 BIGINT NULL,  
LONG_PROP_2 BIGINT NULL,  
DEC_PROP_1 NUMERIC(13,4) NULL,  
DEC_PROP_2 NUMERIC(13,4) NULL,  
BOOL_PROP_1 VARCHAR(1) NULL,  
BOOL_PROP_2 VARCHAR(1) NULL,  
PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),  
FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)   
REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP))  
ENGINE=InnoDB;  

– Trigger 作为 Blob 类型存储(用于 Quartz 用户用 JDBC 创建他们自己定制的 Trigger 类型,JobStore 并不知道如何存储实例的时候)

CREATE TABLE  IF NOT EXISTS  QRTZ_BLOB_TRIGGERS (  
SCHED_NAME VARCHAR(120) NOT NULL,  
TRIGGER_NAME VARCHAR(200) NOT NULL,  
TRIGGER_GROUP VARCHAR(200) NOT NULL,  
BLOB_DATA BLOB NULL,  
PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),  
INDEX (SCHED_NAME,TRIGGER_NAME, TRIGGER_GROUP),  
FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)  
REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP))  
ENGINE=InnoDB COMMENT='Trigger 作为 Blob 类型存储(用于 Quartz 用户用 JDBC 创建他们自己定制的 Trigger 类型,<span style="color:#800080;">JobStore</span> 并不知道如何存储实例的时候)';    

– 以 Blob 类型存储 Quartz 的 Calendar 信息

CREATE TABLE  IF NOT EXISTS  QRTZ_CALENDARS (  
SCHED_NAME VARCHAR(120) NOT NULL,  
CALENDAR_NAME VARCHAR(200) NOT NULL,  
CALENDAR BLOB NOT NULL,  
PRIMARY KEY (SCHED_NAME,CALENDAR_NAME))  
ENGINE=InnoDB COMMENT='以 Blob 类型存储 Quartz 的 Calendar 信息';  

– 存储已暂停的 Trigger 组的信息

CREATE TABLE  IF NOT EXISTS  QRTZ_PAUSED_TRIGGER_GRPS (  
SCHED_NAME VARCHAR(120) NOT NULL,  
TRIGGER_GROUP VARCHAR(200) NOT NULL,  
PRIMARY KEY (SCHED_NAME,TRIGGER_GROUP))  
ENGINE=InnoDB COMMENT='存储已暂停的 Trigger 组的信息';   

– 存储与已触发的 Trigger 相关的状态信息,以及相联 Job 的执行信息

CREATE TABLE  IF NOT EXISTS  QRTZ_FIRED_TRIGGERS (  
SCHED_NAME VARCHAR(120) NOT NULL,  
ENTRY_ID VARCHAR(95) NOT NULL,  
TRIGGER_NAME VARCHAR(200) NOT NULL,  
TRIGGER_GROUP VARCHAR(200) NOT NULL,  
INSTANCE_NAME VARCHAR(200) NOT NULL,  
FIRED_TIME BIGINT(13) NOT NULL,  
SCHED_TIME BIGINT(13) NOT NULL,  
PRIORITY INTEGER NOT NULL,  
STATE VARCHAR(16) NOT NULL,  
JOB_NAME VARCHAR(200) NULL,  
JOB_GROUP VARCHAR(200) NULL,  
IS_NONCONCURRENT VARCHAR(1) NULL,  
REQUESTS_RECOVERY VARCHAR(1) NULL,  
PRIMARY KEY (SCHED_NAME,ENTRY_ID),
KEY `IDX_QRTZ_FT_TRIG_INST_NAME` (SCHED_NAME,INSTANCE_NAME), 
KEY `IDX_QRTZ_FT_INST_JOB_REQ_RCVRY` (SCHED_NAME,INSTANCE_NAME,REQUESTS_RECOVERY),
KEY `IDX_QRTZ_FT_J_G` (SCHED_NAME,JOB_NAME,JOB_GROUP),
KEY `IDX_QRTZ_FT_JG` (SCHED_NAME,JOB_GROUP), 
KEY `IDX_QRTZ_FT_T_G` (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP), 
KEY `IDX_QRTZ_FT_TG` (SCHED_NAME,TRIGGER_GROUP)) 
ENGINE=InnoDB COMMENT='存储与已触发的 Trigger 相关的状态信息,以及相联 Job 的执行信息'; 

– 存储少量的有关 Scheduler 的状态信息,和别的 Scheduler 实例(假如是用于一个集群中)

CREATE TABLE  IF NOT EXISTS  QRTZ_SCHEDULER_STATE (  
SCHED_NAME VARCHAR(120) NOT NULL,  
INSTANCE_NAME VARCHAR(200) NOT NULL,  
LAST_CHECKIN_TIME BIGINT(13) NOT NULL,  
CHECKIN_INTERVAL BIGINT(13) NOT NULL,  
PRIMARY KEY (SCHED_NAME,INSTANCE_NAME))  
ENGINE=InnoDB COMMENT='存储少量的有关 Scheduler 的状态信息,和别的 Scheduler 实例(假如是用于一个集群中)'; 

– 存储程序的悲观锁的信息(假如使用了悲观锁)

CREATE TABLE  IF NOT EXISTS  QRTZ_LOCKS (  
SCHED_NAME VARCHAR(120) NOT NULL,  
LOCK_NAME VARCHAR(40) NOT NULL,  
PRIMARY KEY (SCHED_NAME,LOCK_NAME))  
ENGINE=InnoDB COMMENT='存储程序的悲观锁的信息(假如使用了悲观锁)';   
在Spring Boot中,可以通过集成Quartz框架来实现动态创建定时任务。下面是一个简单的示例,演示如何查询数据库来创建定时任务。 首先,需要在pom.xml文件中添加Quartz和MySQL的依赖: ```xml <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-quartz</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> </dependencies> ``` 然后,需要在application.properties文件中配置数据库连接信息: ```properties spring.datasource.url=jdbc:mysql://localhost:3306/mydb spring.datasource.username=root spring.datasource.password=root spring.datasource.driver-class-name=com.mysql.jdbc.Driver ``` 接下来,可以定义一个ScheduledJob实体类,用于映射数据库中的定时任务数据: ```java @Entity @Table(name = "scheduled_job") public class ScheduledJob { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; private String name; private String group; private String cronExpression; private String description; // getters and setters } ``` 然后,创建一个JobFactory类,继承SpringBeanJobFactory,并重写createJobInstance方法,用于将Job实例交给Spring容器进行管理: ```java public class JobFactory extends SpringBeanJobFactory implements ApplicationContextAware { private transient AutowireCapableBeanFactory beanFactory; @Override public void setApplicationContext(final ApplicationContext context) { beanFactory = context.getAutowireCapableBeanFactory(); } @Override protected Object createJobInstance(final TriggerFiredBundle bundle) throws Exception { final Object job = super.createJobInstance(bundle); beanFactory.autowireBean(job); return job; } } ``` 最后,创建一个JobScheduler类,用于从数据库中读取定时任务数据,并创建对应的定时任务: ```java @Component public class JobScheduler { @Autowired private SchedulerFactory schedulerFactory; @Autowired private JobFactory jobFactory; @Autowired private DataSource dataSource; @PostConstruct public void init() throws Exception { final Scheduler scheduler = schedulerFactory.getScheduler(); scheduler.setJobFactory(jobFactory); final String sql = "select * from scheduled_job"; try (final Connection connection = dataSource.getConnection(); final PreparedStatement statement = connection.prepareStatement(sql); final ResultSet resultSet = statement.executeQuery()) { while (resultSet.next()) { final JobDetail jobDetail = JobBuilder.newJob(QuartzJob.class) .withIdentity(resultSet.getString("name"), resultSet.getString("group")) .withDescription(resultSet.getString("description")) .build(); final Trigger trigger = TriggerBuilder.newTrigger() .withIdentity(resultSet.getString("name"), resultSet.getString("group")) .withSchedule(CronScheduleBuilder.cronSchedule(resultSet.getString("cron_expression"))) .build(); scheduler.scheduleJob(jobDetail, trigger); } scheduler.start(); } } } ``` 在上面的代码中,首先通过SchedulerFactory获取Scheduler实例,并设置JobFactory。然后,通过数据库查询语句从数据库中获取定时任务数据,并创建对应的JobDetail和Trigger实例。最后,将JobDetail和Trigger实例添加到Scheduler中,并启动Scheduler。 需要注意的是,QuartzJob类需要继承QuartzJobBean,并实现executeInternal方法,用于执行定时任务的具体逻辑: ```java public class QuartzJob extends QuartzJobBean { @Override protected void executeInternal(final JobExecutionContext context) throws JobExecutionException { // 定时任务逻辑 } } ``` 至此,一个动态创建定时任务的示例就完成了。需要注意的是,该示例仅供参考,实际应用中还需要考虑很多细节问题,如异常处理、任务状态管理等。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值