For one of the applications that I’m maintaining, I was performing several manual validations on the tables a few times a day; in order to facilitate this tasks, I created a stored procedure to handle them and then send an email with the results using MSSQL mail service.
Also, I wanted this procedure to be executed several times a day, that’s why I decided to use Quartz in order to create a scheduled job for my web application that will call my recently created procedure.
First, I created the class that will represent the Job I want to execute. This class must implement the class
org.quartz.Job, overriding the method execute(..)
package com.dashboard.util;
import org.apache.log4j.Logger;
import org.quartz.Job;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;
import com.dashboard.forms.DashboardForm;
import com.dashboard.services.IService;
import com.dashboard.services.ValuationHealthCheckService;
public class ValuationHealthCheckJob implements Job {
private static final Logger logger = Logger.getLogger(ValuationHealthCheckService.class);
@Override
public void execute(JobExecutionContext arg0) throws JobExecutionException {
DashboardForm form = new DashboardForm();
form.setEmailTo("xxxxx.yyyyy@domain.com");
IService service = new ValuationHealthCheckService();
try {
service.execute(form);
} catch (Exception e) {
logger.error(e);
}
}
}
As you can see, the body of the method execute is really simple. A DashboardForm class, which is simply a java bean with a field “emailTo” and then the creation and execution of my service class. The service class will take care of connecting to the database and execute the stored procedure that will verify the tables and send an email with the result.
Here’s the ValuationHealthCheckService class:
package com.dashboard.services;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import org.apache.log4j.Logger;
import com.dashboard.forms.DashboardForm;
public class ValuationHealthCheckService implements IService {
private static final Logger logger = Logger.getLogger(ValuationHealthCheckService.class);
@Override
public void execute(Object o) throws NamingException, SQLException {
DashboardForm form = (DashboardForm) o;
Connection con = null;
Context ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/MarginDB");
con = ds.getConnection();
String populateAttrs = "{call p_ValuationHealthCheck(?,?)}";
logger.info("{call p_ValuationHealthCheck(?,?)}");
if (form.getEmailTo() == null || form.getEmailTo().equals("")) {
throw new RuntimeException("You must specify at least one email recipient.");
}
PreparedStatement populateAttrsStmt = con.prepareStatement(populateAttrs);
populateAttrsStmt.setString(1, form.getEmailTo());
populateAttrsStmt.setString(2, form.getEmailCC());
populateAttrsStmt.execute();
con.close();
}
}
I’m retrieving the database connection using the datasource I have defined in Tomcat.
After that, I needed to create the servlet in order to setup the schedule for the job.
import java.text.ParseException;
import javax.servlet.http.HttpServlet;
import org.quartz.CronTrigger;
import org.quartz.JobDetail;
import org.quartz.Scheduler;
import org.quartz.SchedulerException;
import org.quartz.SchedulerFactory;
import org.quartz.impl.StdSchedulerFactory;
import com.dashboard.util.ValuationHealthCheckJob;
public class QuartzServlet extends HttpServlet {
public QuartzServlet() throws SchedulerException, ParseException {
SchedulerFactory sf = new StdSchedulerFactory();
Scheduler sched = sf.getScheduler();
// Specify the class of the job I want to execute
JobDetail jd = new JobDetail("ValuationHealthCheck", "AppGroup", ValuationHealthCheckJob.class);
// Create a CRON expression
CronTrigger ct = new CronTrigger("cronTrigger", "cronTrigger", "0 0 15-19 * * ?");
// Add into the Scheduler the job and the trigger
sched.scheduleJob(jd, ct);
sched.start();
}
}
And as a final step, I needed to define the servlet in the web.xml file.
<servlet>
<servlet-name>quartz</servlet-name>
<servlet-class>
com.dashboard.servlets.QuartzServlet
</servlet-class>
<load-on-startup>1</load-on-startup>
</servlet>
Just restart the Tomcat so the servlet (and our job) is loaded. From now on, our job will be executed using the cron expression we defined in the servlet: 0 0 15-19 . This means that the job will be executed everyday, every hour from 15 (3:00 PM) to 19 (7:00PM).
–
Just a note: Make sure you have the JAR file for Quartz in the WEB-INF/lib directory. I’m using maven for my web project so you just need to include the Quartz dependency in the pom.xml:
<dependency>
<groupId>opensymphony</groupId>
<artifactId>quartz</artifactId>
<version>1.6.0</version>
<scope>compile</scope>
</dependency>
–
That’s all. This was the simplest solution I found for my problem (due to lack of time). However, I will work on the integration with Spring as soon as I have free time.
For more information on Quartz: http://www.opensymphony.com/quartz/
