Importing Excel Data into MySQL Database in Spring Boot via REST API using Apache POI Library
In this tutorial, we will show you how to import data from an excel file into a MySQL database using the Apache POI library in Spring Boot via REST API.
Apache POI is an open-source Java library that allows to create and modify various file formats of Microsoft Office documents using Java programming language.
Let the following be our example excel:
A | B | C | D | E | F | G |
---|---|---|---|---|---|---|
SenderId | ReceiverId | InitiatorId | BankCode | ServiceCode | TrxnAmount | FeeAmount |
11112 | 53924 | 32123 | AABank | 4 | 10000.23 | 3.00 |
32355 | 98394 | 98888 | HHBank | 5 | 102.87 | 1.00 |
28999 | 77394 | 27777 | LLBank | 24 | 800.2 | 3 |
40329 | 76738 | 92788 | GGBank | 9 | 1594.00 | 3.00 |
67263 | 82394 | 86282 | MSBank | 20 | 78.00 | 1.00 |
72628 | 32322 | 98883 | VKBank | 9 | 152394.00 | 15.00 |
97388 | 34224 | 22333 | RBBank | 12 | 973992.30 | 10.00 |
54344 | 12221 | 873844 | BPBank | 82 | 672893.42 | 10.00 |
23788 | 67349 | 93748 | TJBank | 99 | 82778.10 | 10.00 |
26663 | 28343 | 55263 | SDBank | 112 | 86237.00 | 10.00 |
Follow the steps below to complete this example:
Adding Dependency
Add the following dependencies to your Spring Boot project:
For MavenAdd to the pom.xml file:
For Gradle
Add to the build.gradle file:
implementation group: 'org.apache.poi', name: 'poi-ooxml', version: '5.2.2'
You can find the other versions of Apache POI in the Maven repository.
Adding Configurations
First, add the following credentials to your resources/application.properties configuration file:
#port on which the application should run
server.port= 8080
#mysql database connection
spring.datasource.url = jdbc:mysql://localhost:3306/test_db
spring.datasource.username = root
spring.datasource.password = Testing123$
spring.datasource.timeBetweenEvictionRunsMillis = 60000
spring.datasource.maxIdle = 1
#below properties will automatically creates and updates database schema
spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=update
Creating Entity Class
Create a Transaction.java entity class for mapping it with transaction table of database:
package com.example.transaction.entity;
import java.time.LocalDateTime;
import javax.persistence.Entity;
import javax.persistence.EntityListeners;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import org.springframework.data.annotation.CreatedBy;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedBy;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.Builder.Default;
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Builder(toBuilder = true)
@EntityListeners(AuditingEntityListener.class)
@Entity
@Table(name = "transaction")
public class Transaction {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
private Long initiatorId;
private Long senderId;
private Long receiverId;
private double trxnAmount;
private double feeAmount;
private String serviceCode;
private String bankCode;
@Default
private boolean refunded = false;
private int status;
@Default
private boolean success = false;
@Default
private boolean deleted = false;
@CreatedDate
private LocalDateTime created;
@LastModifiedDate
private LocalDateTime modified;
@CreatedBy
private Long createdBy;
@LastModifiedBy
private Long modifiedBy;
}
Creating JPA Repository Class
Create a TransactionRepository.java class for performing CRUD operations on the above transaction table:
package com.example.transaction.repository;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;
import com.apache.poi.example.transaction.entity.Transaction;
@Repository
public interface TransactionRepository extends CrudRepository<Transaction, Long> {
}
Creating Service
Create a service class with a method to import data from an excel file to MySQL database:
package com.example.transaction.service;
import java.util.List;
import org.springframework.web.multipart.MultipartFile;
public interface TransactionService {
void importToDb(List<MultipartFile> multipartfiles);
}
Implementation class of the above Transaction service interface:
package com.example.transaction.service.impl;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import com.example.transaction.entity.Transaction;
import com.example.transaction.repository.TransactionRepository;
import com.example.transaction.service.TransactionService;
@Service
public class TransactionServiceImpl implements TransactionService {
@Autowired
private TransactionRepository transactionRepository;
@Override
public void importToDb(List<MultipartFile> multipartfiles) {
if (!multipartfiles.isEmpty()) {
List<Transaction> transactions = new ArrayList<>();
multipartfiles.forEach(multipartfile -> {
try {
XSSFWorkbook workBook = new XSSFWorkbook(multipartfile.getInputStream());
XSSFSheet sheet = workBook.getSheetAt(0);
// looping through each row
for (int rowIndex = 0; rowIndex < getNumberOfNonEmptyCells(sheet, 0) - 1; rowIndex++) {
// current row
XSSFRow row = sheet.getRow(rowIndex);
// skip the first row because it is a header row
if (rowIndex == 0) {
continue;
}
Long senderId = Long.parseLong(getValue(row.getCell(0)).toString());
Long receiverId = Long.parseLong(getValue(row.getCell(1)).toString());
Long initiatorId = Long.parseLong(getValue(row.getCell(2)).toString());
String bankCode = String.valueOf(row.getCell(3));
int serviceCode = Integer.parseInt(row.getCell(4).toString());
double transactionAmount = Double.parseDouble(row.getCell(5).toString());
double feeAmount = Double.parseDouble(row.getCell(6).toString());
Transaction transaction = Transaction.builder().senderId(senderId).receiverId(receiverId)
.initiatorId(initiatorId).bankCode(bankCode).serviceCode(serviceCode)
.trxnAmount(transactionAmount).feeAmount(feeAmount).build();
transactions.add(transaction);
}
} catch (IOException e) {
e.printStackTrace();
}
});
if (!transactions.isEmpty()) {
// save to database
transactionRepository.saveAll(transactions);
}
}
}
private Object getValue(Cell cell) {
switch (cell.getCellType()) {
case STRING:
return cell.getStringCellValue();
case NUMERIC:
return String.valueOf((int) cell.getNumericCellValue());
case BOOLEAN:
return cell.getBooleanCellValue();
case ERROR:
return cell.getErrorCellValue();
case FORMULA:
return cell.getCellFormula();
case BLANK:
return null;
case _NONE:
return null;
default:
break;
}
return null;
}
public static int getNumberOfNonEmptyCells(XSSFSheet sheet, int columnIndex) {
int numOfNonEmptyCells = 0;
for (int i = 0; i <= sheet.getLastRowNum(); i++) {
XSSFRow row = sheet.getRow(i);
if (row != null) {
XSSFCell cell = row.getCell(columnIndex);
if (cell != null && cell.getCellType() != CellType.BLANK) {
numOfNonEmptyCells++;
}
}
}
return numOfNonEmptyCells;
}
}
Creating Web Controller
Create a controller with a REST API endpoint that allows to import excel data into a MySQL database:
package com.example.transaction.controller;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestPart;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import com.example.transaction.service.TransactionService;
@RestController
@RequestMapping(path = "/transactions")
public class TransactionController {
@Autowired
private TransactionService transactionService;
@PostMapping(path = "/import-to-db")
public void importTransactionsFromExcelToDb(@RequestPart(required = true) List<MultipartFile> files) {
transactionService.importToDb(files);
}
}
Enabling JPA Auditing
To enable Spring JPA auditing features like @CreateDate, @CreatedBy, @LastModifiedDate, and @LastModifiedBy, the main Spring Boot class should be annotated with @EnableJpaAuditing as shown in the example below:
package com.example;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.data.jpa.repository.config.EnableJpaAuditing;
@SpringBootApplication
@EnableJpaAuditing
public class ApachePoiExampleApplication {
public static void main(String[] args) {
SpringApplication.run(ApachePoiExampleApplication.class, args);
}
}
This example is complete. You may also be interested in learning how to export MySQL data into excel.