springbatch导出mysql数据到外部文件

╰+攻爆jí腚メ 2023-06-25 15:29 5阅读 0赞

## 前言 ##

在之前的一篇中，我们简单介绍了如何通过自定义itemReader和itemWriter读取外部数据然后导入到mysql中，下面我们简单介绍一下如何将mysql的数据通过springbatch批量导出

举个实际的场景，我们需要将数据库的某个表的数据导出到excel中，如果数据量特别大的时候使用java单线程去做这个事情，会比较耗时而且容易因为IO问题中途断掉，如果使用springbatch批量读取，就可以大大节省数据读取的时间，当然这只是个很简单的应用场景，下面通过代码简单演示一下

**1、定义job类**

@Configuration
    public class FlatFileDemoJobConfiguration { 
    
        @Autowired
        private JobBuilderFactory jobBuilderFactory;
    
        @Autowired
        private StepBuilderFactory stepBuilderFactory;
    
        @Autowired
        @Qualifier("flatFileDemoJdbcPagingReader")
        private ItemReader<Customer> flatFileDemoJdbcPagingReader;
    
        @Autowired
        @Qualifier("flatFileDemoFlatFileWriter")
        private ItemWriter<Customer> flatFileDemoFlatFileWriter;
    
        @Bean
        public Step flatFileOutputDemoStep() { 
            return stepBuilderFactory.get("flatFileOutputDemoStep")
                    .<Customer,Customer>chunk(10)
                    .reader(flatFileDemoJdbcPagingReader)
                    .writer(flatFileDemoFlatFileWriter)
                    .build();
        }
    
        @Bean
        public Job flatFileOutputDemoJob() { 
            return jobBuilderFactory.get("flatFileOutputDemoJob")
                    .start(flatFileOutputDemoStep())
                    .build();
        }
    }

**2、自定义ItemReader**

由于是从数据库读取数据，所以选用JdbcPagingItemReader这个读取器，带有分页的功能

/** * 自定义数据读取器 */
    @Configuration
    public class FlatFileDemoJobReaderConfiguration { 
    
        @Autowired
        public DataSource dataSource;
    
        @Bean
        public JdbcPagingItemReader<Customer> flatFileDemoJdbcPagingReader() { 
            JdbcPagingItemReader<Customer> reader = new JdbcPagingItemReader<>();
            reader.setDataSource(this.dataSource);
            //设置每次分页读取的条数
            reader.setFetchSize(10);
            reader.setRowMapper((rs,rowNum)->{ 
                Customer customer = new Customer();
                customer.setId(rs.getString("id"));
                customer.setFirstName(rs.getString("firstName"));
                customer.setLastName(rs.getString("lastName"));
                customer.setBirthdate(rs.getString("birthdate"));
                return customer;
            });
            //定义查询的数据来源
            MySqlPagingQueryProvider queryProvider = new MySqlPagingQueryProvider();
            queryProvider.setSelectClause("id, firstName, lastName, birthdate");
            queryProvider.setFromClause("from customer");
    
            //读取出来的数据进行排序
            Map<String, Order> sortKeys = new HashMap<>(1);
            sortKeys.put("id", Order.ASCENDING);
            queryProvider.setSortKeys(sortKeys);
            reader.setQueryProvider(queryProvider);
            return reader;
        }
    }

**3、自定义ItemReader**

@Configuration
    public class FlatFileDemoJobWriterConfiguration { 
    
        @Bean
        public FlatFileItemWriter<Customer> flatFileDemoFlatFileWriter() throws Exception { 
            FlatFileItemWriter<Customer> itemWriter = new FlatFileItemWriter<>();
            String path ;
            File file = new File("E:\\java代码\\springBatch\\out.json");
            path = file.getAbsolutePath();
            System.out.println(">> file is created in: " + path);
            itemWriter.setResource(new FileSystemResource(path));
            itemWriter.setLineAggregator(new MyCustomerLineAggregator());
            itemWriter.afterPropertiesSet();
            return itemWriter;
        }
    
    }

注意这里有一个MyCustomerLineAggregator，即自定义输出的数据格式的转换器，其实可以理解为，你从数据库读取出来的数据，最后输出到文件中展现的格式是怎样的呢？可以有json类型的，也可以是上面的csv类型的，甚至可以是xml的类型，都可以自己指定，MyCustomerLineAggregator代码如下：

public class MyCustomerLineAggregator implements LineAggregator<Customer> { 
        //JSON
        private ObjectMapper mapper = new ObjectMapper();
    
        @Override
        public String aggregate(Customer customer) { 
    
            try { 
                return mapper.writeValueAsString(customer);
            } catch (JsonProcessingException e) { 
               throw new RuntimeException("Unable to serialize.",e);
            }
        }
    }

最终我们希望得到的是一个out.json的json格式的文件，下面启动程序，可以看到生成了一个json文件  
![在这里插入图片描述][20191229221711196.png]

打开文件看看，可以看到以json的格式展现的![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nY29uZ3lpNDIw_size_16_color_FFFFFF_t_70]

既然说到这里，我们顺便提供一个简单的解析xml格式的文件的demo，下面直接上代码：

@Configuration
    @EnableBatchProcessing
    public class XmlFileReaderDemo { 
    
        @Autowired
        private JobBuilderFactory jobBuilderFactory;
    
        @Autowired
        private StepBuilderFactory stepBuilderFactory;
    
        @Autowired
        @Qualifier("xmlFileDemoWriter")
        private ItemWriter<? super Customer> xmlFileWriter;
    
        @Bean
        public Job itemXmlFileReader(){ 
            return jobBuilderFactory.get("itemXmlFileReader").start(itemXmlFileStep()).build();
        }
    
        @Bean
        public Step itemXmlFileStep() { 
            return stepBuilderFactory.get("itemXmlFileStep")
                    .<Customer,Customer>chunk(10)
                    .reader(fileXmlDemoReader())
                    .writer(xmlFileWriter)
                    .build();
        }
    
        @Bean
        @StepScope
        public StaxEventItemReader<Customer> fileXmlDemoReader() { 
            StaxEventItemReader<Customer> reader = new StaxEventItemReader();
            reader.setResource(new ClassPathResource("customer.xml"));
            reader.setFragmentRootElementName("customer");
    
            XStreamMarshaller marshaller = new XStreamMarshaller();
            Map<String,Class> params = new HashMap<>();
            params.put("customer",Customer.class);
            marshaller.setAliases(params);
            reader.setUnmarshaller(marshaller);
            return reader;
        }
    
    }

下面是读取的类，即自定义的itemWriter，这里只是做演示，就不再进行数据入库的操作了，直接将读取的数据打印在控制台上

@Component("xmlFileDemoWriter")
    public class XmlFileDemoWriter implements ItemWriter<Customer> { 
    
        @Override
        public void write(List<? extends Customer> list) throws Exception { 
            for(Customer item : list){ 
                System.out.println(item.getFirstName());
            }
        }
    }

resources目录下，提供一个用于读取的xml文件customer.xml  
![在这里插入图片描述][2019122922230859.png]

图中截取了部分的内容，整个结构都是如此  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nY29uZ3lpNDIw_size_16_color_FFFFFF_t_70 1]

下面启动程序看一下是否能够成功解析出来，最终看到解析到了数据，并打印出了firstName输出到控制台  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nY29uZ3lpNDIw_size_16_color_FFFFFF_t_70 2]

**本篇的讲解到此结束，内容比较浅显，有兴趣的同学可深入研究，最后感谢观看！**

[20191229221711196.png]: https://img-blog.csdnimg.cn/20191229221711196.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nY29uZ3lpNDIw_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/2019122922180549.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nY29uZ3lpNDIw,size_16,color_FFFFFF,t_70
[2019122922230859.png]: https://img-blog.csdnimg.cn/2019122922230859.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nY29uZ3lpNDIw_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/2019122922234458.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nY29uZ3lpNDIw,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nY29uZ3lpNDIw_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20191229222529954.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nY29uZ3lpNDIw,size_16,color_FFFFFF,t_70