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, Listcustomers) 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:
– SourceCode: SpringBootUploadDownloadCSVFile