spring boot and batch throw Invalid object name 'BATCH_JOB_INSTANCE'

1
votes

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

2
springspring-bootspring-batch
Have you tried looking at this question Spring Batch Framework - Auto create Batch Table? – WoAiNii
Yep, but it´s doesn´t work and not take my datasource, I dont know that is wrong in my configuration – peter santos
Which database are you using? And are the batch tables created in the database? – Simon Martinelli
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
Yes @WoAiNii, it´s is the problem that I haven´t privileges , I am using SQL Server. – peter santos

2 Answers

1
votes

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.

1
votes

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.