Java解析多层嵌套JSON数组并将数据存入数据库示例

今天前端问我能不能帮忙把菜单数据存进去，我看了下大概就是下面这样的数据结构，就是一个 JSON 数组，然后里面有 children

[
	{
		"name": "1001",
		"path": "/1001",
		"component": "1001.base",
		"meta": {
			"title": "1001",
			"i18nKey": "1001.base",
			"order": 1
		},
		"children": [
			{
				"name": "100101",
				"path": "/1001/01",
				"component": "1001.01",
				"meta": {
					"title": "100101",
					"i18nKey": "1001.01"
				},
				"children": [
					{
						"name": "10010101",
						"path": "/1001/01/01",
						"component": "1001.01.01",
						"meta": {
							"title": "10010101",
							"i18nKey": "1001.01.01"
						}
					}
				]
			}
		]
	},
	{
		"name": "1002",
		"path": "/1002",
		"component": "1002.base",
		"meta": {
			"title": "1002",
			"i18nKey": "1002.base",
			"order": 1
		},
		"children": [
			{
				"name": "100201",
				"path": "/1002/01",
				"component": "1002.01",
				"meta": {
					"title": "100201",
					"i18nKey": "1002.01"
				}
			}
		]
	}
]

然后写了方法用来解析他给我的文本，这里因为里面就一个JSON格式的字符串，我直接把后缀改成了 .json，通过 JsonNode 再递归获取数据

import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;

import java.io.File;
import java.sql.*;
import java.util.*;

public class MenuParser {
    private static final String DB_URL = "jdbc:mysql://192.168.1.119:3306/ceshi?useUnicode=true&characterEncoding=utf-8&useSSL=false";
    private static final String DB_USER = "admin";
    private static final String DB_PASSWORD = "px123456";

    public static void main(String[] args) {
        try {
            // 读取 JSON 文件
            ObjectMapper objectMapper = new ObjectMapper();
            JsonNode rootNode = objectMapper.readTree(new File("C:\\Users\\72364\\Desktop\\fsdownload\\menu.json"));

            // 存储菜单项，使用 tempId 作为 key
            Map<String, MenuItem> menuMap = new LinkedHashMap<>();
            parseMenu(rootNode, null, menuMap); // 递归解析

            // 插入数据到数据库，并更新 parentId
            insertMenusIntoDB(menuMap);

            System.out.println("✅ 数据插入成功，所有层级已正确关联！");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * **递归解析 JSON，分配临时 ID，并维护父子关系**
     * @param nodeList JSON 菜单数据
     * @param parentTempId 父级的临时 ID
     * @param menuMap 存储解析后的菜单项
     */
    private static void parseMenu(JsonNode nodeList, String parentTempId, Map<String, MenuItem> menuMap) {
        for (JsonNode node : nodeList) {
            MenuItem menu = new MenuItem(node);
            String tempId = UUID.randomUUID().toString(); // 生成临时 ID
            menu.setTempId(tempId);
            menu.setParentTempId(parentTempId); // 关联父级的 tempId
            menuMap.put(tempId, menu);

            // 递归解析子菜单
            JsonNode children = node.get("children");
            if (children != null && children.isArray()) {
                parseMenu(children, tempId, menuMap);
            }
        }
    }

    /**
     * **插入菜单数据，并更新 parentId**
     * @param menuMap 存储解析后的菜单项
     */
    private static void insertMenusIntoDB(Map<String, MenuItem> menuMap) {
        String insertSQL = "INSERT INTO qy_platform_menu (parent_id, status, menu_type, menu_name, route_name, " +
                "route_path, component, `order`, i18nKey, icon, hide_in_menu, keep_alive, create_time, update_time, del_flag) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW(), ?)";

        String updateSQL = "UPDATE qy_platform_menu SET parent_id = ? WHERE id = ?";

        Map<String, Long> tempIdToDbId = new HashMap<>(); // 记录 tempId → 数据库 ID

        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
            conn.setAutoCommit(false); // 开启事务

            // **第一遍插入数据库**
            try (PreparedStatement pstmt = conn.prepareStatement(insertSQL, Statement.RETURN_GENERATED_KEYS)) {
                for (MenuItem menu : menuMap.values()) {
                    pstmt.setObject(1, null); // 先插入，parentId 设为 null
                    pstmt.setInt(2, 1);
                    pstmt.setString(3, menu.getMenuType());
                    pstmt.setString(4, menu.getMenuName());
                    pstmt.setString(5, menu.getRouteName());
                    pstmt.setString(6, menu.getRoutePath());
                    pstmt.setString(7, menu.getComponent());
                    pstmt.setInt(8, menu.getOrder() != null ? menu.getOrder() : 0);
                    pstmt.setString(9, menu.getI18nKey());
                    pstmt.setString(10, menu.getIcon());
                    pstmt.setInt(11, 0);
                    pstmt.setInt(12, 0);
                    pstmt.setInt(13, 0);

                    pstmt.executeUpdate();

                    // 获取新插入的 ID
                    try (ResultSet rs = pstmt.getGeneratedKeys()) {
                        if (rs.next()) {
                            long newId = rs.getLong(1);
                            tempIdToDbId.put(menu.getTempId(), newId);
                            menu.setId(newId);
                        }
                    }
                }
            }

            // **第二遍：更新 parentId**
            try (PreparedStatement updateStmt = conn.prepareStatement(updateSQL)) {
                for (MenuItem menu : menuMap.values()) {
                    if (menu.getParentTempId() != null && tempIdToDbId.containsKey(menu.getParentTempId())) {
                        long parentId = tempIdToDbId.get(menu.getParentTempId());
                        long menuId = menu.getId();

                        updateStmt.setLong(1, parentId);
                        updateStmt.setLong(2, menuId);
                        updateStmt.executeUpdate();
                    }
                }
            }

            conn.commit(); // 提交事务
            System.out.println("✅ 数据插入完成，parentId 关联成功！");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * **执行插入 SQL 并获取生成的 ID**
     */
    private static long insertMenuAndGetId(PreparedStatement pstmt, MenuItem menu) throws SQLException {
        pstmt.setObject(1, menu.getParentId());
        pstmt.setInt(2, 1);
        pstmt.setString(3, menu.getMenuType());
        pstmt.setString(4, menu.getMenuName());
        pstmt.setString(5, menu.getRouteName());
        pstmt.setString(6, menu.getRoutePath());
        pstmt.setString(7, menu.getComponent());
        pstmt.setInt(8, menu.getOrder() != null ? menu.getOrder() : 0);
        pstmt.setString(9, menu.getI18nKey());
        pstmt.setString(10, menu.getIcon());
        pstmt.setInt(11, 0);
        pstmt.setInt(12, 0);
        pstmt.setInt(13, 0);

        pstmt.executeUpdate();

        try (ResultSet rs = pstmt.getGeneratedKeys()) {
            if (rs.next()) {
                return rs.getLong(1);
            }
        }
        return -1;
    }
}

这里加了个临时 ID 用来处理上下级的父子关联

import com.fasterxml.jackson.databind.JsonNode;
import lombok.Data;

@Data
public class MenuItem {
    private Long id;             // 数据库 ID
    private Long parentId;       // 真实 parent_id
    private String tempId;       // 临时 ID
    private String parentTempId; // 临时 parent_id

    private Integer status;
    private String menuType;
    private String menuName;
    private String routeName;
    private String routePath;
    private String component;
    private Integer order;
    private String i18nKey;
    private String icon;

    public MenuItem(JsonNode node) {
        if (node.get("name") != null) this.routeName = node.get("name").asText();
        if (node.get("path") != null) this.routePath = node.get("path").asText();
        if (node.get("component") != null) this.component = node.get("component").asText();
        if (node.get("meta") != null) {
            if (node.get("meta").get("order") != null) this.order = node.get("meta").get("order").asInt();
            if (node.get("meta").get("title") != null) this.menuName = node.get("meta").get("title").asText();
            if (node.get("meta").get("i18nKey") != null) this.i18nKey = node.get("meta").get("i18nKey").asText();
            if (node.get("meta").get("icon") != null) this.icon = node.get("meta").get("icon").asText();
        }
    }
}

可以看到数据正常存进去了