MySQL to CSV Spring Boot Batch Example

MySQL to CSV Spring Boot Batch Example | Previously we have seen Spring Boot Batch examples of CSV to MySQL, and CSV to MongoDB. Now let us see MySQL to CSV file through Spring Boot Batch. Also see:-

• Spring Boot Batch API Introduction
• CSV To MySQL Database Spring Boot Batch API Example
• CSV to MongoDB Spring Boot Batch Example
• MongoDB to CSV Spring Boot Batch Example
• MySQL to XML Spring Boot Batch Example

Create a Spring starter project with the following dependencies:- Lombok, Spring Batch, Spring Data JPA, MySQL, Spring web.

In application.properties file:-

# DB details
spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=root

# JPA info
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update
spring.jpa.properties.hibernate.format_sql=true

# disable job run at startup
spring.batch.job.enabled=false 
spring.batch.jdbc.initialize-schema=always

Model class:-

package com.knowprogram.demo.model;

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.Table;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
@Entity
@Table(name = "prod")
public class Product {
    @Id
    @Column(name = "pid")
    private Integer prodId;
    
    @Column(name = "pname")
    private String prodCode;
    
    @Column(name = "pcost")
    private Double prodCost;
    
    @Column(name = "ptax")
    private Double prodTax;
    
    @Column(name = "pdiscount")
    private Double prodDiscount;
}

Repository interface:-

package com.knowprogram.demo.repository;

import org.springframework.data.jpa.repository.JpaRepository;

import com.knowprogram.demo.model.Product;

public interface ProductRepository extends JpaRepository<Product, Integer> {

}

In BatchConfig:-

package com.knowprogram.demo.config;

import org.springframework.batch.core.Job;
import org.springframework.batch.core.JobExecution;
import org.springframework.batch.core.JobExecutionListener;
import org.springframework.batch.core.Step;
import org.springframework.batch.core.job.builder.JobBuilder;
import org.springframework.batch.core.repository.JobRepository;
import org.springframework.batch.core.step.builder.StepBuilder;
import org.springframework.batch.item.ItemProcessor;
import org.springframework.batch.item.ItemReader;
import org.springframework.batch.item.ItemWriter;
import org.springframework.batch.item.data.RepositoryItemReader;
import org.springframework.batch.item.file.FlatFileItemWriter;
import org.springframework.batch.item.file.transform.BeanWrapperFieldExtractor;
import org.springframework.batch.item.file.transform.DelimitedLineAggregator;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.FileSystemResource;
import org.springframework.core.task.SimpleAsyncTaskExecutor;
import org.springframework.data.domain.Sort;
import org.springframework.transaction.PlatformTransactionManager;

import com.knowprogram.demo.model.Product;
import com.knowprogram.demo.repository.ProductRepository;

import java.util.Collections;

@Configuration
public class BatchConfig {

    @Autowired
    private ProductRepository productRepository;

    @Bean
    ItemReader<Product> reader() {
        RepositoryItemReader<Product> reader = new RepositoryItemReader<>();
        reader.setRepository(productRepository);
        reader.setMethodName("findAll");
        reader.setPageSize(10);
        reader.setSort(Collections.singletonMap("prodId", Sort.Direction.ASC));
        return reader;
    }

    @Bean
    ItemProcessor<Product, Product> processor() {
        return item -> item;
    }

    @Bean
    ItemWriter<Product> writer() {
        FlatFileItemWriter<Product> writer = new FlatFileItemWriter<>();
        writer.setResource(new FileSystemResource("products_output.csv"));
        writer.setAppendAllowed(false);
        writer.setLineAggregator(new DelimitedLineAggregator<Product>() {{
            setDelimiter(",");
            setFieldExtractor(new BeanWrapperFieldExtractor<Product>() {{
                setNames(new String[]{"prodId", "prodCode", 
                     "prodCost", "prodTax", "prodDiscount"});
            }});
        }});
        return writer;
    }

    @Bean
    Step step(JobRepository repository, PlatformTransactionManager transactionManager) {
        return new StepBuilder("csv-step", repository)
                .<Product, Product>chunk(10, transactionManager)
                .reader(reader())
                .processor(processor())
                .writer(writer())
                .taskExecutor(new SimpleAsyncTaskExecutor() {
                    private static final long serialVersionUID = 1L;

                    {
                        setConcurrencyLimit(10);
                    }
                })
                .build();
    }

    @Bean
    JobExecutionListener listener() {
        return new JobExecutionListener() {
            @Override
            public void beforeJob(JobExecution jobExecution) {
                System.out.println("MyJobListener.beforeJob()");
            }

            @Override
            public void afterJob(JobExecution jobExecution) {
                System.out.println("MyJobListener.afterJob()");
            }
        };
    }

    @Bean(name = "csvJob")
    Job job(JobRepository jobRepository, PlatformTransactionManager transactionManager) {
        return new JobBuilder("csv-job", jobRepository)
                .listener(listener())
                .flow(step(jobRepository, transactionManager))
                .end()
                .build();
    }
}

In Controller:-

@RestController
package com.knowprogram.demo.controller;

public class ProductBatchController {

    @Autowired
    private JobLauncher jobLauncher;

    @Autowired
    private Job job;

    @GetMapping("/startBatch")
    public String startBatch() throws JobExecutionAlreadyRunningException, 
        JobRestartException,
        JobInstanceAlreadyCompleteException, 
        JobParametersInvalidException {
        JobParameters params = new JobParametersBuilder()
             .addLong("time", System.currentTimeMillis())
             .toJobParameters();

        JobExecution run = jobLauncher.run(job, params);
        return run.getStatus().toString();
    }

}

JpaPagingItemReader and EntityManagerFactory

Instead of using RepositoryItemReader and ProductRepository, we can use JpaPagingItemReader and EntityManagerFactory. In that case, we won’t need the ProductRepository interface therefore we can remove the ProductRepository.

@Configuration
public class BatchConfig {

    @Autowired
    private EntityManagerFactory entityManagerFactory;

    @Bean
    ItemReader<Product> reader() {
        JpaPagingItemReader<Product> reader = new JpaPagingItemReader<>();
        reader.setEntityManagerFactory(entityManagerFactory);
        reader.setQueryString("SELECT p FROM Product p");
        reader.setPageSize(10);
        return reader;
    }

    // others
}

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Search