数据导入导出

指定sql，导出数据为 CSV 格式

示例：

package example.utils;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DataExportUtil {

    /**
     * 导出数据为 CSV 格式
     *
     * @param response HttpServletResponse，用于输出 CSV 文件
     * @param sql      要导出的 SQL 查询语句
     */
    public static void exportToCsv(HttpServletResponse response, String sql) {
        // 设置响应头
        response.setContentType("text/csv;charset=UTF-8");
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Content-Disposition", "attachment;filename=data_export.csv");

        try (PrintWriter writer = response.getWriter();
             Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {

            // 添加 BOM 头，解决 Excel 显示乱码问题
            writer.write('\uFEFF');

            // 写入表头（列名）
            int columnCount = rs.getMetaData().getColumnCount();
            for (int i = 1; i <= columnCount; i++) {
                writer.print(rs.getMetaData().getColumnName(i));
                if (i < columnCount) {
                    writer.print(",");
                }
            }
            writer.println();

            // 写入数据
            while (rs.next()) {
                for (int i = 1; i <= columnCount; i++) {
                    writer.print(rs.getString(i) != null ? rs.getString(i) : ""); // 处理空值
                    if (i < columnCount) {
                        writer.print(",");
                    }
                }
                writer.println();
            }
        } catch (SQLException  | IOException e) {
            e.printStackTrace();
        }
    }
}

将csv格式数据导入数据库

示例：

package example.controller;

import example.utils.DBUtil;

import javax.servlet.ServletException;
import javax.servlet.annotation.MultipartConfig;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.*;
import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@WebServlet("/importCSV")
@MultipartConfig(
        fileSizeThreshold = 1024 * 1024 * 2, // 2MB
        maxFileSize = 1024 * 1024 * 10,      // 10MB
        maxRequestSize = 1024 * 1024 * 50    // 50MB
)
public class ImportCSVServlet extends HttpServlet {
    // 配置项
    private static final String UPLOAD_DIR = "uploaded_files/";
    private static final int EXPECTED_COLUMNS = 5; // 期望列数
    private static final String TABLE_NAME = "artwork";
    private static final String INSERT_SQL = "INSERT INTO " + TABLE_NAME + " (name, artist_id, style, note, exhibition_id) VALUES (?, ?, ?, ?, ?)";

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("text/html; charset=UTF-8");
        response.setCharacterEncoding("UTF-8");

        // 1. 获取上传的文件
        Part filePart = request.getPart("csvFile");
        String fileName = filePart.getSubmittedFileName();

        if (fileName != null && fileName.endsWith(".csv")) {
            // 创建上传目录
            File uploadDir = new File(getServletContext().getRealPath("/") + UPLOAD_DIR);
            if (!uploadDir.exists()) {
                uploadDir.mkdirs();
            }

            // 保存文件
            String filePath = uploadDir.getAbsolutePath() + File.separator + fileName;
            filePart.write(filePath);

            // 2. 解析 CSV 文件
            List<String[]> rows = parseCSV(filePath);
            if (rows.isEmpty()) {
                response.getWriter().write("CSV 文件为空或格式错误！");
                return;
            }

            // 3. 插入数据到数据库
            try (Connection conn = DBUtil.getConnection()) {
                conn.setAutoCommit(false); // 开启事务
                try (PreparedStatement ps = conn.prepareStatement(INSERT_SQL)) {
                    for (String[] row : rows) {
                        if (row.length != EXPECTED_COLUMNS) {
                            System.err.println("跳过无效行：" + String.join(",", row));
                            continue; // 跳过无效行
                        }
                        // 动态设置参数
                        ps.setString(1, row[0]); // name
                        ps.setInt(2, Integer.parseInt(row[1].trim())); // artist_id
                        ps.setString(3, row[2]); // style
                        ps.setString(4, row[3]); // note
                        ps.setInt(5, Integer.parseInt(row[4].trim())); // exhibition_id
                        ps.addBatch();
                    }
                    ps.executeBatch(); // 批量执行
                }
                conn.commit(); // 提交事务
                response.getWriter().write("成功导入数据！");
            } catch (SQLException | NumberFormatException e) {
                e.printStackTrace();
                response.getWriter().write("数据库操作失败：" + e.getMessage());
            }
        } else {
            response.getWriter().write("请上传有效的 CSV 文件！");
        }
    }

    /**
     * 解析 CSV 文件为行列表
     */
    private List<String[]> parseCSV(String filePath) {
        List<String[]> rows = new ArrayList<>();
        try (BufferedReader reader = new BufferedReader(new FileReader(filePath))) {
            // 跳过表头
            reader.readLine();

            String line;
            while ((line = reader.readLine()) != null) {
                rows.add(line.split(","));
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return rows;
    }
}