Spring Batch“无效的对象名称BATCH_JOB_INSTANCE" [英] Spring Batch "Invalid object name BATCH_JOB_INSTANCE"

问题描述

我已经创建了一个春季批处理来查询Azure SQL Server数据库并将数据写入CSV文件.我没有数据库的create权限.运行批处理时出现此错误Invalid Object name BATCH_JOB_INSTANCE.我不希望在主数据库中创建spring batch meta-data表.如果我可以将它们放在另一个本地或内存数据库(如h2db)中，将很有帮助. 我也已经添加了spring-batch-initialize-schema=never，大多数类似问题的答案都是这种情况，但这无济于事.

I've created a spring batch to query a Azure SQL server database and write the data into a CSV file. I do not have create permissions for the database. I get this error Invalid Object name BATCH_JOB_INSTANCE on running the batch. I don't want the spring batch meta-data tables to be created in the main database. Or it would be helpful if I can have them in another local or in-memory db like h2db. I've also added spring-batch-initialize-schema=never already, which was the case with most answers to similar questions on here, but that didn't help.

我解决了Invalid Object name错误，方法是通过扩展DefaultBatchConfigurer类并覆盖setDataSource方法来防止将元数据表创建到主数据库中，从而在内存映射库中创建它们.现在，我想尝试两个选项:

I resolved the Invalid Object name error by preventing the metadata tables from being created into the main database by extending the DefaultBatchConfigurer Class and Overriding the setDataSource method, thus having them created in the in-memory map-repository. Now I want to try two options:

1. 如何在本地数据库或内存数据库(如h2db)中创建元数据表.
2. 或者如果我已经在主数据库中创建了元数据表，则其模式与我要从中获取的主表不同.如何将我的工作指向另一个架构中的那些元数据表，以在其中存储工作和步骤详细数据.

@Configuration
public class SpringBatchConfig extends DefaultBatchConfigurer{

@Override
    public void setDataSource(DataSource datasource) {

    }
...

我的application.properties文件如下:

My application.properties file looks like this:

spring.datasource.url=
spring.datasource.username=
spring.datasource.password=
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver

spring-batch-initialize-schema=never
spring.batch.job.enabled=false

spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect

推荐答案

我创建了一个具有两个数据源的演示.批处理元数据将存储在H2 DB中，而Job数据源是Azure SQL.

I've created a demo with two datasources. Batch metadata will sotre in H2 DB and the Job datasource is Azure SQL.

这是项目结构:

1. 我们需要定义一个DataSourceConfig类，并为DataSource bean使用@Primary批注:

1. We need define a DataSourceConfig class and use @Primary annotation for DataSource bean:

@Configuration
public class DataSourceConfig {

    @Bean(name = "mssqlDataSource")
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource appDataSource(){
        return DataSourceBuilder.create().build();
    }
    
    @Bean(name = "h2DataSource")
    @Primary
    // @ConfigurationProperties(prefix="spring.datasource.h2")
    public DataSource h2DataSource() {
        return DataSourceBuilder.create()
                .url("jdbc:h2:mem:thing:H2;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE")
                .driverClassName("org.h2.Driver")
                .username("sa")
                .password("")                   
                .build();
    }
    
}

1. 在ItemReaderDbDemo类中，我们使用@Autowired @Qualifier("mssqlDataSource")在Spring Batch任务中指定数据源:

1. In the ItemReaderDbDemo class, we use @Autowired @Qualifier("mssqlDataSource") to specify the dataSource in the Spring Batch task:

@Configuration
public class ItemReaderDbDemo {
    
    //generate task Object
    @Autowired
    private JobBuilderFactory jobBuilderFactory;
    
    //Step exec tasks
    //generate step Object
    @Autowired
    private StepBuilderFactory stepBuilderFactory;

    @Autowired
    @Qualifier("mssqlDataSource")
    private DataSource dataSource;

    @Autowired
    @Qualifier("dbJdbcWriter")
    private ItemWriter<? super Todo> dbJdbcWriter;
    
    @Bean
    public Job itemReaderDbDemoJob() {
        return jobBuilderFactory.get("itemReaderDbDemoJob").start(itemReaderDbStep()).build();
    }

    @Bean
    public Step itemReaderDbStep() {
        return stepBuilderFactory.get("itemReaderDbStep")
                .<Todo,Todo>chunk(2)
                .reader(dbJdbcReader())
                .writer(dbJdbcWriter)
                .build();
    }

    @Bean
    @StepScope
    public JdbcPagingItemReader<Todo> dbJdbcReader() {
        JdbcPagingItemReader<Todo> reader = new JdbcPagingItemReader<Todo>();
        reader.setDataSource(dataSource);
        reader.setFetchSize(2);
        reader.setRowMapper(new  RowMapper<Todo>() {
            @Override
            public Todo mapRow(ResultSet rs, int rowNum) throws SQLException {
                Todo todo = new Todo();
                todo.setId(rs.getLong(1));
                todo.setDescription(rs.getString(2));
                todo.setDetails(rs.getString(3));
                return todo;
            }   
            
        });
        SqlServerPagingQueryProvider provider = new  SqlServerPagingQueryProvider();
        provider.setSelectClause("id,description,details");
        provider.setFromClause("from dbo.todo");
        
        //sort
        Map<String,Order> sort = new HashMap<>(1);
        sort.put("id", Order.DESCENDING);
        provider.setSortKeys(sort);
        
        reader.setQueryProvider(provider);
        return reader;
    }
}

1. 这是我的application.properties:

logging.level.org.springframework.jdbc.core=DEBUG

spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.jdbcUrl=jdbc:sqlserver://josephserver2.database.windows.net:1433;database=<Your-Database-Name>;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;
spring.datasource.username=<Your-UserName>
spring.datasource.password=<Your-Password>
spring.datasource.initialization-mode=always

1. 它从我的Azure SQL返回预期结果.顺便说一句，我的Azure sql用户名没有数据库的创建权限.
   结果显示:

1. It return expected result from my Azure SQL. By the way, my Azure sql username does not have create permissions for the database.
   The result shows:

这篇关于Spring Batch“无效的对象名称BATCH_JOB_INSTANCE"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！