Upload and Read CSV File in Spring Boot

Upload and Read CSV File in SpringBoot

Tutorial: “Upload and Read CSV File in Spring Boot – SpringBoot Upload Download Multiple CSV files to MySQL/PostgreSQL with Ajax”

Creating SpringBoot RestAPIs to upload and download multiple CSV files to databases: MySQL and PostgreSQL is one of the most common question in the development world. Going through the tutorial post”SpringBoot Upload Download Multiple CSV files to MySQL/PostgreSQL”, I explain details how to do it by step to step with coding and give you 100% running source code. What we will do?

– I draw an overview diagram architecture of SpringBoot RestAPI Upload CSV Files.
– I use Spring Web to development Spring RestApis.
– I use ApacheCommon or Open CSV libraries to parse and read CSV files.
– I use SpringJPA to save data from CSV files to MySQL and PostgreSQL.
– I implement a SpringBoot Global Exception Handler when uploading with a very big files and fail.
– I use Ajax and Bootstrap to implement a frontend client to upload/download CSV files.

Related posts:


To do the tutorial “Upload and Read CSV File in Spring Boot”, we need prepare Java >= 8, Eclipse with SpringToolSuite, PostgreSQL and pgAdmin or MySQL, a RestClient (I use Advanced Rest Client).

Now let’s go!

Youtube Video Guide: Upload and Read CSV File in Spring Boot

Overview – Upload and Read CSV File in Spring Boot

Here is an overview about workflow data of SpringBoot Upload/Download multiple Csv Files project:

SpringBoot RestAPIs Upload Download Csv Files to MySQL PostgreSQL database with Ajax and RestClient -Overview Architecture
Overview Architecture

– We implement an Ajax or use a Rest client to upload/download Csv files to/from SpringBoot application.
– For manipulating Csv files, we develop an Csv Utils class to write and read data from them.
– We implement a Csv File Service to do CRUD operations with MySQL/PostgreSQL that supported by SpringJPA Repository.

Here is an overview of “SpringBoot Upload Download multiple CSV files to database” project that we will implement in the tutorial:

SpringBoot RestAPIs Upload Download Csv Files to MySQL PostgreSQL database with Ajax and RestClient - project structure
SpringBoot project structure
  • controller package implements Controller RestAPIs classes to upload/download Csv files from Ajax or a Rest-Client
  • repository package defines a CRUD JPA Repository: CustomerRepository
  • services package implements a class CsvFileServices with functions to store and retrieve Csv File’s data
  • utils package implements a class CsvUtils with common functions for reading or writing data to/from Csv files
  • model package defines a class Customer for mapping data between each CSV’s row with each database’s record
  • errorhandler package implements a class RestExceptionHandler to handle an exception: file size exceeded

Create Project – Upload and Read CSV File in Spring Boot

We use Eclipse to create a “SpringBoot upload download file” project with a set of below dependencies:

  • spring-boot-starter-data-jpa dependency is used to interact with PostgreSQL/MySQL database
  • postgresql or mysql-connector-java drivers are used to connect with PostgreSQL/MySQL database
  • spring-boot-starter-web dependency is used to implement Spring RestAPIs Controller

For writting and reading CSV files, we use dependency commons-csv of org.apache.commons or use dependency opencsv of com.opencsv

In the tutorial, we mainly do a demo with MySQL database, so here is the details of necessary dependencies in pom.xml file:

<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-csv -->
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-csv</artifactId>
    <version>1.8</version>
</dependency>		

<dependency>
    <groupId>com.opencsv</groupId>
    <artifactId>opencsv</artifactId>
    <version>4.5</version>
</dependency>		

<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-web</artifactId>
</dependency>

<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<scope>runtime</scope>
</dependency>

SpringBoot Using OpenCsv to Read/Write data to CSV file

We create class OpenCsvUtil.java to read/write csv file with 2 methods:

  • parseCsvFile(InputStream is) is used to read csv file
  • customersToCsv(Writer writer, List customers) is used to write object list to csv writer
OpenCsvUtils methods
OpenCsvUtils methods
package com.loizenjava.updownloadcsv.utils;

import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.Reader;
import java.io.Writer;
import java.util.ArrayList;
import java.util.List;

import com.loizenjava.updownloadcsv.model.Customer;
import com.opencsv.CSVWriter;
import com.opencsv.bean.ColumnPositionMappingStrategy;
import com.opencsv.bean.CsvToBean;
import com.opencsv.bean.CsvToBeanBuilder;
import com.opencsv.bean.StatefulBeanToCsv;
import com.opencsv.bean.StatefulBeanToCsvBuilder;

/**
 * Copyright by https://loizenjava.com
 * 
 * @author loizenjava.com
 */
public class OpenCsvUtil {

	public static List<Customer> parseCsvFile(InputStream is) {
		String[] CSV_HEADER = { "id", "name", "address", "age" };
		Reader fileReader = null;
		CsvToBean<Customer> csvToBean = null;
	
		List<Customer> customers = new ArrayList<Customer>();
		
		try {
			fileReader = new InputStreamReader(is);
	
			ColumnPositionMappingStrategy<Customer> mappingStrategy = new ColumnPositionMappingStrategy<Customer>();
	
			mappingStrategy.setType(Customer.class);
			mappingStrategy.setColumnMapping(CSV_HEADER);
	
			csvToBean = new CsvToBeanBuilder<Customer>(fileReader).withMappingStrategy(mappingStrategy).withSkipLines(1)
					.withIgnoreLeadingWhiteSpace(true).build();
	
			customers = csvToBean.parse();
			
			return customers;
		} catch (Exception e) {
			System.out.println("Reading CSV Error!");
			e.printStackTrace();
		} finally {
			try {
				fileReader.close();
			} catch (IOException e) {
				System.out.println("Closing fileReader/csvParser Error!");
				e.printStackTrace();
			}
		}
		
		return customers;
	}

	public static void customersToCsv(Writer writer, List<Customer> customers) {
		String[] CSV_HEADER = { "id", "name", "address", "age" };
	    
	    StatefulBeanToCsv<Customer> beanToCsv = null;
	 
	    try {
	      // write List of Objects
	      ColumnPositionMappingStrategy<Customer> mappingStrategy = 
	                new ColumnPositionMappingStrategy<Customer>();
	      
	      mappingStrategy.setType(Customer.class);
	      mappingStrategy.setColumnMapping(CSV_HEADER);
	      
	      beanToCsv = new StatefulBeanToCsvBuilder<Customer>(writer)
	          .withMappingStrategy(mappingStrategy)
	                    .withQuotechar(CSVWriter.NO_QUOTE_CHARACTER)
	                    .build();
	 
	      beanToCsv.write(customers);
	    } catch (Exception e) {
	      System.out.println("Writing CSV error!");
	      e.printStackTrace();
	    }
	}
}

SpringBoot Using Apache Commons CSV to Read/Write data to CSV file

We create class OpenCsvUtil.java to read/write csv file with 2 methods:

  • parseCsvFile(InputStream is) is used to read csv file
  • customersToCsv(Writer writer, List customers) is used to write object list to csv writer
ApacheCommonsCsvUtils methods
ApacheCommonsCsvUtils methods
package com.loizenjava.updownloadcsv.utils;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.Writer;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVPrinter;
import org.apache.commons.csv.CSVRecord;
import org.springframework.web.multipart.MultipartFile;

import com.loizenjava.updownloadcsv.model.Customer;

public class ApacheCommonsCsvUtil {
	private static String csvExtension = "csv";
	
	public static void customersToCsv(Writer writer, List<Customer> customers) throws IOException {

		try (CSVPrinter csvPrinter = new CSVPrinter(writer,
				CSVFormat.DEFAULT.withHeader("id", "name", "address", "age"));) {
			for (Customer customer : customers) {
				List<String> data = Arrays.asList(String.valueOf(customer.getId()), customer.getName(),
						customer.getAddress(), String.valueOf(customer.getAge()));

				csvPrinter.printRecord(data);
			}
			csvPrinter.flush();
		} catch (Exception e) {
			System.out.println("Writing CSV error!");
			e.printStackTrace();
		}
	}

	public static List<Customer> parseCsvFile(InputStream is) {
		BufferedReader fileReader = null;
		CSVParser csvParser = null;

		List<Customer> customers = new ArrayList<Customer>();

		try {
			fileReader = new BufferedReader(new InputStreamReader(is, "UTF-8"));
			csvParser = new CSVParser(fileReader,
					CSVFormat.DEFAULT.withFirstRecordAsHeader().withIgnoreHeaderCase().withTrim());

			Iterable<CSVRecord> csvRecords = csvParser.getRecords();

			for (CSVRecord csvRecord : csvRecords) {
				Customer customer = new Customer(Long.parseLong(csvRecord.get("id")), csvRecord.get("name"),
						csvRecord.get("address"), Integer.parseInt(csvRecord.get("age")));

				customers.add(customer);
			}

		} catch (Exception e) {
			System.out.println("Reading CSV Error!");
			e.printStackTrace();
		} finally {
			try {
				fileReader.close();
				csvParser.close();
			} catch (IOException e) {
				System.out.println("Closing fileReader/csvParser Error!");
				e.printStackTrace();
			}
		}

		return customers;
	}
	
	public static boolean isCSVFile(MultipartFile file) {
		String extension = file.getOriginalFilename().split("\\.")[1];
		
		if(!extension.equals(csvExtension)) {
			return false;
		}
		
		return true;
	}

}

SpringBoot Define Spring JPA repository

In the tutorial “Upload and Read CSV File in Spring Boot”, to do CRUD operations with MySQL/PostgreSQL database, we define a simple CustomerRepository interface that extends the org.springframework.data.repository.CrudRepository:

package com.loizenjava.updownloadcsv.repository;

import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import com.loizenjava.updownloadcsv.model.Customer;


/**
 * 
 * Copyright by https://loizenjava.com
 * @author loizenjava.com
 *
 */
@Repository
public interface CustomerRepository extends CrudRepository<Customer, Long>{
}

See related article: How to integrate SpringBoot 2.x with PostgreSQL database using Spring JPA

Implement a CSV File Service – Upload and Read CSV File in Spring Boot

In the tutorial “Upload and Read CSV File in Spring Boot”, We create a CSV File Service CsvFileServices to do 2 tasks:

  • Store data to database with supported API: store(MultipartFile file)
  • Load data from database to a CSV file with supported API ByteArrayInputStream loadFile()

CsvFileServices class uses the Spring JPA repository CustomerRepository to interact with database and use the utility functions of CsvUtils class to manipulate data with Csv files.

Here is coding details:

package com.loizenjava.updownloadcsv.service;

import java.io.IOException;
import java.io.InputStream;
import java.io.Writer;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.loizenjava.updownloadcsv.model.Customer;
import com.loizenjava.updownloadcsv.repository.CustomerRepository;
import com.loizenjava.updownloadcsv.utils.ApacheCommonsCsvUtil;

/**
 * 
 * Copyright https://loizenjava.com
 * @author loizenjava.com
 */
@Service
public class CsvFileServices {
	
	@Autowired
	CustomerRepository customerRepository;

	// Store Csv File's data to database
	public void store(InputStream file) {
		try {
			// Using ApacheCommons Csv Utils to parse CSV file
			List<Customer> lstCustomers = ApacheCommonsCsvUtil.parseCsvFile(file);
			
			// Using OpenCSV Utils to parse CSV file
			// List<Customer> lstCustomers = OpenCsvUtil.parseCsvFile(file);
			
			// Save customers to database
			customerRepository.saveAll(lstCustomers);
		} catch(Exception e) {
			throw new RuntimeException("FAIL! -> message = " + e.getMessage());
		}
	}
	
	// Load Data to CSV File
    public void loadFile(Writer writer) throws IOException {
    	try {
        	List<Customer> customers = (List<Customer>) customerRepository.findAll();
        	
        	// Using ApacheCommons Csv Utils to write Customer List objects to a Writer
             ApacheCommonsCsvUtil.customersToCsv(writer, customers);
        	
        	// Using Open CSV Utils to write Customer List objects to a Writer
        	// OpenCsvUtil.customersToCsv(writer, customers);    		
    	} catch(Exception e) {
    		throw new RuntimeException("Fail! -> Message = " + e.getMessage());
    	}
    }
}

* Note: org.springframework.stereotype.Service indicates that an annotated class is a Service

Implement SpringBoot Upload CSV files RestApi – Upload and Read CSV File in Spring Boot

We create a UploadCsvRestApi.java controller class to do upload a single and multiple csv files with 2 api methods:

Upload CSV Restapi methods
Upload CSV Restapi methods

Springboot Upload single CSV file

We create a posting API /api/upload/csv/single for uploading a single Csv file, and here is a signature detail:

@PostMapping("/single")
public Response uploadSingleCSVFile(@RequestParam("csvfile") MultipartFile csvfile) {
...
}

The function uploadSingleCSVFile does the 2 main taks:

  • Validated task: check name and type of requested file before processing
  • Store CSV file data to database
@PostMapping("/single")
public Response uploadSingleCSVFile(@RequestParam("csvfile") MultipartFile csvfile) {

	Response response = new Response();

	// Checking the upload-file's name before processing
	if (csvfile.getOriginalFilename().isEmpty()) {
		response.addMessage(new Message(csvfile.getOriginalFilename(),
				"No selected file to upload! Please do the checking", "fail"));

		return response;
	}

	// checking the upload file's type is CSV or NOT
	
	if(!ApacheCommonsCsvUtil.isCSVFile(csvfile)) { 
	    response.addMessage(new Message(csvfile.getOriginalFilename(), "Error: this is not a CSV file!", "fail")); 
        return response; 
	}
	  
	 
	try {
		// save file data to database
		csvFileServices.store(csvfile.getInputStream());
		response.addMessage(new Message(csvfile.getOriginalFilename(), "Upload File Successfully!", "ok"));
	} catch (Exception e) {
		response.addMessage(new Message(csvfile.getOriginalFilename(), e.getMessage(), "fail"));
	}

	return response;
}

SpringBoot Upload multiple CSV files – Upload and Read CSV File in Spring Boot

We create a posting API /api/upload/csv/multiple for uploading multiple Csv files, and here is a signature detail:

@PostMapping("/multiple")
public Response uploadMultipleFiles(@RequestParam("csvfiles") MultipartFile[] csvfiles) {
...
}

The function uploadMultipleFiles does the 2 main taks:

  • Validated task: check names and type of all requested files before processing them
  • Store Csv files’ data to database
@PostMapping("/multiple")
public Response uploadMultipleFiles(@RequestParam("csvfiles") MultipartFile[] csvfiles) {

	Response response = new Response();
	/*
	 * Filtering files had been selected for uploading (the files having names)
	 */
	MultipartFile[] readyUploadedFiles = Arrays.stream(csvfiles)
			.filter(x -> !StringUtils.isEmpty(x.getOriginalFilename())).toArray(MultipartFile[]::new);

	/*
	 * Checking whether having at least one file had been selected for uploading
	 */
	if (readyUploadedFiles.length == 0) {
		response.addMessage(new Message("", "No selected file to upload!", "fail"));
		return response;
	}

	/*
	 * Checking uploaded files are CSV files or NOT
	 */

	String notCsvFiles = Arrays.stream(csvfiles).filter(x -> !ApacheCommonsCsvUtil.isCSVFile(x))
							 	.map(x -> x.getOriginalFilename()).collect(Collectors.joining(" , "));

	if (!StringUtils.isEmpty(notCsvFiles)) {
		response.addMessage(new Message(notCsvFiles, "Not Csv Files", "fail"));
		return response;
	}
	 
	/*
	 * Do the uploading
	 */
	for (MultipartFile file : readyUploadedFiles) {
		try {
			csvFileServices.store(file.getInputStream());
			response.addMessage(new Message(file.getOriginalFilename(), "Upload Successfully!", "ok"));
		} catch (Exception e) {
			response.addMessage(new Message(file.getOriginalFilename(), e.getMessage(), "fail"));
		}
	}

	return response;
}

The UploadCsvRestApi uses the CsvFileServices service to store Csv files’ data to database.

@RestController is a convenience annotation that is itself annotated with @Controller and @ResponseBody

The returned class Response contains a set of necessary information to return back to requested client.

response message upload download file
Response Message Upload Download File

Here is coding details of Response class:

package com.loizenjava.updownloadcsv.message;

import java.util.ArrayList;
import java.util.List;

/**
 * Copyright by https://loizenjava.com
 * @author loizenjava.com
 *
 */

public class Response {
	private List<Message> messages = null;
	private List<FileInfo> fileInfos = null;
	private Error error = null;
	private String errStatus = "";
	
	public Response() {
		this.messages = new ArrayList<Message>();
	}
	
	public Response(List<FileInfo> fileInfos) {
		this.fileInfos = fileInfos; 
	}
	
	public Response(String errStatus, Error err) {
		this.errStatus = errStatus;
		this.error = err;
	}

	public void addFileInfo(FileInfo file) {
		this.fileInfos.add(file);
	}

Implement SpringBoot Download CSV file RestApi

We implement a rest controller DownloadCsvRestAPI having a http GET downloadFile() API method with URL /api/download/csv/ to load all entities from database and serve them as a Csv file back to requested client.

DownloadCsvRestAPI controller
DownloadCsvRestAPI controller

Here is coding:

package com.loizenjava.updownloadcsv.controller;

import java.io.IOException;

import javax.servlet.http.HttpServletResponse;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import com.loizenjava.updownloadcsv.service.CsvFileServices;

/**
 * Copyright https://loizenjava.com
 * @author loizenjava.com
 *
 */
@RestController
public class DownloadCsvRestApi {
	
	@Autowired
	CsvFileServices csvFileService;

	/*
	 * Download CSV Files
	 */
	@GetMapping("/api/download/csv/")
	public void downloadFile(HttpServletResponse response) throws IOException {
		response.setContentType("text/csv");
		response.setHeader("Content-Disposition", "attachment; filename=customers.csv");
		csvFileService.loadFile(response.getWriter());
	}
}

With above coding, the API returns a file that has name is customers.csv, type is text/csv (CSV file type) and status code is 200 (.ok()) for successfully processing.

SpringBoot Configure MySQL/PostgreSQL database connection

In the SpringBoot project “Upload and Read CSV File in Spring Boot”, we need interact with database to do CRUD operation to save/retrieve data from CSV file to database, so now we need to add database configuration in application.properties file.

– For PostgreSQL connection, you follow below configuration formatting:


## PostgreSQL
spring.datasource.url=jdbc:postgresql://localhost:5432/loizenjavadb
spring.datasource.username=postgres
spring.datasource.password=123
#drop & create table again, good for testing, comment this in production
spring.jpa.hibernate.ddl-auto=create

– For MySQL connection, you follow below configuration formatting:


spring.datasource.url=jdbc:mysql://localhost:3306/loizenjavadb
spring.datasource.username=root
spring.datasource.password=12345
spring.jpa.generate-ddl=true

Test SpringBoot Upload Download RestAPIs with Rest-Client

We use Advanced Rest Client to test the Upload/Download RestAPIs.
For testing, we prepare 3 Csv files as below content:

csv file 1
csv file 1
file csv 2
file csv 2
file csv 3
file csv 3

1. SpringBoot Upload a Single CSV File:

We create a POST request to URL http://localhost:8080/api/upload/csv/single that has a body with multipart/form-data content type and includes 1 Csv file as below:

Test case 1 - Upload single csv file
Test case 1 – Upload single csv file

Run the “SpringBoot Upload Download CSV Files” project then send the POST request, we get a response successfully as below details:

Test case 1 - Upload single csv file - Response successfully
Test case 1 – Upload single csv file – Response successfully

Checking database, we get a customer table with 5 records as below:

Test case 1 - Upload single csv file - MySQL records
Test case 1 – Upload single csv file – MySQL records

2. SpringBoot Upload Multiple CSV Files:

We create a POST request to URL http://localhost:8080/api/upload/csv/multiple that has a body with multipart/form-data content type and includes 2 Csv files as below:

Test case 2 - Upload Multiple Csv files
Test case 2 – Upload Multiple Csv files

We get a response successfully as below details:

Test case 2 - Upload Multiple Csv Files - Response Successfully
Test case 2 – Upload Multiple Csv Files – Response Successfully

Checking MySQL/PostgreSQL database, Now customer table has 15 records as below:

Test case 2 - Upload Multiple Csv Files - MySQL records
Test case 2 – Upload Multiple Csv Files – MySQL records

3. SpringBoot Download Data as a Single CSV file:

Do a GET request to retrieve a CSV file from URL http://localhost:8080/api/download/csv/:

Test case 3 - Get a Single Csv File
Test case 3 – Get a Single Csv File

4. SpringBoot test a big CSV File Uploading:

We do a big CSV file uploading to SpringBoot RestApi:

Do-a-big-CSV-file-uploading-to-SpringBoot
Do-a-big-CSV-file-uploading-to-SpringBoot

We get a 500 error response with an exception message throwed from SpringBoot server:

org.apache.tomcat.util.http.fileupload.impl.SizeLimitExceededException: the request was rejected because its size (45422988) exceeds the configured maximum (10485760)
	at org.apache.tomcat.util.http.fileupload.impl.FileItemIteratorImpl.init(FileItemIteratorImpl.java:150) ~[tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.tomcat.util.http.fileupload.impl.FileItemIteratorImpl.getMultiPartStream(FileItemIteratorImpl.java:194) ~[tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.tomcat.util.http.fileupload.impl.FileItemIteratorImpl.findNextItem(FileItemIteratorImpl.java:213) ~[tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.tomcat.util.http.fileupload.impl.FileItemIteratorImpl.(FileItemIteratorImpl.java:131) ~[tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.tomcat.util.http.fileupload.FileUploadBase.getItemIterator(FileUploadBase.java:255) ~[tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.tomcat.util.http.fileupload.FileUploadBase.parseRequest(FileUploadBase.java:279) ~[tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.catalina.connector.Request.parseParts(Request.java:2870) ~[tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.catalina.connector.Request.getParts(Request.java:2772) ~[tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.catalina.connector.RequestFacade.getParts(RequestFacade.java:1098) ~[tomcat-embed-core-9.0.33.jar:9.0.33]

This is a Maximum upload size exceeded exception. How to catch and handle the exception? We go to the next session for a right solution.

Implement SpringBoot Rest Exception Handler – Upload and Read CSV File in Spring Boot Tutorial

For handling the Maximum upload size exceeded exception, we create a RestExceptionHandler class that extends the ResponseEntityExceptionHandler class.

package com.loizenjava.updownloadcsv.errorhandler;

import javax.servlet.http.HttpServletRequest;

import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.ControllerAdvice;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartException;
import org.springframework.web.servlet.mvc.method.annotation.ResponseEntityExceptionHandler;

import com.loizenjava.updownloadcsv.message.Error;
import com.loizenjava.updownloadcsv.message.Response;

/**
 * 
 * Copyright by https://loizenjava.com
 * @author loizenjava.com
 *
 */

@ControllerAdvice
public class RestExceptionHandler extends ResponseEntityExceptionHandler {

    // Catch file size exceeded exception!
    @SuppressWarnings({ "rawtypes", "unchecked" })
	@ExceptionHandler(MultipartException.class)
    @ResponseBody
    ResponseEntity<Response> handleControllerException(HttpServletRequest request, Throwable ex) {
        HttpStatus status = getStatus(request);
        Error err = new Error("0x123", ex.getMessage());
        Response res = new Response("error", err);
        
        return new ResponseEntity(res, status);
    }

    private HttpStatus getStatus(HttpServletRequest request) {
        Integer statusCode = (Integer) request.getAttribute("javax.servlet.error.status_code");
        if (statusCode == null) {
            return HttpStatus.INTERNAL_SERVER_ERROR;
        }
        return HttpStatus.valueOf(statusCode);
    }
}

org.springframework.web.bind.annotation.ControllerAdvice is a specialization of @Component for classes that declare @ExceptionHandler, @InitBinder, or @ModelAttribute methods to be shared across multiple @Controller classes.

org.springframework.web.servlet.mvc.method.annotation.ResponseEntityExceptionHandler is a convenient base class for @ControllerAdvice classes that wish to provide centralized exception handling across all @RequestMapping methods through @ExceptionHandler methods. This base class provides an @ExceptionHandler method for handling internal Spring MVC exceptions. This method returns a ResponseEntity for writing to the response with a message converter. (refer Spring docs)

SpringBoot Tuning File Upload Limits

We can tune Spring Boot’s auto-configured MultipartConfigElement with property settings. We add the following properties to your existing properties settings (in application.properties file):

spring.servlet.multipart.max-file-size=4096KB
spring.servlet.multipart.max-request-size=4096KB

The multipart settings are constrained as follows:

  • spring.http.multipart.max-file-size is set to 4096KB, meaning total file size cannot exceed 4096KB.
  • spring.http.multipart.max-request-size is set to 4096KB, meaning total request size for a multipart/form-data cannot exceed 4096KB.

Implement Upload Download Client Html Form

I use Bootstrap framework to create Html view for uploadload and download CSV files as below structure:

Upload CSV Form Data
Upload CSV Form Data

The view clearly has 3 difference row:

  • the first row includes a html form for uploading a single csv file
  • the second row includes a html form for uploading multiple csv files
  • the final row includes a link used to download a csv files

Here is coding:

<!DOCTYPE html>
<html lang="en">
<head>
<title>Upload Download CSV File Example</title>
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.min.js"></script>
  <script src="/js/ajaxupdownloadcsv.js"></script>
</head>
<body>
	<div class="container">
		<div class="row">
			<div class="col-sm-7" style="background-color:#e6fffa; padding:10px; border-radius:3px">
				<h3>Upload Single CSV File</h3>
				<form id="uploadSingleFileForm">
					<div class="form-group">
						<label class="control-label" for="csvfile">Choose a Csv File:</label>
						<input type="file" class="form-control" 
								placeholder="Choose a upload file" id="csvfile" name="csvfile" required></input>
					</div>
					<button type="submit" class="btn btn-danger" id="btnUploadSingleFileSubmit">Submit</button>
				</form>
				<div id="response" style="display:none">
				</div>
			</div>
		</div>
		<hr>		
		<div class="row">
			<div class="col-sm-7" style="background-color:#e6fffa; padding:10px; border-radius:3px">
				<h3>Upload Multiple Files</h3>
				<form id="uploadMultipleFilesForm">
					<div class="form-group">
						<label class="control-label" for="csvfiles">Choose CSV Files:</label>
						<input type="file" class="form-control" 
								placeholder="Choose upload files" id="csvfiles" name="csvfiles" multiple required></input>
					</div>
					<button type="submit" class="btn btn-danger" id="btnUploadMultipleFilesSubmit">Submit</button>
				</form>
				<div id="responses" style="display:none">
				</div>
			</div>
			
		</div>
		<hr>
		<div class="row">
			<div class="col-sm-7">
				<a href="/api/download/csv/" class="btn btn-primary" role="button" id="downloadcsvfile">Download CSV File</a>
			</div>
		</div>
	</div>
</body>
</html>

We save the .html file in static folder under src/main/resources folder of SpringBoot project as index.html name.

Implement Ajax Client Upload CSV files

We continue to implement an Ajax script to upload the data-form:

/**
 * Copyright by https://loizenjava.com
 * Author: loizenjava.com 
 */

$(document).ready(function() {
	
	/**
	 * Upload single file to SpringBoot 
	 * at RestAPI: /api/upload/csv/single
	 */
	$("#uploadSingleFileForm").submit(function(evt) {
		evt.preventDefault();
		
		var formData = new FormData($(this)[0]);
		
		$.ajax({
			url : '/api/upload/csv/single',
			type : 'POST',
			data : formData,
			async : false,
			cache : false,
			contentType : false,
			enctype : 'multipart/form-data',
			processData : false,
			success : function(response) {
				$("#response").empty();
				if(response.errStatus !== "error"){
					var displayInfo = response.messages[0].filename + " : " + response.messages[0].message + "<br>"; 
					
					$("#response").append(displayInfo);
					// add some css
					$("#response").css("display", "block");
					$("#response").css("background-color", "#e6e6ff");
					$("#response").css("border", "solid 1px black");
					$("#response").css("border-radius", "3px");
					$("#response").css("margin", "10px");
					$("#response").css("padding", "10px");
				}else{
					$("#response").css("display", "none");
					var error = response.error.errDesc;
					alert(error);
				}
			},
			error: function(e){
				alert("Fail! " + e);
			}
		});
		
		return false;
	});
	

	/**
	 * Upload single file to SpringBoot 
	 * at RestAPI: /api/upload/csv/multiple
	 */
	$("#uploadMultipleFilesForm").submit(function(evt) {
		evt.preventDefault();
		
		var formData = new FormData($(this)[0]);
		
		$.ajax({
			url : '/api/upload/csv/multiple',
			type : 'POST',
			data : formData,
			async : false,
			cache : false,
			contentType : false,
			enctype : 'multipart/form-data',
			processData : false,
			success : function(response) {
				
				$("#responses").empty();
				if(response.errStatus !== "error"){
					
					var displayInfo = "<ul>";
					
					for(var i=0; i<response.messages.length; i++){
						
						displayInfo += "<li>" + response.messages[i].filename 
											+ "&nbsp; : &nbsp;" + response.messages[i].message
											+ "</li>";
					}
					$("#responses").append(displayInfo + "</ul>");
					$("#responses").css("display", "block");
					
					// add some css
					$("#responses").css("background-color", "#e6e6ff");
					$("#responses").css("border", "solid 1px black");
					$("#responses").css("border-radius", "3px");
					$("#responses").css("margin", "10px");
					$("#responses").css("padding", "10px");
				}else{
					$("#responses").css("display", "none");
					var error = response.error.errDesc;
					alert(error);
				}
			},
			error: function(e){
				alert("Fail! " + e);
			}
		});
		
		return false;
	});
})

The above script has 2 main functions:

  • $("#uploadSingleFileForm").submit(function(evt) is used to upload a single csv file
  • $("#uploadMultipleFilesForm").submit(function(evt) is used to upload multiple csv files

Integrative Testing SpringBoot Upload Download CSV files with Ajax Client

1. Testcase – Upload Single CSV file:

Testcase - Upload Single CSV file - Web Form
Testcase – Upload Single CSV file – Web Form

Network log:

Testcase - Upload Single CSV file
Testcase – Upload Single CSV file

Testcase – Upload Multiple CSV files

Testcase - Upload Multiple CSV files - Web Form
Testcase – Upload Multiple CSV files – Web Form

Network log:

Testcase - Upload Multiple CSV files - Network Log
Testcase – Upload Multiple CSV files – Network Log

2. Testcase – Download CSV file:

Testcase - Download a CSV file
Testcase – Download a CSV file

Network Logs:

Testcase - Download Csv file - Network Log
Testcase – Download Csv file – Network Log

Sourcecode – Upload and Read CSV File in Spring Boot

All features of sourcecode for the tutorial SpringBoot RestAPIs Upload Download Multiple CSV files to MySQL/PostgreSQL with Ajax + RestClient:

  • Backend
    • Upload Single CSV File RestAPI
    • Upload Multiple CSV Files RestAPI
    • Download all data as a single CSV file
  • Frontend
    • Html Upload Single File Form
    • Html Upload Multiple Files Form
    • JQuery Ajax to download all data as a single CSV file

SpringBootUpDownloadCsvMySQL

– GitHub Sourcecode for “SpringBoot Upload Download CSV files to MySQL/PostgreSQL”:

SpringBoot CSV Upload Download csv-Files – GitHub Sourcecode

Further Reading – Upload and Read CSV File in Spring Boot

Related posts:


Leave a Reply

Your email address will not be published. Required fields are marked *