import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; /** Simplified lobster engine menus: 8 visible items under company_menu/sys_menu. */ public class ApplyLobsterSimplifiedMenus { private static final String HOST = "cq-cdb-8fjmemkb.sql.tencentcdb.com:27220"; private static final String USER = "root"; private static final String PASS = "Ylrz_1q2w3e4r5t6y"; private static final String KEEP = "29900,32481,29949,29905,29950,29934,29906,3008,29912"; private static final Object[][] MENUS = { {29900, "\u9f99\u867e\u5f15\u64ce", 0, 100, "/lobster", "", "M", "el-icon-cpu", "0", null}, {32481, "\u5de5\u4f5c\u6d41\u6a21\u7248", 29900, 1, "workflowLobster", "company/workflowLobster/index", "C", "el-icon-share", "0", "workflow:lobster:edit"}, {29949, "\u5de5\u4f5c\u6d41\u6267\u884c", 29900, 2, "workflow-execution", "lobster/workflow-execution/index", "C", "el-icon-guide", "0", "workflow:lobster:edit"}, {29905, "\u5b9e\u4f8b\u76d1\u63a7", 29900, 3, "instance", "lobster/instance/index", "C", "el-icon-monitor", "0", "workflow:lobster:query"}, {29950, "\u5ba2\u6237\u753b\u50cf", 29900, 4, "profile", "lobster/profile/hub", "C", "el-icon-user", "0", "workflow:lobster:profile"}, {29934, "\u5bf9\u5916\u89d2\u8272\u914d\u7f6e", 29900, 5, "identity-config", "lobster/identity-config/index", "C", "el-icon-view", "0", "workflow:lobster:identity"}, {29906, "\u8bdd\u672f\u4f18\u5316\u5ba1\u6838", 29900, 6, "optimization", "lobster/optimization/index", "C", "el-icon-eye-open", "0", "workflow:lobster:query"}, {3008, "\u52a8\u6001\u8282\u70b9\u5ba1\u6279", 29900, 7, "dynamic-impl", "lobster/dynamic-impl/index", "C", "el-icon-connection", "0", "workflow:lobster:audit"}, {29912, "\u805a\u5408\u804a\u5929", 29900, 8, "chat-aggregate", "lobster/chat-aggregate/index", "C", "el-icon-message", "0", "workflow:lobster:query"}, }; public static void main(String[] args) throws Exception { Class.forName("com.mysql.cj.jdbc.Driver"); String tenantDb = args.length > 0 ? args[0] : "fs_tenant_cs1"; String url = "jdbc:mysql://" + HOST + "/" + tenantDb + "?useSSL=false&serverTimezone=GMT%2B8&characterEncoding=utf8&allowMultiQueries=true"; try (Connection c = DriverManager.getConnection(url, USER, PASS)) { c.setAutoCommit(false); try (Statement st = c.createStatement()) { st.executeUpdate("DELETE FROM sys_role_menu WHERE menu_id IN (" + "SELECT menu_id FROM (SELECT menu_id FROM sys_menu WHERE (menu_id=29900 OR parent_id=29900 OR parent_id=29901) " + "AND menu_id NOT IN (" + KEEP + ")) t)"); st.executeUpdate("DELETE FROM company_role_menu WHERE menu_id IN (" + "SELECT menu_id FROM (SELECT menu_id FROM company_menu WHERE (menu_id=29900 OR parent_id=29900 OR parent_id=29901) " + "AND menu_id NOT IN (" + KEEP + ")) t)"); st.executeUpdate("DELETE FROM sys_menu WHERE (menu_id=29900 OR parent_id=29900 OR parent_id=29901) " + "AND menu_id NOT IN (" + KEEP + ")"); st.executeUpdate("DELETE FROM company_menu WHERE (menu_id=29900 OR parent_id=29900 OR parent_id=29901) " + "AND menu_id NOT IN (" + KEEP + ")"); } String replaceSql = "REPLACE INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, " + "menu_type, icon, visible, status, is_frame, is_cache, perms, create_by, create_time, remark) " + "VALUES (?,?,?,?,?, ?,?,?, ?, '0',0,0, ?, 'admin', NOW(), NULL)"; try (PreparedStatement ps = c.prepareStatement(replaceSql)) { for (Object[] row : MENUS) { ps.setInt(1, (Integer) row[0]); ps.setString(2, (String) row[1]); ps.setInt(3, (Integer) row[2]); ps.setInt(4, (Integer) row[3]); ps.setString(5, (String) row[4]); ps.setString(6, (String) row[5]); ps.setString(7, (String) row[6]); ps.setString(8, (String) row[7]); ps.setString(9, (String) row[8]); ps.setString(10, (String) row[9]); ps.addBatch(); } ps.executeBatch(); } try (Statement st = c.createStatement()) { st.executeUpdate( "INSERT IGNORE INTO company_menu (menu_id, menu_name, parent_id, order_num, path, component, " + "is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, company_id, remark) " + "SELECT s.menu_id, s.menu_name, s.parent_id, s.order_num, s.path, s.component, " + "s.is_frame, s.is_cache, s.menu_type, s.visible, s.status, s.perms, s.icon, s.create_by, s.create_time, c.company_id, s.remark " + "FROM sys_menu s CROSS JOIN (SELECT DISTINCT company_id FROM company_menu WHERE company_id IS NOT NULL UNION SELECT 338) c " + "WHERE s.menu_id IN (" + KEEP + ") " + "AND NOT EXISTS (SELECT 1 FROM company_menu x WHERE x.menu_id=s.menu_id AND x.company_id=c.company_id)"); st.executeUpdate( "UPDATE company_menu cm INNER JOIN sys_menu sm ON cm.menu_id=sm.menu_id " + "SET cm.menu_name=sm.menu_name, cm.parent_id=sm.parent_id, cm.order_num=sm.order_num, " + "cm.path=sm.path, cm.component=sm.component, cm.is_frame=sm.is_frame, cm.is_cache=sm.is_cache, " + "cm.menu_type=sm.menu_type, cm.visible=sm.visible, cm.status=sm.status, cm.perms=sm.perms, " + "cm.icon=sm.icon, cm.remark=sm.remark " + "WHERE sm.menu_id IN (" + KEEP + ")"); st.executeUpdate( "INSERT IGNORE INTO sys_role_menu (role_id, menu_id) " + "SELECT r.role_id, s.menu_id FROM sys_role_menu r CROSS JOIN sys_menu s " + "WHERE r.menu_id=29900 AND s.parent_id=29900 AND s.menu_type='C' AND s.visible='0' " + "AND NOT EXISTS (SELECT 1 FROM sys_role_menu x WHERE x.role_id=r.role_id AND x.menu_id=s.menu_id)"); st.executeUpdate( "INSERT IGNORE INTO company_role_menu (role_id, menu_id) " + "SELECT r.role_id, s.menu_id FROM company_role_menu r CROSS JOIN sys_menu s " + "WHERE r.menu_id=29900 AND s.parent_id=29900 AND s.menu_type='C' AND s.visible='0' " + "AND NOT EXISTS (SELECT 1 FROM company_role_menu x WHERE x.role_id=r.role_id AND x.menu_id=s.menu_id)"); } c.commit(); printMenus(c, "company_menu"); printMenus(c, "sys_menu"); System.out.println("Applied simplified lobster menus to " + tenantDb); } } private static void printMenus(Connection c, String table) throws Exception { System.out.println("\n=== " + table + " visible under 29900 ==="); try (Statement st = c.createStatement(); ResultSet rs = st.executeQuery( "SELECT menu_id, menu_name, order_num, path FROM " + table + " WHERE parent_id=29900 AND menu_type='C' AND visible='0' AND status='0' " + "ORDER BY order_num, menu_id")) { int n = 0; while (rs.next()) { n++; System.out.printf("%2d. [%d] %s | %s%n", n, rs.getLong("menu_id"), rs.getString("menu_name"), rs.getString("path")); } System.out.println("count=" + n); } } }