DNchngguo/sql/InsertMenuForVue3.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.time.LocalDateTime;

public class InsertMenuForVue3 {
    public static void main(String[] args) {
        // 数据库连接信息
        String url = "jdbc:dm://192.168.0.145:30236";
        String username = "WATERSHED";
        String password = "WaterShed./1224";
        
        // 加载驱动
        try {
            Class.forName("dm.jdbc.driver.DmDriver");
        } catch (ClassNotFoundException e) {
            System.out.println("Failed to load Dameng driver: " + e.getMessage());
            return;
        }
        
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        try {
            // 建立连接
            conn = DriverManager.getConnection(url, username, password);
            System.out.println("Connected to Dameng database successfully!");
            
            // 设置事务为手动提交
            conn.setAutoCommit(false);
            
            // 1. 插入流域管理主菜单（目录）
            String insertMainMenuSql = "INSERT INTO SYS_MENU (MENU_NAME, PARENT_ID, ORDER_NUM, PATH, COMPONENT, ROUTE_NAME, IS_FRAME, IS_CACHE, MENU_TYPE, VISIBLE, STATUS, PERMS, ICON, CREATE_TIME, UPDATE_TIME) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            
            pstmt = conn.prepareStatement(insertMainMenuSql, PreparedStatement.RETURN_GENERATED_KEYS);
            
            LocalDateTime now = LocalDateTime.now();
            Timestamp timestamp = Timestamp.valueOf(now);
            
            pstmt.setString(1, "流域管理");
            pstmt.setLong(2, 0L); // 父菜单ID，0表示根目录
            pstmt.setInt(3, 100); // 排序号
            pstmt.setString(4, "/watershed"); // 路由地址
            pstmt.setString(5, "Layout"); // 组件路径，目录使用Layout
            pstmt.setString(6, "Watershed"); // 路由名称
            pstmt.setString(7, "1"); // 是否为外链，1否
            pstmt.setString(8, "1"); // 是否缓存，1不缓存
            pstmt.setString(9, "M"); // 类型，M目录
            pstmt.setString(10, "0"); // 显示状态，0显示
            pstmt.setString(11, "0"); // 菜单状态，0正常
            pstmt.setString(12, ""); // 权限字符串
            pstmt.setString(13, "water"); // 菜单图标
            pstmt.setTimestamp(14, timestamp);
            pstmt.setTimestamp(15, timestamp);
            
            int mainRowsAffected = pstmt.executeUpdate();
            System.out.println("Inserted main menu rows: " + mainRowsAffected);
            
            // 获取生成的主菜单ID
            java.sql.ResultSet generatedKeys = pstmt.getGeneratedKeys();
            Long mainMenuId = null;
            if (generatedKeys.next()) {
                mainMenuId = generatedKeys.getLong(1);
                System.out.println("Generated main menu ID: " + mainMenuId);
            }
            
            // 关闭当前PreparedStatement
            pstmt.close();
            
            // 2. 插入流域管理子菜单 - 流域管理首页
            String insertIndexMenuSql = "INSERT INTO SYS_MENU (MENU_NAME, PARENT_ID, ORDER_NUM, PATH, COMPONENT, ROUTE_NAME, IS_FRAME, IS_CACHE, MENU_TYPE, VISIBLE, STATUS, PERMS, ICON, CREATE_TIME, UPDATE_TIME) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            
            pstmt = conn.prepareStatement(insertIndexMenuSql);
            pstmt.setString(1, "流域管理首页");
            pstmt.setLong(2, mainMenuId); // 父菜单ID，指向主菜单
            pstmt.setInt(3, 101); // 排序号
            pstmt.setString(4, "index"); // 路由地址
            pstmt.setString(5, "watershed/index"); // 组件路径
            pstmt.setString(6, "WatershedIndex"); // 路由名称
            pstmt.setString(7, "1"); // 是否为外链，1否
            pstmt.setString(8, "1"); // 是否缓存，1不缓存
            pstmt.setString(9, "C"); // 类型，C菜单
            pstmt.setString(10, "0"); // 显示状态，0显示
            pstmt.setString(11, "0"); // 菜单状态，0正常
            pstmt.setString(12, ""); // 权限字符串
            pstmt.setString(13, "water"); // 菜单图标
            pstmt.setTimestamp(14, timestamp);
            pstmt.setTimestamp(15, timestamp);
            
            int indexRowsAffected = pstmt.executeUpdate();
            System.out.println("Inserted index menu rows: " + indexRowsAffected);
            
            // 关闭当前PreparedStatement
            pstmt.close();
            
            // 3. 插入流域管理子菜单 - 水文水资源管理
            String insertHydrologicalMenuSql = "INSERT INTO SYS_MENU (MENU_NAME, PARENT_ID, ORDER_NUM, PATH, COMPONENT, ROUTE_NAME, IS_FRAME, IS_CACHE, MENU_TYPE, VISIBLE, STATUS, PERMS, ICON, CREATE_TIME, UPDATE_TIME) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            
            pstmt = conn.prepareStatement(insertHydrologicalMenuSql);
            pstmt.setString(1, "水文水资源管理");
            pstmt.setLong(2, mainMenuId); // 父菜单ID，指向主菜单
            pstmt.setInt(3, 102); // 排序号
            pstmt.setString(4, "hydrological"); // 路由地址
            pstmt.setString(5, "watershed/hydrological/index"); // 组件路径
            pstmt.setString(6, "Hydrological"); // 路由名称
            pstmt.setString(7, "1"); // 是否为外链，1否
            pstmt.setString(8, "1"); // 是否缓存，1不缓存
            pstmt.setString(9, "C"); // 类型，C菜单
            pstmt.setString(10, "0"); // 显示状态，0显示
            pstmt.setString(11, "0"); // 菜单状态，0正常
            pstmt.setString(12, ""); // 权限字符串
            pstmt.setString(13, "hydrological"); // 菜单图标
            pstmt.setTimestamp(14, timestamp);
            pstmt.setTimestamp(15, timestamp);
            
            int hydrologicalRowsAffected = pstmt.executeUpdate();
            System.out.println("Inserted hydrological menu rows: " + hydrologicalRowsAffected);
            
            // 提交事务
            conn.commit();
            System.out.println("Menu insertion completed successfully!");
            
        } catch (SQLException e) {
            System.out.println("SQL Exception: " + e.getMessage());
            // 回滚事务
            if (conn != null) {
                try {
                    conn.rollback();
                    System.out.println("Transaction rolled back due to error.");
                } catch (SQLException rollbackEx) {
                    System.out.println("Failed to rollback transaction: " + rollbackEx.getMessage());
                }
            }
        } finally {
            // 关闭资源
            try {
                if (pstmt != null) pstmt.close();
                if (conn != null) conn.close();
                System.out.println("Database connection closed.");
            } catch (SQLException e) {
                System.out.println("Failed to close resources: " + e.getMessage());
            }
        }
    }
}