ylrz_saas_pharmacy/java/scripts/DeleteLegacyLobsterMenu2951Master.java

import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Set;

/** Delete legacy menu 2951 tree from master tenant_sys_menu. */
public class DeleteLegacyLobsterMenu2951Master {
    private static final String URL = "jdbc:mysql://cq-cdb-8fjmemkb.sql.tencentcdb.com:27220/ylrz_saas?useSSL=false&serverTimezone=GMT%2B8";
    private static final String USER = "root";
    private static final String PASS = "Ylrz_1q2w3e4r5t6y";
    private static final long LEGACY_ROOT = 2951L;

    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        try (Connection c = DriverManager.getConnection(URL, USER, PASS)) {
            if (!exists(c, LEGACY_ROOT)) {
                System.out.println("2951 not found in tenant_sys_menu, skip");
                return;
            }
            Set<Long> ids = collect(c, LEGACY_ROOT);
            System.out.println("delete tenant_sys_menu ids=" + ids);
            if (tableExists(c, "sys_role_menu")) {
                deleteIn(c, "sys_role_menu", "menu_id", ids);
            }
            List<Long> ordered = new ArrayList<>(ids);
            Collections.sort(ordered, Collections.reverseOrder());
            int n = 0;
            try (PreparedStatement ps = c.prepareStatement("DELETE FROM tenant_sys_menu WHERE menu_id = ?")) {
                for (Long id : ordered) {
                    ps.setLong(1, id);
                    n += ps.executeUpdate();
                }
            }
            System.out.println("deleted rows=" + n);
        }
    }

    static Set<Long> collect(Connection c, long root) throws SQLException {
        Set<Long> all = new LinkedHashSet<>();
        walk(c, root, all);
        all.add(root);
        return all;
    }

    static void walk(Connection c, long parent, Set<Long> acc) throws SQLException {
        try (PreparedStatement ps = c.prepareStatement("SELECT menu_id FROM tenant_sys_menu WHERE parent_id = ?")) {
            ps.setLong(1, parent);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    long id = rs.getLong(1);
                    if (acc.add(id)) walk(c, id, acc);
                }
            }
        }
    }

    static void deleteIn(Connection c, String table, String col, Set<Long> ids) throws SQLException {
        StringBuilder sb = new StringBuilder("DELETE FROM ").append(table).append(" WHERE ").append(col).append(" IN (");
        int i = 0;
        for (Long ignored : ids) {
            if (i++ > 0) sb.append(',');
            sb.append('?');
        }
        sb.append(')');
        try (PreparedStatement ps = c.prepareStatement(sb.toString())) {
            i = 1;
            for (Long id : ids) ps.setLong(i++, id);
            int n = ps.executeUpdate();
            if (n > 0) System.out.println("deleted " + table + " rows=" + n);
        }
    }

    static boolean exists(Connection c, long id) throws SQLException {
        try (PreparedStatement ps = c.prepareStatement("SELECT 1 FROM tenant_sys_menu WHERE menu_id=?")) {
            ps.setLong(1, id);
            try (ResultSet rs = ps.executeQuery()) { return rs.next(); }
        }
    }

    static boolean tableExists(Connection c, String t) throws SQLException {
        try (ResultSet rs = c.getMetaData().getTables(null, null, t, null)) { return rs.next(); }
    }
}