Cron Integration with postgres and spring

To use cron with postgres we must create cron tables using the following table…
/* DB Upgrade for Quartz */

DROP TABLE IF EXISTS QRTZ_JOB_LISTENERS;
DROP TABLE IF EXISTS QRTZ_TRIGGER_LISTENERS;
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_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(
JOB_NAME VARCHAR(80) NOT NULL,
JOB_GROUP VARCHAR(80) NOT NULL,
DESCRIPTION VARCHAR(120) NULL,
JOB_CLASS_NAME VARCHAR(128) NOT NULL,
IS_DURABLE boolean NOT NULL,
IS_VOLATILE boolean NOT NULL,
IS_STATEFUL boolean NOT NULL,
REQUESTS_RECOVERY boolean NOT NULL,
JOB_DATA bytea NULL,
PRIMARY KEY (JOB_NAME,JOB_GROUP)
);

CREATE TABLE QRTZ_JOB_LISTENERS
(
JOB_NAME VARCHAR(80) NOT NULL,
JOB_GROUP VARCHAR(80) NOT NULL,
JOB_LISTENER VARCHAR(80) NOT NULL,
PRIMARY KEY (JOB_NAME,JOB_GROUP,JOB_LISTENER),
FOREIGN KEY (JOB_NAME,JOB_GROUP)
REFERENCES QRTZ_JOB_DETAILS(JOB_NAME,JOB_GROUP)
);

CREATE TABLE QRTZ_TRIGGERS(
TRIGGER_NAME VARCHAR(80) NOT NULL,
TRIGGER_GROUP VARCHAR(80) NOT NULL,
JOB_NAME VARCHAR(80) NOT NULL,
JOB_GROUP VARCHAR(80) NOT NULL,
IS_VOLATILE boolean NOT NULL,
DESCRIPTION VARCHAR(120) NULL,
NEXT_FIRE_TIME BIGINT NULL,
PREV_FIRE_TIME BIGINT NULL,
TRIGGER_STATE VARCHAR(16) NOT NULL,
TRIGGER_TYPE VARCHAR(8) NOT NULL,
START_TIME BIGINT NOT NULL,
END_TIME BIGINT NULL,
CALENDAR_NAME VARCHAR(80) NULL,
MISFIRE_INSTR SMALLINT NULL,
JOB_DATA bytea NULL,
PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP),
FOREIGN KEY (JOB_NAME,JOB_GROUP)
REFERENCES QRTZ_JOB_DETAILS(JOB_NAME,JOB_GROUP)
);

CREATE TABLE QRTZ_SIMPLE_TRIGGERS
(
TRIGGER_NAME VARCHAR(80) NOT NULL,
TRIGGER_GROUP VARCHAR(80) NOT NULL,
REPEAT_COUNT BIGINT NOT NULL,
REPEAT_INTERVAL BIGINT NOT NULL,
TIMES_TRIGGERED BIGINT NOT NULL,
PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP),
FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP)
REFERENCES QRTZ_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP)
);

CREATE TABLE QRTZ_CRON_TRIGGERS
(
TRIGGER_NAME VARCHAR(80) NOT NULL,
TRIGGER_GROUP VARCHAR(80) NOT NULL,
CRON_EXPRESSION VARCHAR(80) NOT NULL,
TIME_ZONE_ID VARCHAR(80),
PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP),
FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP)
REFERENCES QRTZ_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP)
);

CREATE TABLE QRTZ_BLOB_TRIGGERS
(
TRIGGER_NAME VARCHAR(80) NOT NULL,
TRIGGER_GROUP VARCHAR(80) NOT NULL,
BLOB_DATA bytea NULL,
PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP),
FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP)
REFERENCES QRTZ_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP)
);

CREATE TABLE QRTZ_TRIGGER_LISTENERS
(
TRIGGER_NAME VARCHAR(80) NOT NULL,
TRIGGER_GROUP VARCHAR(80) NOT NULL,
TRIGGER_LISTENER VARCHAR(80) NOT NULL,
PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP,TRIGGER_LISTENER),
FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP)
REFERENCES QRTZ_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP)
);

CREATE TABLE QRTZ_CALENDARS
(
CALENDAR_NAME VARCHAR(80) NOT NULL,
CALENDAR bytea NOT NULL,
PRIMARY KEY (CALENDAR_NAME)
);

CREATE TABLE QRTZ_PAUSED_TRIGGER_GRPS
(
TRIGGER_GROUP VARCHAR(80) NOT NULL,
PRIMARY KEY (TRIGGER_GROUP)
);

CREATE TABLE QRTZ_FIRED_TRIGGERS
(
ENTRY_ID VARCHAR(95) NOT NULL,
TRIGGER_NAME VARCHAR(80) NOT NULL,
TRIGGER_GROUP VARCHAR(80) NOT NULL,
IS_VOLATILE boolean NOT NULL,
INSTANCE_NAME VARCHAR(80) NOT NULL,
FIRED_TIME BIGINT NOT NULL,
STATE VARCHAR(16) NOT NULL,
JOB_NAME VARCHAR(80) NULL,
JOB_GROUP VARCHAR(80) NULL,
IS_STATEFUL boolean NULL,
REQUESTS_RECOVERY boolean NULL,
PRIMARY KEY (ENTRY_ID)
);

CREATE TABLE QRTZ_SCHEDULER_STATE
(
INSTANCE_NAME VARCHAR(80) NOT NULL,
LAST_CHECKIN_TIME BIGINT NOT NULL,
CHECKIN_INTERVAL BIGINT NOT NULL,
RECOVERER VARCHAR(80) NULL,
PRIMARY KEY (INSTANCE_NAME)
);

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

INSERT INTO QRTZ_LOCKS values(‘TRIGGER_ACCESS’);
INSERT INTO QRTZ_LOCKS values(‘JOB_ACCESS’);
INSERT INTO QRTZ_LOCKS values(‘CALENDAR_ACCESS’);
INSERT INTO QRTZ_LOCKS values(‘STATE_ACCESS’);
INSERT INTO QRTZ_LOCKS values(‘MISFIRE_ACCESS’);
commit;
******************************************
Than we must have the scheduling file that will invoke spring scheduler…

/WEB-INF/quartz.properties
/WEB-INF/database.properties

applicationContext

${quartz.scheduler.instanceId}
${quartz.threadPool.class}
${quartz.threadPool.threadCount}
${quartz.threadPool.threadPriority}
6000
${quartz.jobStore.isClustered}
${quartz.jobStore.class}
${quartz.jobStore.driverDelegateClass}
${quartz.jobStore.useProperties}
${quartz.plugin.shutdownHook.class}
${quartz.plugin.shutdownHook.cleanShutdown}
${quartz.plugin.triggHistory.class}

com.onexam.service.DummyJob

${cronExpression.dummyJobCron}

Here the place holder are given in quartz.properties

# cron expression for time

#Expression Meaning
# “* * * * * ?” Every Second
# “0 0 12 * * ?” Fire at 12pm (noon) every day
# “0 15 10 ? * *” Fire at 10:15am every day
# “0 15 10 * * ?” Fire at 10:15am every day
# “0 15 10 * * ? *” Fire at 10:15am every day
# “0 15 10 * * ? 2005” Fire at 10:15am every day during the year 2005
# “0 * 14 * * ?” Fire every minute starting at 2pm and ending at 2:59pm, every day
# “0 0/5 14 * * ?” Fire every 5 minutes starting at 2pm and ending at 2:55pm, every day
# “0 0/5 14,18 * * ?” Fire every 5 minutes starting at 2pm and ending at 2:55pm, AND fire every 5 minutes starting at 6pm and ending at 6:55pm, every day
# “0 0-5 14 * * ?” Fire every minute starting at 2pm and ending at 2:05pm, every day
# “0 10,44 14 ? 3 WED” Fire at 2:10pm and at 2:44pm every Wednesday in the month of March.
# “0 15 10 ? * MON-FRI” Fire at 10:15am every Monday, Tuesday, Wednesday, Thursday and Friday
# “0 15 10 15 * ?” Fire at 10:15am on the 15th day of every month
# “0 15 10 L * ?” Fire at 10:15am on the last day of every month
# “0 15 10 ? * 6L” Fire at 10:15am on the last Friday of every month
# “0 15 10 ? * 6L” Fire at 10:15am on the last Friday of every month
# “0 15 10 ? * 6L 2002-2005” Fire at 10:15am on every last friday of every month during the years 2002, 2003, 2004 and 2005
# “0 15 10 ? * 6#3” Fire at 10:15am on the third Friday of every month

cronExpression.dummyJobCron=* * * * * ?
#cron expression for every minut

#configuration

#Configure Main Scheduler Properties
quartz.scheduler.instanceId=AUTO

#Configure ThreadPool
quartz.threadPool.class=org.quartz.simpl.SimpleThreadPool
quartz.threadPool.threadCount=10
quartz.threadPool.threadPriority=1

#Configure JobStore
quartz.jobStore.misfireThreshold=60000
quartz.jobStore.isClustered=true
quartz.jobStore.class=org.quartz.impl.jdbcjobstore.JobStoreTX
#quartz.jobStore.driverDelegateClass=org.quartz.impl.jdbcjobstore.StdJDBCDelegate
quartz.jobStore.driverDelegateClass=org.quartz.impl.jdbcjobstore.PostgreSQLDelegate

quartz.jobStore.useProperties=false

#Configure Plugins
quartz.plugin.shutdownHook.class=org.quartz.plugins.management.ShutdownHookPlugin
quartz.plugin.shutdownHook.cleanShutdown=true
quartz.plugin.triggHistory.class=org.quartz.plugins.history.LoggingJobHistoryPlugin
….

If you are getting error like
org.postgresql.util.PSQLException: Bad value for type int : \254\3550005sr0025org.quartz.JobDataMap

then remember you need to use
#quartz.jobStore.driverDelegateClass=org.quartz.impl.jdbcjobstore.StdJDBCDelegate
quartz.jobStore.driverDelegateClass=org.quartz.impl.jdbcjobstore.PostgreSQLDelegate

Advertisements
    • Amit
    • February 10th, 2009

    This is very helpfull

  1. Its good to understand, the integration

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: