请求Spring Boot REST API实现CSV上传并导入MySQL完整示例

AI 小北

2026-5-19

完整实现方案：Spring Boot CSV上传 + MySQL导入 + Angular前端

没问题，我给你一套端到端的完整实现方案，从Spring Boot后端的CSV解析、数据库导入，到Angular前端的文件上传组件，所有环节的代码都写清楚了，你可以直接参考复用。

一、后端实现（Spring Boot）

1. 添加必要依赖

首先在pom.xml中添加所需依赖，包括OpenCSV（用于解析CSV）、Spring Data JPA、MySQL驱动和Spring Web：

<dependencies>
    <!-- Spring Web -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <!-- Spring Data JPA -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <!-- MySQL Driver -->
    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
        <scope>runtime</scope>
    </dependency>
    <!-- OpenCSV for CSV parsing -->
    <dependency>
        <groupId>com.opencsv</groupId>
        <artifactId>opencsv</artifactId>
        <version>5.6</version>
    </dependency>
</dependencies>

2. 实体类定义

假设你已经有Event实体，现在定义Attendee实体对应数据库的attendee表，关联对应的Event：

import jakarta.persistence.*;

@Entity
@Table(name = "attendee")
public class Attendee {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(nullable = false)
    private String name;
    
    @Column(nullable = false, unique = true)
    private String email;
    
    private String phone;
    
    @ManyToOne
    @JoinColumn(name = "event_id", nullable = false)
    private Event event;

    // 无参构造（JPA需要）、带参构造、getter和setter
    public Attendee() {}

    public Attendee(String name, String email, String phone, Event event) {
        this.name = name;
        this.email = email;
        this.phone = phone;
        this.event = event;
    }

    // 省略getter和setter，你可以自己生成
}

Event实体参考（如果还没定义）：

import jakarta.persistence.*;

@Entity
@Table(name = "event")
public class Event {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(nullable = false)
    private String name;
    
    // 其他字段（比如时间、地点等）、构造方法、getter和setter
}

3. Repository层

创建两个Repository接口，用于数据库操作：

// AttendeeRepository
import org.springframework.data.jpa.repository.JpaRepository;

public interface AttendeeRepository extends JpaRepository<Attendee, Long> {
}

// EventRepository
import org.springframework.data.jpa.repository.JpaRepository;

public interface EventRepository extends JpaRepository<Event, Long> {
}

4. Service层：CSV解析与批量导入

核心逻辑在这里，负责解析上传的CSV文件，转换为Attendee对象并关联Event，最后批量保存到数据库：

import com.opencsv.CSVReader;
import com.opencsv.CSVReaderBuilder;
import com.opencsv.exceptions.CsvValidationException;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.Reader;
import java.util.ArrayList;
import java.util.List;

@Service
@Transactional
public class AttendeeService {

    private final AttendeeRepository attendeeRepository;
    private final EventRepository eventRepository;

    // 构造注入
    public AttendeeService(AttendeeRepository attendeeRepository, EventRepository eventRepository) {
        this.attendeeRepository = attendeeRepository;
        this.eventRepository = eventRepository;
    }

    public void importAttendeesFromCSV(Long eventId, MultipartFile file) throws IOException, CsvValidationException {
        // 先验证对应的Event是否存在
        Event event = eventRepository.findById(eventId)
                .orElseThrow(() -> new RuntimeException("Event not found with ID: " + eventId));

        // 读取并解析CSV文件
        try (Reader reader = new BufferedReader(new InputStreamReader(file.getInputStream()))) {
            CSVReader csvReader = new CSVReaderBuilder(reader)
                    .withSkipLines(1) // 跳过CSV的表头行
                    .build();

            String[] nextRecord;
            List<Attendee> attendees = new ArrayList<>();

            // 逐行解析CSV
            while ((nextRecord = csvReader.readNext()) != null) {
                // 假设CSV列顺序是：name, email, phone
                Attendee attendee = new Attendee(
                        nextRecord[0].trim(),
                        nextRecord[1].trim(),
                        nextRecord[2].trim(),
                        event
                );
                attendees.add(attendee);
            }

            // 批量保存到数据库（比单条插入效率高）
            attendeeRepository.saveAll(attendees);
        }
    }
}

5. Rest Controller：处理文件上传请求

实现你需要的events/{eventId}/attendee接口，处理前端的文件上传请求，调用Service并返回响应：

import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

@RestController
@RequestMapping("/events")
@CrossOrigin(origins = "http://localhost:4200") // 允许Angular前端跨域请求
public class AttendeeController {

    private final AttendeeService attendeeService;

    public AttendeeController(AttendeeService attendeeService) {
        this.attendeeService = attendeeService;
    }

    @PostMapping("/{eventId}/attendee")
    public ResponseEntity<String> uploadCSV(
            @PathVariable Long eventId,
            @RequestParam("file") MultipartFile file
    ) {
        // 验证文件是否为空
        if (file.isEmpty()) {
            return ResponseEntity.badRequest().body("Please select a CSV file to upload.");
        }

        // 验证文件类型（可选，增强安全性）
        if (!"text/csv".equals(file.getContentType())) {
            return ResponseEntity.badRequest().body("Only CSV files are allowed.");
        }

        try {
            attendeeService.importAttendeesFromCSV(eventId, file);
            return ResponseEntity.ok("CSV uploaded successfully! All attendees have been imported.");
        } catch (Exception e) {
            return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR)
                    .body("Import failed: " + e.getMessage());
        }
    }
}

二、前端实现（Angular）

1. 上传组件HTML模板

创建一个简单的上传界面，包含Event ID输入框、文件选择框和上传按钮：

<div class="container mt-5">
  <h2>Upload Attendees CSV</h2>
  
  <div class="mb-3">
    <label for="eventId" class="form-label">Event ID</label>
    <input type="number" class="form-control" id="eventId" [(ngModel)]="eventId" placeholder="Enter event ID">
  </div>

  <div class="mb-3">
    <label for="csvFile" class="form-label">Select CSV File</label>
    <input type="file" class="form-control" id="csvFile" (change)="onFileSelected($event)" accept=".csv">
  </div>

  <button class="btn btn-primary" (click)="uploadFile()" [disabled]="!selectedFile || !eventId">
    Upload & Import
  </button>

  <!-- 显示上传结果 -->
  <div *ngIf="message" class="mt-3 alert" [ngClass]="isSuccess ? 'alert-success' : 'alert-danger'">
    {{ message }}
  </div>
</div>

2. 组件TypeScript代码

编写逻辑处理文件选择和上传请求：

import { Component } from '@angular/core';
import { HttpClient } from '@angular/common/http';

@Component({
  selector: 'app-attendee-upload',
  templateUrl: './attendee-upload.component.html',
  styleUrls: ['./attendee-upload.component.css']
})
export class AttendeeUploadComponent {
  eventId: number;
  selectedFile: File;
  message: string;
  isSuccess: boolean;

  constructor(private http: HttpClient) {}

  onFileSelected(event: Event): void {
    const input = event.target as HTMLInputElement;
    if (input.files && input.files.length > 0) {
      this.selectedFile = input.files[0];
    }
  }

  uploadFile(): void {
    const formData = new FormData();
    formData.append('file', this.selectedFile);

    const apiUrl = `http://localhost:8080/events/${this.eventId}/attendee`;
    
    this.http.post(apiUrl, formData, { responseType: 'text' })
      .subscribe({
        next: (response) => {
          this.message = response;
          this.isSuccess = true;
          // 重置表单
          this.selectedFile = null;
          this.eventId = null;
        },
        error: (err) => {
          this.message = err.error || 'An error occurred during upload.';
          this.isSuccess = false;
        }
      });
  }
}

三、关键配置与注意事项

数据库配置：在application.properties中添加MySQL连接信息：

spring.datasource.url=jdbc:mysql://localhost:3306/your_database_name
spring.datasource.username=your_mysql_username
spring.datasource.password=your_mysql_password
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

CSV格式要求：确保你的CSV文件第一行是表头，列顺序为name,email,phone，示例如下：

name,email,phone
John Doe,john.doe@example.com,123-456-7890
Jane Smith,jane.smith@example.com,987-654-3210

异常处理优化：可以自定义全局异常处理器，统一处理各类异常（比如Event不存在、CSV格式错误等），返回更友好的响应。
大数据量优化：如果要导入上万条数据，建议分批次保存，或者在application.properties中添加JPA批量配置：

spring.jpa.properties.hibernate.jdbc.batch_size=50
spring.jpa.properties.hibernate.order_inserts=true

内容的提问来源于stack exchange，提问作者Gowri

请求Spring Boot REST API实现CSV上传并导入MySQL完整示例

一、后端实现（Spring Boot） #

1. 添加必要依赖 #

2. 实体类定义 #

3. Repository层 #

4. Service层：CSV解析与批量导入 #

5. Rest Controller：处理文件上传请求 #

二、前端实现（Angular） #

1. 上传组件HTML模板 #

2. 组件TypeScript代码 #

三、关键配置与注意事项 #