java实现excel一万条数据批量导入

举报
赵KK日常技术记录 发表于 2023/06/24 20:21:51 2023/06/24
【摘要】 要实现Excel一万条数据批量导入,可以使用Apache POI库来读取和解析Excel文件,并使用JDBC连接数据库将数据批量插入。以下是一个简单的示例代码:import java.io.File;import java.io.FileInputStream;import java.sql.Connection;import java.sql.DriverManager;import ja...

要实现Excel一万条数据批量导入,可以使用Apache POI库来读取和解析Excel文件,并使用JDBC连接数据库将数据批量插入。以下是一个简单的示例代码:

import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Iterator;

import org.apache.poi.ss.usermodel.Cell;
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;

public class ExcelImporter {

    public static void main(String[] args) throws Exception {
        // 设置数据库连接参数
        String url = "jdbc:mysql://localhost:3306/test";
        String username = "root";
        String password = "123456";
        String driverClassName = "com.mysql.jdbc.Driver";

        // 加载数据库驱动
        Class.forName(driverClassName);

        // 建立数据库连接
        Connection conn = DriverManager.getConnection(url, username, password);

        // 创建预编译SQL语句,用于批量插入数据
        String sql = "INSERT INTO mytable (col1, col2, col3) VALUES (?, ?, ?)";
        PreparedStatement ps = conn.prepareStatement(sql);

        // 读取Excel文件
        File file = new File("data.xlsx");
        FileInputStream fis = new FileInputStream(file);
        Workbook workbook = WorkbookFactory.create(fis);
        Sheet sheet = workbook.getSheetAt(0);

        // 遍历Excel文件中的每一行数据
        Iterator<Row> rowIterator = sheet.rowIterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();

            // 读取每个单元格的数据,并设置预编译SQL语句的参数
            Cell cell1 = row.getCell(0);
            Cell cell2 = row.getCell(1);
            Cell cell3 = row.getCell(2);
            ps.setString(1, cell1.getStringCellValue());
            ps.setString(2, cell2.getStringCellValue());
            ps.setString(3, cell3.getStringCellValue());

            // 将预编译SQL语句添加到批处理中
            ps.addBatch();
        }

        // 执行批处理,将数据批量插入到数据库中
        ps.executeBatch();

        // 关闭资源
        ps.close();
        conn.close();
        fis.close();
    }

}

在上述代码中,我们首先设置了数据库连接参数,然后加载数据库驱动并建立数据库连接。接着,我们创建了一个预编译SQL语句,用于批量插入数据。然后,我们使用Apache POI库读取Excel文件,并遍历每一行数据,将每个单元格的数据设置为预编译SQL语句的参数,并将预编译SQL语句添加到批处理中。最后,我们执行批处理,将数据批量插入到数据库中,并关闭资源。

需要注意的是,上述代码中的Excel文件必须符合以下要求:

  • 文件格式必须为.xlsx格式;
  • 文件中的第一行必须是表头,用于描述每个字段的名称;
  • 文件中的每一行数据必须按照表头的顺序,依次填写每个字段的值。

如果Excel文件不符合上述要求,则需要修改代码来适配不同的文件格式。

优化代码

  1. 使用批量插入:在原有的代码基础上,使用批量插入的方式来导入数据,可以大大提高插入的效率。代码示例如下:
import pymysql

def batch_insert(data):
    try:
        conn = pymysql.connect(host='localhost', user='root', password='123456', db='test', charset='utf8')
        cursor = conn.cursor()
        sql = "INSERT INTO users(id, name, age) VALUES (%s, %s, %s)"
        cursor.executemany(sql, data)
        conn.commit()
        cursor.close()
        conn.close()
    except Exception as e:
        print(e)

data = [
    (1, '张三', 20),
    (2, '李四', 21),
    (3, '王五', 22),
    # ...
]

batch_insert(data)
  1. 使用多线程或协程:在原有的代码基础上,使用多线程或协程的方式来并行处理数据,可以提高处理数据的效率。代码示例如下:
import pymysql
import threading

def insert_data(data):
    try:
        conn = pymysql.connect(host='localhost', user='root', password='123456', db='test', charset='utf8')
        cursor = conn.cursor()
        sql = "INSERT INTO users(id, name, age) VALUES (%s, %s, %s)"
        cursor.executemany(sql, data)
        conn.commit()
        cursor.close()
        conn.close()
    except Exception as e:
        print(e)

def batch_insert(data, batch_size=1000, num_threads=4):
    num_data = len(data)
    num_batches = (num_data + batch_size - 1) // batch_size
    batches = [data[i * batch_size:(i + 1) * batch_size] for i in range(num_batches)]

    threads = []
    for i in range(num_threads):
        t = threading.Thread(target=insert_data, args=(batches[i],))
        threads.append(t)
        t.start()

    for t in threads:
        t.join()

data = [
    (1, '张三', 20),
    (2, '李四', 21),
    (3, '王五', 22),
    # ...
]

batch_insert(data, batch_size=1000, num_threads=4)
  1. 使用数据库事务:在原有的代码基础上,使用数据库事务的方式来插入数据,可以保证数据的完整性和一致性。代码示例如下:
import pymysql

def batch_insert(data):
    try:
        conn = pymysql.connect(host='localhost', user='root', password='123456', db='test', charset='utf8')
        cursor = conn.cursor()
        conn.begin()
        sql = "INSERT INTO users(id, name, age) VALUES (%s, %s, %s)"
        cursor.executemany(sql, data)
        conn.commit()
        cursor.close()
        conn.close()
    except Exception as e:
        print(e)

data = [
    (1, '张三', 20),
    (2, '李四', 21),
    (3, '王五', 22),
    # ...
]

batch_insert(data)
【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。