mysql 触发器所有表_mysql给所有的表创建触发器

从网上复制的一份JDBCUtils

public class JDBCUtils {

/**

* 获取连接方法

*

* @return

*/

public static Connection getConnection() {

Connection conn = null;

try {

Class.forName("com.mysql.jdbc.Driver");

conn = DriverManager.getConnection("jdbc:mysql://localhost:32123/kass", "root", "");

} catch (Exception e) {

e.printStackTrace();

}

return conn;

}

/**

* 释放资源方法

* @param conn

* @param pstmt

* @param rs

*/

public static void release(Connection conn, PreparedStatement pstmt, ResultSet rs) {

if (rs != null) {

try {

rs.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

if (pstmt != null) {

try {

pstmt.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

if (conn != null) {

try {

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

}

在数据库中添加一个用来记录的表

DROP TABLE IF EXISTS `triggertable`;

CREATE TABLE `triggertable` (

`time` datetime NOT NULL,

`tableName` varchar(255) DEFAULT NULL,

`optype` varchar(255) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

批量增加触发器的代码

public class trigger {

//获取所有的表名

private ListgetAllTable( ){

ListresultList = new ArrayList<>();

Connection conn = null;

PreparedStatement pstmt = null;

ResultSet rs = null;

try {

//1、获取连接

conn = JDBCUtils.getConnection();

//2、获取预执行对象

String sql = "show TABLES";

pstmt = conn.prepareStatement(sql);

//4、执行查询或者执行操作

rs = pstmt.executeQuery();

while(rs.next()){

resultList.add( rs.getString("Tables_in_kass") );

}

} catch (SQLException e) {

e.printStackTrace();

}finally{

//5、释放资源

JDBCUtils.release(conn, pstmt, rs);

}

//关闭数据库连接资源

return resultList;

}

//批量删除触发器

@Test

public void delRiggerbatch(){

}

//批量执行触发器

@Test

public void excuterRiggerSql(){

ListallTable = getAllTable();

if(allTable==null || allTable.size()<=0)

return;

if(allTable.contains("triggertable")){

allTable.remove("triggertable");

}

//构建触发器语句 并执行触发器语句

ListtriggerInsertSqlList = buildInsertTriggerSql(allTable);

triggerInsertSqlList.forEach(item-> System.out.println(item));

if( triggerInsertSqlList!=null && triggerInsertSqlList.size()>0 ){

triggerInsertSqlList.forEach(itemSql->{

excuterRiggerSql(itemSql);

});

}

ListtriggerUpdateSqlList = buildUpdateTriggerSql(allTable);

if( triggerUpdateSqlList!=null && triggerUpdateSqlList.size()>0 ){

triggerUpdateSqlList.forEach(itemSql->{

excuterRiggerSql(itemSql);

});

}

ListtriggerDeleteSqlList = buildDeleteTriggerSql(allTable);

if(triggerDeleteSqlList!=null && triggerDeleteSqlList.size()>0){

triggerDeleteSqlList.forEach(itemSql->{

excuterRiggerSql(itemSql);

});

}

}

//执行一个触发器

public void excuterRiggerSql( String sql ){

Connection conn = null;

PreparedStatement pstmt = null;

ResultSet rs = null;

try {

//执行触发器语句

conn = JDBCUtils.getConnection();

//2、获取预执行对象

pstmt = conn.prepareStatement(sql);

//4、执行查询或者执行操作

pstmt.execute();

} catch (SQLException e) {

e.printStackTrace();

}finally {

//5、释放资源

JDBCUtils.release(conn, pstmt, rs);

}

}

public ListbuildInsertTriggerSql( ListallTable ){

if( allTable==null && allTable.size()<=0 ){

return null;

}

ListresultList = new ArrayList<>();

allTable.stream().forEach(item->{

String triggerInserSql = "CREATE DEFINER=`root`@`localhost` TRIGGER `insert_"+item+"` BEFORE INSERT ON "+item+" FOR EACH ROW INSERT INTO triggertable (time, tableName, optype) VALUES (now(), '"+item+"', 'insert');";

resultList.add(triggerInserSql);

});

return resultList;

}

public ListbuildUpdateTriggerSql( ListallTable ){

if( allTable==null && allTable.size()<=0 ){

return null;

}

ListresultList = new ArrayList<>();

allTable.stream().forEach(item->{

String triggerUpdateSql = "CREATE DEFINER=`root`@`localhost` TRIGGER `update_"+item+"` BEFORE UPDATE ON "+item+" FOR EACH ROW INSERT INTO triggertable (time, tableName, optype) VALUES (now(), '"+item+"', 'update');";

resultList.add(triggerUpdateSql);

});

return resultList;

}

public ListbuildDeleteTriggerSql( ListallTable ){

if( allTable==null && allTable.size()<=0 ){

return null;

}

ListresultList = new ArrayList<>();

allTable.stream().forEach(item->{

String triggerDeleteSql = "CREATE DEFINER=`root`@`localhost` TRIGGER `delete_"+item+"` BEFORE DELETE ON "+item+" FOR EACH ROW INSERT INTO triggertable (time, tableName, optype) VALUES (now(), '"+item+"', 'delete');";

resultList.add(triggerDeleteSql);

});

return resultList;

}

}

没有批量删除,采用备份数据库的方式,大量的创建连接对象,以及销毁连接对象.不知道能不能有更好的方式,

小心MySQLIntegrityConstraintViolationException的意思是违反了数据库的完整性约束,用来记录的表的主键一定不能重复