We will not promote any hard-coding in our implementation. Even if you are working first time in Spring Boot application, you will easily implement the functionality by following the steps mentioned here. Further to escape hard-coding we will take some of the values from properties file so that we donโt need to modify the java file in case we have any change request in future. Also, we will use the most popular Apache POI: โThe Java API for Microsoft Documentsโ in our implementation. Now letโs get into the topic โHow to Upload Excel data into Database Using Spring Bootโ.
What all functionality/features will you get from this article?
1) How to save data into the database if input data is in the form of excel sheet.
2) How to upload any file (apart from excel) using a web browser.
3) Above two functionalities are not interdependent. We can execute each functionality separately.
4) Apache POI Latest version (currently 4.1.2) has been used to implement the project.
5) Additionally, code is successfully tested on JDK8, JDK9 and even JDK14 versions.
6) How to read data from excel sheet and insert into database table in spring boot?
What will you learn after implementing this application?
1) How to create a Spring Boot web application that incorporates industry level project design?
2) 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 use Annotations like @Value, @Autowired, @Controller, @Service, @Repository, @Entity, @Id, @GeneratedValue, @GetMapping, @PostMapping etc.
4) Also, Working with Spring Boot Data JPA repository interface.
5) Implementation of the most popular Apache POI (Java API for Microsoft Documents)
6) Then, How to work with application.properties file?
7) How to upload data as a batch into database using Spring Boot?
8) Additionally, How to write modular & reusable code?
9) How to implement dynamic code with minimal changes, keeping future change requests in mind?
10) How to write code without hard-coding the values using Spring Boot?
11) Last but not the least, you will learn โHow to Upload Excel data into Database Using Spring Bootโ.
12) Particularly, How to read data from excel sheet and insert into database table in spring boot.
What all implementations can you do yourself after going through this example?
1) How to read data from excel sheet and insert into database table in spring mvc?
2) How to upload excel file to database using spring boot?
3) spring boot excel file upload example
4) How to read data from excel sheet and insert into database table in spring boot?
5) How to import data from excel to mysql using spring boot?
6) How to upload excel file into database using java?
7) How to read excel file in spring boot example?
8) How to read data from excel and store in the datatable in java?
9) How to upload and read excel file in spring boot?
10) How to write java code to read excel file and insert into database?
11) How to import excel data into database using java?
12) How to upload Excel file into database using Apache POI and Spring Framework?
Software Used in this project
โฆ STS (Spring Tool Suite): Version-> 4.7.1. RELEASE
โ Dependent Starters : Spring Web, Spring Data JPA, MySql Driver
โฆ MySQL Database : Version ->8.0.19 MySQL Community 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 as it 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 have a database ready to store data from excel file. If not, create a MySQL database to save the data. Still, if you just want to test the functionality you can use our configurations as database name โexceldataโ and table name โinvoiceโ accordingly.
Coding Steps on โHow to Upload Excel data into Database Using Spring Boot ?โ
โ Create Project in STS
If you are new in Spring Boot go toย Internal Link to create a sample project in spring boot. While creating a project in STS, add 3 starters โMySql Driverโ, โSpring Data JPAโ and โSpring Webโ. You can also add โSpring Boot DevToolsโ optionally.
Where to place JSP files ?
In order to accommodate JSP files,
1) create folder โwebappsโ under src/main/
2) under โwebappsโ create folder โWEB-INFโ
3) Under โWEB-INFโ create folder โpagesโ to accommodate your JSP files as view part.
4) Now your Folder structure hierarchy should look like โsrc/main/webapps/WEB-INF/pagesโ.
โ Writing Java classes, JSPs & Updating application.properties
Package/Location | Class/Interface name | Create/Update | Purpose |
---|---|---|---|
com.dev.springboot.controller | InvoiceController.java | create | Controller class |
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.java | create | Service implementation class to upload any file |
src/main/resources | application.properties | update | properties file to declare common properties in the project |
src/main/webapp/WEB-INF/pages | uploadPage.jsp | create | UI page to upload a file |
src/main/webapp/WEB-INF/pages | success.jsp | create | UI page to success message when data is saved into database |
Below are the codes for each file
InvoiceController.java
Invoice.java
InvoiceRepository.java
IExcelDataService.java
IFileUploaderService.java
ExcelDataServiceImpl.java
FileUploaderServiceImpl.java
application.properties
#DB Connection Properties spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver 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.hibernate.ddl-auto=update # MVC view properties spring.mvc.view.prefix=/WEB-INF/pages/ spring.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
success.jsp
Once all above files created in STS, your project structure should look something like below screenshot:
.
Running application
In order to run the application, right click on Project, then select Run As >> Spring Boot App.
Testing Results
Finally, in order to test the results paste url in your browser : http://localhost:8080/
Accordingly, you should see the below page :
ย
Furthermore, in order to upload an excel file, click on โChoose Fileโ which will browse to your file system, then select an excel file to be uploaded and click โOK/Openโ. Now click on upload in order to upload the file at the location you provided in application.properties accordingly. Note: Besides 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 below.
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, nothing will happen, it will just redirect to the same page.
ย
Furthermore, you can check the saved records into database as below:
ย
โฅ Note: If you provide the input file name as a value of โapp.upload.fileโ in application.properties 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 need to change values of properties included in the application.properties file
1) Update the values of first 4 properties according to your database
2) Also update the file storage directory path in the value of property โapp.upload.dirโ
3) Then update the upload file path with file name 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 place of โInvoice.javaโ & โInvoiceRepository.javaโ accordingly as per your requirements.
5) Consequently, you need to change all the occurrences of entity class name in โIExcelDataService.javaโ and โExcelDataServiceImpl.javaโ. Itโs โInvoiceโ in our case.
6) In addition, you need to update entity class method names in createList() method of โExcelDataServiceImpl.javaโ.
Can we use this implementation in a real project ?
Of course, you can use this implementation in a real project with modification described above as per your requirement.
What are the changes we need to do if we want to use this functionality in our real project?
You can go through the minimal modifications suggested in the above FAQ section โHow to do our own implementation from this Example with minimal changes?โ of this article accordingly.
Conclusion
After going through all the points mentioned in the article, You should be able to work on โHow to read data from excel sheet and insert into database table in spring boot ?โ. However, If you follow all the steps as mentioned in the article, you will implement it successfully in one go without facing any issue. Additionally, you will also be able to work on โHow to upload any file (apart from excel) using web browserโ confidently. Now, we can expect from you to apply this knowledge in your real time project whenever you have such kind of requirement. Furthermore, if you face any issue on running this project, donโt hesitate to put your comments below.
โฅ Also, if you want to learn how to generate PDF report using Spring Boot, kindly visit our separate article on โHow to generate dynamic PDF report using Spring Bootโ. Additionally, if you want to learn more on Spring Boot trending topics kindly visit our Spring Boot Section.
hey !
Thanks your tutorial has been very useful during my project work .I just have one doubt โฆ..My fields are not inserted at correct place .Could you help me with that?
@Jinal: Could you please elaborate your issue?
Hello,
Many thanks for this great guide on how to upload excel data into a db. I follow it through it work perfectly.
Could you kindly also explain, or point me to an example of, how to do the same using Spring WebFlux instead of spring web?
Thanks! Your tutorial is really good.
Thanks Davinder Sir,
I Always learn new things from your posts.
What I like most is you give information with proofs and working code.
Thanks for sharing these valuable contents
It took too much time to load if i clicked yes after uploaded the file and did not return any result
..can anyone plss help me out ..