英文:
Spring Batch - Unable to save the records into Postgres database
问题
I am using the same code: https://stackoverflow.com/questions/61081682/na-caused-by-java-sql-sqlsyntaxerrorexception-ora-00942-table-or-view-does-n and unable to save the records into DB.
我正在使用相同的代码:https://stackoverflow.com/questions/61081682/na-caused-by-java-sql-sqlsyntaxerrorexception-ora-00942-table-or-view-does-n,并且无法将记录保存到数据库。
I already have the table created
我已经创建了表格
CREATE TABLE "BATCH".employee (
rowid_object int4 NOT NULL,
status varchar NULL
)
WITH (
OIDS=FALSE
) ;
-- Permissions
ALTER TABLE "BATCH".employee OWNER TO postgres;
生命周期状态写入器(LifeCycleStatusWriter.java)
@Component
public class EmployeeWriter implements ItemWriter
@Autowired
@Qualifier(value="postgresDS")
private DataSource dataSourcePostgres;
private NamedParameterJdbcTemplate namedParamJdbcTemplate;
@PostConstruct
private void postConstruct() {
namedParamJdbcTemplate = new NamedParameterJdbcTemplate(this.dataSourcePostgres);
}
String sql = "INSERT INTO BATCH.employee(id, status) VALUES (:rowid_object, :status)";
@SuppressWarnings("unchecked")
@Override
public void write(List<? extends Employee> items) throws Exception {
List<Map<String, Object>> batchValues = new ArrayList<>(items.size());
for (LifeCycleStatus l : items) {
System.out.println("id : "+l.getRowIdObject());
System.out.println("status : "+l.getLifeCycleStatCd());
batchValues.add(new MapSqlParameterSource("id", l.getRowIdObject())
.addValue("status", l.getLifeCycleStatCd()).getValues());
}
int[] updateCounts = namedParamJdbcTemplate.batchUpdate(sql, batchValues.toArray(new Map[items.size()]));
System.out.println(updateCounts);
}
}
写入器(Writer)
@Configuration
public class EmployeeBatchConfig {
private static final String SQL = "SQL HERE";
@Autowired
@Qualifier(value="oracleDS")
private DataSource dataSourceOracle;
@Autowired
@Qualifier(value="postgresDS")
private DataSource dataSourcePostgres;
@Bean(destroyMethod = "")
@StepScope
public JdbcCursorItemReader<Employee> EmployeeReader() throws Exception {
JdbcCursorItemReader<Employee> reader = new JdbcCursorItemReader<>();
reader.setDataSource(this.dataSourceOracle);
reader.setSql(SQL);
reader.setRowMapper(new EmployeeRowMapper());
reader.afterPropertiesSet();
return reader;
}
@Bean
public EmployeeWriter getEmployeeWriter() {
return new EmployeeWriter();
}
}
错误信息(Error)
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [INSERT INTO program.BATCH.employee(rowid_object, status) VALUES (?, ?)]; nested exception is org.postgresql.util.PSQLException: ERROR: relation "batch.employee" does not exist
Position: 13
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:235) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1443) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:647) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:936) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.batchUpdate(NamedParameterJdbcTemplate.java:366) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.batchUpdate(NamedParameterJdbcTemplate.java:354) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
at com.mastercard.customer.data.management.writer.LifeCycleStatusWriter.write(LifeCycleStatusWriter.java:48) ~[classes/:na]
at org.springframework.batch.core.step.item.SimpleChunkProcessor.writeItems(SimpleChunkProcessor.java:193) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
at org.springframework.batch.core.step.item.SimpleChunkProcessor.doWrite(SimpleChunkProcessor.java:159) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
at org.springframework.batch.core.step.item.SimpleChunkProcessor.write(SimpleChunkProcessor.java:294) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
at org.springframework.batch.core.step.item.SimpleChunkProcessor.process(SimpleChunkProcessor.java:217) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
at org.springframework.batch.core.step.item.ChunkOrientedTasklet.execute(ChunkOrientedTasklet.java:77) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:407) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:331) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140) ~[spring-tx-5.2.5.RELEASE.jar:5.2.5.RELEASE]
at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:273) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:82) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:375) ~[spring-batch-infrastructure-4.2.1.RELEASE.jar:4.2.1.RELEASE]
at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215) ~[spring-batch-infrastructure-4.2.1.RELEASE.jar:4.2.1.RELEASE]
at org
英文:
I am using the same code: https://stackoverflow.com/questions/61081682/na-caused-by-java-sql-sqlsyntaxerrorexception-ora-00942-table-or-view-does-n and unable to save the records into DB.
I already have the table created
CREATE TABLE "BATCH".employee (
rowid_object int4 NOT NULL,
status varchar NULL
)
WITH (
OIDS=FALSE
) ;
-- Permissions
ALTER TABLE "BATCH".employee OWNER TO postgres;
LifeCycleStatusWriter.java
@Component
public class EmployeeWriter implements ItemWriter<Employee> {
@Autowired
@Qualifier(value="postgresDS")
private DataSource dataSourcePostgres;
private NamedParameterJdbcTemplate namedParamJdbcTemplate;
@PostConstruct
private void postConstruct() {
namedParamJdbcTemplate = new NamedParameterJdbcTemplate(this.dataSourcePostgres);
}
String sql = "INSERT INTO BATCH.employee(id, status) VALUES (:rowid_object, :status)";
@SuppressWarnings("unchecked")
@Override
public void write(List<? extends Employee> items) throws Exception {
List<Map<String, Object>> batchValues = new ArrayList<>(items.size());
for (LifeCycleStatus l : items) {
System.out.println("id : "+l.getRowIdObject());
System.out.println("status : "+l.getLifeCycleStatCd());
batchValues.add(new MapSqlParameterSource("id", l.getRowIdObject())
.addValue("status", l.getLifeCycleStatCd()).getValues());
}
int[] updateCounts = namedParamJdbcTemplate.batchUpdate(sql, batchValues.toArray(new Map[items.size()]));
System.out.println(updateCounts);
}
}
Writer
@Configuration
public class EmployeeBatchConfig {
private static final String SQL = "SQL HERE";
@Autowired
@Qualifier(value="oracleDS")
private DataSource dataSourceOracle;
@Autowired
@Qualifier(value="postgresDS")
private DataSource dataSourcePostgres;
@Bean(destroyMethod = "")
@StepScope
public JdbcCursorItemReader<Employee> EmployeeReader() throws Exception {
JdbcCursorItemReader<Employee> reader = new JdbcCursorItemReader<>();
reader.setDataSource(this.dataSourceOracle);
reader.setSql(SQL);
reader.setRowMapper(new EmployeeRowMapper());
reader.afterPropertiesSet();
return reader;
}
@Bean
public EmployeeWriter getEmployeeWriter() {
return new EmployeeWriter();
}
}
Error:
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [INSERT INTO program.BATCH.employee(rowid_object, status) VALUES (?, ?)]; nested exception is org.postgresql.util.PSQLException: ERROR: relation "batch.employee" does not exist
Position: 13
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:235) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1443) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:647) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:936) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.batchUpdate(NamedParameterJdbcTemplate.java:366) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.batchUpdate(NamedParameterJdbcTemplate.java:354) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
at com.mastercard.customer.data.management.writer.LifeCycleStatusWriter.write(LifeCycleStatusWriter.java:48) ~[classes/:na]
at org.springframework.batch.core.step.item.SimpleChunkProcessor.writeItems(SimpleChunkProcessor.java:193) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
at org.springframework.batch.core.step.item.SimpleChunkProcessor.doWrite(SimpleChunkProcessor.java:159) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
at org.springframework.batch.core.step.item.SimpleChunkProcessor.write(SimpleChunkProcessor.java:294) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
at org.springframework.batch.core.step.item.SimpleChunkProcessor.process(SimpleChunkProcessor.java:217) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
at org.springframework.batch.core.step.item.ChunkOrientedTasklet.execute(ChunkOrientedTasklet.java:77) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:407) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:331) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140) ~[spring-tx-5.2.5.RELEASE.jar:5.2.5.RELEASE]
at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:273) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:82) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:375) ~[spring-batch-infrastructure-4.2.1.RELEASE.jar:4.2.1.RELEASE]
at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215) ~[spring-batch-infrastructure-4.2.1.RELEASE.jar:4.2.1.RELEASE]
at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:145) ~[spring-batch-infrastructure-4.2.1.RELEASE.jar:4.2.1.RELEASE]
at org.springframework.batch.core.step.tasklet.TaskletStep.doExecute(TaskletStep.java:258) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:208) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:148) [spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
at org.springframework.batch.core.job.AbstractJob.handleStep(AbstractJob.java:410) [spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
at org.springframework.batch.core.job.SimpleJob.doExecute(SimpleJob.java:136) [spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:319) [spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:147) [spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:50) [spring-core-5.2.5.RELEASE.jar:5.2.5.RELEASE]
at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:140) [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.5.RELEASE.jar:5.2.5.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198) [spring-aop-5.2.5.RELEASE.jar:5.2.5.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) [spring-aop-5.2.5.RELEASE.jar:5.2.5.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.5.RELEASE.jar:5.2.5.RELEASE]
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) [spring-aop-5.2.5.RELEASE.jar:5.2.5.RELEASE]
at com.sun.proxy.$Proxy72.run(Unknown Source) [na:na]
at com.mastercard.customer.data.management.CustomerProfileStagingBatchApplication.run(CustomerProfileStagingBatchApplication.java:50) [classes/:na]
at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:784) [spring-boot-2.2.6.RELEASE.jar:2.2.6.RELEASE]
at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:768) [spring-boot-2.2.6.RELEASE.jar:2.2.6.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:322) [spring-boot-2.2.6.RELEASE.jar:2.2.6.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1226) [spring-boot-2.2.6.RELEASE.jar:2.2.6.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1215) [spring-boot-2.2.6.RELEASE.jar:2.2.6.RELEASE]
at com.mastercard.customer.data.management.CustomerProfileStagingBatchApplication.main(CustomerProfileStagingBatchApplication.java:38) [classes/:na]
Caused by: org.postgresql.util.PSQLException: ERROR: relation "batch.employee" does not exist
Position: 13
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2578) ~[postgresql-42.2.11.jar:42.2.11]
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2313) ~[postgresql-42.2.11.jar:42.2.11]
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:331) ~[postgresql-42.2.11.jar:42.2.11]
at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:832) ~[postgresql-42.2.11.jar:42.2.11]
at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:874) ~[postgresql-42.2.11.jar:42.2.11]
at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1569) ~[postgresql-42.2.11.jar:42.2.11]
at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:128) ~[HikariCP-3.4.2.jar:na]
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeBatch(HikariProxyPreparedStatement.java) ~[HikariCP-3.4.2.jar:na]
at org.springframework.jdbc.core.JdbcTemplate.lambda$batchUpdate$2(JdbcTemplate.java:950) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
... 45 common frames omitted
config details
# ORACLE DATASOURCE - Primary
spring.datasource.url=jdbc:oracle:thin:@/****:1527/test
spring.datasource.username=***
spring.datasource.password=****
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
# PostgreSQL DB - "Secondary"
postgres.datasource.url=jdbc:postgresql://localhost:5432/test?currentSchema=BATCH
postgres.datasource.username=****
postgres.datasource.password=****
postgres.datasource.driver-class-name=org.postgresql.Driver
#By default, Spring runs all the job as soon as it has started its context.
spring.batch.job.enabled=false
# Chunk Size to save data
spring.chunk.size=200
spring.batch.initialize-schema=always
Database config
@Configuration
public class DatabaseConfig {
@Autowired
private Environment env;
@Bean(name = "oracleDS")
public DataSource batchDataSource() {
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();
}
// All metadata tables are present here
@Primary
@Bean(name = "postgresDS")
public DataSource mysqlBatchDataSource() {
return DataSourceBuilder.create().url(env.getProperty("postgres.datasource.url"))
.driverClassName(env.getProperty("postgres.datasource.driver-class-name"))
.username(env.getProperty("postgres.datasource.username"))
.password(env.getProperty("postgres.datasource.password")).build();
}
}
答案1
得分: 1
你使用双引号("BATCH".employee
)创建了你的表的模式,现在这些名称是区分大小写的。"BATCH".employee
和 BATCH.employee
是两个不同的名称。尝试使用以下代码:
String sql = "INSERT INTO \"BATCH\".employee(rowid_object, status) VALUES (:rowid_object, :status)";
英文:
You created your tables's schema with double quotes ("BATCH".employee
), and now the names are case sensitive. Ref
"BATCH".employee
and BATCH.employee
are two different names.
Try to use this
String sql = "INSERT INTO \"BATCH\".employee(rowid_object, status) VALUES (:rowid_object, :status)";
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论