Servlet实现批量导入Excel到数据库

蔚落 2024-03-22 18:10 24阅读 0赞

实现步骤：

1.  通过Apache POI库读取Excel文件，将数据存入Java对象中。
2.  使用JDBC连接数据库，将Java对象中的数据批量插入到数据库中。

代码实现：

1.  导入Apache POI和JDBC相关库。

1.  读取Excel文件并将数据存入Java对象中。

// 读取Excel文件
    File file = new File("data.xlsx");
    FileInputStream inputStream = new FileInputStream(file);
    Workbook workbook = new XSSFWorkbook(inputStream);
    Sheet sheet = workbook.getSheetAt(0);
    
    // 遍历Excel文件
    for (Row row : sheet) {
        
        // 跳过第一行表头
        if (row.getRowNum() == 0) {
        
            continue;
        }
        String name = "";
        int age = 0;
        String gender = "";
        String address = "";
        // 遍历每一列数据
        for (Cell cell : row) {
        
            // 获取单元格值
            switch (cell.getColumnIndex()) {
        
                case 0:
                    name = cell.getStringCellValue();
                    break;
                case 1:
                    age = (int) cell.getNumericCellValue();
                    break;
                case 2:
                    gender = cell.getStringCellValue();
                    break;
                case 3:
                    address = cell.getStringCellValue();
                    break;
                default:
                    break;
            }
        }
    
        // 将数据存入Java对象中
        User user = new User();
        user.setName(name);
        user.setAge(age);
        user.setGender(gender);
        user.setAddress(address);
        userList.add(user);
    }

1.  连接数据库并批量插入数据。

String url = "jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC";
    String username = "root";
    String password = "123456";
    
    try (Connection conn = DriverManager.getConnection(url, username, password)) {
        
        // 设置自动提交为false，避免插入数据量过大造成性能问题
        conn.setAutoCommit(false);
    
        String sql = "INSERT INTO user(name, age, gender, address) VALUES (?, ?, ?, ?)";
        PreparedStatement ps = conn.prepareStatement(sql);
    
        // 批量插入数据
        int batchSize = 1000;
        int count = 0;
        for (User user : userList) {
        
            ps.setString(1, user.getName());
            ps.setInt(2, user.getAge());
            ps.setString(3, user.getGender());
            ps.setString(4, user.getAddress());
            ps.addBatch();
    
            count++;
            if (count % batchSize == 0) {
        
                ps.executeBatch();
                conn.commit();
            }
        }
        ps.executeBatch();
        conn.commit();
    } catch (SQLException e) {
        
        e.printStackTrace();
    }

完整代码：

import java.io.File;
    import java.io.FileInputStream;
    import java.io.IOException;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.List;
    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.xssf.usermodel.XSSFWorkbook;
    
    public class ExcelImportServlet {
        
        public void importData() {
        
            List<User> userList = new ArrayList<>();
    
            try {
        
                // 读取Excel文件
                File file = new File("data.xlsx");
                FileInputStream inputStream = new FileInputStream(file);
                Workbook workbook = new XSSFWorkbook(inputStream);
                Sheet sheet = workbook.getSheetAt(0);
    
                // 遍历Excel文件
                for (Row row : sheet) {
        
                    // 跳过第一行表头
                    if (row.getRowNum() == 0) {
        
                        continue;
                    }
                    String name = "";
                    int age = 0;
                    String gender = "";
                    String address = "";
                    // 遍历每一列数据
                    for (Cell cell : row) {
        
                        // 获取单元格值
                        switch (cell.getColumnIndex()) {
        
                            case 0:
                                name = cell.getStringCellValue();
                                break;
                            case 1:
                                age = (int) cell.getNumericCellValue();
                                break;
                            case 2:
                                gender = cell.getStringCellValue();
                                break;
                            case 3:
                                address = cell.getStringCellValue();
                                break;
                            default:
                                break;
                        }
                    }
    
                    // 将数据存入Java对象中
                    User user = new User();
                    user.setName(name);
                    user.setAge(age);
                    user.setGender(gender);
                    user.setAddress(address);
                    userList.add(user);
                }
            } catch (IOException e) {
        
                e.printStackTrace();
            }
    
            String url = "jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC";
            String username = "root";
            String password = "123456";
    
            try (Connection conn = DriverManager.getConnection(url, username, password)) {
        
                // 设置自动提交为false，避免插入数据量过大造成性能问题
                conn.setAutoCommit(false);
    
                String sql = "INSERT INTO user(name, age, gender, address) VALUES (?, ?, ?, ?)";
                PreparedStatement ps = conn.prepareStatement(sql);
    
                // 批量插入数据
                int batchSize = 1000;
                int count = 0;
                for (User user : userList) {
        
                    ps.setString(1, user.getName());
                    ps.setInt(2, user.getAge());
                    ps.setString(3, user.getGender());
                    ps.setString(4, user.getAddress());
                    ps.addBatch();
    
                    count++;
                    if (count % batchSize == 0) {
        
                        ps.executeBatch();
                        conn.commit();
                    }
                }
                ps.executeBatch();
                conn.commit();
            } catch (SQLException e) {
        
                e.printStackTrace();
            }
        }
    }