DataSource issue in Spring batch and Spring Boot

Question

I'm developing a spring boot application integrated with spring batch. When I use standalone spring boot app with below datasource properties it works fine. But when i integrate spring batch to the application it creates error.

application.yml

spring:
  datasource:
    url: jdbc:oracle:thin:@//localhost:1521:orcl
    username: test
    password: Abcd

OracleConfiguration.java

@Primary 
@Configuration
@ConfigurationProperties(prefix = "spring.datasource")
public class OracleConfiguration {

    @NotNull
    private String username;

    @NotNull
    private String password;

    @NotNull
    private String url;

    public void setUsername(String username) {
        this.username = username;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public void setUrl(String url) {
        this.url = url;
    }


    @Bean
    public DataSource dataSource() throws SQLException {

        OracleDataSource dataSource = new OracleDataSource();
        dataSource.setUser(username);
        dataSource.setPassword(password);
        dataSource.setURL(url);
        dataSource.setImplicitCachingEnabled(true);
        dataSource.setFastConnectionFailoverEnabled(true);
        return dataSource;
    } 

DbServiceImpl.java

@Repository
public class DbServiceImpl extends JdbcDaoSupport{

    @Autowired
    DataSource dataSource;


    @PostConstruct
    private void initialize() throws SQLException{
        setDataSource(dataSource);


    public String getCode(String name, String code)
    {
        String sql = "";

        String baseCode = getJdbcTemplate().queryForObject(sql, new Object[] { name, code },String.class);
        return baseCode;

    }

JobConfiguration:

@Bean
public Job myJob() throws Exception {

    Job job =jobBuilderFactory.get("MyJob").start(moveToQueryStep()).build();
    return job;
}

I'm trying to call the getCode() in DbServiceImpl via moveToQueryStep() in the job configuartion. I could set the datasource in DbServiceImpl.java "initialize" method, but when spring batch tries to execute the tasklet step it throws the following error. I tried many solutions but nothing worked. Please help

java.lang.IllegalStateException: Failed to execute CommandLineRunner at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:803) [spring-boot-2.0.4.RELEASE.jar:2.0.4.RELEASE] at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:784) [spring-boot-2.0.4.RELEASE.jar:2.0.4.RELEASE] at org.springframework.boot.SpringApplication.run(SpringApplication.java:338) [spring-boot-2.0.4.RELEASE.jar:2.0.4.RELEASE] at org.springframework.boot.SpringApplication.run(SpringApplication.java:1258) [spring-boot-2.0.4.RELEASE.jar:2.0.4.RELEASE] at org.springframework.boot.SpringApplication.run(SpringApplication.java:1246) [spring-boot-2.0.4.RELEASE.jar:2.0.4.RELEASE] at com.equifax.d360.cb.Application.main(Application.java:20) [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 = ? order by JOB_INSTANCE_ID desc]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:234) ~[spring-jdbc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1402) ~[spring-jdbc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:620) ~[spring-jdbc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:657) ~[spring-jdbc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:688) ~[spring-jdbc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:700) ~[spring-jdbc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
at org.springframework.batch.core.repository.dao.JdbcJobInstanceDao.getJobInstances(JdbcJobInstanceDao.java:230) ~[spring-batch-core-4.0.1.RELEASE.jar:4.0.1.RELEASE]
at org.springframework.batch.core.explore.support.SimpleJobExplorer.getJobInstances(SimpleJobExplorer.java:173) ~[spring-batch-core-4.0.1.RELEASE.jar:4.0.1.RELEASE]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_172]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_172]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_172]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_172]
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343) ~[spring-aop-5.0.8.RELEASE.jar:5.0.8.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:197) ~[spring-aop-5.0.8.RELEASE.jar:5.0.8.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.0.8.RELEASE.jar:5.0.8.RELEASE]
at org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:127) ~[spring-batch-core-4.0.1.RELEASE.jar:4.0.1.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.8.RELEASE.jar:5.0.8.RELEASE]
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) ~[spring-aop-5.0.8.RELEASE.jar:5.0.8.RELEASE]
at com.sun.proxy.$Proxy51.getJobInstances(Unknown Source) ~[na:na]
at org.springframework.batch.core.JobParametersBuilder.getNextJobParameters(JobParametersBuilder.java:254) ~[spring-batch-core-4.0.1.RELEASE.jar:4.0.1.RELEASE]
at org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.execute(JobLauncherCommandLineRunner.java:162) ~[spring-boot-autoconfigure-2.0.4.RELEASE.jar:2.0.4.RELEASE]
at org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.executeLocalJobs(JobLauncherCommandLineRunner.java:179) ~[spring-boot-autoconfigure-2.0.4.RELEASE.jar:2.0.4.RELEASE]
at org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.launchJobFromProperties(JobLauncherCommandLineRunner.java:134) ~[spring-boot-autoconfigure-2.0.4.RELEASE.jar:2.0.4.RELEASE]
at org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.run(JobLauncherCommandLineRunner.java:128) ~[spring-boot-autoconfigure-2.0.4.RELEASE.jar:2.0.4.RELEASE]
at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:800) [spring-boot-2.0.4.RELEASE.jar:2.0.4.RELEASE]
... 5 common frames omitted

Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:774) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1111) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:4845) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1501) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:666) ~[spring-jdbc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:605) ~[spring-jdbc-5.0.8.RELEASE.jar:5.0.8.RELEASE]

Edit 2:

I could solve the issue by following the solution below

https://stackoverflow.com/a/49551527/9813861

Is there a better way other than providing an empty dataSource ?

Answer 1

You need to run spring batch SQL initialization scripts for Oracle. It comes pre-packaged with SQL initialization scripts for most popular database platforms. Spring Boot can detect your database type and execute those scripts on startup. If you use an embedded database, this happens by default. But as you are using Oracle you need to add following in application.properties file:

spring.batch.initialize-schema=always

This is for application.properties file. You need to convert it to correct format for application.yml

Answer 2

batch.jdbc.driver=oracle.jdbc.OracleDriver
batch.jdbc.url=jdbc:oracle:thin:@oracle:1521:xe
batch.jdbc.user=spring
batch.jdbc.password=spring
batch.jdbc.testWhileIdle=false
batch.jdbc.validationQuery=
batch.drop.script=classpath:/org/springframework/batch/core/schema-drop-oracle10g.sql
batch.schema.script=classpath:/org/springframework/batch/core/schema-oracle10g.sql
batch.business.schema.script=business-schema-oracle10g.sql
batch.database.incrementer.class=org.springframework.jdbc.support.incrementer.OracleSequenceMaxValueIncrementer
batch.database.incrementer.parent=sequenceIncrementerParent
batch.lob.handler.class=org.springframework.jdbc.support.lob.OracleLobHandler
batch.grid.size=2
batch.jdbc.pool.size=6
batch.verify.cursor.position=true
batch.isolationlevel=ISOLATION_SERIALIZABLE
batch.table.prefix=BATCH_

Answer 3

try below from example

https://github.com/geekyjaat/spring-batch/blob/master/src/main/resources/import.sql

package com.barley.batch.config;
    
import java.sql.ResultSet;

import javax.sql.DataSource;

import com.barley.batch.listener.JobCompletionNotificationListener;
import com.barley.batch.model.RecordSO;
import com.barley.batch.model.WriterSO;
import com.barley.batch.processor.RecordProcessor;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.batch.core.Job;
import org.springframework.batch.core.Step;
import org.springframework.batch.core.configuration.annotation.EnableBatchProcessing;
import org.springframework.batch.core.configuration.annotation.JobBuilderFactory;
import org.springframework.batch.core.configuration.annotation.StepBuilderFactory;
import org.springframework.batch.core.launch.support.RunIdIncrementer;
import org.springframework.batch.item.ItemProcessor;
import org.springframework.batch.item.ItemReader;
import org.springframework.batch.item.database.BeanPropertyItemSqlParameterSourceProvider;
import org.springframework.batch.item.database.ItemPreparedStatementSetter;
import org.springframework.batch.item.database.JdbcBatchItemWriter;
import org.springframework.batch.item.database.builder.JdbcBatchItemWriterBuilder;
import org.springframework.batch.item.database.builder.JdbcCursorItemReaderBuilder;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
@EnableBatchProcessing
public class BatchConfiguration {

   

 private static final Logger LOGGER = LoggerFactory.getLogger(BatchConfiguration.class);

            @Autowired
            private JobBuilderFactory jobBuilderFactory;
        
            @Autowired
            private StepBuilderFactory stepBuilderFactory;
        
            @Autowired
            private DataSource dataSource;
        
            @Bean
            public ItemReader<RecordSO> reader() {
                return new JdbcCursorItemReaderBuilder<RecordSO>().name("the-reader")
                        .sql("select id, firstName, lastname, random_num from reader").dataSource(dataSource)
                        .rowMapper((ResultSet resultSet, int rowNum) -> {
                            if (!(resultSet.isAfterLast()) && !(resultSet.isBeforeFirst())) {
                                RecordSO recordSO = new RecordSO();
                                recordSO.setFirstName(resultSet.getString("firstName"));
                                recordSO.setLastName(resultSet.getString("lastname"));
                                recordSO.setId(resultSet.getInt("Id"));
                                recordSO.setRandomNum(resultSet.getString("random_num"));
        
                                LOGGER.info("RowMapper record : {}", recordSO);
                                return recordSO;
                            } else {
                                LOGGER.info("Returning null from rowMapper");
                                return null;
                            }
                        }).build();
            }
        
            @Bean
            public ItemProcessor<RecordSO, WriterSO> processor() {
                return new RecordProcessor();
            }
        
            @Bean
            public JdbcBatchItemWriter<WriterSO> writer(DataSource dataSource, ItemPreparedStatementSetter<WriterSO> setter) {
                return new JdbcBatchItemWriterBuilder<WriterSO>()
                        .itemSqlParameterSourceProvider(new BeanPropertyItemSqlParameterSourceProvider<>())
                        .itemPreparedStatementSetter(setter)
                        .sql("insert into writer (id, full_name, random_num) values (?,?,?)").dataSource(dataSource).build();
            }
        
            @Bean
            public ItemPreparedStatementSetter<WriterSO> setter() {
                return (item, ps) -> {
                    ps.setLong(1, item.getId());
                    ps.setString(2, item.getFullName());
                    ps.setString(3, item.getRandomNum());
                };
            }
        
            @Bean
            public Job importUserJob(JobCompletionNotificationListener listener, Step step1) {
                return jobBuilderFactory.get("importUserJob").incrementer(new RunIdIncrementer()).listener(listener).flow(step1)
                        .end().build();
            }
        
            @Bean
            public Step step1(JdbcBatchItemWriter<WriterSO> writer, ItemReader<RecordSO> reader) {
                return stepBuilderFactory.get("step1").<RecordSO, WriterSO>chunk(5).reader(reader).processor(processor())
                        .writer(writer).build();
            }
        }
    
    -------
    
    package com.barley.batch.listener;
    
    import java.util.List;
    
    import com.barley.batch.model.WriterSO;
    
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.batch.core.BatchStatus;
    import org.springframework.batch.core.JobExecution;
    import org.springframework.batch.core.listener.JobExecutionListenerSupport;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.stereotype.Component;
    
    @Component
    public class JobCompletionNotificationListener extends JobExecutionListenerSupport {
    
        private static final Logger log = LoggerFactory.getLogger(JobCompletionNotificationListener.class);
        private final JdbcTemplate jdbcTemplate;
    
        public JobCompletionNotificationListener(JdbcTemplate jdbcTemplate) {
            this.jdbcTemplate = jdbcTemplate;
        }
    
        @Override
        public void afterJob(JobExecution jobExecution) {
            if (jobExecution.getStatus() == BatchStatus.COMPLETED) {
                log.info("!!! JOB FINISHED! Time to verify the results");
    
                List<WriterSO> results = jdbcTemplate.query("SELECT id, full_name, random_num FROM writer", (rs, row) -> {
                    WriterSO writerSO = new WriterSO();
                    writerSO.setId(rs.getLong("id"));
                    writerSO.setFullName(rs.getString("full_name"));
                    writerSO.setRandomNum(rs.getString("random_num"));
                    return writerSO;
                });
    
                for (WriterSO writerSO : results) {
                    log.info("Found <" + writerSO + "> in the database.");
                }
            }
        }
    }

----------------

package com.barley.batch.model;

public class RecordSO {

    private long id;
    private String firstName;
    private String lastName;
    private String randomNum;

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getRandomNum() {
        return randomNum;
    }

    public void setRandomNum(String randomNum) {
        this.randomNum = randomNum;
    }

    @Override
    public String toString() {
        return "RecordSO{" +
                "id=" + id +
                ", firstName='" + firstName + '\'' +
                ", lastName='" + lastName + '\'' +
                ", randomNum='" + randomNum + '\'' +
                '}';
    }
}

--------------

package com.barley.batch.model;

public class WriterSO {

    private long id;
    private String fullName;
    private String randomNum;

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getFullName() {
        return fullName;
    }

    public void setFullName(String fullName) {
        this.fullName = fullName;
    }

    public String getRandomNum() {
        return randomNum;
    }

    public void setRandomNum(String randomNum) {
        this.randomNum = randomNum;
    }

    @Override
    public String toString() {
        return "WriterSO{" +
                "id=" + id +
                ", fullName='" + fullName + '\'' +
                ", randomNum='" + randomNum + '\'' +
                '}';
    }
}

-----------

package com.barley.batch.processor;

import com.barley.batch.model.RecordSO;
import com.barley.batch.model.WriterSO;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.batch.item.ItemProcessor;

public class RecordProcessor implements ItemProcessor<RecordSO, WriterSO> {

    private static final Logger LOGGER = LoggerFactory.getLogger(RecordProcessor.class);

    @Override
    public WriterSO process(final RecordSO item) throws Exception {
        LOGGER.info("Processing Record: {}", item);
        WriterSO writerSo = new WriterSO();
        writerSo.setId(item.getId());
        writerSo.setFullName(item.getFirstName() + " " + item.getLastName());
        writerSo.setRandomNum(String.valueOf(Math.random()).substring(3, 8));
        LOGGER.info("Processed Writer: {}", writerSo);
        return writerSo;
    }
}

---------

package com.barley.batch;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class Application {
    public static void main(String[] args) throws Exception {
        SpringApplication.run(Application.class, args);
    }
}

-------------------

spring.batch.initialize-schema=ALWAYS
spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/batch
spring.datasource.username=root
spring.datasource.password=root
spring.jpa.database-platform=org.hibernate.dialect.MySQLDialect
spring.jpa.show-sql=false
spring.jpa.hibernate.ddl-auto=create-drop

---------------------------

DROP TABLE IF EXISTS reader;
DROP TABLE IF EXISTS writer;

CREATE TABLE `reader` (`id` INT  NOT NULL AUTO_INCREMENT,`firstName`  VARCHAR(20) NULL,`lastName`   VARCHAR(20) NULL,`random_num` VARCHAR(20) NULL, PRIMARY KEY (`id`));

CREATE TABLE `writer` (`id` INT  NOT NULL AUTO_INCREMENT,`full_name`  VARCHAR(40) NULL,`random_num` VARCHAR(20) NULL, PRIMARY KEY (`id`));

INSERT INTO `reader` (`firstName`, `lastName`, `random_num`) VALUES ('abc', 'def', '1');
INSERT INTO `reader` (`firstName`, `lastName`, `random_num`) VALUES ('def', 'zhu', '2');
INSERT INTO `reader` (`firstName`, `lastName`, `random_num`) VALUES ('dummy', 'name', '3');
INSERT INTO `reader` (`firstName`, `lastName`, `random_num`) VALUES ('non', 'pay', '4');
INSERT INTO `reader` (`firstName`, `lastName`, `random_num`) VALUES ('spring', 'batch', '5');