Spring Batch metadata tables are not created automatically?

If jobRepository is created with MapJobRepositoryFactoryBean (metadata in memory), the Spring batch jobs are running successfully.

spring-config.xml

  <bean id="jobRepository"
    class="org.springframework.batch.core.repository.support.MapJobRepositoryFactoryBean">
    <property name="transactionManager" ref="transactionManager" />
  </bean>

Problem

After changing the jobRepository to store metadata into database :

spring-config.xml

  <bean id="jobRepository"
	class="org.springframework.batch.core.repository.support.JobRepositoryFactoryBean">
	<property name="dataSource" ref="dataSource" />
	<property name="transactionManager" ref="transactionManager" />
	<property name="databaseType" value="mysql" />
  </bean>
 
  <bean id="jobLauncher"
	class="org.springframework.batch.core.launch.support.SimpleJobLauncher">
	<property name="jobRepository" ref="jobRepository" />
  </bean>

  <bean id="dataSource"
	class="org.springframework.jdbc.datasource.DriverManagerDataSource">
	<property name="driverClassName" value="com.mysql.jdbc.Driver" />
	<property name="url" value="jdbc:mysql://localhost:3306/test" />
	<property name="username" value="root" />
	<property name="password" value="" />
  </bean>

It prompts the batch_job_instance table doesn’t exist? Why Spring didn’t create those meta tables automatically?


Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table db.batch_job_instance' doesn't exist
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)

Solution

The meta table’s scripts are stored in the spring-batch.jar, you need to create it manually.

spring-batch-meta-data-tables

Normally, you can automatic the table script creation via the Spring XML configuration file. For example,

spring-config.xml

<beans xmlns="http://www.springframework.org/schema/beans"
  xmlns:jdbc="http://www.springframework.org/schema/jdbc" 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.springframework.org/schema/beans 
	http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
	http://www.springframework.org/schema/jdbc 
	http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.xsd">

  <!-- connect to database -->
  <bean id="dataSource"
	class="org.springframework.jdbc.datasource.DriverManagerDataSource">
	<property name="driverClassName" value="com.mysql.jdbc.Driver" />
	<property name="url" value="jdbc:mysql://localhost:3306/test" />
	<property name="username" value="root" />
	<property name="password" value="" />
  </bean>
	
  <!-- create job-meta tables automatically -->
  <jdbc:initialize-database data-source="dataSource">
	<jdbc:script location="org/springframework/batch/core/schema-drop-mysql.sql" />
	<jdbc:script location="org/springframework/batch/core/schema-mysql.sql" />
  </jdbc:initialize-database>

</beans>

Run your Spring batch jobs again, those meta tables will be created automatically.

References

  1. Spring Batch – Meta-Data Schema

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

avatar
13 Comment threads
2 Thread replies
1 Followers
 
Most reacted comment
Hottest comment thread
14 Comment authors
Viraj BhosleRazvanBenamarSanket MehtaJeeva Recent comment authors
newest oldest most voted
Gaurav
Guest
Gaurav

What if I want to create the tables with a Prefix: MY_TABLE_BATCH_JOB_EXECUTION?

Can I still use these scripts in an automated way?

Viraj Bhosle
Guest
Viraj Bhosle

I am using H2 in memory database. Metadata tables are created but there is no data in it. What could be the issue?

Razvan
Guest
Razvan

An update to your solution:
Spring Batch has the possibility regarding to the DataSource implementation to select a schema(link to schema list: https://github.com/spring-projects/spring-batch/tree/master/spring-batch-core/src/main/resources/org/springframework/batch/core)
using the property: spring.batch.initialize-schema=always (Documentation (84.4) https://docs.spring.io/spring-boot/docs/current/reference/html/howto-database-initialization.html)

Sanket Mehta
Guest
Sanket Mehta

Thank you so much. This worked for me.

Jeeva
Guest
Jeeva

These tables are getting created but no data is inserted in any of these tables. Do we have to do anything manually to enable insert into these metadata tables.

Prasad
Guest
Prasad

How can give the meta table create configuration in Spring Boot application.yml/application.properties file?

Stuart
Guest
Stuart

It doesn’t work. I’ve added the ignore-failures as follows as on Oracle it bails when it can’t find the table.

Stuart
Guest
Stuart

jdbc:initialize-database data-source=”dataSource” ignore-failures=”DROPS”

Neha
Guest
Neha

Hello,
I still wonder how we can create the custom tables which are not part of Spring Batch?

Nitish
Guest
Nitish

Can we connect to hive database using the above configuration details?

Temple
Guest
Temple

To deploy to eclipse jetty I made the following (classpath) tweak to the location :

Thanks for the useful article!

salvatore
Guest
salvatore

how to avoid that job tables are destroyed and created each time the job is starting?

Benamar
Guest
Benamar

you can use the tag: jdbc:initialize-database ….. espetially this one : jdbc:script location=”org/springframework/batch/core/schema-drop-mysql.sql , for the first time to create tables , and remove it after the first run :

varaprasad
Guest
varaprasad

i’m using oracle 11g so i searched for 11g i dint get it so i used oracle 10g only and got this exception org.springframework.dao.DataAccessResourceFailureException: Could not obtain last_insert_id(); nested exception is java.sql.SQLSyntaxErrorException: ORA-02201: sequence not allowed here

Srinivasa rao
Guest
Srinivasa rao

Hi Mkyong,
Thank you very much for the post. It solve my 2 days hunting issue with the metadata.

Srinivasarao