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? — WoAiNii
– WoAiNii, Commented Apr 25, 2020 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
– peter santos, Commented Apr 25, 2020 at 19:56
Which database are you using? And are the batch tables created in the database? — Simon Martinelli
– Simon Martinelli, Commented Apr 26, 2020 at 6: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 or here - how to avoid spring batch persistence of metadata in db — WoAiNii
– WoAiNii, Commented Apr 26, 2020 at 11:29
Yes @WoAiNii, it´s is the problem that I haven´t privileges , I am using SQL Server. — peter santos
– peter santos, Commented Apr 27, 2020 at 17:46

Mahmoud Ben Hassine · Accepted Answer · 2020-04-26 12:47:01Z

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, 2020 at 12:47

Mahmoud Ben Hassine

32.1k5 gold badges38 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

peter santos Over a year ago

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 Over a year ago

Thanks @MahmoudBenHassine, I did as suggested other post

peter santos · Accepted Answer · 2020-04-27 21:48:38Z

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.

Collectives™ on Stack Overflow

spring boot and batch throw Invalid object name 'BATCH_JOB_INSTANCE'

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related