Main Tutorials

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 Author

author image
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

Subscribe
Notify of
15 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
Gaurav
9 years ago

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
4 years ago

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

Razvan
5 years ago

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
5 years ago

Thank you so much. This worked for me.

Jeeva
6 years ago

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
6 years ago

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

Stuart
6 years ago

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
6 years ago
Reply to  Stuart

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

Neha
7 years ago

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

Nitish
9 years ago

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

Temple
9 years ago

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

Thanks for the useful article!

salvatore
9 years ago

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

Benamar
5 years ago
Reply to  salvatore

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
9 years ago

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
10 years ago

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

Srinivasarao