Store Quartz Job Data in MySQL JSON Column

MySQLJsonDelegate.java

import com.fasterxml.jackson.databind.ObjectMapper;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.quartz.Job;
import org.quartz.JobDataMap;
import org.quartz.JobDetail;
import org.quartz.impl.JobDetailImpl;
import org.quartz.impl.jdbcjobstore.StdJDBCDelegate;
import org.quartz.spi.ClassLoadHelper;

public class MySQLJsonDelegate extends StdJDBCDelegate {
  private final ObjectMapper objectMapper = new ObjectMapper();

  @Override
  public int insertJobDetail(Connection conn, JobDetail job) throws IOException, SQLException {
    int insertResult;
    try (PreparedStatement ps = conn.prepareStatement(rtp(INSERT_JOB_DETAIL))) {
      ps.setString(1, job.getKey().getName());
      ps.setString(2, job.getKey().getGroup());
      ps.setString(3, job.getDescription());
      ps.setString(4, this.objectMapper.writeValueAsString(job.getJobDataMap()));
      ps.setString(5, job.getJobClass().getName());
      ps.setBoolean(6, job.isDurable());
      ps.setBoolean(7, job.isConcurrentExectionDisallowed());
      ps.setBoolean(8, job.isPersistJobDataAfterExecution());
      ps.setBoolean(9, job.requestsRecovery());
      insertResult = ps.executeUpdate();
      if (insertResult < 1) {
        throw new SQLException("Failed to insert job detail.");
      }
    }
    return insertResult;
  }

  @Override
  public int updateJobDetail(Connection conn, JobDetail job) throws IOException, SQLException {
    int insertResult;
    try (PreparedStatement ps = conn.prepareStatement(rtp(UPDATE_JOB_DETAIL))) {
      ps.setString(1, job.getDescription());
      ps.setString(2, job.getJobClass().getName());
      setBoolean(ps, 3, job.isDurable());
      setBoolean(ps, 4, job.isConcurrentExectionDisallowed());
      setBoolean(ps, 5, job.isPersistJobDataAfterExecution());
      setBoolean(ps, 6, job.requestsRecovery());
      ps.setString(7, this.objectMapper.writeValueAsString(job.getJobDataMap()));
      ps.setString(8, job.getKey().getName());
      ps.setString(9, job.getKey().getGroup());

      insertResult = ps.executeUpdate();
    }

    return insertResult;
  }

  @Override
  public JobDetail selectJobDetail(Connection conn, org.quartz.JobKey jobKey, ClassLoadHelper loadHelper)
      throws ClassNotFoundException, IOException, SQLException {
    try (PreparedStatement ps = conn.prepareStatement(rtp(SELECT_JOB_DETAIL))) {
      ps.setString(1, jobKey.getName());
      ps.setString(2, jobKey.getGroup());
      try (ResultSet rs = ps.executeQuery()) {
        if (!rs.next()) {
          return null;
        }

        JobDataMap jobDataMap;
        String jsonData = rs.getString(COL_JOB_DATAMAP);
        if (jsonData == null || jsonData.length() == 0) {
          jobDataMap = new JobDataMap();
        } else {
          jobDataMap = this.objectMapper.readValue(jsonData, JobDataMap.class);
        }

        JobDetailImpl job = new JobDetailImpl();
        job.setName(rs.getString(COL_JOB_NAME));
        job.setGroup(rs.getString(COL_JOB_GROUP));
        job.setDescription(rs.getString(COL_DESCRIPTION));
        job.setJobClass(loadHelper.loadClass(rs.getString(COL_JOB_CLASS), Job.class));
        job.setDurability(getBoolean(rs, COL_IS_DURABLE));
        job.setRequestsRecovery(getBoolean(rs, COL_REQUESTS_RECOVERY));
        job.setJobDataMap(jobDataMap);

        return job;
      }
    }
  }
}