Spring Batch - Unable to save the records into Postgres database

Question

I am using the same code: na] Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist - Spring Batch 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();
    }
}

from the log, it seems like either batch.employee table doesn't exist or the userId which is used in the application doesn't have access to the table batch.employee. Please share the database user used in the application and provide access to the user. — dassum, Apr 07 '20 at 17:31

Eklavya · Accepted Answer · 2020-04-07T18:44:59.630

1

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)";

edited Apr 07 '20 at 18:44

answered Apr 07 '20 at 18:39

Eklavya

17,618
4
28
57

@JeffCook gives you same error ? Like .. "batch.employee" does not exist – Eklavya Apr 07 '20 at 18:51
You're genius man. You really help me a lot. Its working. I am not sure "BATCH" has come when I created table from Postgres GUI? – Jeff Cook Apr 07 '20 at 18:53
I think error handling us not correct. Hence lot of issue. Could you please guide me here as well https://stackoverflow.com/questions/61086749/spring-batch-unable-to-create-metadata-tables-on-postgres-and-load-actual-data ? – Jeff Cook Apr 07 '20 at 18:54
@JeffCook Okay. Let me try – Eklavya Apr 07 '20 at 18:56
Could you please guide me on how can we automatically created database into taget DB when simply using Spring JDBC and not JPA in batch ? – Jeff Cook Apr 07 '20 at 19:02
@JeffCook You have to use migration tool to auto run query. You can generate Java class to SQL then add in a migration file. – Eklavya Apr 07 '20 at 19:21

Spring Batch - Unable to save the records into Postgres database

1 Answers1

Linked