title: Spring Batch - Unable to create metadata tables on Postgres and load actual data to mysql
body: <p>I am developing <code>Spring Boot v2.2.6.RELEASE + Spring Batch</code>. In this example, I am looking to read csv file, load the data into mysql and spring batch metadata tables into the Postgres database.</p>
  
  <p>But This gives me error:</p>
  
  <pre><code>java.lang.IllegalStateException: Failed to execute CommandLineRunner
      at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:787) [spring-boot-2.2.2.RELEASE.jar:2.2.2.RELEASE]
      at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:768) [spring-boot-2.2.2.RELEASE.jar:2.2.2.RELEASE]
      at org.springframework.boot.SpringApplication.run(SpringApplication.java:322) [spring-boot-2.2.2.RELEASE.jar:2.2.2.RELEASE]
      at org.springframework.boot.SpringApplication.run(SpringApplication.java:1226) [spring-boot-2.2.2.RELEASE.jar:2.2.2.RELEASE]
      at org.springframework.boot.SpringApplication.run(SpringApplication.java:1215) [spring-boot-2.2.2.RELEASE.jar:2.2.2.RELEASE]
      at com.example.SpringBatchMetadataInDiffSchemaApplication.main(SpringBatchMetadataInDiffSchemaApplication.java:27) [classes/:na]
  Caused by: 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:235) ~[spring-jdbc-5.2.2.RELEASE.jar:5.2.2.RELEASE]
      at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-5.2.2.RELEASE.jar:5.2.2.RELEASE]
      at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1443) ~[spring-jdbc-5.2.2.RELEASE.jar:5.2.2.RELEASE]
      at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633) ~[spring-jdbc-5.2.2.RELEASE.jar:5.2.2.RELEASE]
      at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:669) ~[spring-jdbc-5.2.2.RELEASE.jar:5.2.2.RELEASE]
      at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:700) ~[spring-jdbc-5.2.2.RELEASE.jar:5.2.2.RELEASE]
      at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:712) ~[spring-jdbc-5.2.2.RELEASE.jar:5.2.2.RELEASE]
      at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:768) ~[spring-jdbc-5.2.2.RELEASE.jar:5.2.2.RELEASE]
      at org.springframework.batch.core.repository.dao.JdbcJobInstanceDao.getJobInstance(JdbcJobInstanceDao.java:151) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
      at org.springframework.batch.core.repository.support.SimpleJobRepository.getLastJobExecution(SimpleJobRepository.java:271) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_171]
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_171]
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_171]
      at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_171]
      at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344) ~[spring-aop-5.2.2.RELEASE.jar:5.2.2.RELEASE]
      at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198) ~[spring-aop-5.2.2.RELEASE.jar:5.2.2.RELEASE]
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.2.2.RELEASE.jar:5.2.2.RELEASE]
      at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:366) ~[spring-tx-5.2.2.RELEASE.jar:5.2.2.RELEASE]
      at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:99) ~[spring-tx-5.2.2.RELEASE.jar:5.2.2.RELEASE]
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.2.RELEASE.jar:5.2.2.RELEASE]
      at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) ~[spring-aop-5.2.2.RELEASE.jar:5.2.2.RELEASE]
      at com.sun.proxy.$Proxy61.getLastJobExecution(Unknown Source) ~[na:na]
      at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:104) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_171]
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_171]
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_171]
      at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_171]
      at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344) ~[spring-aop-5.2.2.RELEASE.jar:5.2.2.RELEASE]
      at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198) ~[spring-aop-5.2.2.RELEASE.jar:5.2.2.RELEASE]
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.2.2.RELEASE.jar:5.2.2.RELEASE]
      at org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:127) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.2.RELEASE.jar:5.2.2.RELEASE]
      at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) ~[spring-aop-5.2.2.RELEASE.jar:5.2.2.RELEASE]
      at com.sun.proxy.$Proxy58.run(Unknown Source) ~[na:na]
      at com.example.SpringBatchMetadataInDiffSchemaApplication.run(SpringBatchMetadataInDiffSchemaApplication.java:36) [classes/:na]
      at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:784) [spring-boot-2.2.2.RELEASE.jar:2.2.2.RELEASE]
      ... 5 common frames omitted
  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:2497) ~[postgresql-42.2.8.jar:42.2.8]
      at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2233) ~[postgresql-42.2.8.jar:42.2.8]
      at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:310) ~[postgresql-42.2.8.jar:42.2.8]
      at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:446) ~[postgresql-42.2.8.jar:42.2.8]
      at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:370) ~[postgresql-42.2.8.jar:42.2.8]
      at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:149) ~[postgresql-42.2.8.jar:42.2.8]
      at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:108) ~[postgresql-42.2.8.jar:42.2.8]
      at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-3.4.1.jar:na]
      at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-3.4.1.jar:na]
      at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:678) ~[spring-jdbc-5.2.2.RELEASE.jar:5.2.2.RELEASE]
      at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617) ~[spring-jdbc-5.2.2.RELEASE.jar:5.2.2.RELEASE]
      ... 37 common frames omitted
  </code></pre>
  
  <p>JobConfig.java</p>
  
  <pre><code>@Configuration
  public class JobConfig {
      @Autowired
      private StepBuilderFactory stepBuilderFactory;
  
      @Autowired
      private JobBuilderFactory jobBuilderFactory;
  
      @Qualifier("secondaryDS")
      @Autowired
      private DataSource dataSource;
  
      @Bean
      public FlatFileItemReader&lt;Customer&gt; customerItemReader(){
          FlatFileItemReader&lt;Customer&gt; reader = new FlatFileItemReader&lt;&gt;();
          reader.setLinesToSkip(1);
          reader.setResource(new ClassPathResource("/data/customer.csv"));
  
          DelimitedLineTokenizer tokenizer = new DelimitedLineTokenizer();
          tokenizer.setNames(new String[] {"id", "firstName", "lastName", "birthdate"});
  
          DefaultLineMapper&lt;Customer&gt; customerLineMapper = new DefaultLineMapper&lt;&gt;();
          customerLineMapper.setLineTokenizer(tokenizer);
          customerLineMapper.setFieldSetMapper(new CustomerFieldSetMapper());
          customerLineMapper.afterPropertiesSet();
  
          reader.setLineMapper(customerLineMapper);
  
          return reader;
      }
  
      @Bean
      public JdbcBatchItemWriter&lt;Customer&gt; customerItemWriter(){
          JdbcBatchItemWriter&lt;Customer&gt; writer = new JdbcBatchItemWriter&lt;&gt;();
          writer.setDataSource(this.dataSource);
          writer.setSql("INSERT INTO CUSTOMER VALUES (:id, :firstName, :lastName, :birthdate)");
          writer.setItemSqlParameterSourceProvider(new BeanPropertyItemSqlParameterSourceProvider&lt;&gt;());
          writer.afterPropertiesSet();
  
          return writer;
      }
  
      @Bean
      public Step step1() {
          return stepBuilderFactory.get("step1")
                  .&lt;Customer, Customer&gt; chunk(1000)
                  .reader(customerItemReader())
                  .writer(customerItemWriter())
                  .build();
      }
  
      @Bean
      public Job job() {
          return jobBuilderFactory.get("job")
                  .start(step1())
                  .build();
      }   
  }
  </code></pre>
  
  <p>DatabaseConfig.java</p>
  
  <pre><code>@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();
      }
  
      //Ref: https://stackoverflow.com/questions/28275448/multiple-data-source-and-schema-creation-in-spring-boot
      @Bean(name = "primaryEntityManagerFactory")
      public LocalContainerEntityManagerFactoryBean primaryEntityManagerFactory( EntityManagerFactoryBuilder builder) {
          Map&lt;String, Object&gt; properties = new HashMap&lt;&gt;();
          properties.put("hibernate.hbm2ddl.auto", "update");
          return builder
                  .dataSource(getSeconadaryBatchDataSource())
                  .packages("com.example.model")
                  .persistenceUnit("default")
                  .properties(properties)
                  .build();
      }
  
      /*@Bean(name = "secondaryEntityManagerFactory")
      @Primary
      public LocalContainerEntityManagerFactoryBean secondaryEntityManagerFactory(
              EntityManagerFactoryBuilder builder) {
          Map&lt;String, Object&gt; properties = new HashMap&lt;String, Object&gt;();
          properties.put("hibernate.hbm2ddl.auto", "create");
          return builder
                  .dataSource(getPrimaryBatchDataSource())
                  .packages("com.example.model")
                  .persistenceUnit("default")
                  .properties(properties)
                  .build();
      }*/
  }
  </code></pre>
  
  <p>application.properties</p>
  
  <pre><code>spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
  spring.datasource.url=jdbc:mysql://localhost:3306/test
  spring.datasource.username=root
  spring.datasource.password=root
  spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect
  
  spring.jpa.hibernate.ddl-auto=create
  spring.jpa.show-sql=true
  spring.jpa.properties.hibernate.format_sql=true
  
  
  # Postgres
  spring.hello.datasource.driver-class-name=org.postgresql.Driver
  spring.hello.datasource.url=jdbc:postgresql://localhost:5432/program?currentSchema=BATCH
  spring.hello.datasource.username=postgres
  spring.hello.datasource.password=admin
  
  #spring.batch.table-prefix=batchmetadata.BATCH_
  spring.batch.initialize-schema=always
  spring.batch.job.enabled=false
  
  spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
  spring.jpa.hibernate.naming.implicit-strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyJpaImpl
  </code></pre>