1. Create the necessary database tables

dobrinka_stefanova · ‎05-16-2017

Note: This blog is about Java EE 6 applications developed for SAP Cloud Platform. If you are using the Java EE 6 Web Profile runtime, you can use the EJB 3.1 Timer Service in your applications, although it is not part of the EJB 3.1 Lite specification.

By default, EJB timers in Java EE 6 Web Profile at SAP Cloud Platform are persisted in-memory. You can have them use a database as a persistent storage by making some additional configurations. The EJB Timer Service implementation in Java EE 6 Web Profile is based on Quartz scheduler and the necessary configurations are specific for it.

1. Create the necessary database tables

For Quartz to persist to a database, it needs its tables to be available there. So first you need to create Quartz tables in the database. For the different databases you can use different SQL scripts:

If you are using SAP HANA database, use the following script:

delete from qrtz_fired_triggers;

delete from qrtz_simple_triggers;

delete from qrtz_simprop_triggers;

delete from qrtz_cron_triggers;

delete from qrtz_blob_triggers;

delete from qrtz_triggers;

delete from qrtz_job_details;

delete from qrtz_calendars;

delete from qrtz_paused_trigger_grps;

delete from qrtz_locks;

delete from qrtz_scheduler_state;



drop table qrtz_calendars;

drop table qrtz_fired_triggers;

drop table qrtz_blob_triggers;

drop table qrtz_cron_triggers;

drop table qrtz_simple_triggers;

drop table qrtz_simprop_triggers;

drop table qrtz_triggers;

drop table qrtz_job_details;

drop table qrtz_paused_trigger_grps;

drop table qrtz_locks;

drop table qrtz_scheduler_state;

CREATE TABLE qrtz_job_details

  (

    SCHED_NAME VARCHAR2(120) NOT NULL,

    JOB_NAME  VARCHAR2(200) NOT NULL,

    JOB_GROUP VARCHAR2(200) NOT NULL,

    DESCRIPTION VARCHAR2(250) NULL,

    JOB_CLASS_NAME   VARCHAR2(250) NOT NULL, 

    IS_DURABLE VARCHAR2(1) NOT NULL,

    IS_NONCONCURRENT VARCHAR2(1) NOT NULL,

    IS_UPDATE_DATA VARCHAR2(1) NOT NULL,

    REQUESTS_RECOVERY VARCHAR2(1) NOT NULL,

    JOB_DATA BLOB NULL

);



ALTER TABLE qrtz_job_details ADD CONSTRAINT QRTZ_JOB_DETAILS_PK PRIMARY KEY (SCHED_NAME,JOB_NAME,JOB_GROUP);



CREATE TABLE qrtz_triggers

  (

    SCHED_NAME VARCHAR2(120) NOT NULL,

    TRIGGER_NAME VARCHAR2(200) NOT NULL,

    TRIGGER_GROUP VARCHAR2(200) NOT NULL,

    JOB_NAME  VARCHAR2(200) NOT NULL, 

    JOB_GROUP VARCHAR2(200) NOT NULL,

    DESCRIPTION VARCHAR2(250) NULL,

    NEXT_FIRE_TIME NUMBER(13) NULL,

    PREV_FIRE_TIME NUMBER(13) NULL,

    PRIORITY NUMBER(13) NULL,

    TRIGGER_STATE VARCHAR2(16) NOT NULL,

    TRIGGER_TYPE VARCHAR2(8) NOT NULL,

    START_TIME NUMBER(13) NOT NULL,

    END_TIME NUMBER(13) NULL,

    CALENDAR_NAME VARCHAR2(200) NULL,

    MISFIRE_INSTR NUMBER(2) NULL,

    JOB_DATA BLOB NULL

);



ALTER TABLE qrtz_triggers ADD CONSTRAINT QRTZ_TRIGGERS_PK PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP);

ALTER TABLE qrtz_triggers ADD CONSTRAINT QRTZ_TRIGGER_TO_JOBS_FK FOREIGN KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)

			REFERENCES QRTZ_JOB_DETAILS(SCHED_NAME,JOB_NAME,JOB_GROUP);



CREATE TABLE qrtz_simple_triggers

  (

    SCHED_NAME VARCHAR2(120) NOT NULL,

    TRIGGER_NAME VARCHAR2(200) NOT NULL,

    TRIGGER_GROUP VARCHAR2(200) NOT NULL,

    REPEAT_COUNT NUMBER(7) NOT NULL,

    REPEAT_INTERVAL NUMBER(12) NOT NULL,

    TIMES_TRIGGERED NUMBER(10) NOT NULL

);



ALTER TABLE qrtz_simple_triggers ADD CONSTRAINT QRTZ_SIMPLE_TRIG_PK PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP);

ALTER TABLE qrtz_simple_triggers ADD CONSTRAINT QRTZ_SIMPLE_TRIG_TO_TRIG_FK FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) 

			REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP);

				

CREATE TABLE qrtz_cron_triggers

  (

    SCHED_NAME VARCHAR2(120) NOT NULL,

    TRIGGER_NAME VARCHAR2(200) NOT NULL,

    TRIGGER_GROUP VARCHAR2(200) NOT NULL,

    CRON_EXPRESSION VARCHAR2(120) NOT NULL,

    TIME_ZONE_ID VARCHAR2(80)

);



ALTER TABLE qrtz_cron_triggers ADD CONSTRAINT QRTZ_CRON_TRIG_PK PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP);

ALTER TABLE qrtz_cron_triggers ADD CONSTRAINT QRTZ_CRON_TRIG_TO_TRIG_FK FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) 

			REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP);

			

CREATE TABLE qrtz_simprop_triggers

  (          

    SCHED_NAME VARCHAR2(120) NOT NULL,

    TRIGGER_NAME VARCHAR2(200) NOT NULL,

    TRIGGER_GROUP VARCHAR2(200) NOT NULL,

    STR_PROP_1 VARCHAR2(512) NULL,

    STR_PROP_2 VARCHAR2(512) NULL,

    STR_PROP_3 VARCHAR2(512) NULL,

    INT_PROP_1 NUMBER(10) NULL,

    INT_PROP_2 NUMBER(10) NULL,

    LONG_PROP_1 NUMBER(13) NULL,

    LONG_PROP_2 NUMBER(13) NULL,

    DEC_PROP_1 NUMERIC(13,4) NULL,

    DEC_PROP_2 NUMERIC(13,4) NULL,

    BOOL_PROP_1 VARCHAR2(1) NULL,

    BOOL_PROP_2 VARCHAR2(1) NULL

);



ALTER TABLE qrtz_simprop_triggers ADD CONSTRAINT QRTZ_SIMPROP_TRIG_PK PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP);

ALTER TABLE qrtz_simprop_triggers ADD CONSTRAINT QRTZ_SIMPROP_TRIG_TO_TRIG_FK FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)

      			REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP);

      			

CREATE TABLE qrtz_blob_triggers

  (

    SCHED_NAME VARCHAR2(120) NOT NULL,

    TRIGGER_NAME VARCHAR2(200) NOT NULL,

    TRIGGER_GROUP VARCHAR2(200) NOT NULL,

    BLOB_DATA BLOB NULL

);



ALTER TABLE qrtz_blob_triggers ADD CONSTRAINT QRTZ_BLOB_TRIG_PK PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP);

ALTER TABLE qrtz_blob_triggers ADD CONSTRAINT QRTZ_BLOB_TRIG_TO_TRIG_FK FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) 

        REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP);





CREATE TABLE qrtz_calendars

  (

    SCHED_NAME VARCHAR2(120) NOT NULL,

    CALENDAR_NAME  VARCHAR2(200) NOT NULL, 

    CALENDAR BLOB NOT NULL

);



ALTER TABLE qrtz_calendars ADD CONSTRAINT QRTZ_CALENDARS_PK PRIMARY KEY (SCHED_NAME,CALENDAR_NAME);



CREATE TABLE qrtz_paused_trigger_grps

  (

    SCHED_NAME VARCHAR2(120) NOT NULL,

    TRIGGER_GROUP  VARCHAR2(200) NOT NULL

);



ALTER TABLE qrtz_paused_trigger_grps ADD CONSTRAINT QRTZ_PAUSED_TRIG_GRPS_PK PRIMARY KEY (SCHED_NAME,TRIGGER_GROUP);



CREATE TABLE qrtz_fired_triggers 

  (

    SCHED_NAME VARCHAR2(120) NOT NULL,

    ENTRY_ID VARCHAR2(95) NOT NULL,

    TRIGGER_NAME VARCHAR2(200) NOT NULL,

    TRIGGER_GROUP VARCHAR2(200) NOT NULL,

    INSTANCE_NAME VARCHAR2(200) NOT NULL,

    FIRED_TIME NUMBER(13) NOT NULL,

    SCHED_TIME NUMBER(13) NOT NULL,

    PRIORITY NUMBER(13) NOT NULL,

    STATE VARCHAR2(16) NOT NULL,

    JOB_NAME VARCHAR2(200) NULL,

    JOB_GROUP VARCHAR2(200) NULL,

    IS_NONCONCURRENT VARCHAR2(1) NULL,

    REQUESTS_RECOVERY VARCHAR2(1) NULL

);



ALTER TABLE qrtz_fired_triggers ADD CONSTRAINT QRTZ_FIRED_TRIGGER_PK PRIMARY KEY (SCHED_NAME,ENTRY_ID);



CREATE TABLE qrtz_scheduler_state 

  (

    SCHED_NAME VARCHAR2(120) NOT NULL,

    INSTANCE_NAME VARCHAR2(200) NOT NULL,

    LAST_CHECKIN_TIME NUMBER(13) NOT NULL,

    CHECKIN_INTERVAL NUMBER(13) NOT NULL

);



ALTER TABLE qrtz_scheduler_state ADD CONSTRAINT QRTZ_SCHEDULER_STATE_PK PRIMARY KEY (SCHED_NAME,INSTANCE_NAME);



CREATE TABLE qrtz_locks

  (

    SCHED_NAME VARCHAR2(120) NOT NULL,

    LOCK_NAME  VARCHAR2(40) NOT NULL

);



ALTER TABLE qrtz_locks ADD CONSTRAINT QRTZ_LOCKS_PK PRIMARY KEY (SCHED_NAME,LOCK_NAME);







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

If you are testing locally with Derby database, you can use the SQL script from Quartz 2.2.1 distribution.

If you are using Sybase database you can use SQL from Quartz 2.2.1 distribution.

Note: The SQL scripts are located in subfolder docs/dbTables of the distribution archive.

2. Add configuration files to your application

application.properties

You should add application.properties file, to the WEB-INF folder of your application. This file should contain the following properties (you can find detailed explanation of each property in the configuration section in the official Quartz documentation):

org.quartz.scheduler.instanceName=<your_scheduler_name>



org.quartz.scheduler.instanceId=<your_scheduler_id>



org.quartz.jobStore.class=org.quartz.impl.jdbcjobstore.JobStoreCMT



org.quartz.jobStore.dataSource=<your_managed_datasource_name>



org.quartz.jobStore.nonManagedTXDataSource=<your_nonmanaged_datasource_name>



org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate



org.quartz.jobStore.tablePrefix=QRTZ_



org.quartz.jobStore.selectWithLockSQL=SELECT * FROM  {0}LOCKS UPDLOCK WHERE LOCK_NAME = ?



org.quartz.threadPool.threadCount=10



org.quartz.dataSource. <your_managed_datasource_name>.jndiURL=java:comp/env/<your_jndi_url>



org.quartz.dataSource. <your_nonmanaged_datasource_name>.jndiURL=java:comp/env/<your_jndi_url>



org.quartz.dataSource. <your_managed_datasource_name>.jndiAlwaysLookup=true

Example

For example, your application.properties file may look like this:

org.quartz.scheduler.instanceName=TimerService-Test-Scheduler-Name



org.quartz.scheduler.instanceId=TimerService-Test-Scheduler-ID



org.quartz.jobStore.class=org.quartz.impl.jdbcjobstore.JobStoreCMT



org.quartz.jobStore.dataSource=myTxDS



org.quartz.jobStore.nonManagedTXDataSource=myNoTxDS



org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate



org.quartz.jobStore.tablePrefix=QRTZ_



org.quartz.jobStore.selectWithLockSQL=SELECT * FROM  {0}LOCKS UPDLOCK WHERE LOCK_NAME = ?



org.quartz.threadPool.threadCount=10



org.quartz.dataSource.myTxDS.jndiURL=java:comp/env/jdbc/derbyDB



org.quartz.dataSource.myNoTxDS.jndiURL=java:comp/env/jdbc/derbyDB



org.quartz.dataSource.myTxDS.jndiAlwaysLookup=true

3. Add openejb-jar.xml

You should add openejb-jar.xml file to the WEB-INF folder of your application. This file should contain something like this:

<?xml version="1.0" encoding="UTF-8"?>

<openejb-jar xmlns="http://www.openejb.org/openejb-jar/1.1">



    <ejb-deployment ejb-name="ejb 1 using timers">

          <properties>

                openejb.quartz.use-TCCL = true

           </properties>

    </ejb-deployment>



    <ejb-deployment ejb-name="ejb 2 using timers">

           <properties>

                 openejb.quartz.use-TCCL = true

           </properties>

    </ejb-deployment>

</openejb-jar>

For each EJB using timers, openejb-jar.xml must contain a corresponding ejb-deployment element describing it.

Result

If you have persisted your EJB timers correctly into the database, they will be available after restart of your application’s VM on SAP Cloud Platform or your local server. For applications running on the cloud, you can use the restart console command, for example. For the local server, you can use the respective option in your local Eclipse IDE equipped with SAP Cloud Platform tools.

Persisting EJB Timers in a Database

1. Create the necessary database tables

2. Add configuration files to your application

application.properties

Example

3. Add openejb-jar.xml

Result

Get Your SAP HANA Idea Incubator Badge Today!

SCN Mission - SAP HANA Quiz Challenge is now retired

Share your #HANAStory and Win