1 先了解,系统都有哪些主流 业务
2 在给,数据库中所有的表加上触发器,监听业务流程中对哪些表进行了新增修改删除操作
3 再次执行系统的某个业务流程,就可以清晰的看到设计哪些表和数据状态变化了
4 如果一个系统有多个业务模块,就可以使用navicat分组功能,将一个业务涉及表放在一个组中
5 完成以上步骤就可以清晰的了解,主业务 涉及哪些表,都有哪些操作了。
监听库中表触发器方式_代码实例_复制可用
package com.keenly.common.utils;
import lombok.extern.slf4j.Slf4j;
import java.io.*;
import java.sql.*;
import java.time.Duration;
import java.time.Instant;
import java.util.ArrayList;
import java.util.List;
@Slf4j
public class 监听库中表触发器方式_代码实例_复制可用 {
// 扫描文件位置,一般是项目代码中使用表的位置
private static String dir = "D:\\xiaoling_file\\code\\platformkeenly\\src\\main";
private static String dbName = "testwtt";
private static String databaseUrl = "jdbc:mysql://127.0.0.1:3306/"+dbName+"?useUnicode=true&characterEncoding=UTF-8&rewriteBatchedStatements=true&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai";
private static String username = "root";
private static String password = "root";
public static void main(String[] args) {
List<String> tableNames = 生成触发器SQL语句_增加cz_time字段_删除每个表主键_返回库中所有表名();
// 在项目中搜索_都使用了那些表(tableNames);
}
private static void 在项目中搜索_都使用了那些表(List<String> tableNames) {
// 记录开始时间
Instant startTime = Instant.now();
控制台_出统计都有那些表名(tableNames);
// 2 指定要搜索的文件目录和文本条件,是list内容
File directory = new File(dir);
// 在目录中查询指定文本条件的文件
List<String> foundStrings = 查询指定目录下_文本符合条件的内容(directory, tableNames);
int i = 0;
for (String foundString : foundStrings) {
System.out.println(foundString);
i++;
}
System.out.println(i);
//3 输出_未在文件中找到的_搜索字符串_集合
List<String> notFoundStrings = 输出_未在文件中找到的_搜索字符串_集合(tableNames, foundStrings);
int i1 = 0;
for (String notFoundString : notFoundStrings) {
System.out.println(notFoundString);
i1++;
}
System.out.println(i1);
// TODO 后期可以根据 要求 增加把 未在文件中找到的_搜索字符串_集合 表删除
// 记录结束时间
Instant endTime = Instant.now();
输出执行时间(startTime, endTime);
}
private static void 输出执行时间(Instant startTime, Instant endTime) {
// 计算执行时间
Duration duration = Duration.between(startTime, endTime);
// 显示开始时间和结束时间(这里只显示时间部分,不显示日期)
System.out.println("开始时间: " + startTime.atZone(java.time.ZoneId.systemDefault()).toLocalTime());
System.out.println("结束时间: " + endTime.atZone(java.time.ZoneId.systemDefault()).toLocalTime());
// 显示执行时间(秒和毫秒)
long seconds = duration.getSeconds();
long nanos = duration.getNano();
long millis = nanos / 1_000_000; // 将纳秒转换为毫秒
System.out.printf("执行时间: %d 秒 %d 毫秒%n", seconds, millis % 1000);
}
private static void 控制台_出统计都有那些表名(List<String> tableNames) {
int i = 0;
for (String item : tableNames) {
System.out.println(item);
i++;
}
System.out.println(i);
}
/**
* 查询指定目录下_文本符合条件的内容
*
* @param directory 搜索目录
* @param searchStrings 指定的搜索字符串列表
*/
private static List<String> 查询指定目录下_文本符合条件的内容(File directory, List<String> searchStrings) {
List<File> files = listFilesInDirectory(directory);
//foundStrings 存储的是在文本中找到的内容
List<String> foundStrings = new ArrayList<>();
// 遍历文件搜索指定字符串
files.parallelStream().forEach(file -> {
// for (File file : files) {
try (BufferedReader reader = new BufferedReader(new FileReader(file))) {
String line;
// 在文件中逐行搜索
while ((line = reader.readLine()) != null) {
for (String searchString : searchStrings) {
// 如果找到且之前未记录,则添加到已找到列表
// if (line.contains(searchString) && !foundStrings.contains(searchString)) {
if (containsIgnoreCase(line, searchString) && !containsIgnoreCase(foundStrings, searchString)) {
//foundStrings 存储的是在文本中找到的内容
foundStrings.add(searchString);
}
}
// 如果已找到所有搜索字符串,则停止当前文件的搜索
if (foundStrings.size() == searchStrings.size()) {
break;
}
}
} catch (IOException e) {
e.printStackTrace();
}
// }
});
return foundStrings;
}
/**
* 判断字符串是否包含另一个字符串(不区分大小写)
*
* @param original 原始字符串
* @param search 要搜索的字符串
* @return 如果原始字符串包含搜索字符串(不区分大小写),则返回true;否则返回false
*/
public static boolean containsIgnoreCase(String original, String search) {
if (original == null || search == null) {
return false; // 如果任一字符串为null,则返回false
}
// 将两个字符串都转换为小写(或大写),然后进行比较
return original.toLowerCase().contains(search.toLowerCase());
}
/**
* 判断列表中是否包含某个不区分大小写的字符串
*
* @param list 要检查的列表
* @param searchTerm 要搜索的字符串
* @return 如果列表中包含不区分大小写的搜索字符串,则返回true;否则返回false
*/
public static boolean containsIgnoreCase(List<String> list, String searchTerm) {
if (list == null || searchTerm == null) {
return false; // 如果列表或搜索字符串为null,则返回false
}
// 遍历列表,将每个字符串和搜索字符串都转换为小写,然后进行比较
for (String str : list) {
if (str.toLowerCase().equals(searchTerm.toLowerCase())) {
return true; // 如果找到匹配的字符串,则返回true
}
}
// 如果没有找到匹配的字符串,则返回false
return false;
}
/**
* 列出指定目录下的所有文件(包括子目录中的文件)
*
* @param directory 指定目录
* @return 文件列表
*/
private static List<File> listFilesInDirectory(File directory) {
List<File> fileList = new ArrayList<>();
File[] files = directory.listFiles();
if (files != null) {
// 递归列出所有文件
for (File file : files) {
if (file.isDirectory()) {
fileList.addAll(listFilesInDirectory(file));
} else {
fileList.add(file);
}
}
}
return fileList;
}
private static List<String> 输出_未在文件中找到的_搜索字符串_集合(List<String> searchStrings, List<String> foundStrings) {
// 输出未在文件中找到的搜索字符串
/*这段代码的意思是检查searchStrings列表中的每个搜索字符串是否在foundStrings列表中。
foundStrings列表记录了在遍历文件时已经找到的匹配字符串。
如果某个searchString不在foundStrings中,说明在遍历过的文件中没有找到这个字符串。*/
List<String> notFoundStrings = new ArrayList<>();
for (String searchString : searchStrings) {
if (!foundStrings.contains(searchString)) {
notFoundStrings.add(searchString);
}
}
return notFoundStrings;
}
public static List<String> 生成触发器SQL语句_增加cz_time字段_删除每个表主键_返回库中所有表名() {
// 设置SQL变量
String columnName1 = "cz";
String columnName2 = "time";
String columnType1 = "VARCHAR(255) DEFAULT NULL";
String columnType2 = "DATETIME DEFAULT NULL";
// 准备Statement用于执行SQL
PreparedStatement preparedStatement = null;
try (Connection connection = DriverManager.getConnection(databaseUrl, username, password)) {
List<String> list = new ArrayList<>();
FileWriter fileWriterCFQ = new FileWriter("生成触发器sql脚本.txt");
// 获取指定数据库的所有表名
ResultSet tables = getTables(connection, dbName);
while (tables.next()) {
// 获取表名
String tableName = tables.getString("table_name");
list.add(tableName);
生成触发器SQL语句_写入文本中(connection, tableName, fileWriterCFQ);
preparedStatement = 给库中所有表_都增加cz_time字段(tableName, columnName1, columnType1, columnName2, columnType2, connection);
preparedStatement = 删除日志库每个表主键_避免记录出现重复问题(tableName, connection);
}
关闭(preparedStatement, fileWriterCFQ, tables, connection);
return list;
} catch (SQLException | IOException e) {
e.printStackTrace();
}
return null;
}
private static void 关闭(PreparedStatement preparedStatement, FileWriter fileWriterCFQ, ResultSet tables, Connection connection) throws SQLException, IOException {
// 处理可能出现的异常(这里简化处理)
if (preparedStatement != null) {
preparedStatement.close();
}
// 关闭
fileWriterCFQ.close();
if (tables != null) {
tables.close();
}
if (connection != null) {
connection.close();
}
}
private static void 生成触发器SQL语句_写入文本中(Connection connection, String tableName, FileWriter fileWriterCFQ) throws SQLException, IOException {
// 获取表的所有字段
ResultSet columns = getColumns(connection, dbName, tableName);
StringBuilder formattedColumns = new StringBuilder();
while (columns.next()) {
String columnName = columns.getString("COLUMN_NAME");
formattedColumns.append(columnName).append(", ");
}
// 循环结束后,移除最后的逗号和空格
formattedColumns.setLength(formattedColumns.length() - 2);
// 生成触发器SQL语句,写入文本中
String triggerSQL = generateTriggerSQL(dbName, tableName, formattedColumns.toString());
fileWriterCFQ.write(triggerSQL);
}
private static boolean columnExists(Connection conn, String tableName, String columnName) throws SQLException {
DatabaseMetaData metaData = conn.getMetaData();
ResultSet columns = metaData.getColumns(null, null, tableName, columnName);
return columns.next();
}
private static PreparedStatement 删除日志库每个表主键_避免记录出现重复问题(String tableName, Connection connection) throws SQLException {
if (!primaryKeyExists(connection, tableName)) {
return null;
}
try {
PreparedStatement preparedStatement;
// 构建ALTER TABLE SQL语句
String sql = "ALTER TABLE " + tableName + " DROP PRIMARY KEY;";
// 执行SQL语句
preparedStatement = connection.prepareStatement(sql);
preparedStatement.executeUpdate();
return preparedStatement;
} catch (SQLException e) {
log.error("删除" + tableName + "表主键,异常:" + e.getMessage());
}
return null;
}
private static PreparedStatement 给库中所有表_都增加cz_time字段(String tableName, String columnName1, String columnType1, String columnName2, String columnType2, Connection connection) throws SQLException {
boolean cz = columnExists(connection, tableName, "cz");
boolean time = columnExists(connection, tableName, "time");
if (cz || time) {
return null;
}
PreparedStatement preparedStatement;
// 构建ALTER TABLE SQL语句
String sql = "ALTER TABLE " + tableName + " ADD " + columnName1 + " " + columnType1 + ", ADD " + columnName2 + " " + columnType2;
// 执行SQL语句
preparedStatement = connection.prepareStatement(sql);
preparedStatement.executeUpdate();
return preparedStatement;
}
private static boolean primaryKeyExists(Connection conn, String tableName) throws SQLException {
DatabaseMetaData metaData = conn.getMetaData();
ResultSet primaryKeyInfo = metaData.getPrimaryKeys(null, null, tableName);
boolean next = primaryKeyInfo.next();
return next; // 如果有结果则表示存在主键
}
private static ResultSet getTables(Connection connection, String dbName) throws SQLException {
DatabaseMetaData metaData = connection.getMetaData();
return metaData.getTables(dbName, null, "%", new String[]{"TABLE"});
}
private static ResultSet getColumns(Connection connection, String dbName, String tableName) throws SQLException {
DatabaseMetaData metaData = connection.getMetaData();
return metaData.getColumns(dbName, null, tableName, "%");
}
private static String generateTriggerSQL(String dbName, String tableName, String columns) {
String addPrefixNew = addPrefix("NEW", columns);
String addPrefixOLD = addPrefix("OLD", columns);
// 生成触发器 SQL 语句 DROP TRIGGER IF EXISTS `ysb_drawing_log1i`;
String insertTrigger =
"DROP TRIGGER IF EXISTS " + tableName + "_insert;\n" +
"delimiter ;;\n" +
"CREATE TRIGGER " + tableName + "_insert AFTER INSERT ON " + tableName + " FOR EACH ROW\n" +
"BEGIN\n" +
" INSERT INTO " + dbName + "_log." + tableName + " ( cz,time, " + columns + ")\n" +
" VALUES ( 'insert' , NOW() , " + addPrefixNew + " );\n" +
"END\n" +
";;\n" +
"delimiter ;\n\n";
String updateTrigger =
"DROP TRIGGER IF EXISTS " + tableName + "_update;\n" +
"delimiter ;;\n" +
"CREATE TRIGGER " + tableName + "_update AFTER UPDATE ON " + tableName + " FOR EACH ROW\n" +
"BEGIN\n" +
" INSERT INTO " + dbName + "_log." + tableName + " ( cz,time, " + columns + ")\n" +
" VALUES ( 'update' , NOW() , " + addPrefixNew + " );\n" +
"END\n" +
";;\n" +
"delimiter ;\n\n";
String deleteTrigger =
"DROP TRIGGER IF EXISTS " + tableName + "_delete;\n" +
"delimiter ;;\n" +
"CREATE TRIGGER " + tableName + "_delete AFTER DELETE ON " + tableName + " FOR EACH ROW\n" +
"BEGIN\n" +
" INSERT INTO " + dbName + "_log." + tableName + " ( cz,time, " + columns + ")\n" +
" VALUES ( 'delete' , NOW() , " + addPrefixOLD + " );\n" +
"END\n" +
";;\n" +
"delimiter ;\n\n";
return insertTrigger + updateTrigger + deleteTrigger;
}
private static String addPrefix(String prefix, String input) {
StringBuilder output = new StringBuilder();
String[] strings = input.split(",");
for (String str : strings) {
// output.append("NEW.").append(str.trim()).append(" , ");
output.append(prefix + ".").append(str.trim()).append(" , ");
}
// 删除最后多余的逗号和空格
if (output.length() > 2) {
output.delete(output.length() - 2, output.length());
}
return output.toString();
}
}