Hello all,

Greetings of the day!

Today we will see how to create a CSV file by reading data from a database table using the Spring Batch application, so let's get started.

So here are some success criteria for our application.

We will have a student table with below three columns and we will export the data from this table to a CSV file

ID NAME PERCENTAGE

Note: Spring Batch requires metadata tables. See my below post to find a script that creates the tables you need

Script To Create Spring Batch Meta Data Tables

Now let's go to the Spring Initializr and create a Spring Batch application with the following dependencies:

Next, create an entity class Student.java that maps to the student table.

package com.student.report.model;

import lombok.Getter;
import lombok.Setter;

import javax.persistence.*;

@Entity
@Table(name = "student")
@Getter
@Setter
public class Student {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column
    private String name;

    @Column
    private Double percentage;
}

Since we are using an Oracle database, let's add the Oracle dependency.
<dependency>
  <groupId>oracle</groupId>
  <artifactId>ojdbc6</artifactId>
  <version>11.2.0.3</version>
</dependency>

The next step is to configure database-related properties in application.properties as shown below
spring.datasource.initialize=true
spring.datasource.url=jdbc:oracle:thin:@localhost:1521:orcl
spring.datasource.username=system
spring.datasource.password=add-your-password
spring.datasource.driver-class-name=oracle.jdbc.driver\
  .OracleDriver
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect\
  .Oracle10gDialect
spring.jpa.hibernate.ddl-auto = update
spring.datasource.initialization-mode=always
Spring Boot will automatically create a student table in your Oracle database. Now let's add some records to the student table using Spring Boot which will be exported to a CSV file by Spring Batch.

For that create a data.sql file under src/main/resources and add the below insert script in data.sql so spring can execute it on startup.
insert into student values(1,'Prachi',67);
insert into student values(2,'Ankit',72);
insert into student values(3,'Mayur',82);
Now add a mapper that maps the data returned from the Student table to the Student entity. So create a StudentMapper.java that implements the RowMapper interface.
package com.student.report.mapper;

import com.student.report.model.Student;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;
import java.sql.ResultSet;
import java.sql.SQLException;

@Component
public class StudentMapper implements RowMapper<Student> {

    @Override
    public Student mapRow(ResultSet resultSet, int i)
    		throws SQLException {
        Student student=new Student();
        student.setId(resultSet.getLong("id"));
        student.setName(resultSet.getString("name"));
        student.setPercentage(resultSet.getDouble("percentage"));
        return student;
    }
}
Next, create a BatchConfig.java class that uses the JdbcCursorItemReader to read the data from the database, the StudentMapper to map the data to the Student entity, the FlatFileItemWriter to create the CSV file, and writes the data to the CSV file. 
It also uses the FlatFileHeaderCallback to add headers to the CSV file.

We will add multiple beans inside the BatchConfig.java file 
  •  JdbcCursorItemReader - Reads data from a database, maps the result to a Student using a StudentMapper, and sends the result to a Writer i.e. FlatFileItemWriter.
  
  @Bean
  public JdbcCursorItemReader<Student> jdbcCursorItemReader(){
      JdbcCursorItemReader<Student> jdbcCursorItemReader = 
        	new JdbcCursorItemReader<>();
      jdbcCursorItemReader.setDataSource(dataSource);
      jdbcCursorItemReader.setSql("SELECT * FROM student");
      jdbcCursorItemReader.setRowMapper(studentMapper);
      return jdbcCursorItemReader;
    }

  • FlatFileItemWriter - Here we have configured the file location where the CSV will be exported, the delimiter, and the header callback to set the header of the CSV file. FlatFileItemWriter get`s the student's records according to the commit interval and it will write the data to a CSV file.
 
  @Bean
  public FlatFileItemWriter<Student> flatFileItemWriter(){
    FlatFileItemWriter<Student> flatFileItemWriter =
          new FlatFileItemWriter<>();
    
    flatFileItemWriter.setResource
       (new FileSystemResource
          ("F://Code_Space//StudentReportMgt//student.csv"));
    flatFileItemWriter
    .setLineAggregator(new DelimitedLineAggregator<Student>() 
     {{
      setDelimiter(",");
      setFieldExtractor(new BeanWrapperFieldExtractor<Student>()
      {{
      setNames(new String[] { "id", "name","percentage"});
       }});
        
     }});
    
    flatFileItemWriter.setHeaderCallback
          (writer -> writer.write("Id,Name,Percentage"));
     return flatFileItemWriter;
    }
Below is the complete code for BatchConfig.java with a Spring batch job configured.
package com.student.report.config;

@Configuration
public class BatchConfig {

@Autowired
    private JobBuilderFactory jobBuilderFactory;

    @Autowired
    private StepBuilderFactory stepBuilderFactory;

    @Autowired
    private StudentMapper studentMapper;

    @Autowired
    private DataSource dataSource;

    @Bean(name = "generateCSVReportCard")
    public Job generateCSVReportCard() {
        return jobBuilderFactory
                .get("generateReportCard")
                .incrementer(new RunIdIncrementer())
                .start(processStudentReport()).build();
    }

    @Bean
    public Step processStudentReport() {
        return stepBuilderFactory.get("processStudentReport")
                .<Student,Student>chunk(1)
                .reader(jdbcCursorItemReader())
                .writer(flatFileItemWriter())
                .build();
    }

    @Bean
    public JdbcCursorItemReader<Student> jdbcCursorItemReader(){
        JdbcCursorItemReader<Student> jdbcCursorItemReader
                = new JdbcCursorItemReader<>();
        jdbcCursorItemReader.setDataSource(dataSource);
        jdbcCursorItemReader.setSql("SELECT * FROM student");
        jdbcCursorItemReader.setRowMapper(studentMapper);
        return jdbcCursorItemReader;
    }

    @Bean
    public FlatFileItemWriter<Student> flatFileItemWriter(){
        FlatFileItemWriter<Student> flatFileItemWriter
                = new FlatFileItemWriter<>();
        flatFileItemWriter.setResource
          (new FileSystemResource
           ("F://Code_Space//StudentReportMgt//student.csv"));
        flatFileItemWriter.setLineAggregator
           (new DelimitedLineAggregator<Student>() 
           {{
             setDelimiter(",");
             setFieldExtractor(
             	new BeanWrapperFieldExtractor<Student>() 
             {{
             	setNames(new String[]
              	{ "id", "name","percentage"});
             }});
            }});
        flatFileItemWriter.setHeaderCallback
                (writer -> writer.write("Id,Name,Percentage"));
        return flatFileItemWriter;
    }
}
We have done a complete setup of the spring batch job now we will run this batch job, for that add the below CRON expression and job in the StudentReportMgtApplication.java file
You also need to add the @EnableBatchProcessing annotation to your main class.
package com.student.report;

@SpringBootApplication
@EnableBatchProcessing
public class StudentReportMgtApplication {

	@Autowired
	private JobLauncher jobLauncher;

	@Autowired
	Job generateCSVReportCard;

	public static void main(String[] args) {
		SpringApplication.run
        	(StudentReportMgtApplication.class, args);
	}

	@Scheduled(cron = "0 */1 * * * ?")
	public void perform() throws Exception
	{
		JobParameters params = new JobParametersBuilder()
			.addString("JobID",
                String.valueOf(System.currentTimeMillis()))
			.toJobParameters();
		jobLauncher.run(generateCSVReportCard, params);
	}
}

Below is the project structure for reference.