Quartz with MySQL - Java Spring Boot Example

In this tutorial, you'll learn how to build a scheduler application with Spring Quartz and MySQL in Java Spring Boot.

Quartz is an open-source job scheduling library that can be integrated with any size of Java applications.

Quartz Scheduler can be used to execute different tasks at a pre-determined time or when the scheduled time arrives. For example: A task to run in exactly every 2 hours, a task to run on every weekdays except holidays at 12:30 PM, a task to run in every 2 days at 10:30 PM.

Spring Boot offers spring-boot-starter-quartz 'Starter' which makes working with the Quartz easier and faster. When Quartz is added to a Spring Boot application, the Scheduler is auto-configured via the SchedulerFactoryBean abstraction and also the beans of the following types are automatically picked up:

Calender - is used to define time and is associated with Trigger.
Trigger - is used to trigger a particular Job.
JobDetail - is used to define a Job detail.
JobBuilder - is used to build an instance of JobDetail.

Quartz uses JobStore which are responsible to keep track of all the work data that is given to the scheduler. By default Quartz uses in-memory JobStore but in this tutorial, we will use JDBCJobStore to keep all of its data in a MySQL database via JDBC.

In this example, we will build a scheduler application using Quartz with Spring Boot and MySQL to schedule and unschedule jobs via REST APIs.

Follow the steps below to complete this example:

Create a Spring Boot Application

Go to Spring Initializr at https://start.spring.io and create a Spring Boot application with details as follows:

Project: Choose Gradle Project or Maven Project.
Language: Java
Spring Boot: Latest stable version of Spring Boot is selected by default. So leave it as is.
Project Metadata: Provide group name in the Group field. The group name is the id of the project. In Artifact field, provide the name of your project. In the package field, provide package name for your project. Next, select your preferred version of Java that is installed on your computer and is available on your local environment.
Dependencies: Add dependencies for Spring Web, Spring Boot DevTools, Quartz Scheduler, MySQL Driver, and Spring Data JPA .

Refer to the image below for example:

Click the GENERATE button and save/download the project zip bundle.
Extract the project to your preferred working directory.
Import the project in your preferred Java development IDE such as Eclipse or IntelliJ IDEA.

Add Dependency

When we use Quartz with relational databases like MySQL, we also need to add c3p0 library. c3p0 library is a mature, highly concurrent JDBC Connection pooling library. Find the latest version of c3p0 in the c3p0 Maven Repository.

Gradle Depencencies

The following listing shows the dependencies in the build.gradle file:

build.gradle


implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
implementation 'org.springframework.boot:spring-boot-starter-web'
developmentOnly 'org.springframework.boot:spring-boot-devtools'
runtimeOnly 'mysql:mysql-connector-java'
implementation 'org.springframework.boot:spring-boot-starter-quartz'
compile group: 'com.mchange', name: 'c3p0', version: '0.9.5.5'

Maven Depencencies

The following listing shows the dependencies in the pom.xml file:

pom.xml

Add Application Configurations

Open the application.properties file and copy the code listed below. Do not forget to update the spring.datasource.url, the spring.datasource.username and spring.datasource.password values to make them relevant to your project. The MySQL configuration defined in this file is not for Quartz. Quartz uses a separate configuration file that we will create later.

src/main/resources/application.properties


#port on which the application will run
server.port= 8080

#mysql database connection
spring.datasource.url = jdbc:mysql://localhost:3306/test_buddy
spring.datasource.username = root
spring.datasource.password = Testing123
spring.datasource.timeBetweenEvictionRunsMillis = 60000
spring.datasource.maxIdle = 1
spring.jpa.generate-ddl=true                                                        
spring.jpa.hibernate.ddl-auto=update

#Quartz Log level 
logging.level.org.springframework.scheduling.quartz=DEBUG
logging.level.org.quartz=DEBUG

Create Quartz Properties File

Quartz uses a properties file called quartz.properties. This file contains the most basic configuration of Quartz.

Create a quartz.properties file inside the src/main/resources/ folder of your project and copy the configurations to it from the example below:

src/main/resources/quartz.properties


#Quartz
org.quartz.scheduler.instanceName = SampleJobScheduler
org.quartz.scheduler.instanceId = AUTO
org.quartz.scheduler.idleWaitTime = 10000
org.quartz.threadPool.class = org.quartz.simpl.SimpleThreadPool
org.quartz.threadPool.threadCount = 4
org.quartz.threadPool.threadPriority = 5
org.quartz.jobStore.class = org.quartz.impl.jdbcjobstore.JobStoreTX
org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate
org.quartz.jobStore.tablePrefix = QRTZ_
org.quartz.jobStore.misfireThreshold = 60000
org.quartz.jobStore.isClustered = false
org.quartz.jobStore.maxMisfiresToHandleAtATime = 10
org.quartz.jobStore.useProperties = true

#quartz mysql database connection
org.quartz.jobStore.dataSource = mySql
org.quartz.dataSource.mySql.driver = com.mysql.cj.jdbc.Driver
org.quartz.dataSource.mySql.URL = jdbc:mysql://localhost:3306/test_new_database
org.quartz.dataSource.mySql.user = root
org.quartz.dataSource.mySql.password = Testing123
org.quartz.dataSource.mySql.maxConnections = 10
org.quartz.dataSource.mySql.idleConnectionValidationSeconds = 50
org.quartz.dataSource.mySql.validationQuery=select 0 from dual
org.quartz.dataSource.mySql.maxIdleTime = 60

Here is an explanation of the above configurations:

org.quartz.scheduler.instanceName - The value of this can be any string that will help you to distinguish schedulers when multiple instances are used within the same program. Incase of using the clustering features, same name must be used for every instance in the cluster which is logically the same Scheduler. In this example, we are naming our scheduler as SampleJobScheduler.
org.quartz.scheduler.instanceId - This value of this can be any string but must be unique for all schedulers within a cluster. You can use the value AUTO if you wish the Id to be auto-generated for you. Or you can also use the value SYS_PROP if you wish the Id to come from the system property.
org.quartz.scheduler.idleWaitTime - This value of this property are in milliseconds. It is the amount of time the scheduler must wait for re-queries for available triggers when the scheduler is otherwise idle. Avoid using values less than 5000 ms as it will cause excessive database querying and therefore are not recommended.
org.quartz.threadPool.class - Name of the ThreadPool implementation you wish to use. Quartz comes with org.quartz.simpl.SimpleThreadPool which provides a fixed-size pool of threads that live the lifetime of the scheduler.
org.quartz.threadPool.threadCount - There are 4 threads in the thread pool that can run 4 jobs simultaneously.
org.quartz.threadPool.threadPriority - This can be any number between Thread.MIN_PRIORITY(1) to Thread.MAX_PRIORITY(10). The default is Thread.NORM_PRIORITY (5).
org.quartz.jobStore.class - You need to tell Quartz which JobStore to use for storing scheduling information such as details of jobs, triggers, calendars. The org.quartz.impl.jdbcjobstore.JobStoreTX will tell Quartz to use database for helding Quartz`s data.
org.quartz.jobStore.driverDelegateClass - Driver delegates understand the particular dialect of different database systemms. The value org.quartz.impl.jdbcjobstore.StdJDBCDelegate is known to work with many databases.
org.quartz.jobStore.tablePrefix - This is JDBCJobStore's table prefix string property that must be equal to the prefix given to Quartz's tables created in your database.
org.quartz.jobStore.misfireThreshold - The value of this property are in milliseconds. It is the amount of time the scheduler will tolerate a trigger to pass its next-fire-time by, before being considered misfired. The default value is 60000 ms which is equal to 60 seconds.
org.quartz.jobStore.isClustered - Set the value of this property to true if you wish to use clustering features otherwise false.
org.quartz.jobStore.maxMisfiresToHandleAtATime - The maximum number of misfired triggers to handle in a given time. Handling too many misfired at one time can cause the database tables to be locked long enough that the performance of other triggers may slow down.
org.quartz.jobStore.useProperties - Set the value of this property to true to instruct JDBCJobStore that all values in JobDataMaps will be Strings and can be stored as name-value pairs, rather than storing more complex objects in their serialized form.

Create a Quartz Configuration Java Class

Spring's SchedulerFactoryBean is a FactoryBean for creating and configuring a Quartz Scheduler. It also manages the scheduler's life cycle and exposes the scheduler as bean reference, so lets create a QuartzConfig.java Java class for creating a bean reference of SchedulerFactoryBean and JobFactory. A JobFactory is responsible for producing Job instances.

This QuartzConfig.java class should be annotated with @Configuration annotation. The @Configuration annotation indicates that this is a configuration class and will be used by the Spring application context to create beans for your application.

src/main/java/com/example/config/QuartzConfig.java


package com.example.config;

import java.io.IOException;
import java.util.Properties;
import org.quartz.spi.JobFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.config.PropertiesFactoryBean;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.ClassPathResource;
import org.springframework.scheduling.quartz.SchedulerFactoryBean;

@Configuration
public class QuartzConfig {

    @Autowired
    ApplicationContext applicationContext;

    @Bean
    public JobFactory jobFactory() {
        AutowiringSpringBeanJobFactory jobFactory = new AutowiringSpringBeanJobFactory();
        jobFactory.setApplicationContext(applicationContext);
        return jobFactory;
    }

    @Bean
    public SchedulerFactoryBean schedulerFactoryBean() throws IOException {

        SchedulerFactoryBean schedulerFactory = new SchedulerFactoryBean();
        schedulerFactory.setQuartzProperties(quartzProperties());
        schedulerFactory.setWaitForJobsToCompleteOnShutdown(true);
        schedulerFactory.setAutoStartup(true);
        schedulerFactory.setJobFactory(jobFactory());
        return schedulerFactory;
    }

    public Properties quartzProperties() throws IOException {
        PropertiesFactoryBean propertiesFactoryBean = new PropertiesFactoryBean();
        propertiesFactoryBean.setLocation(new ClassPathResource("/quartz.properties"));
        propertiesFactoryBean.afterPropertiesSet();
        return propertiesFactoryBean.getObject();
    }

}

Create a AutowiringSpringBeanJobFactory class

To make autowiring from inside the Job class possible, we need to create a Java class that should extend SpringBeanJobFactory and implement ApplicationContextAware, so lets create a AutowiringSpringBeanJobFactory.java Java class to bring Spring and Quartz together.

src/main/java/com/example/config/AutowiringSpringBeanJobFactory.java


package com.example.config;

import org.quartz.spi.TriggerFiredBundle;
import org.springframework.beans.factory.config.AutowireCapableBeanFactory;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.scheduling.quartz.SpringBeanJobFactory;

public class AutowiringSpringBeanJobFactory extends SpringBeanJobFactory
implements ApplicationContextAware{

    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;
    }
}

Create an Entity class

The application saves messages data in a MySQL database table and updates the message's makeVisibleAt value to true at the scheduled time. To perform these operations we will create an entity class Message.java.

An entity class is an object wrapper for a database table. The attributes of the entity class are mapped to the columns of the database table. The entity class should be annotated with @Entity and @Table.

Spring provides @CreatedBy, @LastModifiedBy, @CreatedDate, @LastModifiedDate to support the tracking of who created or modified an entity. To benefit from that functionality, you should annotate your entity class with @EntityListeners(AuditingEntityListener.class) and your main configuration class with @EnableJpaAuditing.

src/main/java/com/example/model/Message.java


package com.example.model;

import java.time.LocalDateTime;
import javax.persistence.Entity;
import javax.persistence.EntityListeners;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;

@Entity
@Table(name = "message")
@EntityListeners(AuditingEntityListener.class)
public class Message {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;
    private String content;
    private Boolean visible = false;
    private Long makeVisibleAt;
    @CreatedDate
    private LocalDateTime created;
    @LastModifiedDate
    private LocalDateTime modified;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getContent() {
        return content;
    }

    public void setContent(String content) {
        this.content = content;
    }

    public Boolean getVisible() {
        return visible;
    }

    public void setVisible(Boolean visible) {
        this.visible = visible;
    }

    public Long getMakeVisibleAt() {
        return makeVisibleAt;
    }

    public void setMakeVisibleAt(Long makeVisibleAt) {
        this.makeVisibleAt = makeVisibleAt;
    }

    public LocalDateTime getCreated() {
        return created;
    }

    public void setCreated(LocalDateTime created) {
        this.created = created;
    }

    public LocalDateTime getModified() {
        return modified;
    }

    public void setModified(LocalDateTime modified) {
        this.modified = modified;
    }

}

Create a Data Transfer Object

Create a MessageDto.java Java class. This class should only contains getter/setter methods with serialization and deserialization mechanism but should not contain any business logic. Using this class, you can decide which data to return and which data to not return in remote calls to promote security and loose coupling.

src/main/java/com/example/dto/MessageDto.java


package com.example.dto;

import java.time.LocalDateTime;

public class MessageDto {
    private String content;
    private Long makeVisibleAt;
    private LocalDateTime created;
    private LocalDateTime modified;
    private String status;

    public String getContent() {
        return content;
    }

    public void setContent(String content) {
        this.content = content;
    }

    public Long getMakeVisibleAt() {
        return makeVisibleAt;
    }

    public void setMakeVisibleAt(Long makeVisibleAt) {
        this.makeVisibleAt = makeVisibleAt;
    }

    public LocalDateTime getCreated() {
        return created;
    }

    public void setCreated(LocalDateTime created) {
        this.created = created;
    }

    public LocalDateTime getModified() {
        return modified;
    }

    public void setModified(LocalDateTime modified) {
        this.modified = modified;
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }

}

Create Repository interface

To perform CRUD (Create Read Update Delete) operations on the table, create an interface and extend it with{" "} CrudRepository interface. The CrudRepository interface provides generic CRUD operations on a repository for a specific type. For this example, we will create MessageRepository.java interface.

src/main/java/com/example/repository/MessageRepository.java


package com.example.respository;

import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;
import com.example.model.Message;

@Repository
public interface MessageRepository extends CrudRepository<Message, Integer> {

}

Create a Job class

A Job can be any Java class that implements the Job interface of Quartz, so lets create a MessageJob.java Java class. The Job interface has a single execute method where the actual work for that particular job is written.

Inside the execute method, you can retrieve data that you may wish an instance of that job instance must have while it executes. The data are passed to a Job instance via the JobDataMap class which is part of the JobDetail object. The data are stored in JobDataMap prior to adding the job to the scheduler.

src/main/java/com/example/job/MessageJob.java


package com.example.job;

import java.util.Optional;
import org.quartz.Job;
import org.quartz.JobDataMap;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;
import org.quartz.JobKey;
import org.quartz.SchedulerException;
import org.quartz.TriggerKey;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import com.example.model.Message;
import com.example.respository.MessageRepository;

public class MessageJob implements Job {
    private static final Logger log = LoggerFactory.getLogger(MessageJob.class);

    @Autowired
    private MessageRepository messageRepository;

    @Override
    public void execute(JobExecutionContext context) throws JobExecutionException {

        /* Get message id recorded by scheduler during scheduling */
        JobDataMap dataMap = context.getJobDetail().getJobDataMap();

        String messageId = dataMap.getString("messageId");

        log.info("Executing job for message id {}", messageId);

        /* Get message from database by id */
        int id = Integer.parseInt(messageId);
        Optional<Message> messageOpt = messageRepository.findById(id);

        /* update message visible in database */
        Message message = messageOpt.get();
        message.setVisible(true);
        messageRepository.save(message);

        /* unschedule or delete after job gets executed */

        try {
            context.getScheduler().deleteJob(new JobKey(messageId));

            TriggerKey triggerKey = new TriggerKey(messageId);

            context.getScheduler().unscheduleJob(triggerKey);

        } catch (SchedulerException e) {
            e.printStackTrace();
        }
    }

}

Create a Web Controller

The following is a MessageSchedulingController.java Java class that shows how to create a web controller with REST APIs to schedule and unschedule jobs:

src/main/java/com/example/controller/MessageSchedulingController.java


package com.example.controller;

import java.io.IOException;
import java.util.Date;
import java.util.Optional;
import org.quartz.JobBuilder;
import org.quartz.JobDetail;
import org.quartz.JobKey;
import org.quartz.Scheduler;
import org.quartz.SchedulerException;
import org.quartz.SimpleScheduleBuilder;
import org.quartz.SimpleTrigger;
import org.quartz.TriggerBuilder;
import org.quartz.TriggerKey;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import com.example.config.QuartzConfig;
import com.example.dto.MessageDto;
import com.example.job.MessageJob;
import com.example.model.Message;
import com.example.respository.MessageRepository;

@RestController
@RequestMapping(path = "/messages")
public class MessageSchedulingController {

    @Autowired
    private QuartzConfig quartzConfig;
    @Autowired
    private MessageRepository messageRepository;

    @PostMapping(path = "/schedule-visibility")
    public @ResponseBody  MessageDto scheduleMessageVisibility(@RequestBody  MessageDto messageDto) {
        try {
            // save messages in table
            Message message = new Message();
            message.setContent(messageDto.getContent());
            message.setVisible(false);
            message.setMakeVisibleAt(messageDto.getMakeVisibleAt());

            message = messageRepository.save(message);

            // Creating JobDetail instance
            String id = String.valueOf(message.getId());
            JobDetail jobDetail = JobBuilder.newJob(MessageJob.class).withIdentity(id).build();

            // Adding JobDataMap to jobDetail
            jobDetail.getJobDataMap().put("messageId", id);

            // Scheduling time to run job
            Date triggerJobAt = new Date(message.getMakeVisibleAt());

            SimpleTrigger trigger = TriggerBuilder.newTrigger().withIdentity(id)
                    .startAt(triggerJobAt).withSchedule(SimpleScheduleBuilder.simpleSchedule()
                            .withMisfireHandlingInstructionFireNow())
                    .build();
            // Getting scheduler instance
            Scheduler scheduler = quartzConfig.schedulerFactoryBean().getScheduler();
            scheduler.scheduleJob(jobDetail, trigger);
            scheduler.start();

            messageDto.setStatus("SUCCESS");

        } catch (IOException | SchedulerException e) {
            // scheduling failed
            messageDto.setStatus("FAILED");
            e.printStackTrace();
        }
        return messageDto;
    }


    @DeleteMapping(path = "/{messageId}/unschedule-visibility")
    public @ResponseBody  MessageDto unscheduleMessageVisibility(
            @PathVariable(name = "messageId") Integer messageId) {

        MessageDto messageDto = new MessageDto();

        Optional<Message> messageOpt = messageRepository.findById(messageId);
        if (!messageOpt.isPresent()) {
            messageDto.setStatus("Message Not Found");
            return messageDto;
        }

        Message message = messageOpt.get();
        message.setVisible(false);
        messageRepository.save(message);

        String id = String.valueOf(message.getId());

        try {
            Scheduler scheduler = quartzConfig.schedulerFactoryBean().getScheduler();

            scheduler.deleteJob(new JobKey(id));
            TriggerKey triggerKey = new TriggerKey(id);
            scheduler.unscheduleJob(triggerKey);
            messageDto.setStatus("SUCCESS");

        } catch (IOException | SchedulerException e) {
            messageDto.setStatus("FAILED");
            e.printStackTrace();
        }
        return messageDto;
    }
}

The controller has a POST method and a DELETE method. The POST method is mapped to /messages/schedule-visibility and the DELETE method is mapped to /messages/{messageId}/unschedule-visibility.

Enable JPA Auditing

To benefit from @CreatedBy, @LastModifiedBy, @CreatedDate, @LastModifiedDate functionality, we need to enable JPA auditing by annotating the Main class with @EnableJpaAuditing. The following code shows how to do so:

src/main/java/com/example/quartzmysql/QuartzMysqlSampleApplication.java


package com.example;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.data.jpa.repository.config.EnableJpaAuditing;

@EnableJpaAuditing
@SpringBootApplication
public class QuartzMysqlSampleApplication {

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

}

Create Quartz Tables

The last thing you'll need to do is, create Quartz configuration tables in your database. To create Quartz tables, run the following MySQL script in your database:

NOTE: You should use a separate database for Quartz.


DROP TABLE IF EXISTS QRTZ_FIRED_TRIGGERS;

DROP TABLE IF EXISTS QRTZ_PAUSED_TRIGGER_GRPS;

DROP TABLE IF EXISTS QRTZ_SCHEDULER_STATE;

DROP TABLE IF EXISTS QRTZ_LOCKS;

DROP TABLE IF EXISTS QRTZ_SIMPLE_TRIGGERS;

DROP TABLE IF EXISTS QRTZ_SIMPROP_TRIGGERS;

DROP TABLE IF EXISTS QRTZ_CRON_TRIGGERS;

DROP TABLE IF EXISTS QRTZ_BLOB_TRIGGERS;

DROP TABLE IF EXISTS QRTZ_TRIGGERS;

DROP TABLE IF EXISTS QRTZ_JOB_DETAILS;

DROP TABLE IF EXISTS QRTZ_CALENDARS;

CREATE TABLE QRTZ_JOB_DETAILS(

SCHED_NAME VARCHAR(120) NOT NULL,

JOB_NAME VARCHAR(190) NOT NULL,

JOB_GROUP VARCHAR(190) NOT NULL,

DESCRIPTION VARCHAR(250) 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 NULL,

PRIMARY KEY (SCHED_NAME,JOB_NAME,JOB_GROUP))

ENGINE=InnoDB;

CREATE TABLE QRTZ_TRIGGERS (
SCHED_NAME VARCHAR(120) NOT NULL,
TRIGGER_NAME VARCHAR(190) NOT NULL,
TRIGGER_GROUP VARCHAR(190) NOT NULL,
JOB_NAME VARCHAR(190) NOT NULL,
JOB_GROUP VARCHAR(190) 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(190) NULL,
MISFIRE_INSTR SMALLINT(2) NULL,
JOB_DATA BLOB NULL,
PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
FOREIGN KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)
REFERENCES QRTZ_JOB_DETAILS(SCHED_NAME,JOB_NAME,JOB_GROUP))

ENGINE=InnoDB;

CREATE TABLE QRTZ_SIMPLE_TRIGGERS (
SCHED_NAME VARCHAR(120) NOT NULL,
TRIGGER_NAME VARCHAR(190) NOT NULL,
TRIGGER_GROUP VARCHAR(190) 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;

CREATE TABLE QRTZ_CRON_TRIGGERS (
SCHED_NAME VARCHAR(120) NOT NULL,
TRIGGER_NAME VARCHAR(190) NOT NULL, 
TRIGGER_GROUP VARCHAR(190) 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;

CREATE TABLE QRTZ_SIMPROP_TRIGGERS(
SCHED_NAME VARCHAR(120) NOT NULL,
TRIGGER_NAME VARCHAR(190) NOT NULL,
TRIGGER_GROUP VARCHAR(190) 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;

CREATE TABLE QRTZ_BLOB_TRIGGERS (
SCHED_NAME VARCHAR(120) NOT NULL,
TRIGGER_NAME VARCHAR(190) NOT NULL,
TRIGGER_GROUP VARCHAR(190) 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;

CREATE TABLE QRTZ_CALENDARS (
SCHED_NAME VARCHAR(120) NOT NULL,
CALENDAR_NAME VARCHAR(190) NOT NULL, 
CALENDAR BLOB NOT NULL,
PRIMARY KEY (SCHED_NAME,CALENDAR_NAME))

ENGINE=InnoDB;

CREATE TABLE QRTZ_PAUSED_TRIGGER_GRPS (
SCHED_NAME VARCHAR(120) NOT NULL,
TRIGGER_GROUP VARCHAR(190) NOT NULL,
PRIMARY KEY (SCHED_NAME,TRIGGER_GROUP))

ENGINE=InnoDB;

CREATE TABLE QRTZ_FIRED_TRIGGERS (
SCHED_NAME VARCHAR(120) NOT NULL,
ENTRY_ID VARCHAR(95) NOT NULL, 
TRIGGER_NAME VARCHAR(190) NOT NULL,
TRIGGER_GROUP VARCHAR(190) NOT NULL,
INSTANCE_NAME VARCHAR(190) 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(190) NULL,
JOB_GROUP VARCHAR(190) NULL,
IS_NONCONCURRENT VARCHAR(1) NULL,
REQUESTS_RECOVERY VARCHAR(1) NULL,
PRIMARY KEY (SCHED_NAME,ENTRY_ID))

ENGINE=InnoDB;

CREATE TABLE QRTZ_SCHEDULER_STATE (SCHED_NAME VARCHAR(120) NOT NULL,
INSTANCE_NAME VARCHAR(190) NOT NULL,
LAST_CHECKIN_TIME BIGINT(13) NOT NULL,
CHECKIN_INTERVAL BIGINT(13) NOT NULL,
PRIMARY KEY (SCHED_NAME,INSTANCE_NAME))

ENGINE=InnoDB;

CREATE TABLE QRTZ_LOCKS (SCHED_NAME VARCHAR(120) NOT NULL,
LOCK_NAME VARCHAR(40) NOT NULL,
PRIMARY KEY (SCHED_NAME,LOCK_NAME))

ENGINE=InnoDB;

CREATE INDEX IDX_QRTZ_J_REQ_RECOVERY ON QRTZ_JOB_DETAILS(SCHED_NAME,
REQUESTS_RECOVERY);

CREATE INDEX IDX_QRTZ_J_GRP ON QRTZ_JOB_DETAILS(SCHED_NAME,JOB_GROUP);

CREATE INDEX IDX_QRTZ_T_J ON QRTZ_TRIGGERS(SCHED_NAME,JOB_NAME,JOB_GROUP);

CREATE INDEX IDX_QRTZ_T_JG ON QRTZ_TRIGGERS(SCHED_NAME,JOB_GROUP);

CREATE INDEX IDX_QRTZ_T_C ON QRTZ_TRIGGERS(SCHED_NAME,CALENDAR_NAME);

CREATE INDEX IDX_QRTZ_T_G ON QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_GROUP);

CREATE INDEX IDX_QRTZ_T_STATE ON QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_STATE);

CREATE INDEX IDX_QRTZ_T_N_STATE ON QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,
TRIGGER_GROUP,TRIGGER_STATE);

CREATE INDEX IDX_QRTZ_T_N_G_STATE ON QRTZ_TRIGGERS(SCHED_NAME,
TRIGGER_GROUP, TRIGGER_STATE);

CREATE INDEX IDX_QRTZ_T_NEXT_FIRE_TIME ON QRTZ_TRIGGERS(SCHED_NAME,
NEXT_FIRE_TIME);

CREATE INDEX IDX_QRTZ_T_NFT_ST ON QRTZ_TRIGGERS(SCHED_NAME, 
TRIGGER_STATE,NEXT_FIRE_TIME);

CREATE INDEX IDX_QRTZ_T_NFT_MISFIRE ON QRTZ_TRIGGERS(SCHED_NAME, 
MISFIRE_INSTR,NEXT_FIRE_TIME);

CREATE INDEX IDX_QRTZ_T_NFT_ST_MISFIRE ON QRTZ_TRIGGERS(SCHED_NAME,
MISFIRE_INSTR,NEXT_FIRE_TIME,TRIGGER_STATE);

CREATE INDEX IDX_QRTZ_T_NFT_ST_MISFIRE_GRP ON QRTZ_TRIGGERS(
SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME,TRIGGER_GROUP,TRIGGER_STATE);

CREATE INDEX IDX_QRTZ_FT_TRIG_INST_NAME ON QRTZ_FIRED_TRIGGERS(
SCHED_NAME,INSTANCE_NAME);

CREATE INDEX IDX_QRTZ_FT_INST_JOB_REQ_RCVRY ON QRTZ_FIRED_TRIGGERS(
SCHED_NAME,INSTANCE_NAME,REQUESTS_RECOVERY);

CREATE INDEX IDX_QRTZ_FT_J_G ON QRTZ_FIRED_TRIGGERS(SCHED_NAME,
JOB_NAME,JOB_GROUP);

CREATE INDEX IDX_QRTZ_FT_JG ON QRTZ_FIRED_TRIGGERS(SCHED_NAME,
JOB_GROUP);

CREATE INDEX IDX_QRTZ_FT_T_G ON QRTZ_FIRED_TRIGGERS(SCHED_NAME,
TRIGGER_NAME,TRIGGER_GROUP);

CREATE INDEX IDX_QRTZ_FT_TG ON QRTZ_FIRED_TRIGGERS(SCHED_NAME,
TRIGGER_GROUP);

commit;

Your application may encounter the following error if Quartz cannot find it's configuration tables.

Error: Caused by: java.sql.SQLSyntaxErrorException: Table 'database_name.QRTZ_TRIGGERS' doesn't exist.

Run the Application

Run your scheduler application and test scheduling and unscheduling of jobs using an HTTP requester tool, such as Postman or any other similar tools. The following examples shows how to do so:

Scheduling Example

NOTE: makeVisibleAt is the schedule time in milliseconds for executing the job.

Unscheduling Example

Summary

Congratulations, you have learned how to schedule and unschedule jobs using Quartz Scheduler with MySQL in Java Spring Boot.