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.
Also, 
