如何用MySQL与Spring实现JSON文件数据读写及测验题JSON入库

我来帮你梳理下实现步骤，针对你的测验应用场景，我们可以用Spring Data JPA配合MySQL来完成JSON文件和数据库之间的双向操作——既可以把本地JSON文件的题目数据存入数据库方便管理，也能从数据库读取数据生成JSON或者直接供应用使用。

一、数据库表结构设计 #

你的JSON是嵌套层级结构（测验→题目→选项），用关系型表存储更便于后续的增删改查和维护，设计三张关联表：

1. quiz表：存储测验的基础信息

CREATE TABLE quiz (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    description TEXT
);

2. question表：存储题目，关联所属测验

CREATE TABLE question (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name TEXT NOT NULL,
    question_type_id INT,
    quiz_id INT NOT NULL,
    FOREIGN KEY (quiz_id) REFERENCES quiz(id) ON DELETE CASCADE
);

3. option表：存储选项，关联所属题目

CREATE TABLE option (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    is_answer BOOLEAN DEFAULT FALSE,
    question_id INT NOT NULL,
    FOREIGN KEY (question_id) REFERENCES question(id) ON DELETE CASCADE
);

二、Spring项目配置 #

1. 添加依赖（Maven示例） #

在pom.xml中引入核心依赖：

<dependencies>
    <!-- Spring Data JPA 简化数据库操作 -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <!-- MySQL数据库驱动 -->
    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
        <scope>runtime</scope>
    </dependency>
    <!-- Jackson 处理JSON与Java对象的转换 -->
    <dependency>
        <groupId>com.fasterxml.jackson.core</groupId>
        <artifactId>jackson-databind</artifactId>
    </dependency>
    <!-- Spring Boot基础启动依赖 -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter</artifactId>
    </dependency>
</dependencies>

2. 数据库连接配置 #

在application.properties中配置MySQL连接信息：

spring.datasource.url=jdbc:mysql://localhost:3306/your_database_name?useSSL=false&serverTimezone=UTC
spring.datasource.username=your_db_username
spring.datasource.password=your_db_password
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true

三、定义实体类 #

根据JSON结构和数据库表，创建对应的Java实体类，配置关联关系和级联操作：

1. Option选项实体 #

import jakarta.persistence.*;

@Entity
@Table(name = "option")
public class Option {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    private String name;
    @Column(name = "is_answer")
    private Boolean isAnswer;

    @ManyToOne
    @JoinColumn(name = "question_id")
    private Question question;

    // 无参构造、全参构造、getter/setter（建议用Lombok的@Data注解简化）
    public Option() {}

    public Option(String name, Boolean isAnswer) {
        this.name = name;
        this.isAnswer = isAnswer;
    }

    // getter和setter省略
}

2. Question题目实体 #

import jakarta.persistence.*;
import java.util.List;

@Entity
@Table(name = "question")
public class Question {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    private String name;
    @Column(name = "question_type_id")
    private Integer questionTypeId;

    @ManyToOne
    @JoinColumn(name = "quiz_id")
    private Quiz quiz;

    @OneToMany(mappedBy = "question", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<Option> options;

    // 构造方法、getter/setter
    public Question() {}

    public Question(String name, Integer questionTypeId, List<Option> options) {
        this.name = name;
        this.questionTypeId = questionTypeId;
        this.options = options;
        // 自动绑定选项与题目的关联
        options.forEach(option -> option.setQuestion(this));
    }
}

3. Quiz测验实体 #

import jakarta.persistence.*;
import java.util.List;

@Entity
@Table(name = "quiz")
public class Quiz {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    private String name;
    private String description;

    @OneToMany(mappedBy = "quiz", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<Question> questions;

    // 构造方法、getter/setter
    public Quiz() {}

    public Quiz(String name, String description, List<Question> questions) {
        this.name = name;
        this.description = description;
        this.questions = questions;
        // 自动绑定题目与测验的关联
        questions.forEach(question -> question.setQuiz(this));
    }
}

四、创建Repository接口 #

通过Spring Data JPA的Repository接口，快速实现数据库CRUD操作：

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

public interface QuizRepository extends JpaRepository<Quiz, Integer> {}

public interface QuestionRepository extends JpaRepository<Question, Integer> {}

public interface OptionRepository extends JpaRepository<Option, Integer> {}

五、从JSON文件读取数据并插入MySQL #

创建服务类，使用Jackson的ObjectMapper读取JSON文件，转换成实体类后批量保存到数据库：

import com.fasterxml.jackson.databind.ObjectMapper;
import org.springframework.stereotype.Service;
import java.io.File;
import java.io.IOException;

@Service
public class QuizDataService {
    private final QuizRepository quizRepository;
    private final ObjectMapper objectMapper;

    // 构造注入依赖
    public QuizDataService(QuizRepository quizRepository, ObjectMapper objectMapper) {
        this.quizRepository = quizRepository;
        this.objectMapper = objectMapper;
    }

    // 读取JSON文件并保存到数据库
    public void importQuizFromJson(String filePath) throws IOException {
        // 将JSON文件转换成Quiz对象
        Quiz quiz = objectMapper.readValue(new File(filePath), Quiz.class);
        // 级联保存测验、题目、选项
        quizRepository.save(quiz);
    }
}

你可以在Spring Boot启动类中触发导入操作：

import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class QuizApplication implements CommandLineRunner {
    private final QuizDataService quizDataService;

    public QuizApplication(QuizDataService quizDataService) {
        this.quizDataService = quizDataService;
    }

    public static void main(String[] args) {
        SpringApplication.run(QuizApplication.class, args);
    }

    @Override
    public void run(String... args) throws Exception {
        // 替换为你的file.json实际路径
        quizDataService.importQuizFromJson("src/main/resources/file.json");
    }
}

六、从MySQL读取数据并生成JSON（或供应用使用） #

在服务类中添加导出方法，将数据库中的数据转换成JSON，或者直接返回实体对象供应用使用：

import com.fasterxml.jackson.core.JsonProcessingException;

@Service
public class QuizDataService {
    // 已有代码...

    // 根据ID读取测验并转换成格式化JSON
    public String exportQuizToJson(Integer quizId) throws JsonProcessingException {
        Quiz quiz = quizRepository.findById(quizId)
                .orElseThrow(() -> new RuntimeException("未找到ID为" + quizId + "的测验"));
        // 生成格式化的JSON字符串
        return objectMapper.writerWithDefaultPrettyPrinter().writeValueAsString(quiz);
    }

    // 读取所有测验数据（供应用直接调用）
    public Iterable<Quiz> getAllQuizzes() {
        return quizRepository.findAll();
    }
}

注意事项 #

• 确保JSON文件的字段名、数据类型和实体类完全匹配（比如isAnswer在JSON中是布尔值，实体类对应Boolean类型）。
• 如果JSON中的ID是自定义值而非自增，可以修改实体类的@GeneratedValue策略，或者去掉自增逻辑手动设置ID。
• 添加异常处理：针对文件不存在、JSON格式错误、数据库连接失败等情况，建议增加try-catch块或抛出异常给上层处理。
• 如需批量导入多个测验，可将JSON结构改为数组，用objectMapper.readValue()读取成List<Quiz>后批量保存。

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