业务系统最终都会反馈到DB上,观察DB变化,可以知道业务系统的处理逻辑和处理方法。下面的代码,用来对MYSQL所有表建立触发器,结合JSP可以观察到DB的变化。附件是效果图。
[quote][color=red]下面的SQL脚本,做两件事,生成存储记录变化的表,和生成触发器SQL的存储过程[/color][/quote]
[quote][color=red]JSP脚本,用来观察数据变化[/color][/quote]
[quote][color=red]JSP脚本,用来删除记录表zz_modify中所有的数据的。[/color][/quote]
[quote][color=red]下面的SQL脚本,做两件事,生成存储记录变化的表,和生成触发器SQL的存储过程[/color][/quote]
drop TABLE if EXISTS zz_modify;
CREATE TABLE `zz_modify` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`content` text,
`createtime` datetime DEFAULT NULL,
`tablename` varchar(4000) DEFAULT NULL,
`oprtype` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- 存储过程,用来生成删除触发器的SQL。
drop PROCEDURE if EXISTS p_droptrgs;
CREATE PROCEDURE `p_droptrgs`()
BEGIN
-- 定义变量
declare tab_name varchar(400);
declare tmp_tabschema varchar(400) DEFAULT 'rmp_2';
declare v_sql_all text DEFAULT '';
declare cur_tab_done int DEFAULT 0;
-- 定义所有表的遍历游标
DECLARE cur_tab cursor for select table_name from information_schema.`TABLES` where TABLE_SCHEMA = tmp_tabschema and TABLE_NAME != 'zz_modify';
-- 将结束标志绑定到游标
declare continue handler FOR SQLSTATE '02000' SET cur_tab_done = 1;
-- 打开游标
OPEN cur_tab;
REPEAT
FETCH cur_tab into tab_name;
-- call p_createtrgsbytable(tmp_tabName, tmp_tabschema);
set v_sql_all = CONCAT(v_sql_all,'drop trigger if EXISTS trg_insert_', tab_name, '; \n');
set v_sql_all = CONCAT(v_sql_all,'drop trigger if EXISTS trg_update_', tab_name, '; \n');
set v_sql_all = CONCAT(v_sql_all,'drop trigger if EXISTS trg_delete_', tab_name, '; \n');
UNTIL cur_tab_done
end REPEAT;
CLOSE cur_tab;
select v_sql_all;
END;
drop FUNCTION if EXISTS f_createtrgsbytable;
CREATE FUNCTION `f_createtrgsbytable`(tab_name varchar(400), tab_schema varchar(400)) RETURNS text CHARSET utf8
DETERMINISTIC
BEGIN
-- 定义变量
declare tmp_col_name varchar(4000);
DECLARE v_sql_all longtext DEFAULT '';
declare v_sql_insert longtext DEFAULT '';
declare v_sql_update longtext DEFAULT '';
declare v_sql_delete longtext DEFAULT '';
declare cur_tab_done int DEFAULT 0;
-- 定义所有表的遍历游标
DECLARE cur_tab cursor for select COLUMN_NAME from information_schema.`COLUMNS` where table_name = tab_name and TABLE_SCHEMA = tab_schema;
-- 将结束标志绑定到游标
declare continue handler FOR SQLSTATE '02000' SET cur_tab_done = 1;
set v_sql_insert = CONCAT(v_sql_insert,'drop trigger if EXISTS trg_insert_', tab_name, ';');
set v_sql_update = CONCAT(v_sql_update,'drop trigger if EXISTS trg_update_', tab_name, ';');
set v_sql_delete = CONCAT(v_sql_delete,'drop trigger if EXISTS trg_delete_', tab_name, ';');
set v_sql_insert = concat(v_sql_insert, 'create trigger trg_insert_', tab_name, ' after insert on ', tab_name, ' for each row begin insert into zz_modify(content, createtime, tablename, oprtype) value(concat(');
set v_sql_update = concat(v_sql_update, 'create trigger trg_update_', tab_name, ' after update on ', tab_name, ' for each row begin insert into zz_modify(content, createtime, tablename, oprtype) value(concat(');
set v_sql_delete = concat(v_sql_delete, 'create trigger trg_delete_', tab_name, ' after delete on ', tab_name, ' for each row begin insert into zz_modify(content, createtime, tablename, oprtype) value(concat(');
-- 打开游标
OPEN cur_tab;
REPEAT
FETCH cur_tab into tmp_col_name;
set v_sql_insert = CONCAT(v_sql_insert, '\'#&old.', tmp_col_name, '=\',', '\'-\'', ',\'~new.', tmp_col_name, '=\',', 'IFNULL(new.', tmp_col_name, ', \'nu-ll\'),');
set v_sql_update = CONCAT(v_sql_update, '\'#&old.', tmp_col_name, '=\',', 'IFNULL(old.', tmp_col_name, ', \'nu-ll\') ,\'~new.', tmp_col_name, '=\',', 'IFNULL(new.', tmp_col_name, ', \'nu-ll\'),');
set v_sql_delete = CONCAT(v_sql_delete, '\'#&old.', tmp_col_name, '=\',', 'IFNULL(old.', tmp_col_name, ', \'nu-ll\') ,\'~new.', tmp_col_name, '=\',', '\'-\'', ',');
UNTIL cur_tab_done
end REPEAT;
CLOSE cur_tab;
-- 截取处理
set v_sql_insert = CONCAT(SUBSTRING(v_sql_insert,1,LENGTH(v_sql_insert) - 1), ') ,now(), \'', tab_name, '\', \'insert\' ); end;\n');
set v_sql_update = CONCAT(SUBSTRING(v_sql_update,1,LENGTH(v_sql_update) - 1), ') ,now(), \'', tab_name, '\', \'update\' ); end;\n');
set v_sql_delete = CONCAT(SUBSTRING(v_sql_delete,1,LENGTH(v_sql_delete) - 1), ') ,now(), \'', tab_name, '\', \'delete\' ); end;\n');
return CONCAT(v_sql_insert,v_sql_update,v_sql_delete);
END;
-- 执行存储过程,用来生成产生触发器的SQL。
drop PROCEDURE if EXISTS p_createtrgs;
CREATE PROCEDURE `p_createtrgs`()
BEGIN
-- 定义变量
declare tmp_tabName varchar(400);
declare tmp_tabschema varchar(400) DEFAULT 'rmp_2';
declare v_sql_all longtext DEFAULT '';
declare cur_tab_done int DEFAULT 0;
-- 定义所有表的遍历游标
DECLARE cur_tab cursor for select table_name from information_schema.`TABLES` where TABLE_SCHEMA = tmp_tabschema and TABLE_NAME != 'zz_modify';
-- 将结束标志绑定到游标
declare continue handler FOR SQLSTATE '02000' SET cur_tab_done = 1;
-- 打开游标
OPEN cur_tab;
REPEAT
FETCH cur_tab into tmp_tabName;
-- call p_createtrgsbytable(tmp_tabName, tmp_tabschema);
set v_sql_all = CONCAT(v_sql_all, f_createtrgsbytable(tmp_tabName, tmp_tabschema), '\n');
UNTIL cur_tab_done
end REPEAT;
CLOSE cur_tab;
select v_sql_all;
END;
[quote][color=red]JSP脚本,用来观察数据变化[/color][/quote]
<!--首先导入一些必要的packages-->
<%@page import="java.text.MessageFormat"%>
<%@ page import="java.io.*"%>
<%@ page import="java.util.*"%>
<!--告诉编译器使用SQL包-->
<%@ page import="java.sql.*" %>
<%@ page import="com.mysql.*" %>
<!--设置中文输出-->
<%@ page contentType="text/html; charset=UTF-8"%>
<html>
<head>
<title>数据变动观察</title>
<link rel="shortcut icon" href="http://a.fsdn.com/con/img/sftheme/favicon.ico">
</head>
<body>
<div style="margin:10px;width:100%;heigth:50px;">
<a href="del_modify.jsp" style="margin:0 5 0 5;">清理所有数据 </a>
<a href="comp.jsp" style="margin:0 5 0 5;">查询数据变化</a>
</div>
<%
Connection con;
Statement stmt;
ResultSet rs;
//加载驱动程序,下面的代码为加载MySQL驱动程序
Class.forName("com.mysql.jdbc.Driver");
//注册MySQL驱动程序
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
//用适当的驱动程序连接到数据库
//String dbUrl = "jdbc:mysql://172.16.34.12:3306/cec?user=root&password=abcd1001&useUnicode=true&characterEncoding=UTF-8";
//String dbUser = "cec"; //用户名
//String dbPwd = "cec"; //密码
String dbUrl = "jdbc:mysql://192.168.9.139:3306/cec_yxt?user=root&password=abcd1001&useUnicode=true&characterEncoding=UTF-8";
String dbUser = "root"; //用户名
String dbPwd = "123456"; //密码
//建立数据库连接
con = java.sql.DriverManager.getConnection(dbUrl, dbUser, dbPwd);
//创建一个JDBC声明
stmt = con.createStatement();
//查询记录
rs = stmt.executeQuery("select id,content,createtime,tablename,oprtype from zz_modify");
//输出查询结果
int idx = 0;
while (rs.next())
{
String content = rs.getString("content");
String createtime = rs.getString("createtime");
String tablename = rs.getString("tablename");
String oprtype = rs.getString("oprtype");
out.println("<table border=1 width='80%' style='margin-left:auto;margin-right:auto;margin-top:10px;margin-bottom:10px;'>");
out.println(MessageFormat.format("<caption style='margin-top:10px;margin-bottom:10px;'>第【{0}】次表【{1}】在【{2}】被修改【{3}】。</caption>", ""+(++idx), tablename, createtime, oprtype));
if (content == null || "".equals(content))
{
//打印所显示的数据
out.println("<tr width='100%'>"
+ "<td width='100%'>没有获取到修改内容。</td>"
+ "</tr>");
}
else
{
// 打印 表头
out.println("<tr width='100%' style='word-break: break-all;'>"
+ "<th width='5%'> 表字段 </td>"
+ "<th width='20%'> 旧的值 </td>"
+ "<th width='10%'> 新的值 </td>"
+ "</tr>");
StringTokenizer st = new StringTokenizer(content, "#&");
while (st.hasMoreTokens())
{
String row = st.nextToken();
String[] rowarr = row.split("~");
String oldContent = rowarr[0];
String newContent = rowarr[1];
String fieldName = oldContent.substring(oldContent.indexOf('.') + 1, oldContent.indexOf('='));
String oldValue = oldContent.substring(oldContent.indexOf('=') + 1);
String newValue = newContent.substring(newContent.indexOf('=') + 1);
if (newValue.equals(oldValue))
{
//打印所显示的数据
out.println("<tr width='100%' style='word-break: break-all;'>"
+ "<td width='5%'>" + fieldName + "</td>"
+ "<td width='20%'>" + oldValue + "</td>"
+ "<td width='10%'>" + newValue + "</td>"
+ "</tr>");
}
else
{
//打印所显示的数据
out.println("<tr width='100%' style='word-break: break-all;color:red;'>"
+ "<td width='5%'>" + fieldName + "</td>"
+ "<td width='20%'>" + oldValue + "</td>"
+ "<td width='10%'>" + newValue + "</td>"
+ "</tr>");
}
}
}
out.println("</table>");
}
//关闭数据库连结
rs.close();
stmt.close();
con.close();
%>
</body>
</html>
[quote][color=red]JSP脚本,用来删除记录表zz_modify中所有的数据的。[/color][/quote]
<!--首先导入一些必要的packages-->
<%@page import="java.text.MessageFormat"%>
<%@ page import="java.io.*"%>
<%@ page import="java.util.*"%>
<!--告诉编译器使用SQL包-->
<%@ page import="java.sql.*" %>
<%@ page import="com.mysql.*" %>
<!--设置中文输出-->
<%@ page contentType="text/html; charset=UTF-8"%>
<div style="margin:10px;width:100%;heigth:50px;">
<a href="del_modify.jsp" style="margin:0 5 0 5;">清理所有数据 </a>
<a href="comp.jsp" style="margin:0 5 0 5;">查询数据变化</a>
</div>
<%
Connection con;
Statement stmt;
ResultSet rs;
//加载驱动程序,下面的代码为加载MySQL驱动程序
Class.forName("com.mysql.jdbc.Driver");
//注册MySQL驱动程序
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
//用适当的驱动程序连接到数据库
//String dbUrl = "jdbc:mysql://172.16.34.12:3306/cec?user=root&password=abcd1001&useUnicode=true&characterEncoding=UTF-8";
//String dbUser = "cec"; //用户名
//String dbPwd = "cec"; //密码
String dbUrl = "jdbc:mysql://192.168.9.12:3306/cec_for_yxt_test?user=root&password=abcd1001&useUnicode=true&characterEncoding=UTF-8";
String dbUser = "cec"; //用户名
String dbPwd = "n2h@5B_AoP"; //密码
//建立数据库连接
con = java.sql.DriverManager.getConnection(dbUrl, dbUser, dbPwd);
//创建一个JDBC声明
stmt = con.createStatement();
//查询记录
stmt.execute("delete from zz_modify");
stmt.close();
con.close();
%>