How to configure spring batch not to auto create batch tables for storing meta data?

Question

I'm working on a spring batch which uses JPA to perform CRUD operations on PostGres database. I'm using Spring boot 2.1.3. Even though I added the below configuration to disable Spring batch to use my postgres database for storing batch job meta data information, I' getting "ERROR: relation "batch_job_instance" does not exist" exception as shown below. Also I have followed the solution mentioned in here. Can anyone please suggest on what additional things needs to be done?

hibernate.temp.use_jdbc_metadata_defaults=false
spring.batch.initialize-schema=never
spring.batch.initializer.enabled=false

Exception:

 Exception in thread "main" 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 org.postgresql.util.PSQLException: ERROR: relation "batch_job_instance" does not exist
  Position: 39
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:234)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1444)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:632)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:669)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:700)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:712)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:768)
    at org.springframework.batch.core.repository.dao.JdbcJobInstanceDao.getJobInstance(JdbcJobInstanceDao.java:148)
    at org.springframework.batch.core.repository.support.SimpleJobRepository.getLastJobExecution(SimpleJobRepository.java:297)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
    at com.sun.proxy.$Proxy69.getLastJobExecution(Unknown Source)
    at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:101)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:127)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
    at com.sun.proxy.$Proxy78.run(Unknown Source)
    at com.test.BatchApplication.main(BatchApplication.java:28)
Caused by: org.postgresql.util.PSQLException: ERROR: relation "batch_job_instance" does not exist
  Position: 39
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143)
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:106)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
    at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:678)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617)
    ... 31 more

In the current latest Spring batch 5.0, this solution helped me https://stackoverflow.com/a/75480766/6073148 — Zahid Khan, Feb 17 '23 at 06:34

Mahmoud Ben Hassine · Accepted Answer · 2019-03-04T18:32:23.517

10

Since you told Spring Boot to not create meta-data tables, you need to create them manually upfront in your datasource. For Postgres, you need to execute the schema-postgresql.sql script on your database before running your job.

EDIT: If you don't want to persist meta-data tables, you can use the in-memory Map-based job repository:

@Configuration
@EnableBatchProcessing
public class MyJobConfiguration extends DefaultBatchConfigurer {

    @Override
    protected JobRepository createJobRepository() throws Exception {
        MapJobRepositoryFactoryBean factoryBean = new MapJobRepositoryFactoryBean();
        factoryBean.afterPropertiesSet();
        return factoryBean.getObject();
    }

    // other beans related to job definition
}

Note that the Map-based job repository is not intended for production use.

Another option it to use the JDBC based job repository but with an embedded database, like HSQLDB, H2, etc.

Hope this helps.

edited Mar 04 '19 at 18:32

answered Mar 01 '19 at 20:47

Mahmoud Ben Hassine

28,519
3
32
50

I don't want my batch job instance to create meta-data tables at all. Can you please suggest if we can achieve this by any configuration changes ? – Tech Guy Mar 04 '19 at 14:28
I added an example to my answer. Hope it helps. – Mahmoud Ben Hassine Mar 04 '19 at 18:32
@MahmoudBenHassine - This works though I don't have H2 dependency in my pom.xml file. Could you please explain how this is working ? – PAA Apr 07 '19 at 17:18
The map job repository does not use an in-memory database (it uses Maps to store meta-data), that's why it works even if you don't have h2 in your classpath. The second option in my answer is to use the jdbc based job repository which requires a database (be it an in-memory db or not). Hope this clarifies your confusion. – Mahmoud Ben Hassine Apr 07 '19 at 20:52
@MahmoudBenHassine - Thanks 1st part is clean and clear to me. 2nd option part is still not clear. Would you mind in clarifying again please? Its important to understand how it works ! Any link would also help – PAA Apr 08 '19 at 18:43
For the second option you create a real data source object pointing to a real database just like mysql or oracle, but one that is in the JVM's memory instead. So the jdbc based job repository will issue SQL queries to get/save meta-data instead of doing `Map.get(jobInstanceId)` for example to get a job instance or `Map.put(stepExecutionId, stepExecution)` to save a step execution. Hope this clarifies further. – Mahmoud Ben Hassine Apr 09 '19 at 06:44
this somehow corrupts JPA transactions management... "javax.persistence.TransactionRequiredException: no transaction is in progress" – mirec Oct 30 '19 at 21:50

PAA · Answer 2 · 2022-06-04T17:10:09.057

Here is the working code: https://www.linkedin.com/pulse/spring-batch-create-metadata-table-different-schema-ashtikar/

This shows how to use different schema for metadata tables and other schema to store actual records.

@Configuration
public class DatabaseConfig {
    @Autowired
    private Environment env;


    // For Test schema
    @Bean(name="secondaryDS")
    @ConfigurationProperties(prefix="spring.datasource")
    public DataSource getSeconadaryBatchDataSource(){
        return DataSourceBuilder.create()
                .url(env.getProperty("spring.datasource.url"))
                .driverClassName(env.getProperty("spring.datasource.driver-class-name"))
                .username(env.getProperty("spring.datasource.username"))
                .password(env.getProperty("spring.datasource.password"))
                .build();
    }


    // For "batchmetadata" tables
    @Bean(name="primaryDS")
    @Primary
    @ConfigurationProperties(prefix="spring.hello.datasource")
    public DataSource getPrimaryBatchDataSource(){
        return DataSourceBuilder.create()
                .url(env.getProperty("spring.hello.datasource.url"))
                .driverClassName(env.getProperty("spring.hello.datasource.driver-class-name"))
                .username(env.getProperty("spring.hello.datasource.username"))
                .password(env.getProperty("spring.hello.datasource.password"))
                .build();
    }
    
    @Bean(name = "primaryEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean primaryEntityManagerFactory( EntityManagerFactoryBuilder builder) {
        Map<String, Object> properties = new HashMap<String, Object>();
        properties.put("hibernate.hbm2ddl.auto", "update");
        return builder
                .dataSource(getSeconadaryBatchDataSource())
                .packages("com.example.model")
                .persistenceUnit("default")
                .properties(properties)
                .build();
    }
}

How to configure spring batch not to auto create batch tables for storing meta data?

2 Answers2

Linked

Related