Spring boot开发从Excel表格导入大数据量（十万级/百万级）数据到数据库（MySQL）的工具

本文链接：https://blog.csdn.net/shenhua1630/article/details/103035503

本文介绍了如何使用Spring Boot开发一个工具，用于从Excel文件高效地导入大量（如十万级/百万级）数据到MySQL数据库。通过创建项目、配置依赖、编写工具类、实体类、接口和实现类，实现了数据的读取、存储和写入。使用特定的ExcelUtil类读取xls/xlsx文件，并在项目启动时自动运行导入逻辑，以快速将数据插入数据库。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

首先创建一个项目，我这里项目名称为：utils-self 。可使用Spring Assistant 创建，如下图，选择 web、jdbc、mysql三个模块的依赖，点击next。
设置配置文件 application.properties，如下：

server.port=8076
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/library_basic_v0431?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=root

引入相应的依赖，我这里要用到的依赖如下：pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.2.1.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.bai</groupId>
	<artifactId>utils-self</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>utils-self</name>
	<description>utils for work</description>

	<properties>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
			<exclusions>
				<exclusion>
					<groupId>org.junit.vintage</groupId>
					<artifactId>junit-vintage-engine</artifactId>
				</exclusion>
			</exclusions>
		</dependency>

		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>RELEASE</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/xerces/xercesImpl -->
		<dependency>
			<groupId>xerces</groupId>
			<artifactId>xercesImpl</artifactId>
			<version>2.12.0</version>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId<