java實現“資料平滑升級”

@

目錄

• 一、摘要
• 二、前提場景說明：
• 三、專案用到的指令碼和程式碼
  • 1.專案目錄長這樣
  • 2.java程式碼、配置檔案、部分指令碼
  • 3.升級包中的部分檔案

一、摘要

所謂的資料平滑升級：指的是比如舊的系統程式碼咱稱之為V4.6版本，V4.6涉及的部分庫表欄位只有5個欄位，而新版本V4.7部分表欄位有10個欄位，所謂的“資料平滑升級”指的僅僅是新系統版本在不停止執行的情況下執行指令碼，能讓V4.6的庫表舊資料同步到新版本V4.7中使用而已（即新版本V4.7系統用舊版本V4.6資料）。

二、前提場景說明：

• 難點1：V4.6庫表涉及的表太多且資料龐大，不可能把所有表匯出成sql，再去新版本資料庫中執行sql恢復資料，這明顯不現實，因此我們想開發一個指令碼，只要執行指令碼就能把舊版本資料同步到新版本系統庫表中。
• 難點2：我們的系統用到了檢視（檢視就是多個表關聯查詢的展示結果），而檢視中會有部分欄位，比如我們的叫網元ID（neId）和節點ID（nodeId）、它們是動態更新的，隨著安裝系統時按照id自增序列動態變化生成的，舉個例子就類似表索引id是動態自增的一樣，無法控制。那麼這就會造成V4.6中的資料neId和V4.7中的資料內容相同，但是neId和nodeId卻不一樣，所以需要動態匹配更新neId和nodeId值。
  
  

問題：你會問我們為啥會需要neId保持一致這麼個東西？設計時候不多餘嗎？
答案：我們會需要多系統服務之間通訊，只有確保兩邊服務系統的neId相同，才會觸發相應的請求，關聯進行查詢執行介面呼叫。總之neId就是為了多服務通訊之間的一個必要引數。

• 難點3：針對的難點2，光編寫指令碼是無法有效快速實現的，那麼我們考慮透過編寫java業務邏輯程式碼生成表的新舊neId、nodeId值的excel檔案，然後讓指令碼讀取excel檔案迴圈遍歷表並更新neId即可實現透過匹配更新效果。
• 難點4：該指令碼得總共包含3步：備份舊資料、恢復舊資料到臨時庫、更新正式庫資料。
• 注意點5：我們不是針對所有庫表資料都進行同步，而是只針對部分業務資料進行同步，比如告警資料、統計資料等。針對的資料庫只有lte_ems和olap資料庫下的部分表資料。
• 注意點6：使用前請先閱讀《4.6升級到4.7說明.docx》+ 部分資料平滑升級4.6升4.7升級流程圖.png。
• 注意點7：我們專案是安裝在伺服器路徑/home/ems/路徑下，所以程式碼中指定的路徑名稱跟這個類似。
• 注意點8：升級指令碼命令生成的兩個csv：neIdMapping.csv和nodeIdMapping.csv大概長這樣。
  

詳情使用升級指令碼步驟請看文件《4.6升級到4.7說明.docx》+ 部分資料平滑升級4.6升4.7升級流程圖.png，文件和升級包可上我的資源中免費進行下載。

三、專案用到的指令碼和程式碼

1.專案目錄長這樣

2.java程式碼、配置檔案、部分指令碼

EmsDTO實體類

public class EmsDTO {
	private String ID;
	private String NAME;
	private String NAME_KEY;
	private String TYPE;
	private String IP;
	private String TYPE_CODE;
	public String getID() {
		return ID;
	}
	public void setID(String iD) {
		ID = iD;
	}
	public String getNAME() {
		return NAME;
	}
	public void setNAME(String nAME) {
		NAME = nAME;
	}
	public String getNAME_KEY() {
		return NAME_KEY;
	}
	public void setNAME_KEY(String nAME_KEY) {
		NAME_KEY = nAME_KEY;
	}
	public String getTYPE() {
		return TYPE;
	}
	public void setTYPE(String tYPE) {
		TYPE = tYPE;
	}
	public String getIP() {
		return IP;
	}
	public void setIP(String iP) {
		IP = iP;
	}
	public String getTYPE_CODE() {
		return TYPE_CODE;
	}
	public void setTYPE_CODE(String tYPE_CODE) {
		TYPE_CODE = tYPE_CODE;
	}
	public EmsDTO(String iD, String nAME, String nAME_KEY, String tYPE, String iP, String tYPE_CODE) {
		super();
		ID = iD;
		NAME = nAME;
		NAME_KEY = nAME_KEY;
		TYPE = tYPE;
		IP = iP;
		TYPE_CODE = tYPE_CODE;
	}
	public EmsDTO() {
		super();
		// TODO Auto-generated constructor stub
	}	
}

KeyValueString實體類

public class KeyValueString {	
	private String key;
	private String value;
	
	public String getKey() {
		return key;
	}
	public void setKey(String key) {
		this.key = key;
	}
	public String getValue() {
		return value;
	}
	public void setValue(String value) {
		this.value = value;
	}
	
	@Override
	public String toString() {
		return "KeyValueString [key=" + key + ", value=" + value + "]";
	}
}

KeyValueString2實體類

public class KeyValueString2 {	
	private String neId_4_6;
	private String neId_4_7;
	public String getNeId_4_6() {
		return neId_4_6;
	}
	public void setNeId_4_6(String neId_4_6) {
		this.neId_4_6 = neId_4_6;
	}
	public String getNeId_4_7() {
		return neId_4_7;
	}
	public void setNeId_4_7(String neId_4_7) {
		this.neId_4_7 = neId_4_7;
	}
	@Override
	public String toString() {
		return "KeyValueString2 [neId_4_6=" + neId_4_6 + ", neId_4_7=" + neId_4_7 + "]";
	}
}

TableColumns實體類，對映表lte_ems_tables.csv+olap_tables.csv

public class TableColumns {
	private String tableName;
	private String nodeId;
	private String neId;
	private String equipmentId;
	
	public String getTableName() {
		return tableName;
	}
	public void setTableName(String tableName) {
		this.tableName = tableName;
	}
	public String getNodeId() {
		return nodeId;
	}
	public void setNodeId(String nodeId) {
		this.nodeId = nodeId;
	}
	public String getNeId() {
		return neId;
	}
	public void setNeId(String neId) {
		this.neId = neId;
	}
	public String getEquipmentId() {
		return equipmentId;
	}
	public void setEquipmentId(String equipmentId) {
		this.equipmentId = equipmentId;
	}
	@Override
	public String toString() {
		return "TableColumns [tableName=" + tableName + ", nodeId=" + nodeId + ", neId=" + neId + ", equipmentId="
				+ equipmentId + "]";
	}
}

Config配置類

public class Config {
	
	/**
	 * 更改資料庫時，臨時插入的欄位，代表已經更新過的行。為了防止已經更新過的NEID，與即將更新的相同，會發生二次更新
	 */
	public static final int CUSTOM_TAG_VALUE = 9999999;
	
	/**
	 * 4.6的neId對應4.7的neId對映關係表
	 */
	public static final String NE_ID_MAPPING_FILE_PATH = "/home/ems/upgrade/neIdMapping.csv";
	
	/**
	 * 4.6的nodeId對應4.7的nodeId對映關係表
	 */
	public static final String NODE_ID_MAPPING_FILE_PATH = "/home/ems/upgrade/nodeIdMapping.csv";
	
	/**
	 * lte_ems要更新的表名與要更新的欄位對映關係表
	 */
	public static final String LTE_EMS_TABLES_FILE_PATH = "/home/ems/upgrade/lte_ems_tables.csv";
	
	/**
	 * olap要更新的表名與要更新的欄位對映關係表
	 */
	public static final String OLAP_TABLES_FILE_PATH = "/home/ems/upgrade/olap_tables.csv";
	
	public static final String[] CSV_HEADER = new String[] {"neId_4_6", "neId_4_7"};
	
	public static final String[] TABLE_COLUMN_CSV_HEADER = new String[] {"tableName", "nodeId", "neId", "equipmentId"};
	
	public static final String[] TABLE_COLUMN_OLAP_HEADER = new String[] {"tableName","neId"};
}

DBToolKit 資料庫工具類

import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.Optional;
import java.util.Properties;

/**
 * 資料庫工具類
 *
 * @author 211145187
 * @date 2022-04-27
 */
public class DBToolKit {

    private static final Map<String, String> DRIVERS;

    private static final String MYSQL;

    private static final String POSTGRES;

    private static final String MSSQL;
    
    private static final String ORACLE;

    static {
        MYSQL = "MYSQL";
        POSTGRES = "POSTGRESQL";
        MSSQL = "SQLSERVER";
        ORACLE = "ORACLE";
        DRIVERS = new HashMap<>(8);
        DRIVERS.put(MYSQL, "com.mysql.jdbc.Driver");
        DRIVERS.put(POSTGRES, "org.postgresql.Driver");
        DRIVERS.put(MSSQL, "com.microsoft.sqlserver.jdbc.SQLServerDriver");
        DRIVERS.put(ORACLE, "oracle.jdbc.OracleDriver");
    }
    
    public static Connection getConnection(Properties properties, String dbName) {
    	Connection con = null;
		String ipAndPort = properties.getProperty("poc9000.db.ipAndPort");
		String username = properties.getProperty("poc9000.db.username");
		String password = properties.getProperty("poc9000.db.password");
		String driver = properties.getProperty("poc9000.db.driver");

		try {
			Class.forName(driver);
			String url = "jdbc:mysql://" + ipAndPort + "/" + dbName + "?useUinicode=true&characterEcoding=utf-8";
			con = DriverManager.getConnection(url, username, password);
		} catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
		return con;
	}


    /**
     * @param dbType    MYSQL/POSTGRES/MSSQL
     * @param ip        database ip
     * @param port      database port
     * @param spareIp   database spare ip
     * @param sparePort database spare port
     * @param dbName    database name
     * @param username  database username
     * @param password  database password
     * @return Connection database connection
     */
    @Deprecated
    public static Connection getConnectionWithRetry(final String dbType, final String ip, final int port, final String spareIp, final int sparePort,
                                                    final String dbName, final String username, final String password) {
        Optional<Connection> conn = DBToolKit.getConnectionWithRetry(dbType, ip, port, dbName, username, password);
        return conn.orElseGet(() -> DBToolKit.getConnectionWithRetry(dbType, spareIp, sparePort, dbName, username, password).orElse(null));
    }

    /**
     * @param dbType   MYSQL/POSTGRES/MSSQL
     * @param ip       database ip
     * @param port     database port
     * @param dbName   database name
     * @param username database username
     * @param password database password
     * @return Connection database connection
     */
    @Deprecated
    private static Optional<Connection> getConnectionWithRetry(final String dbType, final String ip, final int port,
                                                               final String dbName, final String username, final String password) {
        // 最多嘗試3次
        int max = 3;
        Connection connection = null;
        for (int i = 0; i < max; i++) {
            try {
                connection = getConnection(dbType, ip, port, dbName, username, password);
                if (connection != null) {
                    break;
                }
            } catch (ClassNotFoundException | SQLException e) {
                e.printStackTrace();
            }
        }

        return Optional.ofNullable(connection);
    }

    public static Connection getConnection(final String dbType, final String ip, final int port,
                                            final String dbName, final String username, final String password)
            throws ClassNotFoundException, SQLException {
        
        // 連線資料庫超時時間，單位：秒
        int requestTimeOut = 2;
        
        // 載入驅動程式
        Class.forName(DRIVERS.get(dbType.toUpperCase()));
        // 獲取連線物件
        final String url = buildUrl(dbType.toUpperCase(), ip, port, dbName, username);
        DriverManager.setLoginTimeout(requestTimeOut);
        
        if (ORACLE.equals(dbType.toUpperCase())) {
            // oracle的資料庫儲存，服務名儲存到username欄位，使用者名稱儲存到dbName欄位
            return DriverManager.getConnection(url, dbName, password);
        } else {
            return DriverManager.getConnection(url, username, password);
        } 
    }

    private static String buildUrl(final String dbType, final String ip, final int port, final String dbName, final String username) {
        StringBuilder url = new StringBuilder();
        url.append("jdbc:").append(dbType.toLowerCase());
        if (MYSQL.equals(dbType)) {
            url.append("://").append(ip).append(":").append(port).append("/").append(dbName);
        } else if (POSTGRES.equals(dbType)) {
            url.append("://").append(ip).append(":").append(port).append("/").append(dbName);
        } else if (MSSQL.equals(dbType)) {
            url.append("://").append(ip).append(":").append(port).append(";").append("DatabaseName").append("=").append(dbName);
        } else if (ORACLE.equals(dbType)) {
            // oracle的資料庫儲存，服務名儲存到username欄位，使用者名稱儲存到dbName欄位
            url.append(":thin:@").append(ip).append(":").append(port).append(":").append(username);
        }
        return url.toString();
    }


    /**
     * 關閉資料連線 針對查詢
     *
     * @param conn database Connection
     * @param ps   database PreparedStatement
     * @param rs   database ResultSet
     */
    public static void close(Connection conn, PreparedStatement ps, ResultSet rs) {
        closeResultSet(rs);
        closeStatement(ps);
        closeConnection(conn);
    }
    
    public static void close(PreparedStatement ps, ResultSet rs) {
        closeResultSet(rs);
        closeStatement(ps);
    }

    public static void closeConnection(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void closeStatement(PreparedStatement ps) {
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void closeResultSet(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

SuperCsvUtil 建立csv檔案的工具類

import com.hytera.poc9000.beans.KeyValueString2;
import com.hytera.poc9000.beans.TableColumns;
import com.hytera.poc9000.config.Config;
import org.supercsv.cellprocessor.Optional;
import org.supercsv.cellprocessor.ift.CellProcessor;
import org.supercsv.io.CsvBeanReader;
import org.supercsv.io.CsvBeanWriter;
import org.supercsv.io.ICsvBeanReader;
import org.supercsv.io.ICsvBeanWriter;
import org.supercsv.prefs.CsvPreference;

import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
/**
 * 
 * @author 211145187
 * @date 2022-05-13
 * https://super-csv.github.io/super-csv/examples_writing.html
 *
 */
public class SuperCsvUtil {

	public static List<KeyValueString2> getList(String filePath) throws IOException {
		List<KeyValueString2> emps = new ArrayList<KeyValueString2>();
		ICsvBeanReader beanReader = null;
		try {
			beanReader = new CsvBeanReader(new FileReader(filePath), CsvPreference.STANDARD_PREFERENCE);
			beanReader.getHeader(true);
			
			final CellProcessor[] processors = getProcessors();
			KeyValueString2 emp;
			while ((emp = beanReader.read(KeyValueString2.class, Config.CSV_HEADER, processors)) != null) {
				emps.add(emp);
			}
		} finally {
			if (beanReader != null) {
	            try {
	                beanReader.close();
	            } catch (IOException ex) {
	                
	            }
	        }
		}
		return emps;
	}
	
	public static List<TableColumns> getTableColumnList(String filePath) throws IOException {
		List<TableColumns> emps = new ArrayList<TableColumns>();
		ICsvBeanReader beanReader = null;
		try {
			beanReader = new CsvBeanReader(new FileReader(filePath), CsvPreference.STANDARD_PREFERENCE);
			beanReader.getHeader(true);
//			final String[] header = beanReader.getHeader(true);
			final CellProcessor[] processors = new CellProcessor[] { 
					new Optional(),
					new Optional(),
					new Optional(),
					new Optional()
			};

			TableColumns emp;
			while ((emp = beanReader.read(TableColumns.class, Config.TABLE_COLUMN_CSV_HEADER, processors)) != null) {
				emps.add(emp);
			}
		} finally {
			if (beanReader != null) {
	            try {
	                beanReader.close();
	            } catch (IOException ex) {
	                
	            }
	        }
		}

		return emps;
	}
	
	
	public static List<TableColumns> getTableColumnOlapList(String filePath) throws IOException {
		List<TableColumns> emps = new ArrayList<TableColumns>();
		ICsvBeanReader beanReader = null;
		try {
			beanReader = new CsvBeanReader(new FileReader(filePath), CsvPreference.STANDARD_PREFERENCE);
			beanReader.getHeader(true);
			final CellProcessor[] processors = new CellProcessor[] { 
					new Optional(),
					new Optional()
			};

			TableColumns emp;
			while ((emp = beanReader.read(TableColumns.class, Config.TABLE_COLUMN_OLAP_HEADER, processors)) != null) {
				emps.add(emp);
			}
		} finally {
			if (beanReader != null) {
	            try {
	                beanReader.close();
	            } catch (IOException ex) {
	                
	            }
	        }
		}
		return emps;
	}
	
	public void writeCsv(List<KeyValueString2> list, String filePath) throws IOException {
		ICsvBeanWriter beanWriter = null;
		try {
			beanWriter = new CsvBeanWriter(new FileWriter(filePath), 
					CsvPreference.STANDARD_PREFERENCE);
			beanWriter.writeHeader(Config.CSV_HEADER);
			for (KeyValueString2 keyValueString : list) {
				beanWriter.write(keyValueString, Config.CSV_HEADER, getProcessors());
			}
		} finally {
			if (beanWriter != null) {
				beanWriter.close();
			}
		}
	}
	
	private static CellProcessor[] getProcessors() {
		final CellProcessor[] processors = new CellProcessor[] { 
				//new UniqueHashCode(),
				//new NotNull(),
				new Optional(),
				new Optional()
		};
		return processors;
	}
}

CreateCsv 建立csv的業務邏輯

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Properties;
import java.util.Set;
import com.hytera.poc9000.beans.EmsDTO;
import com.hytera.poc9000.beans.KeyValueString2;
import com.hytera.poc9000.config.Config;
import com.hytera.poc9000.utils.DBToolKit;
import com.hytera.poc9000.utils.SuperCsvUtil;
import lombok.extern.slf4j.Slf4j;

@Slf4j
public class CreateCsv {

	private static String DB_NAME_4_6 = "lte_ems_temp";
	private static String DB_NAME_4_7 = "lte_ems";

	public static void createCsv(Properties properties) throws SQLException, IOException {
		log.warn("begin create /home/ems/upgrade/neIdMapping.csv and nodeIdMapping.csv");
		List<EmsDTO> emsList46 = getEmsList(properties, DB_NAME_4_6);
		List<EmsDTO> emsList47 = getEmsList(properties, DB_NAME_4_7);
		
		Map<String, String> neIdMap46 = getNeIdMap(emsList46);
		Map<String, String> neIdMap47 = getNeIdMap(emsList47);
		
		Map<String, String> nodeIdMap46 = getNodeIdMap(emsList46);
		Map<String, String> nodeIdMap47 = getNodeIdMap(emsList47);
		
		mapHandler(nodeIdMap46, nodeIdMap47, Config.NODE_ID_MAPPING_FILE_PATH);
		
		log.warn("End create /home/ems/upgrade/nodeIdMapping.csv");
		
		mapHandler(neIdMap46, neIdMap47, Config.NE_ID_MAPPING_FILE_PATH);

		log.warn("End create /home/ems/upgrade/neIdMapping.csv");

	}
	
	private static void mapHandler(Map<String, String> neIdMap46, 
			Map<String, String> neIdMap47, String filePath) throws IOException {
		List<KeyValueString2> neIdMappingList = new ArrayList<>();
		
		KeyValueString2 ks = null;
		Set<Map.Entry<String, String>> entries = neIdMap46.entrySet();
		for (Map.Entry<String, String> entry : entries) {
			ks = new KeyValueString2();
			if (neIdMap47.containsKey(entry.getKey())) {
				ks.setNeId_4_6(entry.getValue()); // 4.6 的neID
				ks.setNeId_4_7(neIdMap47.get(entry.getKey())); // 4.7的neId
				neIdMappingList.add(ks);
			} else {
				ks.setNeId_4_6(entry.getValue()); // 4.6 的neID
				ks.setNeId_4_7(""); // 4.7 的neID
				neIdMappingList.add(ks);
			}

		}

		Set<Map.Entry<String, String>> entries7 = neIdMap47.entrySet();
		for (Map.Entry<String, String> entry : entries7) {
			ks = new KeyValueString2();
			if (!neIdMap46.containsKey(entry.getKey())) {
				ks.setNeId_4_6(""); // 4.6 的neID
				ks.setNeId_4_7(entry.getValue()); // 4.7的neId
				neIdMappingList.add(ks);
			}

		}

		// 寫入檔案
		SuperCsvUtil sp = new SuperCsvUtil();
		sp.writeCsv(neIdMappingList, filePath);
	}

	public static List<EmsDTO> getEmsList(Properties properties ,String dbName) throws SQLException {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<EmsDTO> list = new ArrayList<>();
		String ipAndPort = properties.getProperty("poc9000.db.ipAndPort");
		String username = properties.getProperty("poc9000.db.username");
		String password = properties.getProperty("poc9000.db.password");
		String driver = properties.getProperty("poc9000.db.driver");

		try {
			Class.forName(driver);
			String url = "jdbc:mysql://" + ipAndPort + "/" + dbName + "?useUinicode=true&characterEcoding=utf-8";
			con = DriverManager.getConnection(url, username, password);

			// 查詢sql
			String sql = "select ID,NAME,TYPE_CODE,NAME_KEY,IP,TYPE from ne_resources WHERE TYPE != 'cluster'";
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			EmsDTO ems = null;
			while (rs.next()) {
				ems = new EmsDTO();

				ems.setID(null == rs.getString("ID") ? "" : rs.getString("ID"));
				ems.setIP(null == rs.getString("IP") ? "" : rs.getString("IP"));
				ems.setNAME(null == rs.getString("NAME") ? "" : rs.getString("NAME"));
				ems.setNAME_KEY(null == rs.getString("NAME_KEY") ? "" : rs.getString("NAME_KEY"));
				ems.setTYPE(null == rs.getString("TYPE") ? "" : rs.getString("TYPE"));
				ems.setTYPE_CODE(null == rs.getString("TYPE_CODE") ? "" : rs.getString("TYPE_CODE"));
				list.add(ems);
			}

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			DBToolKit.close(con, pstmt, rs);
		}
		return list;
	}
	/**
	 * nodeIdMapping.csv  TYPE_CODE、NAME_KEY、NAME、TYPE、IP這5個欄位匹配的
	 * @param list
	 * @return
	 */
	private static Map<String, String> getNodeIdMap(List<EmsDTO> list) {
		Map<String, String> map40 = new HashMap<>();
		for (EmsDTO es : list) {
			String key = es.getNAME().concat("@").concat(es.getNAME_KEY()).concat("@").concat(es.getTYPE()).concat("@")
					.concat(es.getTYPE_CODE()).concat("@").concat(es.getIP());
			map40.put(key, es.getID());
		}
		return map40;
	}
	
	/**
	 * neIdMapping.csv  TYPE_CODE、IP、TYPE這3個欄位匹配的，其中TYPE=DP，是固定的
	 * @param list
	 * @return
	 */
	private static Map<String, String> getNeIdMap(List<EmsDTO> list) {
		Map<String, String> map40 = new HashMap<>();
		for (EmsDTO es : list) {
			String type = es.getTYPE();
			if(!Objects.equals(type, "DP")) {
				continue;
			}
			String key = es.getTYPE_CODE().concat("@").concat(es.getIP());
			map40.put(key, es.getID());
		}
		return map40;
	}
}

UpdateDB 主入口main函式所在位置

import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Objects;
import java.util.Properties;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.support.PropertiesLoaderUtils;
import lombok.extern.slf4j.Slf4j;

/**
 * @author 211145187
 * @date 2022/4/26
 */
@Slf4j
public class UpdateDB {

    private static Properties properties;

    public static void main(String[] args) throws InterruptedException, SQLException {
    	log.warn("main methed commit, args:{}", Arrays.toString(args));
    	if(args == null || args.length == 0) {
    		args[0] = "updateneid";
    	}
    	loadConfig();
        if(Objects.equals("createcsv", args[0].trim().toLowerCase())) {
        	try {
				CreateCsv.createCsv(properties);
			} catch (SQLException e) {
				log.error("SQLException:{}", e.getMessage());
			} catch (IOException e) {
				log.error("IOException:{}", e.getMessage());
			}
        	return;
        }
        // 更改temp資料庫中的值，在temp資料庫中更新，更新後備份成SQL檔案，並還原到正式庫
        if(Objects.equals("updateneid", args[0].trim().toLowerCase())) {
        	try {
        		UpdateNeId.updateNeId(properties);
			} catch (Exception e) {
				log.error("Exception:{}", e.getMessage());
			}
        	return;
        }
        // 在正式庫，更新nesofversion欄位
        if(Objects.equals("updatenesofversion", args[0].trim().toLowerCase())) {
        	try {
        		UpdateNeSofVersion.selectStaticMainInfo(properties);
			} catch (Exception e) {
				log.error("Exception:{}", e.getMessage());
			}
        	return;
        }
        
    }
    
    /**
     * 載入配置檔案
     */
    private static void loadConfig() {
        try {
            String filePath = System.getProperty("user.dir") + "/config.properties";
            InputStream in = new BufferedInputStream(new FileInputStream(filePath));
            properties = new Properties();
            properties.load(in);
            System.out.println("System.getProperty(\"user.dir\") "+System.getProperty("user.dir") );
        } catch (IOException e) {
            log.error("Failed to read main configuration file:{}", e.getMessage());
            ClassPathResource classPathResource = new ClassPathResource("config.properties");
            try {
                properties = PropertiesLoaderUtils.loadProperties(classPathResource);
            } catch (IOException e1) {
                log.error("Failed to read path configuration file:{}", e1.getMessage());
            }
        }
    }
}

UpdateNeId 更新neId

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;
import org.springframework.util.StringUtils;
import com.hytera.poc9000.beans.KeyValueString2;
import com.hytera.poc9000.beans.TableColumns;
import com.hytera.poc9000.config.Config;
import com.hytera.poc9000.utils.DBToolKit;
import com.hytera.poc9000.utils.SuperCsvUtil;
import lombok.extern.slf4j.Slf4j;

/**
 * 
 * @author 211145187
 *
 */
@Slf4j
public class UpdateNeId {
	
	public static void updateNeId(Properties properties) {
		log.warn("begin update NeId");
		try {
			List<KeyValueString2> neIdMappingList = SuperCsvUtil.getList(Config.NE_ID_MAPPING_FILE_PATH);
			if(!checkCsvKeyValue(neIdMappingList)) {
				log.error("neId csv file format is incorrect, please check neId csv file.");
				return;
			}
			List<KeyValueString2> nodeIdMappingList = SuperCsvUtil.getList(Config.NODE_ID_MAPPING_FILE_PATH);
			if(!checkCsvKeyValue(nodeIdMappingList)) {
				log.error("nodeId csv file format is incorrect, please check nodeId csv file.");
				return;
			}
			updateLteEmsTempNeIdAndNodeId(properties, neIdMappingList, nodeIdMappingList);
			updateOlapNeId(properties, neIdMappingList);
			
		} catch (IOException e) {
			log.error("IOException:{}", e.getMessage());
		} catch (SQLException e) {
			log.error("SQLException:{}", e.getMessage());
		} catch (Exception e) {
			log.error("Exception:{}", e.getMessage());
		}
		log.warn("update complete!");
	}

	private static boolean checkCsvKeyValue(List<KeyValueString2> neIdMappingList) {
		if(neIdMappingList == null || neIdMappingList.size() == 0) {
			return false;
		}
		for (KeyValueString2 keyValueString2 : neIdMappingList) {
			if(StringUtils.isEmpty(keyValueString2.getNeId_4_6()) 
					|| StringUtils.isEmpty(keyValueString2.getNeId_4_7())
					|| keyValueString2.getNeId_4_6().trim().equals("")
					|| keyValueString2.getNeId_4_7().trim().equals("")) {
				return false;
			}
		}
		return true;
	}

	private static void updateLteEmsTempNeIdAndNodeId(Properties properties, List<KeyValueString2> neIdMappingList
			, List<KeyValueString2> nodeIdMappingList) throws IOException, SQLException {
		log.warn("begin update lte ems alarm tables");
		Connection con = DBToolKit.getConnection(properties, "lte_ems_temp");
		
		// 獲取要更新的表列表
		List<TableColumns> lteEmsTableList = SuperCsvUtil.getTableColumnList(Config.LTE_EMS_TABLES_FILE_PATH);

		for (TableColumns tableColumns : lteEmsTableList) {
			// 更新neId
			if(!StringUtils.isEmpty(tableColumns.getNeId())) {
				updateOneTable(con, tableColumns.getTableName(), tableColumns.getNeId(),
						neIdMappingList);
			}
			// 更新nodeId
			if(!StringUtils.isEmpty(tableColumns.getNodeId())) {
				updateOneTable(con, tableColumns.getTableName(), tableColumns.getNodeId(),
						nodeIdMappingList);
			}
			
		}
		DBToolKit.closeConnection(con);
		log.warn("update lte ems alarm tables complete!");
	}
	
	private static void updateOlapNeId(Properties properties, List<KeyValueString2> neIdMappingList) throws IOException, SQLException {
		log.warn("begin update olap tables");
		// 獲取要更新的表列表
		List<TableColumns> olapTableList = SuperCsvUtil.getTableColumnOlapList(Config.OLAP_TABLES_FILE_PATH);
		Connection con = DBToolKit.getConnection(properties, "olap_temp");
		for (TableColumns tableColumns : olapTableList) {
			if(StringUtils.isEmpty(tableColumns.getNeId())) {
				continue;
			}
			updateOneTable(con, tableColumns.getTableName(), tableColumns.getNeId(),
					neIdMappingList);
		}
		DBToolKit.closeConnection(con);
		log.warn("update olap tables complete!");
	}
	
	 
    private static void updateOneTable(Connection con, String tableName, String updateColumnName, 
    		List<KeyValueString2> mappingList) throws SQLException, IOException {
    	log.info("update table:{}, Column:{}", tableName, updateColumnName);
        // 對錶增加新欄位，更改資料庫時，臨時插入的欄位，代表已經更新過的行。為了防止已經更新過的NEID，與即將更新的相同，會發生二次更新
    	String updatedTagAddSql = "ALTER TABLE "+ tableName +" ADD updated_tag INT DEFAULT 0 NOT NULL";
    	
    	PreparedStatement pstmt = con.prepareStatement(updatedTagAddSql);
    	pstmt.execute();
    	DBToolKit.closeStatement(pstmt);
    	
    	String sql = "UPDATE "+tableName+" SET "+updateColumnName+" = ?, updated_tag = 1 " + 
        		"	WHERE "+updateColumnName+" = ? and updated_tag = 0" ;

    	for (KeyValueString2 keyValueString2 : mappingList) {
    		pstmt = con.prepareStatement(sql);
    		String ne47 = null == keyValueString2.getNeId_4_7() ? "-1" :keyValueString2.getNeId_4_7();
    		String ne46 = null == keyValueString2.getNeId_4_6() ? "-1" :keyValueString2.getNeId_4_6();
    		pstmt.setLong(1, Long.parseLong(ne47));
    		pstmt.setLong(2, Long.parseLong(ne46));
    		pstmt.execute();
    		DBToolKit.closeStatement(pstmt);
		}
    	
    	//刪除臨時欄位
    	String updatedTagDelSql = "ALTER TABLE "+ tableName +" DROP COLUMN updated_tag";
    	pstmt = con.prepareStatement(updatedTagDelSql);
    	pstmt.execute();
    	DBToolKit.closeStatement(pstmt);    	
    }
}

UpdateNeSofVersion 更新neSofVersion

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Properties;
import com.hytera.poc9000.utils.DBToolKit;
import com.mysql.jdbc.JDBC4PreparedStatement;
import lombok.extern.slf4j.Slf4j;

/**
 * 入口：UpdateNeId
 * @author 211145187
 *
 */
@Slf4j
public class UpdateNeSofVersion {
	
	/**
	 * 要更新的表
	 */
	private static final Map<String, String> updateTableAndColumnMap;
	
	static {
		updateTableAndColumnMap = new HashMap<String, String>();
		updateTableAndColumnMap.put("fmalarm_current_event", "eventId");
		updateTableAndColumnMap.put("fmalarm_current_to_history_buf", "alarmId");
		updateTableAndColumnMap.put("fmalarm_redefine_info", "alarmId");
		updateTableAndColumnMap.put("fmalarm_syn_buf", "alarmId");
		updateTableAndColumnMap.put("fmalarm_historical", "alarmId");
	}
	
	public static void selectStaticMainInfo(Properties properties) {
		log.warn("select static main info");
		Map<Integer, String> alarmIdVersionMap = new HashMap<Integer, String>(32);
		try {
			Connection con = DBToolKit.getConnection(properties, "lte_ems");
			String sql = "select alarmId ,max(neSofVersion) as neSofVersion from fmalarm_static_main_info group by alarmId";
	    	PreparedStatement pstmt = con.prepareStatement(sql);
	    	ResultSet rs = pstmt.executeQuery();
	    	while (rs.next()) {
	    		alarmIdVersionMap.put(rs.getInt("alarmId"), rs.getString("neSofVersion"));
			}
	    	DBToolKit.close(pstmt, rs);
	    	updateNeSofVersion(con, alarmIdVersionMap);
		} catch (SQLException e) {
			log.error("SQLException:{}", e.getMessage());
		} catch (Exception e) {
			log.error("Exception:{}", e.getMessage());
		}
		log.warn("Update NeSofVersion complete!");
	}

	
	/**
	 * fmalarm_current 表有neSofVersion欄位，但是不需要處理。當前表在說明文件要求使用者要處理，處理到歷史表
	 * fmalarm_historical 表有neSofVersion欄位，需要處理。處理方式：查詢alarmId(distinct)，根據alarmId更改對應的neSofVersion欄位值
	 * @param con
	 * @throws SQLException
	 * @throws IOException
	 */
    private static void updateNeSofVersion(Connection con, Map<Integer, String> alarmIdVersionMap) throws SQLException, IOException {
    	PreparedStatement pstmt = null;
    	PreparedStatement pstmt2 = null;
    	ResultSet rs;
    	for (Entry<String, String> entry : updateTableAndColumnMap.entrySet()) {
    		String tableName = entry.getKey();
    		String columnName = entry.getValue();
    		String sql = "select distinct(" + columnName + ") as alarmId from " + tableName;
        	pstmt = con.prepareStatement(sql);
        	rs = pstmt.executeQuery();
        	while (rs.next()) {
        		int thisColumnAlarmId = rs.getInt("alarmId");
        		// 迴圈更新表中的neSofVersion欄位
        		String updateSql = "UPDATE " + tableName + " SET neSofVersion = ? "
        				+ "	WHERE " + columnName + " = ? " ;
        		pstmt2 = con.prepareStatement(updateSql);
        		pstmt2.setString(1, alarmIdVersionMap.get(thisColumnAlarmId));
        		pstmt2.setInt(2, thisColumnAlarmId);
				log.info("updateSql:{}", ((JDBC4PreparedStatement)pstmt2).asSql());
        		pstmt2.execute();
        		DBToolKit.closeStatement(pstmt2);
    		}
        	DBToolKit.close(pstmt, rs);
    	}
    	DBToolKit.closeConnection(con);
    }	
}

config.properties 配置檔案

poc9000.db.ipAndPort=127.0.0.1:3306
poc9000.db.username=xx
poc9000.db.password=xx
poc9000.db.driver=com.mysql.jdbc.Driver

buildJars.bat 執行指令碼把dependency資料夾複製到UpgradePackage資料夾中

mvn dependency:copy-dependencies -DoutputDirectory=target/dependency

pom.xml pom依賴

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
	<groupId>com.hytera</groupId>
    <artifactId>updateDB</artifactId>
    <version>1.0</version>
    <packaging>jar</packaging>

    <name>updateDB</name>
    <description>poc 9000 4.6 to 4.7, alarm and olap db update</description>
	
	<properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>
	
    <dependencies>
        
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
			<version>2.1.1.RELEASE</version>
        </dependency>
		<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.46</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
            <version>1.18.6</version>
        </dependency>
        <dependency>
            <groupId>net.sf.supercsv</groupId>
            <artifactId>super-csv</artifactId>
            <version>2.4.0</version>
        </dependency>
    </dependencies>

    <build>  
        <finalName>updateDB</finalName>
        <plugins>
            <plugin>  
                <groupId>org.apache.maven.plugins</groupId>  
                <artifactId>maven-jar-plugin</artifactId>  
                <version>2.4</version>  
                <configuration>  
                    <archive>
                        <manifest>  
                            <addClasspath>true</addClasspath>  
                            <classpathPrefix>dependency/</classpathPrefix>
                            <mainClass>com.hytera.poc9000.UpdateDB</mainClass>  
                        </manifest>  
                    </archive>
                </configuration>  
            </plugin>  
        </plugins>  
    </build>
</project>

start.bat 用於驗證jar包中createCsv 能否順利執行並生成csv檔案

jre\bin\java -jar updateDB.jar createCsv >>log.log 2>&1 &

開發說明.txt

1.在IDE，執行maven install
2.執行buildJars.bat，下載相關依賴儲存到target/dependency目錄下
3.複製updateDB.jar、target目錄中的dependency目錄和classes\config.properties到UpgradePackage資料夾下

3.升級包中的部分檔案

change_olap_temp.sql 代表V4.7版本相比V4.6版本中庫表改動，包括欄位增刪改、表的新增等等

-- 呼叫總時長每天二次統計表
DROP TABLE IF EXISTS `secondary_call_duration_month`;
CREATE TABLE `secondary_call_duration_month` (
         `ne_id` int(11) DEFAULT NULL COMMENT '所屬網元ID',
         `ts_day` datetime DEFAULT NULL COMMENT '統計的月份',
         `total_time` bigint(20) DEFAULT NULL COMMENT '呼叫總時長',
         `op_date` datetime DEFAULT NULL COMMENT '操作時間',
         `caller_model` varchar(255) DEFAULT NULL COMMENT '主叫終端型號',
         `model_records_number` int(11) DEFAULT NULL COMMENT '終端記錄數',
         INDEX(ts_day)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

lte_ems_tables.csv 代表lte_ems庫下涉及的表

tableName,nodeId,neId,equipmentId
fmalarm_config_alarmtype,,,
fmalarm_config_alarmtype_lang,,,
...

olap_tables.csv 代表olap庫下涉及的表

tableName,neId
base_account,ne_id
base_cycle_statistics,
base_digital_0101,ne_id
base_digital_0102,ne_id
...

upgradeShell 代表shell指令碼

#!/bin/sh
#
# 升級指令碼
# 執行/home/ems/upgrade/upgradeShell dbBackup 對4.6的資料庫進行備份
# 執行/home/ems/upgrade/upgradeShell dbRestore  1.建立CSV檔案，2匯入4.6資料庫
# 執行/home/ems/upgrade/upgradeShell update 進行升級操作
# @date 2022-06-01
#

file_path=/home/ems/upgrade/
log=$file_path/log.log

commond=$1

host='127.0.0.1' 
#10.161.42.145
#埠 18753
port='18753'
#使用者
user='xx'
#密碼
pwd='xx'
#備份的資料庫名稱，屬組
dbs_name=("lte_ems" "olap")
#備份資料庫檔案的儲存目錄
data_dir='/home/ems/upgrade/dbBackup'

# if no args specified, show usage
if [ $# -le 0 ]; then
  echo '請指定引數'
  exit 1
fi

#建立儲存備份資料庫檔案的目錄
#date輸出當前日期
mkdir -p $data_dir

date_now=$(date "+%Y-%m-%d %H:%M:%S")

if [ $1 = "dbBackup" ]
  then
  echo "-----------------------begin dbBackup--------------------------">>$log
  #資料庫備份到資料夾------------------------------------------------------------
  
  	
for db_item in ${dbs_name[*]}
do
 /home/ems/3rdparty/mysql/bin/mysqldump -h$host -P$port -u$user -p$pwd -R --databases $db_item  > $data_dir/$db_item.sql
  if [ $? -eq 0 ]  # -eq表示等於
  then
       echo -e "$db_item資料庫4.6版本備份成功~\n"
  else
       echo -e "$db_item資料庫備份失敗~\n"
  fi
done

  #-------------------------------------------------------------------------------
  echo "end dbBackup">>$log
  elif [ $1 = "dbRestore" ]
  then
  echo "---------------------begin dbRestore--------------------------">>$log
  #先還原資料庫-------------------------------------------------------------------
  #
  	
sed -i '/lte_ems/d' $data_dir/lte_ems.sql
sed -i '/olap/d' $data_dir/olap.sql

echo "---------------------1 還原4.6temp庫--------------------------">>$log
echo -e "準備進行資料庫temp 臨時庫4.6 還原。。。。。。。\n"

/home/ems/3rdparty/mysql/bin/mysql -h$host -P$port -u$user -p$pwd <<EOF

SET FOREIGN_KEY_CHECKS = 0;

create database IF NOT EXISTS lte_ems_temp;
use lte_ems_temp;
source /home/ems/upgrade/dbBackup/lte_ems.sql;

create database IF NOT EXISTS olap_temp;
use olap_temp;
source /home/ems/upgrade/dbBackup/olap.sql;
EOF
echo "---------------------2 完成還原4.6--------------------------">>$log
echo -e "資料庫還原 olap_temp  lte_ems_temp  4.6版本完成 。。。。。。。\n"
  #後建立CSV檔案------------------------------------------------------------------
echo -e "建立CSV 匯出檔案開始。。。。。。。\n"
  /home/ems/3rdparty/java/bin/java -jar updateDB.jar createCsv >>log.log 2>&1
  # 更改資料庫結構 change_lte_ems.sql -> lte_ems_temp change_olap_temp.sql-> olap_temp
  #------------------------------------------------------------------------------------
  
echo "---------------------3 CSV匯出4.6--------------------------">>$log
echo -e "建立CSV 匯出檔案完成。。。。。。。\n"

echo "---------------------4 更新4.6->4.7庫欄位--------------------------">>$log
echo -e "更新olap_temp /lte_ems_temp 欄位更新4.6開始。。。。。。。\n"
 /home/ems/3rdparty/mysql/bin/mysql -h$host -P$port -u$user -p$pwd <<EOF

use olap_temp;
source /home/ems/upgrade/change_olap_temp.sql;
EOF
echo -e "更新olap_temp /lte_ems_temp 升級4.7指令碼完成。。。。。。。\n"
echo "----------------End dbRestore----------------">>$log

  elif [ $1 = "update" ]
  then
  
  echo "---------------------1 變更updateNeId--------------------------">>$log
  /home/ems/3rdparty/java/bin/java -jar updateDB.jar updateNeId >>log.log 2>&1
  
 echo "更新4.7 temp臨時庫完成">>$log
  
  # 匯入temp -> 非temp資料
 echo "---------------------2 變更備份--------------------------">>$log
  
  /home/ems/3rdparty/mysql/bin/mysqldump -h$host -P$port -u$user -p$pwd -R  lte_ems_temp fm_export_task fmalarm_config_alarmtype fmalarm_config_alarmtype_lang fmalarm_config_brdtype fmalarm_config_brdtype_lang fmalarm_config_clearsta fmalarm_config_clearsta_lang fmalarm_config_confirm fmalarm_config_confirm_lang fmalarm_config_levelcol fmalarm_config_levelcol_lang fmalarm_current_event fmalarm_historical fmalarm_nms_status fmalarm_notification_alarm fmalarm_notification_alarmlevel fmalarm_notification_main fmalarm_notification_method fmalarm_notification_ne fmalarm_redefine_info fmalarm_shieldrule_alarm fmalarm_shieldrule_main fmalarm_shieldrule_ne fmalarm_static_info fmalarm_static_main_info fmalarm_syn_buf fmalarm_tone fmalarm_tone_cycle fmalarm_tone_switch fmalarm_user_def_info fmalarm_user_def_ne_info > /home/ems/upgrade/changeEms.sql
	
  /home/ems/3rdparty/mysql/bin/mysqldump -h$host -P$port -u$user -p$pwd -R  olap_temp > /home/ems/upgrade/changeOlap.sql
  
 echo "---------------------3 變更還原正式庫--------------------------">>$log
   
/home/ems/3rdparty/mysql/bin/mysql -h$host -P$port -u$user -p$pwd <<EOF

SET FOREIGN_KEY_CHECKS = 0;

use lte_ems;
source /home/ems/upgrade/changeEms.sql;

use olap;
source /home/ems/upgrade/changeOlap.sql;
EOF

  elif [ $1 = "updateNeSofVersion" ]
  then
  echo "---------------------4 更改 neSofVersion 欄位值--------------------------">>$log
  /home/ems/3rdparty/java/bin/java -jar updateDB.jar updateNeSofVersion >>log.log 2>&1
  
  ##
  else
  echo "commond error">>$log
fi



echo "----------End--------------">>$log
echo "----------Good bye !!!----------">>$log
echo "">>$log