Auto Create Quartz Tables at Startup

I recently needed to add Quartz support to a Spring project, along with a mechanism for automatically creating the Quartz tables at application startup. I did a bit of googling on how to get Quartz to auto-create its tables, but didn’t find anything. Here’s what I ended up doing to solve the problem.

First, lets consider the basic configuration of a Quartz Scheduler in Spring. If you’re planning on storing Jobs in a relational database, then your entry will look something like this :

   <bean id="quartzScheduler" class="org.springframework.scheduling.quartz.SchedulerFactoryBean">
      <property name="dataSource">
         <ref bean="dataSource"/>
      </property>
      <property name="transactionManager">
         <ref bean="transactionManager"/>
      </property>
      <property name="quartzProperties">
         <props>
            <prop key="org.quartz.jobStore.tablePrefix">QRTZ_</prop>
            <prop key="org.quartz.jobStore.selectWithLockSQL">SELECT * FROM {0}LOCKS UPDLOCK WHERE LOCK_NAME = ?</prop>
         </props>
      </property>
   </bean>

At this point, you could start up the application, but you’d get JDBC errors when Quartz tries to access its tables. You could pop out to a SQL console and manually create these tables, but you’d be faced with the same problem each time you deployed to a new environment.

To remedy this, we’ll make use of Spring’s DataSourceInitializer class, which will run designated SQL scripts when deployed :

<!--
   This will execute SQL scripts to recreate the quartz tables at 
   appserver boot time. 
-->
<bean id="quartzDbInitializer" class="org.springframework.jdbc.datasource.init.DataSourceInitializer">
  <property name="dataSource" ref="dataSource"/>
  <property name="enabled" value="true"/>
  <property name="databasePopulator">
    <bean class="org.springframework.jdbc.datasource.init.ResourceDatabasePopulator">
      <property name="continueOnError" value="true"/>
      <property name="ignoreFailedDrops" value="true"/>
      <property name="sqlScriptEncoding" value="UTF-8"/>
      <property name="scripts">
        <array>
          <value type="org.springframework.core.io.Resource">
            classpath:META-INF/quartz/oracle/drop-quartz-tables.sql
          </value>
          <value type="org.springframework.core.io.Resource">
            classpath:META-INF/quartz/oracle/create-quartz-tables.sql
          </value>
        </array>
      </property>
    </bean>
  </property>
</bean>

The above will execute two sql scripts each time the application is started :

• META-INF/quartz/oracle/drop-quartz-tables.sql
• META-INF/quartz/oracle/create-quartz-tables.sql

Note – you’ll probably want to remove / comment out the drop-quartz-tables.sql eventually (it will drop your quartz tables each time you restart the application – ok for kicking off development, but not viable long term).

Just one more small thing left to do : we need to add a dependency between our quartzScheduler bean and the quartzDbInitializer bean (so that the quartzDbInitializer bean will be instantiated BEFORE the quartzScheduler bean). To do this, simply add a “depends-on” attribute to the quartzScheduler bean (as shown below) :

   <bean id="quartzScheduler" class="org.springframework.scheduling.quartz.SchedulerFactoryBean" depends-on="quartzDbInitializer">
      <property name="dataSource">
         <ref bean="dataSource"/>
      </property>
      <property name="transactionManager">
         <ref bean="transactionManager"/>
      </property>
      <property name="quartzProperties">
         <props>
            <prop key="org.quartz.jobStore.tablePrefix">QRTZ_</prop>
            <prop key="org.quartz.jobStore.selectWithLockSQL">SELECT * FROM {0}LOCKS UPDLOCK WHERE LOCK_NAME = ?</prop>
         </props>
      </property>
   </bean>

That’s it.

Advertisement