From 274d27015f46a692015dbe0dfc8f8a453df3a2c2 Mon Sep 17 00:00:00 2001
From: lyh <925863403@qq.com>
Date: 星期三, 25 六月 2025 14:07:14 +0800
Subject: [PATCH] 同步工控网
---
lxzn-module-system/lxzn-system-start/src/main/java/org/jeecg/codegenerate/CreateTrigger.java | 199 +++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 199 insertions(+), 0 deletions(-)
diff --git a/lxzn-module-system/lxzn-system-start/src/main/java/org/jeecg/codegenerate/CreateTrigger.java b/lxzn-module-system/lxzn-system-start/src/main/java/org/jeecg/codegenerate/CreateTrigger.java
new file mode 100644
index 0000000..a5085dc
--- /dev/null
+++ b/lxzn-module-system/lxzn-system-start/src/main/java/org/jeecg/codegenerate/CreateTrigger.java
@@ -0,0 +1,199 @@
+package org.jeecg.codegenerate;
+
+import java.io.FileWriter;
+import java.io.IOException;
+import java.sql.*;
+import java.util.ArrayList;
+import java.util.List;
+
+/**
+ * @Author: Lius
+ * @CreateTime: 2025-06-23
+ * @Description: SQL Server 瑙﹀彂鍣ㄧ敓鎴愬伐鍏�
+ */
+public class CreateTrigger {
+
+ // 鏁版嵁搴撹繛鎺ヤ俊鎭�
+ private static final String JDBC_URL = "jdbc:sqlserver://192.168.1.118:1433;databaseName=LXZN_TEST_HANGYU_GK";
+ private static final String USERNAME = "sa";
+ private static final String PASSWORD = "123";
+
+ public static void main(String[] args) {
+ String schemaName = "dbo"; // SQL Server 榛樿鏋舵瀯
+ String logTableName = "log_table"; // 鏃ュ織琛ㄥ悕
+
+ try (Connection connection = DriverManager.getConnection(JDBC_URL, USERNAME, PASSWORD)) {
+ // 鑾峰彇鎸囧畾鏋舵瀯涓嬬殑鎵�鏈夎〃
+ List<String> tables = getTables(connection, schemaName);
+
+ // 鐢熸垚瑙﹀彂鍣� SQL 骞朵繚瀛樺埌鏂囦欢
+ generateTriggersForTables(connection, schemaName, logTableName, tables);
+
+ } catch (SQLException | IOException e) {
+ e.printStackTrace();
+ }
+ }
+
+ /**
+ * 鑾峰彇鎸囧畾鏋舵瀯涓嬬殑鎵�鏈夎〃
+ */
+ private static List<String> getTables(Connection connection, String schemaName) throws SQLException {
+ List<String> tables = new ArrayList<>();
+ DatabaseMetaData metaData = connection.getMetaData();
+
+ try (ResultSet resultSet = metaData.getTables(null, schemaName, null, new String[]{"TABLE"})) {
+ while (resultSet.next()) {
+ String tableName = resultSet.getString("TABLE_NAME");
+ tables.add(tableName);
+ }
+ }
+
+ return tables;
+ }
+
+ /**
+ * 鑾峰彇琛ㄧ殑瀛楁鍒楄〃
+ */
+ private static List<String> getTableColumns(Connection connection, String schemaName, String tableName) throws SQLException {
+ List<String> columns = new ArrayList<>();
+ DatabaseMetaData metaData = connection.getMetaData();
+
+ try (ResultSet resultSet = metaData.getColumns(null, schemaName, tableName, null)) {
+ while (resultSet.next()) {
+ String columnName = resultSet.getString("COLUMN_NAME");
+ columns.add(columnName);
+ }
+ }
+
+ return columns;
+ }
+
+ /**
+ * 鑾峰彇琛ㄧ殑涓婚敭鍒�
+ */
+ private static String getPrimaryKeyColumn(Connection connection, String schemaName, String tableName) throws SQLException {
+ DatabaseMetaData metaData = connection.getMetaData();
+ try (ResultSet resultSet = metaData.getPrimaryKeys(null, schemaName, tableName)) {
+ if (resultSet.next()) {
+ return resultSet.getString("COLUMN_NAME");
+ }
+ }
+ return "id"; // 榛樿浣跨敤id浣滀负涓婚敭锛屽鏋滄病鏈夋壘鍒颁富閿�
+ }
+
+ /**
+ * 涓烘墍鏈夎〃鐢熸垚瑙﹀彂鍣�
+ */
+ private static void generateTriggersForTables(Connection connection, String schemaName, String logTableName, List<String> tables) throws SQLException, IOException {
+ try (FileWriter writer = new FileWriter("triggers.sql")) {
+ for (String tableName : tables) {
+ // 鑾峰彇琛ㄧ粨鏋勪俊鎭�
+ List<String> columns = getTableColumns(connection, schemaName, tableName);
+ String primaryKey = getPrimaryKeyColumn(connection, schemaName, tableName);
+
+ // 鐢熸垚瑙﹀彂鍣� SQL
+ String triggerSQL = generateTriggerSQL(schemaName, tableName, logTableName, columns, primaryKey);
+
+ // 灏嗙敓鎴愮殑瑙﹀彂鍣� SQL 鍐欏叆鏂囦欢
+ writer.write("-- 瑙﹀彂鍣ㄨ〃: " + tableName + "\n");
+ writer.write(triggerSQL);
+ writer.write("\n\n");
+
+ // 濡傛灉闇�瑕侊紝鍙互鐩存帴鍦ㄦ暟鎹簱涓垱寤鸿Е鍙戝櫒
+ // createTrigger(connection, triggerSQL);
+ }
+ System.out.println("鎴愬姛鐢熸垚瑙﹀彂鍣ㄥ苟淇濆瓨鍒癟riggers.sql涓�!");
+ }
+ }
+
+ /**
+ * 鐢熸垚 SQL Server 瑙﹀彂鍣� SQL
+ */
+ private static String generateTriggerSQL(String schemaName, String tableName, String logTableName, List<String> columns, String primaryKey) {
+ StringBuilder triggerSQL = new StringBuilder();
+
+ // 瑙﹀彂鍣ㄥご閮�
+ triggerSQL.append("CREATE TRIGGER [")
+ .append(tableName)
+ .append("_trigger]\n")
+ .append("ON [")
+ .append(schemaName)
+ .append("].[").append(tableName).append("]\n")
+ .append("AFTER INSERT, UPDATE, DELETE\n")
+ .append("AS\n")
+ .append("BEGIN\n")
+ .append(" SET NOCOUNT ON;\n\n");
+
+ // 鎻掑叆鎿嶄綔
+ triggerSQL.append(" -- 鎻掑叆鎿嶄綔\n")
+ .append(" IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)\n")
+ .append(" BEGIN\n")
+ .append(" INSERT INTO [").append(logTableName).append("] (action, create_time, sql_log)\n")
+ .append(" SELECT 'INSERT', GETDATE(), 'INSERT INTO [").append(tableName).append("] (");
+
+ // 鎷兼帴瀛楁鍚�
+ for (int i = 0; i < columns.size(); i++) {
+ triggerSQL.append("[").append(columns.get(i)).append("]");
+ if (i < columns.size() - 1) {
+ triggerSQL.append(", ");
+ }
+ }
+
+ triggerSQL.append(") VALUES (''' + ");
+
+ // 鎷兼帴瀛楁鍊�
+ for (int i = 0; i < columns.size(); i++) {
+ triggerSQL.append("ISNULL(CAST(i.").append(columns.get(i)).append(" AS NVARCHAR(MAX)), '') + '''");
+ if (i < columns.size() - 1) {
+ triggerSQL.append(", ''' + ");
+ }
+ }
+
+ triggerSQL.append(");'\n")
+ .append(" FROM inserted i;\n")
+ .append(" END\n\n");
+
+ // 鏇存柊鎿嶄綔
+ triggerSQL.append(" -- 鏇存柊鎿嶄綔\n")
+ .append(" IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)\n")
+ .append(" BEGIN\n")
+ .append(" INSERT INTO [").append(logTableName).append("] (action, create_time, sql_log)\n")
+ .append(" SELECT 'UPDATE', GETDATE(), 'UPDATE [").append(tableName).append("] SET ");
+
+ // 鎷兼帴鏇存柊瀛楁
+ for (int i = 0; i < columns.size(); i++) {
+ triggerSQL.append("[").append(columns.get(i)).append("] = ''' + ISNULL(CAST(i.").append(columns.get(i)).append(" AS NVARCHAR(MAX)), '') + '''");
+ if (i < columns.size() - 1) {
+ triggerSQL.append(", ");
+ }
+ }
+
+ triggerSQL.append(" WHERE [").append(primaryKey).append("] = ''' + ISNULL(CAST(d.").append(primaryKey).append(" AS NVARCHAR(MAX)), '') + ''';'\n")
+ .append(" FROM inserted i\n")
+ .append(" INNER JOIN deleted d ON i.").append(primaryKey).append(" = d.").append(primaryKey).append(";\n")
+ .append(" END\n\n");
+
+ // 鍒犻櫎鎿嶄綔
+ triggerSQL.append(" -- 鍒犻櫎鎿嶄綔\n")
+ .append(" IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)\n")
+ .append(" BEGIN\n")
+ .append(" INSERT INTO [").append(logTableName).append("] (action, create_time, sql_log)\n")
+ .append(" SELECT 'DELETE', GETDATE(), 'DELETE FROM [").append(tableName).append("] WHERE [").append(primaryKey).append("] = ''' + ISNULL(CAST(d.").append(primaryKey).append(" AS NVARCHAR(MAX)), '') + ''';'\n")
+ .append(" FROM deleted d;\n")
+ .append(" END\n\n");
+
+ triggerSQL.append("END;\n");
+
+ return triggerSQL.toString();
+ }
+
+// /**
+// * 鍦ㄦ暟鎹簱涓垱寤鸿Е鍙戝櫒
+// */
+// private static void createTrigger(Connection connection, String triggerSQL) throws SQLException {
+// try (Statement statement = connection.createStatement()) {
+// statement.execute(triggerSQL);
+// System.out.println("Trigger created successfully!");
+// }
+// }
+}
--
Gitblit v1.9.3