ylrz_saas_pharmacy/java/scripts/SyncCompanyMenuAll.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class SyncCompanyMenuAll {
    private static final String URL =
            "jdbc:mysql://cq-cdb-8fjmemkb.sql.tencentcdb.com:27220/fs_tenant_cs1"
                    + "?useSSL=false&characterEncoding=utf8&serverTimezone=GMT%2B8&allowMultiQueries=true";

    private static final String KEEP =
            "29900,32481,29949,29905,29950,29934,29906,3008,29912";

    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String sql =
                "INSERT 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, 338, s.remark "
                        + "FROM sys_menu s WHERE s.menu_id IN (" + KEEP + ") "
                        + "AND NOT EXISTS (SELECT 1 FROM company_menu x WHERE x.menu_id=s.menu_id);"
                        + "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, cm.company_id=338 "
                        + "WHERE sm.menu_id IN (" + KEEP + ");"
                        + "DELETE FROM company_menu WHERE (parent_id=29900 OR parent_id=29901) "
                        + "AND menu_id NOT IN (" + KEEP + ");";
        try (Connection c = DriverManager.getConnection(URL, "root", "Ylrz_1q2w3e4r5t6y");
             Statement st = c.createStatement()) {
            st.execute(sql);
            ResultSet rs = st.executeQuery(
                    "SELECT COUNT(*) FROM company_menu WHERE parent_id=29900 AND menu_type='C' AND visible='0' AND status='0'");
            rs.next();
            System.out.println("company_menu visible leaf count: " + rs.getInt(1));
        }
    }
}