Uploading Data Into Mysql Is Taking a Long Time

Many clients provide their data in the form of excel canvas and request programmer to save information technology in your database every bit it is . If y'all e'er have implemented 'How to Upload excel data into Database using Spring Boot', it'due south practiced. If not, no worries!, we will acquire the same in this commodity. In the end, yous will discover it very easy & fast way to get it done. In fact, in one case yous go through the consummate article yous will observe that you only need to provide the location of your excel file in awarding.properties file . Y'all volition go the data uploaded into database by making small changes in your own entity classes only.

We volition not promote any hard-coding in our implementation. Even if you are working kickoff time in Spring Boot webapp, you volition easily implement the functionality by following the steps mentioned hither. Further to escape hard-coding we volition take some of the values from properties file so that we don't modify the java file in example we have any change request in future. Besides, we will use the virtually popular Apache POI: The Java API for Microsoft Documents in our implementation. Now let's get into the topic "How to Upload Excel information into Database Using Spring Kicking".

Table of Contents (Click on links below to navigate)

ane What all functionality/features volition yous get from this article?
2 What will you learn subsequently implementing this application?
3 What all implementations can you exercise yourself after going through this example?
four Software Used in this project
- 4.ane External Dependencies
5 Prerequisites
vi Coding Steps
- 6.one — Create Project in STS
  - 6.i.ane Where to place JSP files ?
- 6.two — Writing Coffee classes, JSPs & Updating awarding.properties
  - half dozen.ii.1 InvoiceController.coffee
  - 6.2.2 Invoice.java
  - half-dozen.2.3 InvoiceRepository.java
  - six.2.4 IExcelDataService.java
  - 6.ii.5 IFileUploaderService.coffee
  - half-dozen.2.6 ExcelDataServiceImpl.java
  - vi.ii.7 FileUploaderServiceImpl.java
  - 6.2.eight application.backdrop
  - 6.2.9 uploadPage.jsp
  - 6.two.x success.jsp
vii Running application
8 Testing Results
9 How to do our own implementation from this Example with minimal changes?
10 Can we use this implementation in a existent project ?
11 What are the changes do we need to exercise if we want to use this functionality in our real project ?
12 Conclusion

What all functionality/features will you get from this article?

1) How to save data into the database if input information is in the form of excel sheet.
2) How to upload any file (apart from excel) using a web browser.
three) Higher up ii functionalities are not interdependent. We can execute each functionality separately.
3) Apache POI Latest version (currently 4.one.2) has been used to implement the project.
iv) Additionally, code is successfully tested on JDK8, JDK9 and even JDK14 versions.
5) How to read data from excel canvass and insert into database tabular array in leap kick?

What volition yous larn after implementing this awarding?

1) How to create a Spring Kick spider web application that incorporates industry level project blueprint?
ii) How to design a Java web application, including all layers using Controller, Service, Repository, UI etc. as in real time project designs?
3) Equally important, Where and how to employ Annotations like @Value, @Autowired, @Controller, @Service, @Repository, @Entity, @Id, @GeneratedValue, @GetMapping, @PostMapping etc.
4) Also, Working with Spring Boot Information JPA repository interface.
5) Implementation of the near popular Apache POI (Java API for Microsoft Documents)
6) Then, How to work with application.properties file?

vii) How to upload data as a batch into database using Spring Boot?
8) Additionally, How to write modular & reusable code?
ix) How to implement dynamic code with minimal changes, keeping hereafter change requests in listen?
10) How to write code without hard-coding the values using Bound Boot?
11) Last just not the least, you will acquire "How to Upload Excel data into Database Using Spring Kick".
12) Particularly, How to read data from excel sheet and insert into database table in leap kick.

What all implementations can y'all do yourself afterwards going through this example?

one) How to read data from excel canvas and insert into database table in spring mvc?
ii) How to upload excel file to database using spring kicking?
3) leap boot excel file upload instance
4) How to read information from excel sheet and insert into database tabular array in spring kicking?
five) How to import data from excel to mysql using spring boot?
half dozen) How to upload excel file into database using java?

7) How to read excel file in jump boot example?
8) How to read data from excel and store in datatable in coffee?
9) How to upload and read excel file in spring kick?
x) How to write java lawmaking to read excel file and insert into database?
11) How to import excel information into database using java?
12) How to upload Excel file into database using Apache POI and Spring Framework?

Software Used in this projection

♦ STS (Spring Tool Suite): Version-> 4.7.1. RELEASE
⇒ Dependent Starters : Leap Spider web, Bound Data JPA, MySql Driver
♦ MySQL Database : Version ->8.0.19 MySQL Customs Server
♦ JDK8 or later versions (Extremely tested on JDK8, JDK9 and JDK14)

External Dependencies

We will add following apache 'poi-ooxml' dependency in pom.xml every bit information technology is an external jar to get the features of Microsoft Excel.

                      <dependency>                                              <groupId>org.apache.poi</groupId>                                              <artifactId>poi-ooxml</artifactId>                                              <version>4.1.2</version>                                </dependency>

Prerequisites

You should accept a database set up to store data from excel file. If not, create a MySQL database to save the information. Nonetheless, if yous just want to test the functionality you can utilise our configurations as database name 'exceldata' and table name 'invoice' accordingly.

Coding Steps

— Create Project in STS

If you are new in Bound Boot get to Internal Link to create a sample projection in spring boot. While creating a project in STS, add three starters 'MySql Driver', 'Jump Data JPA' and 'Leap Web'. You tin besides add 'Spring Boot DevTools' optionally.

Where to place JSP files ?

In order to conform JSP files,

1) create folder 'webapps' nether src/main/
2) under 'webapps' create folder 'WEB-INF'
3) Nether 'Web-INF' create folder 'pages' to conform your JSP files as view part.
four) Now your Binder structure hierarchy should look like "src/chief/webapps/WEB-INF/pages".

— Writing Coffee classes, JSPs & Updating application.backdrop

Package/Location	Form/Interface name	Create/Update	Purpose
com.dev.springboot.controller	InvoiceController.coffee	create	Controller grade
com.dev.springboot.entity	Invoice.java	create	Model/Entity class
com.dev.springboot.repository	InvoiceRepository.java	create	Repository Interface
com.dev.springboot.service	IExcelDataService.java	create	Service Interface for DB operations
com.dev.springboot.service	IFileUploaderService.java	create	Service Interface to upload any file
com.dev.springboot.service.impl	ExcelDataServiceImpl.java	create	Service implementation class for DB operations
com.dev.springboot.service.impl	FileUploaderServiceImpl.coffee	create	Service implementation grade to upload any file
src/main/resources	application.properties	update	backdrop file to declare common backdrop in the projection
src/main/webapp/Spider web-INF/pages	uploadPage.jsp	create	UI page to upload a file
src/main/webapp/Spider web-INF/pages	success.jsp	create	UI page to success message when information is saved into database

Beneath are the codes for each file

InvoiceController.java

            package com.dev.springboot.controller;  import java.util.Listing;  import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import org.springframework.ui.Model; import org.springframework.spider web.bind.annotation.GetMapping; import org.springframework.spider web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.multipart.MultipartFile; import org.springframework.spider web.servlet.mvc.support.RedirectAttributes;  import com.dev.springboot.entity.Invoice; import com.dev.springboot.repository.InvoiceRepository; import com.dev.springboot.service.IExcelDataService; import com.dev.springboot.service.IFileUploaderService;  @Controller public form InvoiceController { 	 	@Autowired 	IFileUploaderService fileService; 	 	@Autowired 	IExcelDataService excelservice; 	 	@Autowired 	InvoiceRepository repo; 	 	@GetMapping("/")     public String index() {         return "uploadPage";     }      @PostMapping("/uploadFile")     public String uploadFile(@RequestParam("file") MultipartFile file, RedirectAttributes redirectAttributes) {          fileService.uploadFile(file);          redirectAttributes.addFlashAttribute("bulletin",             "Y'all have successfully uploaded '"+ file.getOriginalFilename()+"' !");         try { 			Thread.sleep(3000); 		} catch (InterruptedException e) { 			// TODO Auto-generated grab block 			eastward.printStackTrace(); 		}         return "redirect:/";     }          @GetMapping("/saveData")     public String saveExcelData(Model model) {     	     	List<Invoice> excelDataAsList = excelservice.getExcelDataAsList();     	int noOfRecords = excelservice.saveExcelData(excelDataAsList);     	model.addAttribute("noOfRecords",noOfRecords);     	return "success";     } }

Invoice.coffee

Invoice.java

            package com.dev.springboot.entity;  import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.Id;  @Entity public form Invoice {  	@Id 	@GeneratedValue 	private Long id; 	individual String name; 	private Double amount; 	private String number; 	private String receivedDate; 	 	public Invoice() { 		 	} 	 	public Long getId() { 		return id; 	} 	public void setId(Long id) { 		this.id = id; 	} 	public String getName() { 		render proper noun; 	} 	public void setName(String name) { 		this.name = name; 	} 	public Double getAmount() { 		return amount; 	} 	public void setAmount(Double amount) { 		this.amount = amount; 	} 	public String getNumber() { 		return number; 	} 	public void setNumber(String number) { 		this.number = number; 	} 	public Cord getReceivedDate() { 		return receivedDate; 	} 	public void setReceivedDate(Cord receivedDate) { 		this.receivedDate = receivedDate; 	} 	 	public Invoice(Long id, String proper name, Double amount, String number, String receivedDate) { 		super(); 		this.id = id; 		this.name = proper noun; 		this.amount = corporeality; 		this.number = number; 		this.receivedDate = receivedDate; 	} }

InvoiceRepository.coffee

Repository form every bit InvoiceRepository.java

            bundle com.dev.springboot.repository;  import org.springframework.information.jpa.repository.JpaRepository;  import com.dev.springboot.entity.Invoice;  public interface InvoiceRepository extends JpaRepository<Invoice, Long> {  }

IExcelDataService.java

IExcelDataService.coffee

            parcel com.dev.springboot.service;  import java.util.List;  import com.dev.springboot.entity.Invoice;  public interface IExcelDataService {  	List<Invoice> getExcelDataAsList(); 	 	int saveExcelData(Listing<Invoice> invoices); }

IFileUploaderService.coffee

IFileUploaderService.java

            packet com.dev.springboot.service;  import org.springframework.web.multipart.MultipartFile;  public interface IFileUploaderService {  	public void uploadFile(MultipartFile file); }

ExcelDataServiceImpl.java

ExcelDataServiceImpl.coffee

            bundle com.dev.springboot.service.impl;  import java.io.File; import java.io.</yoastmark>IOException; import java.util.ArrayList; import java.util.List;  import org.apache.poi.EncryptedDocumentException; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DataFormatter; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.manufactory.notation.Value; import org.springframework.stereotype.Service;  import com.dev.springboot.entity.Invoice; import com.dev.springboot.repository.InvoiceRepository; import com.dev.springboot.service.IExcelDataService;  @Service public class ExcelDataServiceImpl implements IExcelDataService {  	@Value("${app.upload.file:${user.habitation}}") 	public String EXCEL_FILE_PATH;  	@Autowired 	InvoiceRepository repo;  	Workbook workbook;  	public List<Invoice> getExcelDataAsList() {  		List<String> list = new ArrayList<Cord>();  		// Create a DataFormatter to format and get each cell's value equally String 		DataFormatter dataFormatter = new DataFormatter();  		// Create the Workbook 		try { 			workbook = WorkbookFactory.create(new File(EXCEL_FILE_PATH)); 		} catch (EncryptedDocumentException | IOException e) { 			east.printStackTrace(); 		}  		// Retrieving the number of sheets in the Workbook 		System.out.println("-------Workbook has '" + workbook.getNumberOfSheets() + "' Sheets-----");  		// Getting the Sheet at alphabetize zero 		Sheet sheet = workbook.getSheetAt(0);  		// Getting number of columns in the Sheet 		int noOfColumns = sail.getRow(0).getLastCellNum(); 		Organisation.out.println("-------Sheet has '"+noOfColumns+"' columns------");  		// Using for-each loop to iterate over the rows and columns 		for (Row row : canvas) { 			for (Jail cell cell : row) { 				Cord cellValue = dataFormatter.formatCellValue(cell); 				list.add(cellValue); 			} 		}  		// filling excel information and creating listing as List<Invoice> 		List<Invoice> invList = createList(listing, noOfColumns);  		// Closing the workbook 		try { 			workbook.close(); 		} catch (IOException e) { 			// TODO Machine-generated catch block 			eastward.printStackTrace(); 		}  		return invList; 	}  	private List<Invoice> createList(List<String> excelData, int noOfColumns) {  		ArrayList<Invoice> invList = new ArrayList<Invoice>();  		int i = noOfColumns; 		do { 			Invoice inv = new Invoice();  			inv.setName(excelData.go(i)); 			inv.setAmount(Double.valueOf(excelData.become(i + i))); 			inv.setNumber(excelData.go(i + 2)); 			inv.setReceivedDate(excelData.become(i + 3));  			invList.add together(inv); 			i = i + (noOfColumns);  		} while (i < excelData.size()); 		render invList; 	}  	@Override 	public int saveExcelData(List<Invoice> invoices) { 		invoices = repo.saveAll(invoices); 		return invoices.size(); 	} }

FileUploaderServiceImpl.java

            package com.dev.springboot.service.impl;  import java.io.File; import coffee.nio.file.Files; import java.nio.file.Path; import java.nio.file.Paths; import java.nio.file.StandardCopyOption; import java.util.List;  import org.springframework.beans.factory.notation.Value; import org.springframework.stereotype.Service; import org.springframework.util.StringUtils; import org.springframework.web.multipart.MultipartFile;  import com.dev.springboot.entity.Invoice; import com.dev.springboot.service.IFileUploaderService;  @Service public class FileUploaderServiceImpl implements IFileUploaderService {  	 	public List<Invoice> invoiceExcelReaderService() { 		render zero; 	} 	 	@Value("${app.upload.dir:${user.home}}")     public Cord uploadDir;      public void uploadFile(MultipartFile file) {          try {             Path copyLocation = Paths                 .go(uploadDir + File.separator + StringUtils.cleanPath(file.getOriginalFilename()));             Files.copy(file.getInputStream(), copyLocation, StandardCopyOption.REPLACE_EXISTING);         } catch (Exception due east) {             e.printStackTrace();             throw new RuntimeException("Could not store file " + file.getOriginalFilename()                 + ". Please try again!");         }     } }

application.properties

                      #DB Connexion Backdrop                    spring.datasource.driver-course-name=com.mysql.cj.jdbc.Commuter                    spring.datasource.url=jdbc:mysql://localhost:3306/exceldata                    spring.datasource.username=root                    spring.datasource.password=devs                                # Data JPA Properties                    spring.jpa.database-platform=org.hibernate.dialect.MySQL8Dialect                    spring.jpa.show-sql=true                    spring.jpa.hide.ddl-machine=update                                # MVC view properties                    spring.mvc.view.prefix=/Web-INF/pages/                    leap.mvc.view.suffix=.jsp                                # upload file                    app.upload.dir=D:/ExcelUploadRepo                                # Excel file as input for storing it's data                    app.upload.file=D:/ExcelUploadRepo/InvoiceDetailsSheet.xlsx

uploadPage.jsp

            <%@ page linguistic communication="java" contentType="text/html; charset=ISO-8859-ane"     pageEncoding="ISO-8859-1"%> <!DOCTYPE html> <html>    <head>    <meta charset="ISO-8859-i">       <championship>File Upload Page</title>    </head>        <body>    	  <h2>File Upload & Data Salve Page</h2>    	         <course   activity ="uploadFile" method = "Post" enctype = "multipart/form-data">       <br /> <br />          Please select a file to upload :           <input type = "file" proper name = "file" value = "Scan File" /> <br /> <br />          Press hither to upload the file :          <input type = "submit" value = "upload" /> <br /> <br />                     <h4 way="colour: dark-green">${bulletin}</h4> <br />                     Do yous desire to salvage excel data into database ? <a href="saveData"><b>Yes</b></a> &nbsp &nbsp <a href="/"><b>No</b></a>       </class>    </body> </html>

success.jsp

            <%@ page contentType = "text/html; charset = UTF-viii" %> <html>    <head>       <title>Data Save Instance</title>    </head>    <body>            <b> Total ${noOfRecords} records uploaded !</b>     </body> </html>

One time all above files created in STS, your project construction should look something similar below screenshot:

img_33_excel1

Running application

In lodge to run the application, correct click on Project, and so select Run As >> Bound Boot App.

Testing Results

Finally, in order to exam the results paste url in your browser : http://localhost:8080/
Accordingly, you should see the below page :

img_33_excel2

Furthermore, in order to upload an excel file, click on 'Choose File' which volition browse to your file system, then select an excel file to exist uploaded and click 'OK/Open'. Now click on upload in order to upload the file at the location you provided in application.backdrop accordingly. Note: Also excel files, this functionality will also work for any file having other extensions. Consequently, once the file is uploaded, you will see the success message with file name you uploaded as beneath.

How to Upload Excel data into Database Using Spring Boot ?

In order to upload excel records into database click on 'Yes' link. Consequently, you will see the success page immediately with number of records uploaded into database. In contrast, if you click no, zip volition happen, it volition just redirect to the same page.

Uploading Excel data into Database Using Spring Boot

Furthermore, yous tin can check the saved records into database as below:

img_33_excel5
♥ Note: If you lot provide the input file proper name as a value of 'app.upload.file' in application.backdrop and put the excel file in the same folder, You can directly save the excel records into database just by clicking on the 'Yes' link. In this case, Even You don't have to upload the file.

How to do our own implementation from this Example with minimal changes?

In fact, you demand to change values of properties included in the awarding.properties file
1) Update the values of first 4 properties according to your database
two) Also update the file storage directory path in the value of property 'app.upload.dir'
3) So update the upload file path with file proper noun in the value of property 'app.upload.file'

Moreover, Below are the changes in java files :
4) To store excel records into database you need to create new Entity class, new Repository class in identify of 'Invoice.coffee' & 'InvoiceRepository.coffee' accordingly as per your requirements.
5) Consequently, you need to alter all the occurrences of entity form name in 'IExcelDataService.coffee' and 'ExcelDataServiceImpl.java'. It'southward 'Invoice' in our instance.
6) In addition, you need to update entity course method names in createList() method of 'ExcelDataServiceImpl.java'.

Can nosotros use this implementation in a existent projection ?

Of course, you can utilise this implementation in a existent project with modification described in a higher place equally per your requirement.

What are the changes do we need to exercise if we want to use this functionality in our real project ?

Yous tin go through the minimal modifications suggested in the higher up FAQ department 'How to practise our ain implementation from this Example with minimal changes?' of this article accordingly .

Determination

After going through all the points mentioned in the commodity, Yous should exist able to work on 'How to read data from excel sheet and insert into database tabular array in spring kick ?'. Withal, If you follow all the steps equally mentioned in the article, you will implement it successfully in one become without facing any event. Additionally, you lot will as well exist able to work on 'How to upload any file (autonomously from excel) using web browser' confidently. At present, we tin await from you lot that you volition employ this knowledge in your real fourth dimension project when required to exercise so. Furthermore, if you face whatever issue on running this project, don't hesitate to put your comments beneath.

♥ Also, if you lot want to learn 'How to generate dynamic PDF report using Spring Boot', Kindly visit internal commodity. Additionally, if you lot want to larn more on Leap Kicking trending topics kindly visit our blog.

thompsonbettandow1937.blogspot.com

Source: https://javatechonline.com/how-to-upload-excel-data-into-database-using-spring-boot/