package com.artfess.dataShare.util; import com.artfess.base.util.StringUtil; import com.artfess.base.util.UniqueIdUtil; import org.springframework.jdbc.core.JdbcTemplate; import java.util.HashMap; import java.util.Map; /** * mysql数据库操作工具类 */ public class MySQLUtils { public static void initChangeSource(JdbcTemplate template,String user, String pwd,String dbName){ createDataUser(template,user,pwd); createDataBase(template,dbName); createGrant(template,"select,insert,update,create,trigger",dbName,user); } //创建用户 public static void createDataUser(JdbcTemplate template,String user, String pwd){ String sql="create user "+user+"@'%' IDENTIFIED WITH mysql_native_password BY '"+pwd+"'"; template.execute(sql); } //删除用户 public static void dropDataUser(JdbcTemplate template,String user){ String sql = "drop user " + user + "@'%'"; template.execute(sql); } public static void updateDateUser(JdbcTemplate template,String user,String pwd){ String sql = "set password for '"+user+"'@'%' = '"+pwd+"'"; template.execute(sql); } //创建数据库 public static void createDataBase(JdbcTemplate template,String dbName){ String sql="create database "+dbName; template.execute(sql); } //用户数据库授权 默认全部权限 grant=select,insert,update public static void createGrant(JdbcTemplate template,String grant,String dbName,String user){ if(StringUtil.isEmpty(grant)){ grant="all"; } String sql="grant "+grant+" on "+dbName+".* to "+user; template.execute(sql); } public static void createGrant(JdbcTemplate template,String grant,String dbName,String tableName,String user){ if(StringUtil.isEmpty(grant)){ grant="all"; } String sql="grant "+grant+" on "+dbName+"."+tableName+" to "+user; template.execute(sql); } public static void revokeGrant(JdbcTemplate template,String grant,String dbName,String tableName,String user){ if(StringUtil.isEmpty(grant)){ grant="all"; } String sql="revoke "+grant+" on "+dbName+"."+tableName+" from "+user; template.execute(sql); } public static void removeBaseUser(JdbcTemplate template,String dbName,String user){ String sql="drop database "+dbName; template.execute(sql); String sqlUser="drop user "+user; template.execute(sqlUser); } public static Map createTiger(JdbcTemplate template, String tableName, String companyName, String sysName, String sysCode, String name, String code){ Map map=new HashMap<>(); //String id= UniqueIdUtil.getSuid(); /* String insertTigerSql="CREATE TRIGGER "+tableName+"_insert_tiger " + tableName + "\n"+ " AFTER INSERT ON " + tableName + " FOR EACH ROW \n" + " BEGIN \n" + " insert into BIZ_CLUSTER_COLLECT_DATA(id_,COMPANY_NAME_,SYS_NAME_,SYS_CODE_,NAME_,CODE_,TABLE_NAME_,SRC_ID_,DATA_TYPE_,CREATE_TIME_) " + " values(new.id_,'"+companyName+"','"+sysName+"','"+sysCode+"','"+name+"','"+code+"','"+tableName+"',new.id_,1,now());\n"+ " insert into BIZ_CLUSTER_TEMP_DATA(id_,COMPANY_NAME_,SYS_NAME_,SYS_CODE_,NAME_,CODE_,TABLE_NAME_,SRC_ID_,DATA_TYPE_,CREATE_TIME_) " + " values(new.id_,'"+companyName+"','"+sysName+"','"+sysCode+"','"+name+"','"+code+"','"+tableName+"',new.id_,1,now());\n"+ " END";*/ String insertTigerSql=initTriggerSql( tableName, companyName, sysName, sysCode, name, code,"INSERT","1"); template.execute("drop trigger if exists "+tableName+"_INSERT_tiger"); template.execute(insertTigerSql); map.put("insert",insertTigerSql); //String id= UniqueIdUtil.getSuid(); /* String insertUpdateSql="CREATE TRIGGER "+tableName+"_update_tiger \n"+ " AFTER UPDATE ON " + tableName + " FOR EACH ROW \n" + " BEGIN \n" + " insert into BIZ_CLUSTER_COLLECT_DATA(id_,COMPANY_NAME_,SYS_NAME_,SYS_CODE_,NAME_,CODE_,TABLE_NAME_,SRC_ID_,DATA_TYPE_,CREATE_TIME_) " + " values(new.id_,'"+companyName+"','"+sysName+"','"+sysCode+"','"+name+"','"+code+"','"+tableName+"',new.id_,2,now());\n"+ " insert into BIZ_CLUSTER_TEMP_DATA(id_,COMPANY_NAME_,SYS_NAME_,SYS_CODE_,NAME_,CODE_,TABLE_NAME_,SRC_ID_,DATA_TYPE_,CREATE_TIME_) " + " values(new.id_,'"+companyName+"','"+sysName+"','"+sysCode+"','"+name+"','"+code+"','"+tableName+"',new.id_,2,now());\n"+ " END";*/ String insertUpdateSql=initTriggerSql( tableName, companyName, sysName, sysCode, name, code,"UPDATE","2"); template.execute("drop trigger if exists "+tableName+"_UPDATE_tiger"); template.execute(insertUpdateSql); map.put("update",insertUpdateSql); //String id= UniqueIdUtil.getSuid(); /*String insertDelSql="CREATE TRIGGER "+tableName+"_del_tiger " + tableName + "\n"+ " AFTER DELETE ON " + tableName + " FOR EACH ROW \n" + " BEGIN \n" + " insert into BIZ_CLUSTER_COLLECT_DATA(id_,COMPANY_NAME_,SYS_NAME_,SYS_CODE_,NAME_,CODE_,TABLE_NAME_,SRC_ID_,DATA_TYPE_,CREATE_TIME_) " + " values(new.id_,'"+companyName+"','"+sysName+"','"+sysCode+"','"+name+"','"+code+"','"+tableName+"',new.id_,3,now());\n"+ " insert into BIZ_CLUSTER_TEMP_DATA(id_,COMPANY_NAME_,SYS_NAME_,SYS_CODE_,NAME_,CODE_,TABLE_NAME_,SRC_ID_,DATA_TYPE_,CREATE_TIME_) " + " values(new.id_,'"+companyName+"','"+sysName+"','"+sysCode+"','"+name+"','"+code+"','"+tableName+"',new.id_,3,now());\n"+ " END";*/ String insertDelSql=initTriggerSql( tableName, companyName, sysName, sysCode, name, code,"DELETE","3"); template.execute("drop trigger if exists "+tableName+"_DELETE_tiger"); template.execute(insertDelSql); map.put("delete",insertDelSql); return map; } public static String initTriggerSql(String tableName,String companyName,String sysName,String sysCode,String name,String code,String type,String typeIndex){ String triggerSql="CREATE TRIGGER "+tableName+"_"+type+"_tiger \n"+ " AFTER "+type+" ON " + tableName + " FOR EACH ROW \n" + " BEGIN \n" + " insert into BIZ_CLUSTER_COLLECT_DATA(id_,COMPANY_NAME_,SYS_NAME_,SYS_CODE_,NAME_,CODE_,TABLE_NAME_,SRC_ID_,DATA_TYPE_,CREATE_TIME_) " + " values(uuid(),'"+companyName+"','"+sysName+"','"+sysCode+"','"+name+"','"+code+"','"+tableName+"',new.id_,"+typeIndex+",now());\n"+ " insert into BIZ_CLUSTER_TEMP_DATA(id_,COMPANY_NAME_,SYS_NAME_,SYS_CODE_,NAME_,CODE_,TABLE_NAME_,SRC_ID_,DATA_TYPE_,CREATE_TIME_) " + " values(uuid(),'"+companyName+"','"+sysName+"','"+sysCode+"','"+name+"','"+code+"','"+tableName+"',new.id_,"+typeIndex+",now());\n"+ " END"; if("DELETE".equals(type)){ triggerSql="CREATE TRIGGER "+tableName+"_"+type+"_tiger \n"+ " AFTER "+type+" ON " + tableName + " FOR EACH ROW \n" + " BEGIN \n" + " insert into BIZ_CLUSTER_COLLECT_DATA(id_,COMPANY_NAME_,SYS_NAME_,SYS_CODE_,NAME_,CODE_,TABLE_NAME_,SRC_ID_,DATA_TYPE_,CREATE_TIME_) " + " values(uuid(),'"+companyName+"','"+sysName+"','"+sysCode+"','"+name+"','"+code+"','"+tableName+"',old.id_,"+typeIndex+",now());\n"+ " insert into BIZ_CLUSTER_TEMP_DATA(id_,COMPANY_NAME_,SYS_NAME_,SYS_CODE_,NAME_,CODE_,TABLE_NAME_,SRC_ID_,DATA_TYPE_,CREATE_TIME_) " + " values(uuid(),'"+companyName+"','"+sysName+"','"+sysCode+"','"+name+"','"+code+"','"+tableName+"',old.id_,"+typeIndex+",now());\n"+ " END"; } return triggerSql; } public static void createCollectTable(JdbcTemplate template){ //数据采集临时表(用于采集任务) //template.execute("drop table if exists BIZ_CLUSTER_TEMP_DATA"); String tempTable="create table BIZ_CLUSTER_TEMP_DATA\n" + "(\n" + " ID_ VARCHAR(64) not null,\n" + " COMPANY_NAME_ VARCHAR(120) not null default '' comment '单位名称',\n" + " SYS_NAME_ VARCHAR(120) comment '系统名称',\n" + " SYS_CODE_ VARCHAR(120) comment '系统编码',\n" + " NAME_ varchar(64) default null comment '数据资源项名称',\n" + " CODE_ varchar(64) comment '数据资源项编码',\n" + " TABLE_NAME_ VARCHAR(64) comment '数据表名',\n" + " SRC_ID_ VARCHAR(64) default '' comment '数据表ID',\n" + " DATA_TYPE_ int default NULL comment '数据操作标记(1:新增,2:修改,3:删除)',\n" + " CREATE_TIME_ datetime default null comment '数据操作时间',\n" + " primary key (ID_)\n" + ")"; template.execute(tempTable); //template.execute("alter table BIZ_CLUSTER_TEMP_DATA comment '数据变化临时表(用于数据变化统计)'"); //数据变化临时表(用于数据变化统计) //template.execute("drop table if exists BIZ_CLUSTER_TEMP_DATA"); String collectTable="create table BIZ_CLUSTER_COLLECT_DATA\n" + "(\n" + " ID_ VARCHAR(64) not null,\n" + " COMPANY_NAME_ VARCHAR(120) not null default '' comment '单位名称',\n" + " SYS_NAME_ VARCHAR(120) comment '系统名称',\n" + " SYS_CODE_ VARCHAR(120) comment '系统编码',\n" + " NAME_ varchar(64) default NULL comment '数据资源项名称',\n" + " CODE_ varchar(64) comment '数据资源项编码',\n" + " TABLE_NAME_ VARCHAR(64) comment '数据表名',\n" + " SRC_ID_ VARCHAR(64) default NULL comment '数据表ID',\n" + " DATA_TYPE_ int default NULL comment '数据操作标记(1:新增,2:修改,3:删除)',\n" + " CREATE_TIME_ datetime default null comment '数据操作时间',\n" + " primary key (ID_)\n" + ")"; template.execute(collectTable); //template.execute("alter table BIZ_CLUSTER_COLLECT_DATA comment '数据采集临时表(用于采集任务)'"); } }