import java.nio.file.*; import java.sql.*; import java.util.*; /** 阶段4 验收:sql/perm.sql 在 dev 库连续执行两遍,比对 9 张表行数与关键数据是否稳定。 */ public class PermSqlIdempotencyCheck { static final String URL = "jdbc:postgresql://192.168.0.106:5432/zsjz-ai"; static final String[] TABLES = {"perm_dept", "perm_user", "perm_post", "perm_role", "perm_menu", "perm_user_role", "perm_role_menu", "perm_role_dept", "perm_user_post"}; public static void main(String[] args) throws Exception { String sql = Files.readString(Path.of(args.length > 0 ? args[0] : "sql/perm.sql")); List stmts = split(sql); System.out.println("解析语句数: " + stmts.size()); try (Connection c = DriverManager.getConnection(URL, "postgres", "postgres")) { c.setAutoCommit(true); Map before = counts(c); System.out.println("执行前行数: " + before); for (int round = 1; round <= 2; round++) { long t0 = System.currentTimeMillis(); int ok = 0; for (String s : stmts) { try (Statement st = c.createStatement()) { st.execute(s); ok++; } catch (SQLException e) { System.out.println("第" + round + "遍失败语句: " + s.substring(0, Math.min(120, s.length()))); throw e; } } Map after = counts(c); System.out.println("第" + round + "遍执行成功 " + ok + " 条, 耗时 " + (System.currentTimeMillis() - t0) + "ms, 行数: " + after); if (round == 2 && !after.equals(before)) { System.out.println("!! 第二遍后行数与执行前不一致(存在非幂等写入)"); } } System.out.println("--- 关键数据校验 ---"); query(c, "SELECT user_id, user_name, nick_name, status, del_flag, left(password,7) AS pwd_prefix FROM perm_user ORDER BY user_id"); query(c, "SELECT ur.user_id, r.role_key FROM perm_user_role ur JOIN perm_role r ON r.role_id=ur.role_id ORDER BY ur.user_id"); query(c, "SELECT (SELECT count(*) FROM perm_menu) AS menu_total, (SELECT count(*) FROM perm_role_menu WHERE role_id=1) AS superadmin_bound"); query(c, "SELECT count(*) AS orphan_user_role FROM perm_user_role ur WHERE NOT EXISTS (SELECT 1 FROM perm_user u WHERE u.user_id=ur.user_id)"); query(c, "SELECT count(*) AS orphan_role_menu FROM perm_role_menu rm WHERE NOT EXISTS (SELECT 1 FROM perm_menu m WHERE m.menu_id=rm.menu_id)"); query(c, "SELECT count(*) AS sys_user_total, (SELECT count(*) FROM perm_user) AS perm_user_total FROM sys_user"); } } static Map counts(Connection c) throws SQLException { Map m = new LinkedHashMap<>(); for (String t : TABLES) { try (Statement st = c.createStatement(); ResultSet rs = st.executeQuery("SELECT count(*) FROM " + t)) { rs.next(); m.put(t, rs.getLong(1)); } } return m; } static void query(Connection c, String sql) throws SQLException { System.out.println("SQL> " + sql); try (Statement st = c.createStatement(); ResultSet rs = st.executeQuery(sql)) { ResultSetMetaData md = rs.getMetaData(); while (rs.next()) { StringBuilder sb = new StringBuilder(" "); for (int i = 1; i <= md.getColumnCount(); i++) { sb.append(md.getColumnLabel(i)).append('=').append(rs.getString(i)).append(" "); } System.out.println(sb); } } } /** 去掉 -- 行注释后按分号切分,忽略单引号字符串内的分号 */ static List split(String sql) { StringBuilder noComments = new StringBuilder(); for (String line : sql.split("\n")) { String trimmed = line.stripLeading(); if (trimmed.startsWith("--")) { continue; } noComments.append(line).append('\n'); } List out = new ArrayList<>(); StringBuilder cur = new StringBuilder(); boolean inStr = false; String s = noComments.toString(); for (int i = 0; i < s.length(); i++) { char ch = s.charAt(i); if (ch == '\'') { inStr = !inStr; } if (ch == ';' && !inStr) { String stmt = cur.toString().trim(); if (!stmt.isEmpty()) { out.add(stmt); } cur.setLength(0); } else { cur.append(ch); } } if (!cur.toString().trim().isEmpty()) { out.add(cur.toString().trim()); } return out; } }