package org.jeecg.modules.system.controller;
|
|
import io.swagger.annotations.Api;
|
import io.swagger.annotations.ApiOperation;
|
import lombok.extern.slf4j.Slf4j;
|
import org.jeecg.common.api.vo.Result;
|
import org.jeecg.common.aspect.annotation.AutoLog;
|
import org.jeecg.modules.system.vo.TriggerVo;
|
import org.springframework.web.bind.annotation.GetMapping;
|
import org.springframework.web.bind.annotation.RequestBody;
|
import org.springframework.web.bind.annotation.RequestMapping;
|
import org.springframework.web.bind.annotation.RestController;
|
|
import java.io.FileWriter;
|
import java.io.IOException;
|
import java.sql.*;
|
import java.util.ArrayList;
|
import java.util.List;
|
|
/**
|
* @Author: Lius
|
* @CreateTime: 2025-06-26
|
* @Description:
|
*/
|
@Api(tags = "触发器")
|
@RestController
|
@RequestMapping("/sys/trigger")
|
@Slf4j
|
public class TriggerCreateController {
|
|
@AutoLog(value = "触发器-生成触发器")
|
@ApiOperation(value = "触发器-生成触发器", notes = "触发器-生成触发器")
|
@GetMapping(value = "/list")
|
public Result create(@RequestBody TriggerVo triggerVo) {
|
String schemaName = "dbo"; // SQL Server 默认架构
|
String logTableName = "log_table"; // 日志表名
|
|
try (Connection connection = DriverManager.getConnection(triggerVo.getJdbcUrl(), triggerVo.getUsername(), triggerVo.getPassword())) {
|
// 获取指定架构下的所有表
|
List<String> tables = getTables(connection, schemaName);
|
|
// 生成触发器 SQL 并保存到文件
|
generateTriggersForTables(connection, schemaName, logTableName, tables);
|
|
} catch (SQLException | IOException e) {
|
e.printStackTrace();
|
}
|
|
return Result.ok();
|
}
|
|
/**
|
* 获取指定架构下的所有表
|
*/
|
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("成功生成触发器并保存到Triggers.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();
|
}
|
}
|
