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

upload-download-csv-file-thymeleaf-springboot-spring-jpa-apache-commons-csv-mysql---feature-image

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:

upload-download-csv-file-thymeleaf-springboot-spring-jpa-apache-commons-csv-mysql---project-structure

-> CSV File:

upload-download-csv-file-thymeleaf-springboot-spring-jpa-apache-commons-csv-mysql---csv-file

-> Results:

upload-download-csv-file-thymeleaf-springboot-spring-jpa-apache-commons-csv-mysql---front-end

-> MySQL:

upload-download-csv-file-thymeleaf-springboot-spring-jpa-apache-commons-csv-mysql---mysql-records

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 CSV File to 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:

upload-download-csv-file-thymeleaf-springboot-spring-jpa-apache-commons-csv-mysql---csv-file

– SourceCode: SpringBootUploadDownloadCSVFile

8 thoughts on “CSV File – Upload/Download using Apache Commons-CSV + SpringBoot RestAPIs + Spring JPA + Thymeleaf to MySQL”

  1. 728289 204724Spot lets start function on this write-up, I in fact believe this remarkable internet site requirements additional consideration. Ill far more likely be once once again you just read additional, thank you that data. 46754

  2. 536789 211884Excellent post nevertheless , I was wanting to know in case you could write a litte much more on this subject? Id be really thankful should you could elaborate a bit bit further. Bless you! 725457

  3. Write more, thats all I have to say. Literally, it
    seems as though you relied on the video to make your point.

    You obviously know what youre talking about, why
    throw away your intelligence on just posting videos to your weblog when you
    could be giving us something enlightening to read?

  4. I’m really impressed with your writing skills and also with
    the layout on your blog. Is this a paid theme or did you modify it yourself?
    Anyway keep up the nice quality writing, it is rare to see a nice blog like
    this one these days.

Leave a Reply

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