spring boot and batch throw Invalid object name 'BATCH_JOB_INSTANCE'

Question

I m stucked with spring boot in my app , it's dont want to run schema nor tables , only connect to my database SQL Server and query information, so I disabled this line in file application.properties


    spring.batch.initialize-schema=never
    spring.datasource.initialization-mode=never

but doesn´t work , I dont know what´s wrong, already configure my datasource, thanks in advance , I hope someone can help me, sorry my english is poor.

I am using spring boot 2.2.6, my code of DataSourceConfiguration


    .....
    @EnableAutoConfiguration(exclude={DataSourceAutoConfiguration.class})
    public class DataSourceConfiguration {


        @Bean
        @Primary
        @Qualifier("jobsDataSource")
        public DataSource hsqldbDataSource() throws SQLException {
            final SimpleDriverDataSource dataSource = new SimpleDriverDataSource();
            dataSource.setDriver(new org.hsqldb.jdbcDriver());
            dataSource.setUrl("jdbc:hsqldb:mem:mydb");
            dataSource.setUsername("sa");
            dataSource.setPassword("");
            return dataSource;
        }

        @Bean
        public JdbcTemplate jdbcTemplate(final DataSource dataSource) {
            return new JdbcTemplate(dataSource);
        }

        @Bean
        @Qualifier("sqlserverDataSource")
        public DataSource sqlserverDataSource() throws SQLException {
            final SimpleDriverDataSource dataSource = new SimpleDriverDataSource();
            dataSource.setDriver(new net.sourceforge.jtds.jdbc.Driver());
            dataSource.setUrl("jdbc:jtds:sqlserver://x.y.z.t:1433/mydb");
            dataSource.setUsername("usersqlserver");
            dataSource.setPassword("xxxxxx");
            return dataSource;
        }

        @Bean
        public JdbcTemplate sqlJdbcTemplate(@Qualifier("sqlserverDataSource") final DataSource dataSource) {
            return new JdbcTemplate(dataSource);
        }

        public PlatformTransactionManager transactionManager(){
            return new ResourcelessTransactionManager();
        }


        @Bean
        public BatchConfigurer configurer(@Qualifier("sqlserverDataSource") DataSource dataSource){
            return new DefaultBatchConfigurer(){
                @Override
                protected JobRepository createJobRepository() throws Exception {
                    JobRepositoryFactoryBean factory = new JobRepositoryFactoryBean();
                    factory.setDataSource(dataSource);
                    factory.setTransactionManager(transactionManager());

                    return factory.getObject();
    //               MapJobRepositoryFactoryBean mapJobRepositoryFactoryBean = new MapJobRepositoryFactoryBean(transactionManager());
    //                  mapJobRepositoryFactoryBean.setTransactionManager(transactionManager());
    //                  return mapJobRepositoryFactoryBean.getObject();
                }

                @Override
                protected JobLauncher createJobLauncher() throws Exception {
                    SimpleJobLauncher jobLauncher = new SimpleJobLauncher();
                    jobLauncher.setJobRepository(createJobRepository());
                    jobLauncher.setTaskExecutor(new SimpleAsyncTaskExecutor());
                    jobLauncher.afterPropertiesSet();
                    return jobLauncher;
                }
            };
        }
    }

Here is the error


    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 java.sql.SQLException: Invalid object name 'BATCH_JOB_INSTANCE'.
        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.query(JdbcTemplate.java:669) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:700) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:712) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:768) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.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.isJobInstanceExists(SimpleJobRepository.java:91) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_161]
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_161]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_161]
        at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_161]
        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.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:366) ~[spring-tx-5.2.5.RELEASE.jar:5.2.5.RELEASE]
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118) ~[spring-tx-5.2.5.RELEASE.jar:5.2.5.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.$Proxy54.isJobInstanceExists(Unknown Source) ~[na:na]
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_161]
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_161]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_161]
        at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_161]
        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.$Proxy46.isJobInstanceExists(Unknown Source) ~[na:na]
        at org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.getNextJobParameters(JobLauncherCommandLineRunner.java:199) ~[spring-boot-autoconfigure-2.2.6.RELEASE.jar:2.2.6.RELEASE]
        at org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.execute(JobLauncherCommandLineRunner.java:191) ~[spring-boot-autoconfigure-2.2.6.RELEASE.jar:2.2.6.RELEASE]
        at org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.executeLocalJobs(JobLauncherCommandLineRunner.java:166) ~[spring-boot-autoconfigure-2.2.6.RELEASE.jar:2.2.6.RELEASE]
        at org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.launchJobFromProperties(JobLauncherCommandLineRunner.java:153) ~[spring-boot-autoconfigure-2.2.6.RELEASE.jar:2.2.6.RELEASE]
        at org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.run(JobLauncherCommandLineRunner.java:148) ~[spring-boot-autoconfigure-2.2.6.RELEASE.jar:2.2.6.RELEASE]
        at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:784) [spring-boot-2.2.6.RELEASE.jar:2.2.6.RELEASE]
        ... 10 common frames omitted
    Caused by: java.sql.SQLException: Invalid object name 'BATCH_JOB_INSTANCE'.
        at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372) ~[jtds-1.3.1.jar:1.3.1]
        at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988) ~[jtds-1.3.1.jar:1.3.1]
        at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421) ~[jtds-1.3.1.jar:1.3.1]

Thanks in advance

Have you tried looking at this question [Spring Batch Framework - Auto create Batch Table](https://stackoverflow.com/questions/33249942/spring-batch-framework-auto-create-batch-table)? — WoAiNii, Apr 25 '20 at 18:06
Yep, but it´s doesn´t work and not take my datasource, I dont know that is wrong in my configuration — peter santos, Apr 25 '20 at 19:56
Which database are you using? And are the batch tables created in the database? — Simon Martinelli, Apr 26 '20 at 06:28
I think he needs to run spring batch without using db to store jobs status/persistance/etc., as described [here - spring batch without persisting metadata to database](https://stackoverflow.com/questions/25077549/spring-batch-without-persisting-metadata-to-database) or [here - how to avoid spring batch persistence of metadata in db](https://stackoverflow.com/questions/48937453/how-to-avoid-spring-batch-persistence-of-metadata-in-db) — WoAiNii, Apr 26 '20 at 11:29
Yes @WoAiNii, it´s is the problem that I haven´t privileges , I am using SQL Server. — peter santos, Apr 27 '20 at 17:46

score 2 · Accepted Answer · answered Apr 26 '20 at 12:47

2

Spring Batch requires a few tables in your database to run. So you need to create those tables manually upfront or tell Spring Boot to do it for you.

Since you told Spring Boot to not create those tables for you by setting spring.batch.initialize-schema=never, then you need to create them yourself in your target datasource.

So in your case, you need to run the DDL script to create Spring Batch tables on your SQL server instance before running your job.

answered Apr 26 '20 at 12:47

Mahmoud Ben Hassine

28,519
3
32
50

Thanks @MahmoudBenHassine, only that my problem is the layer dao throwing NullPointerException from ItemProcessor that is in CompositeProcessor, it is weird because of from Junit test is ok , thanks again friend. – peter santos Apr 28 '20 at 14:46
Thanks @MahmoudBenHassine, I did as suggested other post – peter santos Apr 28 '20 at 16:25

peter santos · Answer 2 · 2020-04-27T21:48:38.843

Alright,I have had removing this line and I did as you suggested and it´s worked, only that dao is NullpointerException, it´s interface and is used in the Itemprocessor:

@Bean
    public BatchConfigurer configurer(@Qualifier("sqlserverDataSource") DataSource dataSource){
        return new DefaultBatchConfigurer(){
//            @Override
//            protected JobRepository createJobRepository() throws Exception {
//                JobRepositoryFactoryBean factory = new JobRepositoryFactoryBean();
//                factory.setDataSource(dataSource);
//                factory.setTransactionManager(transactionManager());
//               // factory.setIsolationLevelForCreate("ISOLATION_READ_COMMITTED");
//                //factory.setTablePrefix("dbo.tmpAccWebServ");
//                //factory.setDatabaseType("SQLSERVER");
//                //factory.setMaxVarCharLength(1000);
//                return factory.getObject();
////                 MapJobRepositoryFactoryBean mapJobRepositoryFactoryBean = new MapJobRepositoryFactoryBean(transactionManager());
////                    mapJobRepositoryFactoryBean.setTransactionManager(transactionManager());
////                    return mapJobRepositoryFactoryBean.getObject();
//            }
//            
//            @Override
//            protected JobLauncher createJobLauncher() throws Exception {
//                SimpleJobLauncher jobLauncher = new SimpleJobLauncher();
//                jobLauncher.setJobRepository(createJobRepository());
//                jobLauncher.setTaskExecutor(new SimpleAsyncTaskExecutor());
//                jobLauncher.afterPropertiesSet();
//                return jobLauncher;
//            }

            @Override
            public void setDataSource(DataSource dataSource) {
                // override to do not set datasource even if a datasource exist.
                // initialize will use a Map based JobRepository (instead of database)
            } //
        }; 
    }//

This is the code of CustomerItemProcessor

public class CustomerItemProcessor implements ItemProcessor<beangenerico,ThreadLocal<CopyOnWriteArrayList<beanCustomer>>> {
    @Autowired
    private CustomerDAO customerDAO;

    private ThreadLocal<CopyOnWriteArrayList<beanCustomer>> listbean = new ThreadLocal<CopyOnWriteArrayList<beanCustomer>>();

    public ThreadLocal<CopyOnWriteArrayList<beanCustomer>> process(beangenerico rangos) throws Exception {
        System.out.println("entro a customitemprocessor");

            listbean.set(new CopyOnWriteArrayList<beanCustomer>());

            System.out.println("rangos:"+rangos.getIni()+"-"+rangos.getFin()); 
            listbean = customerDAO.getAccAgentes(rangos);


            if(listbean != null) {
                //customer.setId(currentCustomer.getId());
                return listbean;
            } else {
                return null;
            }

    }


    @Autowired
    public void setCustomerDAO(CustomerDAOImpl customerDAO) {
        this.customerDAO = customerDAO;
    }
}

This is the code of interface

public interface CustomerDAO {
    ThreadLocal<CopyOnWriteArrayList<beanCustomer>> getAccAgentes(beangenerico bean);
}

This is the code if CustomerDAOImpl

@Repository
@Component
public class CustomerDAOImpl  implements CustomerDAO{ 

private String SP_SQL = "{call mysp(?, ?)}";
@Autowired
@Qualifier("sqlserverDataSource")
DataSource dataSource;

@Autowired
JdbcTemplate jdbcTemplate;

public  ThreadLocal<CopyOnWriteArrayList<beanCustomer>> getAccAgentes(beangenerico bean) {  
...
//query resulset
}

public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }
}

Thanks in advance for your help.

spring boot and batch throw Invalid object name 'BATCH_JOB_INSTANCE'

2 Answers2