Spring Batch Example -CSV To Database with Spring Boot & Oracle

Hello everyone,

Greetings today!

Today we will develop a Spring Batch application that saves data from a CSV file to a database using Spring Boot.

Before you start, if you want more details on Spring Batch components and architecture, read my post on Spring Batch Component Architecture

Below are some of the success criteria for the application.

Application Name :: StudentReportCardGeneration

Success Criteria

The school authority will place one CSV file in the following format containing all student grades.

Roll-No,Maths-Marks,English-Marks,Science-Marks,Email-Address

233,22,55,55,test@yopmail.com

234,66,77,88,test2@yopmail.com

Read a CSV file and add all student grades to the Student_Marks table.
Calculation of student grades and pass or fail rating status for all students
Student results and percentages must be emailed to each student.

Below is a flow chart of the application to be developed.

Let's get started.

Several metadata tables are required to run a Spring Batch application.

Read the post below and use the script provided to create the required tables in oracle.

Scripts to create spring batch metadata tables in oracle

Create a basic project structure using Spring Initializr with the following dependencies and import the downloaded project into your IDE.

Configure batch jobs in the BatchConfig class.

So there are two steps

processMarksCSVFile [readCSVFile as a reader for incoming CSV files, StudentMarksProcessor as a processor to calculate percentages and results, and WriteStudentMarks as a writer to save the CSV file records in the database. ]
emailResultStep will be the tasklet used to send the results to the students.

In readCSVFile, I created a line mapper that maps records from a CSV file to a StudentReportCard.

package com.student.report.config;

@Configuration
public class BatchConfig {

    @Autowired
    private JobBuilderFactory jobBuilderFactory;

    @Autowired
    private StepBuilderFactory stepBuilderFactory;

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

    @Bean
    public Step processMarksCSVFile() {
        return stepBuilderFactory.get("processMarksCSVFile")
        	.chunk(1)
                .reader(readCSVFile())
                .processor(studentMarksProcessor())
                .writer(writeStudentMarks())
                .build();
    }

    @Bean
    public FlatFileItemReader readCSVFile() {
        FlatFileItemReader csvFileReader
        	= new FlatFileItemReader<>();
        csvFileReader.setResource
        	(new FileSystemResource
          ("F://CodeSpace//Students//Student_Marks.csv"));
        csvFileReader.setLinesToSkip(1);
        csvFileReader.setLineMapper(getStudentLineMapper());
        return csvFileReader;
    }

    @Bean
    public LineMapper getStudentLineMapper() {
        DefaultLineMapper studentLineMapper
        	= new DefaultLineMapper();
        studentLineMapper.setLineTokenizer
        	(new DelimitedLineTokenizer() {
            {
                setNames
                (new String[] {
                	"Roll-No", "Maths-Marks", 
                	"English-Marks", "Science-Marks",
                    "Email-Address"
                   });
            }
        });
        studentLineMapper.setFieldSetMapper
        	(new BeanWrapperFieldSetMapper() {
            {
                setTargetType(StudentReportCard.class);
            }
        });
        return studentLineMapper;
    }

    @Bean
    public StudentMarksProcessor studentMarksProcessor() {
        return new StudentMarksProcessor();
    }

    @Bean
    public StudentMarksWriter writeStudentMarks() {
        return new StudentMarksWriter();
    }


    @Bean
    public Step emailResultStep(){
        return stepBuilderFactory.get("emailResultStep")
        	.tasklet(emailResultsTasklet())
                .build();
    }

    @Bean(name ="emailResultsTasklet" )
    public EmailResultsTasklet emailResultsTasklet(){
        return new EmailResultsTasklet();
    }
}

Each record in the CSV file is mapped to StudentReportCard.java.

package com.student.report.model;

import lombok.*;

import javax.persistence.*;
import java.math.BigDecimal;

@Setter
@Getter
@ToString
@AllArgsConstructor
@NoArgsConstructor
@Entity
@Table(name = "STUDENT_MARKS")
public class StudentReportCard {

    @Id
    @Column(name = "ROLL_NO")
    private long rollNo;

    @Column(name ="EMAIL_ADDRESS")
    private String emailAddress;

    @Column(name = "MATHS_MARKS")
    private BigDecimal mathsMarks;

    @Column(name = "SCIENCE_MARKS")
    private BigDecimal scienceMarks;

    @Column(name = "ENGLISH_MARKS")
    private BigDecimal englishMarks;

    @Column(name = "PECENTAGE")
    private BigDecimal percentage;

    @Column(name = "RESULT")
    private String result;
}

After the reader reads the data and the line mapper maps the data to the StudentReportCard, each record is processed by the StudentMarksProcessor as part of step 1.

Below is the code for StudentMarksProcessor.java

After calculating the student percentages and results, put those values in the same StudentReportCard POJO and pass it to the writer as part of Step 1.

package com.student.report.processor;

import com.student.report.model.StudentReportCard;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.batch.item.ItemProcessor;

import java.math.BigDecimal;

public class StudentMarksProcessor implements
	ItemProcessor {

    private static final Logger LOGGER =
    	LoggerFactory.getLogger(StudentMarksProcessor.class);

    @Override
    public StudentReportCard process
    	(StudentReportCard studentReportCard) throws Exception {

        BigDecimal percentage
        	=calculatePecentage(studentReportCard);
        studentReportCard.setPercentage(percentage);
        if(percentage.compareTo(new BigDecimal(35))>=0){
            studentReportCard.setResult("Pass");
        }else{
            studentReportCard.setResult("Fail");
        }
        return studentReportCard;
    }

    private BigDecimal calculatePecentage
    	(StudentReportCard studentReportCard)
        {
        	return ((studentReportCard.getEnglishMarks()
            	.add(studentReportCard.getMathsMarks())
                .add(studentReportCard.getScienceMarks()))
                .multiply(new BigDecimal(100)))
                .divide(new BigDecimal(300),2
                	,BigDecimal.ROUND_HALF_UP);
    }
}

Create a repository layer for storing records in the database using Spring Boot JPA.

Below is the code for StudentReportCardRepository.java.

package com.student.report.repository;

import com.student.report.model.StudentReportCard;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface StudentReportCardRepository 
	extends JpaRepository {
}

So, after calculating each student's percentage and score, we'll create a writer that will be used to save the records from the CVS file to the database.

Below is the code for StudentMarksWriter.java

package com.student.report.writer;

import com.student.report.model.StudentReportCard;
import com.student.report.repository.StudentReportCardRepository;
import org.springframework.batch.item.ItemWriter;
import org.springframework.beans.factory.annotation.Autowired;

import java.util.List;

public class StudentMarksWriter implements ItemWriter {

    @Autowired
    private StudentReportCardRepository 
    	studentReportCardRepository;

    @Override
    public void write(List list)
    	throws Exception {
         list.stream().forEach(x->{
           studentReportCardRepository.save(x);
         });
    }
}

Now that we have all the records in the file in the STUDENT_MARKS table in the database, let's learn how to use tasklet. We have already configured the EmailResultsTasklet tasklet with the emailResultStep() step.

So create an EmailResultsTasklet. Below is the code for it. Here we are retrieving records from the database and sending the results to each student.

package com.student.report.tasklet;

public class EmailResultsTasklet implements Tasklet {

    private static final Logger logger=
    	LoggerFactory.getLogger(EmailResultsTasklet.class);

    @Autowired
    private StudentReportCardRepository 
    	studentReportCardRepository;

    @Autowired
    private EmailService emailService;

    @Override
    public RepeatStatus execute
    	(StepContribution stepContribution,
        	ChunkContext chunkContext) throws Exception {

        studentReportCardRepository.findAll()
        	.stream()
            .forEach(x->{
              emailService.sendSimpleMessage
              (x.getEmailAddress(),
              "Your Result","Your result "+x.getResult() 
              +" with "+x.getPercentage() +"%");
        });

        stepContribution.setExitStatus(ExitStatus.COMPLETED);
        return RepeatStatus.FINISHED;
    }
}

To send an email you need to create an EmailService, so below is the code for that.

package com.student.report.service;

@Component
public class EmailService  {

    private static final Logger LOGGER = 
    	LoggerFactory.getLogger(EmailService.class);

    @Autowired
    private JavaMailSender emailSender;

    public void sendSimpleMessage(
            String to, String subject, String text) {
        try{
            SimpleMailMessage message = new SimpleMailMessage();
            message.setFrom("add-email-address
            	-from-which-you-want-to-send-mails");
            message.setTo(to);
            message.setSubject(subject);
            message.setText(text);
            emailSender.send(message);
        }catch(Exception e){
            LOGGER.error("Error while sending email "
            	+e.getMessage(),e);
        }
    }
}

Since we are using Spring Boot, we need to configure the following database and email configuration in application.properties:

spring.datasource.url=jdbc:oracle:thin:@localhost:1521:orcl
spring.datasource.username=username
spring.datasource.password=password
spring.datasource.driver-class-name=
	oracle.jdbc.driver.OracleDriver
spring.jpa.hibernate.ddl-auto=create

spring.mail.host=smtp.gmail.com
spring.mail.port=587
spring.mail.username=username[email address]   
spring.mail.password=password
spring.mail.properties.mail.smtp.auth=true
spring.mail.properties.mail.smtp.starttls.enable=true

Schedule a batch job to run in the main class as follows:

package com.student.report;

@SpringBootApplication
@EnableBatchProcessing
public class StudentReportMgtApplication {

	@Autowired
	JobLauncher jobLauncher;

	@Autowired
	Job generateReportCard;

	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(generateReportCard, params);
	}
}

Below is the project structure for reference.