CSV File – Upload/Download using Apache Commons-CSV + SpringBoot RestAPIs + Spring JPA + Thymeleaf to MySQL

In the post, Grokonez guides how to Upload/Download CSV Files to MySQL database using SpringBoot + Spring JPA with Apache Commons-CSV library

Technologies

– Spring Boot – 2.0.6.RELEASE
– Spring JPA
– Thymeleaf
– MySQL
– Apache Commons-CSV

Goal

We create a SpringBoot project as below:

-> CSV File:

-> Results:

-> MySQL:

Practice

Create SpringBoot project

We create a SpringBoot project with below dependencies:

	org.springframework.boot
	spring-boot-starter-thymeleaf


	org.springframework.boot
	spring-boot-starter-web



	org.apache.commons
	commons-csv
	1.5



	org.springframework.boot
	spring-boot-starter-data-jpa



	mysql
	mysql-connector-java

Customer Model

– Create Customer.java model:

package com.ozenero.csvfile.model;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "customer")
public class Customer {
	
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private long id;
	
	@Column(name = "name")
	private String name;
	
	@Column(name = "address")
	private String address;
	
	@Column(name = "age")
	private int age;
 
	public Customer() {
	}
 
	public Customer(long id, String name, String address, int age) {
		this.id = id;
		this.name = name;
		this.address = address;
		this.age = age;
	}
 
	public long getId() {
		return id;
	}
 
	public void setId(long id) {
		this.id = id;
	}
 
	public String getName() {
		return name;
	}
 
	public void setName(String name) {
		this.name = name;
	}
 
	public String getAddress() {
		return address;
	}
 
	public void setAddress(String address) {
		this.address = address;
	}
 
	public int getAge() {
		return age;
	}
 
	public void setAge(int age) {
		this.age = age;
	}
 
	@Override
	public String toString() {
		return "Customer [id=" + id + ", name=" + name + ", address=" + address + ", age=" + age + "]";
	}
 
}

JPA Repository

– Create CustomerRepository.java:

package com.ozenero.csvfile.repository;

import org.springframework.data.repository.CrudRepository;

import com.ozenero.csvfile.model.Customer;

public interface CustomerRepository extends CrudRepository{
}

CSV Utils

– Implement CsvUtils.java:

package com.ozenero.csvfile.utils;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.PrintWriter;
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 com.ozenero.csvfile.model.Customer;

public class CsvUtils {
	
	public static void customersToCsv(PrintWriter writer, List customers) throws IOException {
		
		try (
				CSVPrinter csvPrinter = new CSVPrinter(writer, CSVFormat.DEFAULT
	                    .withHeader("id", "name", "address", "age"));
		) {
			for (Customer customer : customers) {
				List 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 parseCsvFile(InputStream is) {
		BufferedReader fileReader = null;
		CSVParser csvParser = null;

		List customers = new ArrayList();
		
		try {
			fileReader = new BufferedReader(new InputStreamReader(is, "UTF-8"));
			csvParser = new CSVParser(fileReader,
					CSVFormat.DEFAULT.withFirstRecordAsHeader().withIgnoreHeaderCase().withTrim());
 
			Iterable 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;
	}
}

RestAPIs Controller

Upload Controller

– Implement UploadFileController.java:

package com.ozenero.csvfile.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;

import com.ozenero.csvfile.fileservice.FileServices;

@Controller
public class UploadFileController {
	
	@Autowired
	FileServices fileServices;
	
    @GetMapping("/")
    public String index() {
        return "multipartfile/uploadform.html";
    }
    
    @PostMapping("/")
    public String uploadMultipartFile(@RequestParam("uploadfile") MultipartFile file, Model model) {
		try {
			fileServices.store(file);
			model.addAttribute("message", "File uploaded successfully!");
		} catch (Exception e) {
			model.addAttribute("message", "Fail! -> uploaded filename: " + file.getOriginalFilename());
		}
        return "multipartfile/uploadform.html";
    }
}

Download Controller

– Implement DownloadFileController.java file:

package com.ozenero.csvfile.controller;

import java.io.IOException;

import javax.servlet.http.HttpServletResponse;

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

import com.ozenero.csvfile.fileservice.FileServices;

@Controller
public class DownloadFileController {

	@Autowired
	FileServices fileServices;

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

Viewers

– Implement upload/download viewers:

    
    
    
	
	
	
	

 

	

	

		

			

				
Upload CSV File to MySQL
				

					

						Upload File:
						
					
					Upload
				
				

					
					Download CSV File from MySQL
				
			
		
	
	

Application Config

– In application.properties, add configuration:

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

SourceCode

– CSV File as below format:

– SourceCode: SpringBootUploadDownloadCSVFile