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.

Advertisements

7 Responses to Auto Create Quartz Tables at Startup

  1. HFC says:

    Where is the DTD for ? Otherwise very useful, thanks.

  2. lhankins says:

    @HFC – The XML snippets are from spring applicationContext.xml files. You can learn more about them here :

    http://static.springsource.org/spring/docs/3.0.x/spring-framework-reference/html/

    In particular, this chapter gives you some example applicationContext.xml files, along with the required XSD references at the top :

    http://static.springsource.org/spring/docs/3.0.x/spring-framework-reference/html/beans.html#beans-factory-instantiation

  3. HFC says:

    Thanks, I realized your example was part of a Spring configuration, but mine would not accept the lt array gt element, until I replaced array with list. Which scheme is that in…

  4. lhankins says:

    I believe its coming from spring-beans-3.0.xsd

  5. Josep Prat says:

    Thank you! Very helpful.

  6. Augusto says:

    Thanks for sharing this!! I almost started implementing something similar to DataSourceInitializer :).

  7. Thanks for sharing good article! It’s very helpful

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: