Spring批量上传CSV文件并相应地插入数据库 [英] Spring batch to upload a CSV file and insert into database accordingly

问题描述

我的项目有这个要求，用户上传一个必须推送到mysql数据库的CSV文件。我知道我们可以使用Spring批处理大量的记录。但我无法找到我的这个要求的任何教程/示例代码。我遇到的所有教程都硬编码了CSV文件名，如下所示：

My project has this requirement where user uploads a CSV file which has to be pushed to mysql database. I know we can use Spring batch to process large number of records. But I'm not able to find any tutorial/sample code for this requirement of mine. All the tutorials which I came across just hardcoded the CSV file name in it like below:

https://spring.io/guides/gs/batch-processing/

我会需要使用用户上传的文件并进行相应的处理。这里的任何帮助将不胜感激..

I'll need to use the file uploaded by user and process it accordingly. Any help here would be appreciated..

如果没有使用Spring批处理，是否还有其他方法可以将上传的CSV数据插入mysql？

If not with Spring batch, is there any other way to insert the uploaded CSV data to mysql?

推荐答案

请将此作为主要参考： http://walkingtechie.blogspot.co.uk/2017/03/spring-batch-csv-file-to-mysql.html
这解释了如何使用Batch将CSV文件导入MySQL数据库。

Please have this as main reference: http://walkingtechie.blogspot.co.uk/2017/03/spring-batch-csv-file-to-mysql.html This explains how you use Batch to import a CSV file into a MySQL database.

但是，正如你所说，所有的例子都假设一个硬编码文件是不是你想要的。

However, as you said, all the example assume an hardcode file which is not what you want.

在下面的代码中，重要的位（与我提供的链接中的示例不同）是采用多部分文件并保存的控制器它在一个临时文件夹中。
然后文件名作为参数传递给Job：

In the code below, the important bits (that differ from the example in the link I provided) are the Controller that take a multipart file and save it in a temporary folder. Then the file name is passed to the Job as parameter:

JobExecution jobExecution = jobLauncher.run(importUserJob, new JobParametersBuilder()
                .addString("fullPathFileName", fileToImport.getAbsolutePath())
                .toJobParameters());

最后，importReader使用param fullPathFileName加载用户上传的文件：

Finally, the importReader uses the param fullPathFileName to load the file uploaded by the user:

      @Bean
      public FlatFileItemReader<Person> importReader(@Value("#{jobParameters[fullPathFileName]}") String pathToFile) {
        FlatFileItemReader<Person> reader = new FlatFileItemReader<>();
        reader.setResource(new FileSystemResource(pathToFile));

这里给出了一个完整的代码（未经测试，但它有大部分组件）：

Here the full code (not tested but it has most of the components) to give you an idea:

@Configuration
@EnableBatchProcessing
public class BatchConfig{

    @Bean
    public ResourcelessTransactionManager batchTransactionManager(){
        ResourcelessTransactionManager transactionManager = new ResourcelessTransactionManager();
        return transactionManager;
    }

    @Bean
    protected JobRepository jobRepository(ResourcelessTransactionManager batchTransactionManager) throws Exception{
        MapJobRepositoryFactoryBean jobRepository = new MapJobRepositoryFactoryBean();
        jobRepository.setTransactionManager(batchTransactionManager);
        return (JobRepository)jobRepository.getObject();
    }

    @Bean
    public JobLauncher jobLauncher(JobRepository jobRepository) throws Exception {
        SimpleJobLauncher jobLauncher = new SimpleJobLauncher();
        jobLauncher.setJobRepository(jobRepository);
        return jobLauncher;
    }

}

@Configuration
public class ImportJobConfig {

    @Bean
    public FlatFileItemReader<Person> importReader(@Value("#{jobParameters[fullPathFileName]}") String pathToFile) {
        FlatFileItemReader<Person> reader = new FlatFileItemReader<>();
        reader.setResource(new FileSystemResource(pathToFile));
        reader.setLineMapper(new DefaultLineMapper<Person>() {{
            setLineTokenizer(new DelimitedLineTokenizer() {{
                setNames(new String[]{"firstName", "lastName"});
            }});
            setFieldSetMapper(new BeanWrapperFieldSetMapper<Person>() {{
                setTargetType(Person.class);
            }});
        }});
        return reader;
    }

    @Bean
    public PersonItemProcessor processor() {
        return new PersonItemProcessor();
    }

    @Bean
    public JdbcBatchItemWriter<Person> writer() {
        JdbcBatchItemWriter<Person> writer = new JdbcBatchItemWriter<>();
        writer.setItemSqlParameterSourceProvider(
                new BeanPropertyItemSqlParameterSourceProvider<Person>());
        writer.setSql("INSERT INTO people (first_name, last_name) VALUES (:firstName, :lastName)");
        writer.setDataSource(dataSource);
        return writer;
    }
    // end::readerwriterprocessor[]

    // tag::jobstep[]
    @Bean
    public Job importUserJob(JobCompletionNotificationListener listener) {
        return jobBuilderFactory.get("importUserJob").incrementer(new RunIdIncrementer())
                .listener(listener).flow(step1()).end().build();
    }

    @Bean
    public Step step1(@Qualifier("importReader") ItemReader<Person> importReader) {
        return stepBuilderFactory.get("step1").<Person, Person>chunk(10).reader(importReader)
                .processor(processor()).writer(writer()).build();
    }

}

@RestController
public class MyImportController {

    @Autowired private JobLauncher jobLauncher;
    @Autowired private Job importUserJob;

    @RequestMapping(value="/import/file", method=RequestMethod.POST)
    public String create(@RequestParam("file") MultipartFile multipartFile) throws IOException{

        //Save multipartFile file in a temporary physical folder
        String path = new ClassPathResource("tmpuploads/").getURL().getPath();//it's assumed you have a folder called tmpuploads in the resources folder
        File fileToImport = new File(path + multipartFile.getOriginalFilename());
        OutputStream outputStream = new FileOutputStream(fileToImport);
        IOUtils.copy(multipartFile.getInputStream(), outputStream);
        outputStream.flush();
        outputStream.close();       

        //Launch the Batch Job
        JobExecution jobExecution = jobLauncher.run(importUserJob, new JobParametersBuilder()
                .addString("fullPathFileName", fileToImport.getAbsolutePath())
                .toJobParameters());        

        return "OK";
    }

}

这篇关于Spring批量上传CSV文件并相应地插入数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！