Spring Batch Framework - Auto create Batch Table

Question

I just created a batch job using Spring Batch framework, but I don't have Database privileges to run CREATE SQL. When I try to run the batch job I hit the error while the framework tried to create TABLE_BATCH_INSTANCE. I try to disable the

<jdbc:initialize-database data-source="dataSource" enabled="false">    
 ...
</jdbc:initialize-database>

But after I tried I still hit the error

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT JOB_INSTANCE_ID, JOB_NAME from BATCH_JOB_INSTANCE where JOB_NAME = ? and JOB_KEY = ?]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

Anyway can disable the SQL, I just want to test my reader writer and processor work properly.

Answer 1

UPDATE: As of spring 2.5.0, you should use spring.batch.jdbc.initialize-schema instead. See source.

---

With Spring Boot 2.0 you probably need this: https://docs.spring.io/spring-boot/docs/2.0.0.M7/reference/htmlsingle/#howto-initialize-a-spring-batch-database

spring.batch.initialize-schema=always

By default it will only create the tables if you are using an embedded database.

Or

 spring.batch.initialize-schema=never

To permanently disable it.

Answer 2

Spring Batch uses the database to save metadata for its recover/retry functionality.

If you can't create tables in the database then you have to disable this behaviour

If you can create the batch metadata tables but not in runtime then you might create them manually

Answer 3

Spring Batch required following tables to run job

• BATCH_JOB_EXECUTION
• BATCH_JOB_EXECUTION_CONTEXT
• BATCH_JOB_EXECUTION_PARAMS
• BATCH_JOB_EXECUTION_SEQ
• BATCH_JOB_INSTANCE
• BATCH_JOB_SEQ
• BATCH_STEP_EXECUTION
• BATCH_STEP_EXECUTION_CONTEXT
• BATCH_STEP_EXECUTION_SEQ

If you are using h2 db then it will create all required table by default

• spring.h2.console.enabled=true
• spring.datasource.url=jdbc:h2:mem:testdb
• spring.datasource.driverClassName=org.h2.Driver
• spring.datasource.username=sa
• spring.datasource.password=
• spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

When you start using Mysql or any other database you need to add follwing properties into application.properties

               spring.batch.initialize-schema=always

Answer 4

Since Spring Boot 2.5.0 use

# to always initialize the datasource:
spring.batch.jdbc.initialize-schema=always

# to only initialize an embedded datasource:
spring.batch.jdbc.initialize-schema=embedded

# to never initialize the datasource:
spring.batch.jdbc.initialize-schema=never

(spring.batch.initialize-schema is deprecated since 2.5.0 for removal in 2.7.0)

Answer 5

To enable auto create spring batch data-schema simply add this line to your spring application.properties file :

spring.batch.initialize-schema=always

To understand more about Spring batch meta-data schema :

https://docs.spring.io/spring-batch/trunk/reference/html/metaDataSchema.html

Answer 6

Seems silly, but someone can have the same problem.

I was receiving this error after drop all tables from a database. When I tried to start the Spring Batch, I received the error:

bad SQL grammar [SELECT JOB_INSTANCE_ID, JOB_NAME from BATCH_JOB_INSTANCE where JOB_NAME = ? and JOB_KEY = ?]

and:

Invalid object name 'BATCH_JOB_INSTANCE'

This happened to me because I drop the tables without restart the service. The service was started and receive the database metadata with the Batch tables on the database. After drop them and not restart the server, the Spring Batch thought that the tables still exists.

After restart the Spring Batch server and execute the batch again, the tables were created without error.

Answer 7

When running with Spring Boot:

Running with Spring Boot v1.5.14.RELEASE, Spring v4.3.18.RELEASE

This should be enough:

spring:
    batch:
        initializer:
            enabled: false

The initialize-schema did not work for this Spring boot version. After that I was able to copy the SQL scripts from the spring-core jar and change the table capitalization since this was my issue with the automatic table creation under Windows/Mac/Linux.

Answer 8

<?xml version="1.0" encoding="UTF-8"?>
<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">


    <!-- 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/springbatch" />
        <property name="username" value="root" />
        <property name="password" value="" />
    </bean>

    <!-- transaction manager -->
    <bean id="transactionManager" class="org.springframework.batch.support.transaction.ResourcelessTransactionManager" />

    <!-- 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>

And make sure you are using compatible spring-jdbc -version with spring-batch. Most probably spring-jdbc-3.2.2.RELEASE.JAR compatible.

Answer 9

<jdbc:initialize-database/> tag is parsed by Spring using InitializeDatabaseBeanDefinitionParser. You can try debugging this class in your IDE to make sure what values are being picked up for enabled attribute. Also this value can be disabled by using JVM parameter -Dspring.batch.initializer.enabled=false

Answer 10

this works for me: Spring boot 2.0

  batch:
        initialize-schema: never
        initializer:
            enabled: false

Answer 11

Use the following setting as the suggested one above has been deprecated:

spring.batch.jdbc.initialize-schema=always

Answer 12

In your DataSourceConfig you should add this code. Once the app comes up it deletes any existing schema , or creates a new one .

private DataSource dataSource() {
    EmbeddedDatabaseBuilder builder = new EmbeddedDatabaseBuilder();
    return builder.setType(EmbeddedDatabaseType.HSQL)
      .addScript("classpath:org/springframework/batch/core/schema-drop-h2.sql")
      .addScript("classpath:org/springframework/batch/core/schema-h2.sql")
      .build();
}

If you are not able to do this , the schema scripts are present in your .m2 folder . Run the scripts manually. Location is inside spring-batch-core jar.

C:\Users\XXX.m2\repository\org\springframework\batch\spring-batch-core\4.3.7.

Split open this jar and find sql schema script you want.

• org\springframework\batch\core\schema-h2.sql.
• org\springframework\batch\core\schema-drop-h2.sql.