引自免费微信小程序：皆我百晓生
为了在 MySQL 数据库中直观地表示上述 JSON 数据结构，并且便于查询，我们可以创建四个独立的表：users, phases, grades, 和 classes。这四个表之间通过外键关联起来，形成层次化的数据模型。
首先，我们定义各个表的结构：
-
用户表（users）：
- id (INT PRIMARY KEY AUTO_INCREMENT)
- school_id (INT NOT NULL)
-
学段表（phases）：
- id (INT PRIMARY KEY AUTO_INCREMENT)
- user_id (INT NOT NULL, FOREIGN KEY references users(id))
- phase_data (JSON NOT NULL) // 用于存储与学段相关的额外数据，例如阶段名称等
-
年级表（grades）：
- id (INT PRIMARY KEY AUTO_INCREMENT)
- phase_id (INT NOT NULL, FOREIGN KEY references phases(id))
- grade_data (JSON NOT NULL) // 用于存储与年级相关的额外数据，例如年级名称等
-
班级表（classes）：
- id (INT PRIMARY KEY AUTO_INCREMENT)
- grade_id (INT NOT NULL, FOREIGN KEY references grades(id))
- class_id (INT NOT NULL) // 用于存储班级ID
下面是创建这些表的 SQL 语句：
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
school_id INT NOT NULL
);
CREATE TABLE phases (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
phase_data JSON NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE TABLE grades (
id INT PRIMARY KEY AUTO_INCREMENT,
phase_id INT NOT NULL,
grade_data JSON NOT NULL,
FOREIGN KEY (phase_id) REFERENCES phases(id)
);
CREATE TABLE classes (
id INT PRIMARY KEY AUTO_INCREMENT,
grade_id INT NOT NULL,
class_id INT NOT NULL,
FOREIGN KEY (grade_id) REFERENCES grades(id)
);
注意，这里我们没有将年级内的班级 ID 列为单独的一列，而是将其包含在一个 JSON 数组中，这样可以在不违反第一范式的情况下简化数据模型。如果你需要频繁查询某个年级的所有班级，可能需要在应用层处理这些 JSON 数据。
为了插入数据，你需要将 JSON 对象转换成相应的表结构。例如，对于给定的 JSON 数据，你可以创建一个方法来处理这个过程。以下是一个简单的 Java 示例，展示了如何将 JSON 转换为 MySQL 插入语句：
import com.fasterxml.jackson.databind.ObjectMapper;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class DataConverter {
private static final String USER_URL = "jdbc:mysql://localhost:3306/your_database";
private static final String USER_USERNAME = "your_username";
private static final String USER_PASSWORD = "your_password";
public void insertData(String jsonStr) throws IOException, SQLException {
ObjectMapper mapper = new ObjectMapper();
Map<String, Object> userData = mapper.readValue(jsonStr, Map.class);
// Insert User
Connection conn = DriverManager.getConnection(USER_URL, USER_USERNAME, USER_PASSWORD);
String insertUserSql = "INSERT INTO users (id, school_id) VALUES (?, ?)";
PreparedStatement pstmt = conn.prepareStatement(insertUserSql);
pstmt.setInt(1, ((Map<String, Integer>) userData.get("user")).get("id"));
pstmt.setInt(2, ((Map<String, Integer>) userData.get("user")).get("school_id"));
pstmt.executeUpdate();
// Iterate over phases and insert them
for (Map<String, Object> phase : (List<Map<String, Object>>) userData.get("user").get("phase")) {
insertPhaseAndGrades(conn, phase);
}
conn.close();
}
private void insertPhaseAndGrades(Connection conn, Map<String, Object> phaseData) throws SQLException {
// Insert Phase
String insertPhaseSql = "INSERT INTO phases (user_id, phase_data) VALUES (?, ?)";
PreparedStatement pstmt = conn.prepareStatement(insertPhaseSql);
pstmt.setInt(1, ((Map<String, Integer>) phaseData.get("user")).get("id"));
pstmt.setString(2, phaseData.toString());
pstmt.executeUpdate();
// Iterate over grades and insert them
for (Map<String, Object> gradeData : (List<Map<String, Object>>) phaseData.get("grade")) {
insertGradeAndClasses(conn, gradeData);
}
}
private void insertGradeAndClasses(Connection conn, Map<String, Object> gradeData) throws SQLException {
// Insert Grade
String insertGradeSql = "INSERT INTO grades (phase_id, grade_data) VALUES (?, ?)";
PreparedStatement pstmt = conn.prepareStatement(insertGradeSql);
pstmt.setInt(1, ((Map<String, Integer>) gradeData.get("phase")).get("id"));
pstmt.setString(2, gradeData.toString());
pstmt.executeUpdate();
// Extract class IDs from the JSON array
List<Integer> classIds = extractClassIds((List<Map<String, Integer>>) gradeData.get("grade").get("clazz_ids"));
// Insert Classes
String insertClassSql = "INSERT INTO classes (grade_id, class_id) VALUES (?, ?)";
pstmt = conn.prepareStatement(insertClassSql);
for (int classId : classIds) {
pstmt.setInt(1, ((Map<String, Integer>) gradeData.get("phase")).get("id"));
pstmt.setInt(2, classId);
pstmt.addBatch();
}
pstmt.executeBatch();
}
private List<Integer> extractClassIds(List<Map<String, Integer>> clazzIdsList) {
List<Integer> classIds = new ArrayList<>();
for (Map<String, Integer> clazzIdMap : clazzIdsList) {
classIds.add(clazzIdMap.get("class_id"));
}
return classIds;
}
}
在这个示例中，我们使用了 Jackson 库来解析 JSON。你需要先添加依赖到你的项目中：
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-databind</artifactId>
<version>2.12.4</version>
</dependency>
然后你可以调用 insertData 方法并传入 JSON 字符串，它会处理插入所有相关数据到数据库中。
要查询数据，你可以编写 SQL 查询语句，根据实际需求进行选择。由于 JSON 数据存储在 phase_data 和 grade_data 字段中，你可能需要在应用层解析这些字段的内容。例如，如果你想获取某个用户的所有班级，可以使用以下 SQL 查询：
SELECT c.class_id FROM classes c
JOIN grades g ON c.grade_id = g.id
JOIN phases p ON g.phase_id = p.id
WHERE p.user_id = ?;
在 Java 中执行这个查询，你可以使用 PreparedStatement 类似于之前插入数据的方法。
