Data restoration (Case 1)

1. Background:

The customer requires that the employee ID be changed to have a specific rule. This number is similar to the employee ID and may be referenced by dozens of tables.

2. Ideas:

First, find the specific table and specific fields associated with this field, check and determine the final SQL statement one by one, write the logic rules, and output the repair statement in the form of program output.

3. Specific implementation:

　　

Import Java. util. arraylist; import Java. util. hashmap; import Java. util. list; import Java. util. map; import Java. util. map. entry; import Java. util. set; import Org. apache. log4j. logger; import Eman. spring. basejdbcdao; import Eman. spring. JDBC. basejdbctemplate; public class updateemanhumanidsql extends basejdbcdao {private basejdbctemplate JDBC = This. getjdbctemplate (); Private Static logger log4j = logger. getrootlo Gger ();/*** program entry * @ author * @ date July 22, August 31, 2018 * @ Param type */public static void main (string [] ARGs) {New updateemanhumanidsql (). comprehensivecallmethod ("11"); // test}/*** method call logic * @ author * @ date July 22, August 31, 2018 * @ Param type */Public void comprehensivecallmethod (string type) {// 1. Obtain all humanid lists in the human table <string> humanlist = This. gethumanlist (); // 2. splice the humanid according to the 8-bit rule to obtain the map with the key as the original humanid and the value as the new humanid <string, STR Ing> humanmap = This. gethumanmap (humanlist, type); // 3. Obtain the table name and table fields to be updated. The key is the table name, and the value is the map of the list set involving fields <string, list <string> tablemap = This. gettablemap (); // 4. Execute the update statement this. mainlogicmethod (tablemap, humanlist, humanmap );} /*** get the humanid Set * @ author * @ date July 22, August 31, 2018 * @ return list <string> */private list <string> gethumanlist () {string SQL = "select humanid from human where humanid <> 'admin'"; return JDBC. eman Forlist (SQL, String. class);}/***** obtain the original humanid and splice a new humanid based on the company ID, return the * @ author * @ date August 31, 2018 * @ Param humanidlist-humanid Set * @ Param type * @ return Map <string, string> * Key-original humanid, value-New humanid */private Map <string, string> gethumanmap (list <string> humanidlist, string type) {hashmap <string, string> humanmap = new hashmap <string, string> (); If (humanidlist = NULL | humanidlist. isempty () {Return NULL;} string newhumanid = ""; for (string humanid: humanidlist) {newhumanid = humanid; If (newhumanid. touppercase (). contains ("KT") {newhumanid = newhumanid. substring (2, newhumanid. length ();} If (newhumanid. length () = 4) {humanmap. put (humanid, type + "00" + newhumanid);} else if (newhumanid. length () = 5) {humanmap. put (humanid, type + "0" + newhumanid);} else if (newhumanid. Length () = 6) {humanmap. put (humanid, type + newhumanid);} else if (newhumanid. length () = 8) {humanmap. put (humanid, newhumanid) ;}} return humanmap ;} /*** obtain the table related to humanid and corresponding column * @ author * @ date, December 31, August 31, 2018 * @ return Map <string, string> * Key-Table Name, value-list set of Field Names */private Map <string, list <string> gettablemap () {string SQL = "select columns. table_name, columns. column_name "+" from information_schema.co Lumns "+" where data_type like '% char' "+" and (column_name like '% human %' "+" or column_name like '% bytes %' "+" or column_name like '% person %' "+" or column_name like '% executor %' "+" or column_name like '% loginid %' "+" or column_name like '% projectadderid %' "+ "Or column_name like '% man % ') "+" or column_name like '% designer %') "+" or column_name like '% employeeid %') "+" or Co Lumn_name like '% checkid %') "+" and column_name not like '% operation %' "+" and column_name not in ('wit _ treatmanner ', 'humanmonitorid ', 'humanmonitorlengthid', 'humansort '"+", 'managestate', 'humanname', 'manufactureclassid', 'directoryproperties', 'describerfrom ') "+" and table_name not like 'v _ % '"+" and table_name not like' % view % '"; return JDBC. emanformaplist (SQL, null, String. class, String. clas S );} /*** main logic ** @ author * @ date July 22, August 31, 2018 * @ Param tablemap-the table involved and the set of fields * @ Param humanlist-humanid Set * @ Param humanmap-key-original humanid, value-New humanid */private void mainlogicmethod (Map <string, list <string> tablemap, list <string> humanlist, Map <string, string> humanmap) {If (tablemap = NULL | tablemap. isempty () {// return directly if the table is empty;} set <entry <string, list <string> entryset = tablemap. entryset (); ST Ring Key = ""; // The tablemap Key List <string> value = new arraylist <string> (); // tablemap Value List <string> List = new arraylist <string> (); // specifies the table and the set of values to be updated in the specified column int I = 1; // try {This. begintransaction (); For (Entry <string, list <string> entry: entryset) {key = entry. getkey (); value = entry. getvalue (); If (value = NULL) {continue;} system. out. println ("-- Update the" + I + "table:" + key); // debug information, which can be ignored! For (string column: Value) {If ("Bom ". equals (key) {system. out. println ();} List = This. getrelationhumanid (Key, column); // obtain the IF (list = NULL | list. size () = 0) {// skip system directly if it is null. out. println ("-- column" + I + "table" + key + "" + column + "No need to update records! "); // Debug information, which can be ignored! Continue;} system. Out. println ("-- Update column:" + column); // debug information, which can be ignored! For (string STR: List) {// execute the update if (humanmap. containskey (STR) {This. updaterelationhumanid (Key, column, STR, humanmap. get (STR) ;}}} I ++;} This. commit ();} catch (exception e) {log4j. warn ("update failed! ", E); E. printstacktrace ();}} /*** obtain the value to be updated for the corresponding field in the current table * @ author * @ date July 22, August 31, 2018 * @ Param tablename * @ Param columnname * @ return list <string>-value to be updated set */private list <string> getrelationhumanid (string tablename, string columnname) {string SQL = "select distinct" + columnname + "from" + tablename + "where" + columnname + "is not null and" + columnname + "<> ''"; return JDBC. emanforlist (SQL, String. class );} /*** obtain the update statement * @ author * @ date July 22, August 31, 2018 * @ Param tablename-table name * @ Param columnname-field name * @ Param humanid-original humanid * @ Param newhumanid-New humanid */private void updaterelationhumanid (string tablename, string columnname, string humanid, string newhumanid) {string SQL = "Update" + tablename + "set" + columnname + "= '" + newhumanid + "'where" + columnname + "='" + humanid + "'"; this. JDBC. update (SQL); system. out. println (SQL );}}

 

3.2 disable foreign keys and triggers

-- Disable all foreign key constraints
Declare @ sql1 nvarchar (1000)
Declare @ tabname varchar (50)
Declare @ tabfkn varchar (100)
Declare tabs cursor for select B. Name tabname, A. Name tabfk from sysobjects A, sysobjects B where a. xtype = 'F' and A. parent_obj = B. ID

Open tabs
Fetch next from tabs into @ tabname, @ tabfkn

While @ fetch_status = 0
Begin
Set @ sql1 = 'alter table ['+ @ tabname +'] nocheck constraint '+ @ tabfkn + ';';
-- Print @ sql1;
Exec sp_executesql @ sql1;
Fetch next from tabs into @ tabname, @ tabfkn
End
Close tabs
Deallocate tabs
Go

-- Disable all trigger Constraints
Declare @ sql1 nvarchar (1000)
Declare @ tabname varchar (50)
Declare @ tabfkn varchar (100)
Declare tabs cursor for select B. Name tabname, A. Name tabfk from sysobjects A, sysobjects B where a. xtype = 'tr' and A. parent_obj = B. ID

Open tabs
Fetch next from tabs into @ tabname, @ tabfkn

While @ fetch_status = 0
Begin
Set @ sql1 = 'alter table ['+ @ tabname +'] disable trigger '+ @ tabfkn + ';';
-- Print @ sql1;
Exec sp_executesql @ sql1;
Fetch next from tabs into @ tabname, @ tabfkn
End
Close tabs
Deallocate tabs
Go

3.3 enable foreign keys and triggers

-- Enable all trigger Constraints
Declare @ sql1 nvarchar (1000)
Declare @ tabname varchar (50)
Declare @ tabfkn varchar (100)
Declare tabs cursor for select B. Name tabname, A. Name tabfk from sysobjects A, sysobjects B where a. xtype = 'tr' and A. parent_obj = B. ID

Open tabs
Fetch next from tabs into @ tabname, @ tabfkn

While @ fetch_status = 0
Begin
Set @ sql1 = 'alter table ['+ @ tabname +'] Enable trigger '+ @ tabfkn + ';';
-- Print @ sql1;
Exec sp_executesql @ sql1;
Fetch next from tabs into @ tabname, @ tabfkn
End
Close tabs
Deallocate tabs
Go

-- Enable all foreign key constraints
Declare @ sql1 nvarchar (1000)
Declare @ tabname varchar (50)
Declare @ tabfkn varchar (100)
Declare tabs cursor for select B. Name tabname, A. Name tabfk from sysobjects A, sysobjects B where a. xtype = 'F' and A. parent_obj = B. ID

Open tabs
Fetch next from tabs into @ tabname, @ tabfkn

While @ fetch_status = 0
Begin
Set @ sql1 = 'alter table ['+ @ tabname +'] Check constraint '+ @ tabfkn + ';';
-- Print @ sql1;
Exec sp_executesql @ sql1;
Fetch next from tabs into @ tabname, @ tabfkn
End
Close tabs
Deallocate tabs
Go

 

Data restoration (Case 1)