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.
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.
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?
I am using H2 in memory database. Metadata tables are created but there is no data in it. What could be the issue?
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)
Thank you so much. This worked for me.
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.
How can give the meta table create configuration in Spring Boot application.yml/application.properties file?
It doesn’t work. I’ve added the ignore-failures as follows as on Oracle it bails when it can’t find the table.
jdbc:initialize-database data-source=”dataSource” ignore-failures=”DROPS”
Hello,
I still wonder how we can create the custom tables which are not part of Spring Batch?
Can we connect to hive database using the above configuration details?
To deploy to eclipse jetty I made the following (classpath) tweak to the location :
Thanks for the useful article!
how to avoid that job tables are destroyed and created each time the job is starting?
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 :
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
Hi Mkyong,
Thank you very much for the post. It solve my 2 days hunting issue with the metadata.
—
Srinivasarao