春季批处理 – 无法在Postgres上创建元数据表并将实际数据加载到mysql

huangapple
go评论80阅读模式
英文:

Spring Batch - Unable to create metadata tables on Postgres and load actual data to mysql

问题

以下是您提供的内容的翻译：

我正在开发Spring Boot v2.2.6.RELEASE + Spring Batch。在这个示例中，我希望读取CSV文件，将数据加载到MySQL中，并将Spring Batch元数据表加载到Postgres数据库中。

但是，这给我带来了错误：

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]
    ...
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
    ...
Caused by: org.postgresql.util.PSQLException: ERROR: relation "batch_job_instance" does not exist
    ...

JobConfig.java

@Configuration
public class JobConfig {
    @Autowired
    private StepBuilderFactory stepBuilderFactory;

    @Autowired
    private JobBuilderFactory jobBuilderFactory;

    @Qualifier("secondaryDS")
    @Autowired
    private DataSource dataSource;

    @Bean
    public FlatFileItemReader<Customer> customerItemReader() {
        ...
    }

    @Bean
    public JdbcBatchItemWriter<Customer> customerItemWriter() {
        ...
    }

    @Bean
    public Step step1() {
        ...
    }

    @Bean
    public Job job() {
        ...
    }
}

DatabaseConfig.java

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

    @Bean(name = "secondaryDS")
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource getSeconadaryBatchDataSource() {
        ...
    }

    @Bean(name = "primaryDS")
    @Primary
    @ConfigurationProperties(prefix = "spring.hello.datasource")
    public DataSource getPrimaryBatchDataSource() {
        ...
    }

    @Bean(name = "primaryEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean primaryEntityManagerFactory(EntityManagerFactoryBuilder builder) {
        ...
    }
}

application.properties

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

请注意，我已经按照您的要求仅返回翻译的内容，没有包含额外的内容。如果您有任何问题或需要进一步的帮助，请随时提问。

英文:

I am developing Spring Boot v2.2.6.RELEASE + Spring Batch. In this example, I am looking to read csv file, load the data into mysql and spring batch metadata tables into the Postgres database.

But This gives me error:

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 &quot;batch_job_instance&quot; 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 &quot;batch_job_instance&quot; 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

JobConfig.java

@Configuration
public class JobConfig {
	@Autowired
	private StepBuilderFactory stepBuilderFactory;
	
	@Autowired
	private JobBuilderFactory jobBuilderFactory;
	
	@Qualifier(&quot;secondaryDS&quot;)
	@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(&quot;/data/customer.csv&quot;));
		
		DelimitedLineTokenizer tokenizer = new DelimitedLineTokenizer();
		tokenizer.setNames(new String[] {&quot;id&quot;, &quot;firstName&quot;, &quot;lastName&quot;, &quot;birthdate&quot;});
		
		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(&quot;INSERT INTO CUSTOMER VALUES (:id, :firstName, :lastName, :birthdate)&quot;);
		writer.setItemSqlParameterSourceProvider(new BeanPropertyItemSqlParameterSourceProvider&lt;&gt;());
		writer.afterPropertiesSet();
		
		return writer;
	}
	
	@Bean
	public Step step1() {
		return stepBuilderFactory.get(&quot;step1&quot;)
				.&lt;Customer, Customer&gt; chunk(1000)
				.reader(customerItemReader())
				.writer(customerItemWriter())
				.build();
	}
	
	@Bean
	public Job job() {
		return jobBuilderFactory.get(&quot;job&quot;)
				.start(step1())
				.build();
	}	
}

DatabaseConfig.java

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

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

	// For &quot;batchmetadata&quot; tables
	@Bean(name=&quot;primaryDS&quot;)
	@Primary
	@ConfigurationProperties(prefix=&quot;spring.hello.datasource&quot;)
	public DataSource getPrimaryBatchDataSource(){
		return DataSourceBuilder.create()
				.url(env.getProperty(&quot;spring.hello.datasource.url&quot;))
				.driverClassName(env.getProperty(&quot;spring.hello.datasource.driver-class-name&quot;))
				.username(env.getProperty(&quot;spring.hello.datasource.username&quot;))
				.password(env.getProperty(&quot;spring.hello.datasource.password&quot;))
				.build();
	}
	
	//Ref: https://stackoverflow.com/questions/28275448/multiple-data-source-and-schema-creation-in-spring-boot
	@Bean(name = &quot;primaryEntityManagerFactory&quot;)
	public LocalContainerEntityManagerFactoryBean primaryEntityManagerFactory( EntityManagerFactoryBuilder builder) {
	    Map&lt;String, Object&gt; properties = new HashMap&lt;&gt;();
	    properties.put(&quot;hibernate.hbm2ddl.auto&quot;, &quot;update&quot;);
	    return builder
	            .dataSource(getSeconadaryBatchDataSource())
	            .packages(&quot;com.example.model&quot;)
	            .persistenceUnit(&quot;default&quot;)
	            .properties(properties)
	            .build();
	}

	/*@Bean(name = &quot;secondaryEntityManagerFactory&quot;)
	@Primary
	public LocalContainerEntityManagerFactoryBean secondaryEntityManagerFactory(
	        EntityManagerFactoryBuilder builder) {
	    Map&lt;String, Object&gt; properties = new HashMap&lt;String, Object&gt;();
	    properties.put(&quot;hibernate.hbm2ddl.auto&quot;, &quot;create&quot;);
	    return builder
	            .dataSource(getPrimaryBatchDataSource())
	            .packages(&quot;com.example.model&quot;)
	            .persistenceUnit(&quot;default&quot;)
	            .properties(properties)
	            .build();
	}*/
}

application.properties

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

答案1

得分: 1

也许您正在使用类似于“BATCH_JOB_INSTANCE” 的引用创建表。
同时检查在 datasource.url 中是否指定了模式，因为您在查询中没有使用模式名称。

英文:

Maybe you are creating table using quote like "BATCH_JOB_INSTANCE".
Also check is schema is specified in datasource.url since you don't use schema name in query.

答案2

得分: 0

感谢Abinash的大力帮助。我只是简单地颠倒了主要和次要。它起作用了。

# 数据源 - Postgres - 主要
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://localhost:5432/program?currentSchema=BATCH
spring.datasource.username=postgres
spring.datasource.password=admin
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect
spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true

spring.jpa.hibernate.ddl-auto=create
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

# MySQL - 次要
spring.hello.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.hello.datasource.url=jdbc:mysql://localhost:3306/test
spring.hello.datasource.username=root
spring.hello.datasource.password=root

#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

此外，使用以下内容也起作用。

writer.setSql("INSERT INTO \"BATCH\".customer VALUES (:id, :firstName, :lastName, :birthdate)");
英文:

Thanks Abinash for great help. I simply reverse Primary and Secondary. It worked.

# Datasource - Postgres - Primary
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://localhost:5432/program?currentSchema=BATCH
spring.datasource.username=postgres
spring.datasource.password=admin
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect
spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true

spring.jpa.hibernate.ddl-auto=create
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

# MySQL- Secondary
spring.hello.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.hello.datasource.url=jdbc:mysql://localhost:3306/test
spring.hello.datasource.username=root
spring.hello.datasource.password=root

#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

Also, using below worked.

writer.setSql(&quot;INSERT INTO \&quot;BATCH\&quot;.customer VALUES (:id, :firstName, :lastName, :birthdate)&quot;);

huangapple
  • 本文由 发表于 2020年4月8日 02:18:37
  • 转载请务必保留本文链接：https://go.coder-hub.com/61086749.html
  • java
  • mysql
  • postgresql
  • spring-batch
  • spring-boot
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定