优雅的进行线上数据订正

深碍√TFBOYSˉ_ 2023-02-24 08:30 93阅读 0赞

在系统开发上线过程中，难免会遇到在测试过程中没有覆盖到的场景，而且还有可能生成脏数据。这个时候就需要对错误数据进行一一修改了。如果遇到需要修改的数据量比较少，那么可以进行手动的编辑 SQL。如果数据量过大的时候，进行手动编辑 SQL 不仅耗时还很容易出现错误。做为一个 coder 我们可以使用程序来解决这个问题。

下面我就把我的方法分享出来，核心步骤如下：

*  首先，需要订正的数据以 CSV 文件格式下载下来
 *  然后，把需要修订的 SQL 模板确定下来，比如 `update order set status = 1, update_time = now() where order_id = {}`；
 *  接着通过程序读取 CSV ，读取里面的订单 ID，接着遍历数据把 SQL 写入文件并确认一下 SQL
 *  最后只需要在线上执行上面生成的 SQL 文件就可以了

从上面的 `{}` 符就可以看出这个和日志打印的占位符替换一样，因为我是把 log4j2 里面的占位符替换扒拉了下来做为 SQL 模板的变量替换。

下面的项目依赖了以下 Jar 包：

*  lombok：简化 java pojo 对象
 *  commons-csv ： apache 提供的 csv 操作框架
 *  fastjson：alibaba 提供的 json 操作工具

## 1、StringBuilders.java ##

从 log4j 框架里面直接获取到的一个工具类

> StringBuilders.java

public class StringBuilders {
    
    	/**
    	 * Appends a text representation of the specified object to the specified StringBuilder,
    	 * if possible without allocating temporary objects.
    	 *
    	 * @param stringBuilder the StringBuilder to append the value to
    	 * @param obj the object whose text representation to append to the StringBuilder
    	 */
    	public static void appendValue(final StringBuilder stringBuilder, final Object obj) {
    		if (!appendSpecificTypes(stringBuilder, obj)) {
    			stringBuilder.append(obj);
    		}
    	}
    
    	public static boolean appendSpecificTypes(final StringBuilder stringBuilder, final Object obj) {
    		if (obj == null || obj instanceof String) {
    			stringBuilder.append((String) obj);
    		} else if (obj instanceof CharSequence) {
    			stringBuilder.append((CharSequence) obj);
    		} else if (obj instanceof Integer) { // LOG4J2-1437 unbox auto-boxed primitives to avoid calling toString()
    			stringBuilder.append(((Integer) obj).intValue());
    		} else if (obj instanceof Long) {
    			stringBuilder.append(((Long) obj).longValue());
    		} else if (obj instanceof Double) {
    			stringBuilder.append(((Double) obj).doubleValue());
    		} else if (obj instanceof Boolean) {
    			stringBuilder.append(((Boolean) obj).booleanValue());
    		} else if (obj instanceof Character) {
    			stringBuilder.append(((Character) obj).charValue());
    		} else if (obj instanceof Short) {
    			stringBuilder.append(((Short) obj).shortValue());
    		} else if (obj instanceof Float) {
    			stringBuilder.append(((Float) obj).floatValue());
    		} else if (obj instanceof Byte) {
    			stringBuilder.append(((Byte) obj).byteValue());
    		} else {
    			return false;
    		}
    		return true;
    	}
    
    }

## 2、ParameterFormatter.java ##

从 log4j 框架里面直接 copy 的工具类，用于占位符替换。

> ParameterFormatter.java

public class ParameterFormatter {
 	/**
 	 * Prefix for recursion.
 	 */
 	static final String RECURSION_PREFIX = "[...";
 	/**
 	 * Suffix for recursion.
 	 */
 	static final String RECURSION_SUFFIX = "...]";
 
 	/**
 	 * Prefix for errors.
 	 */
 	static final String ERROR_PREFIX = "[!!!";
 	/**
 	 * Separator for errors.
 	 */
 	static final String ERROR_SEPARATOR = "=>";
 	/**
 	 * Separator for error messages.
 	 */
 	static final String ERROR_MSG_SEPARATOR = ":";
 	/**
 	 * Suffix for errors.
 	 */
 	static final String ERROR_SUFFIX = "!!!]";
 
 	private static final char DELIM_START = '{';
 	private static final char DELIM_STOP = '}';
 	private static final char ESCAPE_CHAR = '\\';
 
 	private ParameterFormatter() {
 	}
 
 	/**
 	 * Replace placeholders in the given messagePattern with arguments.
 	 *
 	 * @param messagePattern the message pattern containing placeholders.
 	 * @param arguments the arguments to be used to replace placeholders.
 	 * @return the formatted message.
 	 */
 
 	public static String format(final String messagePattern, final Object... arguments){
 		final StringBuilder result = new StringBuilder();
 		final int argCount = arguments == null ? 0 : arguments.length;
 		formatMessage(result, messagePattern, arguments, argCount);
 		return result.toString();
 	}
 
 	/**
 	 * Replace placeholders in the given messagePattern with arguments.
 	 *
 	 * @param buffer the buffer to write the formatted message into
 	 * @param messagePattern the message pattern containing placeholders.
 	 * @param arguments the arguments to be used to replace placeholders.
 	 */
 	public static void formatMessage(final StringBuilder buffer, final String messagePattern,
 							 final Object[] arguments, final int argCount) {
 		if (messagePattern == null || arguments == null || argCount == 0) {
 			buffer.append(messagePattern);
 			return;
 		}
 		int escapeCounter = 0;
 		int currentArgument = 0;
 		int i = 0;
 		final int len = messagePattern.length();
 		for (; i < len - 1; i++) { // last char is excluded from the loop
 			final char curChar = messagePattern.charAt(i);
 			if (curChar == ESCAPE_CHAR) {
 				escapeCounter++;
 			} else {
 				if (isDelimPair(curChar, messagePattern, i)) { // looks ahead one char
 					i++;
 
 					// write escaped escape chars
 					writeEscapedEscapeChars(escapeCounter, buffer);
 
 					if (isOdd(escapeCounter)) {
 						// i.e. escaped: write escaped escape chars
 						writeDelimPair(buffer);
 					} else {
 						// unescaped
 						writeArgOrDelimPair(arguments, argCount, currentArgument, buffer);
 						currentArgument++;
 					}
 				} else {
 					handleLiteralChar(buffer, escapeCounter, curChar);
 				}
 				escapeCounter = 0;
 			}
 		}
 		handleRemainingCharIfAny(messagePattern, len, buffer, escapeCounter, i);
 	}
 
 	/**
 	 * Returns {@code true} if the specified char and the char at {@code curCharIndex + 1} in the specified message
 	 * pattern together form a "{}" delimiter pair, returns {@code false} otherwise.
 	 */
 	// Profiling showed this method is important to log4j performance. Modify with care!
 	// 22 bytes (allows immediate JVM inlining: < 35 bytes) LOG4J2-1096
 	private static boolean isDelimPair(final char curChar, final String messagePattern, final int curCharIndex) {
 		return curChar == DELIM_START && messagePattern.charAt(curCharIndex + 1) == DELIM_STOP;
 	}
 
 	/**
 	 * Detects whether the message pattern has been fully processed or if an unprocessed character remains and processes
 	 * it if necessary, returning the resulting position in the result char array.
 	 */
 	// Profiling showed this method is important to log4j performance. Modify with care!
 	// 28 bytes (allows immediate JVM inlining: < 35 bytes) LOG4J2-1096
 	private static void handleRemainingCharIfAny(final String messagePattern, final int len,
 												 final StringBuilder buffer, final int escapeCounter, final int i) {
 		if (i == len - 1) {
 			final char curChar = messagePattern.charAt(i);
 			handleLastChar(buffer, escapeCounter, curChar);
 		}
 	}
 
 	/**
 	 * Processes the last unprocessed character and returns the resulting position in the result char array.
 	 */
 	// Profiling showed this method is important to log4j performance. Modify with care!
 	// 28 bytes (allows immediate JVM inlining: < 35 bytes) LOG4J2-1096
 	private static void handleLastChar(final StringBuilder buffer, final int escapeCounter, final char curChar) {
 		if (curChar == ESCAPE_CHAR) {
 			writeUnescapedEscapeChars(escapeCounter + 1, buffer);
 		} else {
 			handleLiteralChar(buffer, escapeCounter, curChar);
 		}
 	}
 
 	/**
 	 * Processes a literal char (neither an '\' escape char nor a "{}" delimiter pair) and returns the resulting
 	 * position.
 	 */
 	// Profiling showed this method is important to log4j performance. Modify with care!
 	// 16 bytes (allows immediate JVM inlining: < 35 bytes) LOG4J2-1096
 	private static void handleLiteralChar(final StringBuilder buffer, final int escapeCounter, final char curChar) {
 		// any other char beside ESCAPE or DELIM_START/STOP-combo
 		// write unescaped escape chars
 		writeUnescapedEscapeChars(escapeCounter, buffer);
 		buffer.append(curChar);
 	}
 
 	/**
 	 * Writes "{}" to the specified result array at the specified position and returns the resulting position.
 	 */
 	// Profiling showed this method is important to log4j performance. Modify with care!
 	// 18 bytes (allows immediate JVM inlining: < 35 bytes) LOG4J2-1096
 	private static void writeDelimPair(final StringBuilder buffer) {
 		buffer.append(DELIM_START);
 		buffer.append(DELIM_STOP);
 	}
 
 	/**
 	 * Returns {@code true} if the specified parameter is odd.
 	 */
 	// Profiling showed this method is important to log4j performance. Modify with care!
 	// 11 bytes (allows immediate JVM inlining: < 35 bytes) LOG4J2-1096
 	private static boolean isOdd(final int number) {
 		return (number & 1) == 1;
 	}
 
 	/**
 	 * Writes a '\' char to the specified result array (starting at the specified position) for each pair of
 	 * '\' escape chars encountered in the message format and returns the resulting position.
 	 */
 	// Profiling showed this method is important to log4j performance. Modify with care!
 	// 11 bytes (allows immediate JVM inlining: < 35 bytes) LOG4J2-1096
 	private static void writeEscapedEscapeChars(final int escapeCounter, final StringBuilder buffer) {
 		final int escapedEscapes = escapeCounter >> 1; // divide by two
 		writeUnescapedEscapeChars(escapedEscapes, buffer);
 	}
 
 	/**
 	 * Writes the specified number of '\' chars to the specified result array (starting at the specified position) and
 	 * returns the resulting position.
 	 */
 	// Profiling showed this method is important to log4j performance. Modify with care!
 	// 20 bytes (allows immediate JVM inlining: < 35 bytes) LOG4J2-1096
 	private static void writeUnescapedEscapeChars(int escapeCounter, final StringBuilder buffer) {
 		while (escapeCounter > 0) {
 			buffer.append(ESCAPE_CHAR);
 			escapeCounter--;
 		}
 	}
 
 	/**
 	 * Appends the argument at the specified argument index (or, if no such argument exists, the "{}" delimiter pair) to
 	 * the specified result char array at the specified position and returns the resulting position.
 	 */
 	// Profiling showed this method is important to log4j performance. Modify with care!
 	// 25 bytes (allows immediate JVM inlining: < 35 bytes) LOG4J2-1096
 	private static void writeArgOrDelimPair(final Object[] arguments, final int argCount, final int currentArgument,
 											final StringBuilder buffer) {
 		if (currentArgument < argCount) {
 			recursiveDeepToString(arguments[currentArgument], buffer, null);
 		} else {
 			writeDelimPair(buffer);
 		}
 	}
 
 	/**
 	 * This method performs a deep toString of the given Object.
 	 * Primitive arrays are converted using their respective Arrays.toString methods while
 	 * special handling is implemented for "container types", i.e. Object[], Map and Collection because those could
 	 * contain themselves.
 	 * 
 	 * It should be noted that neither AbstractMap.toString() nor AbstractCollection.toString() implement such a
 	 * behavior. They only check if the container is directly contained in itself, but not if a contained container
 	 * contains the original one. Because of that, Arrays.toString(Object[]) isn't safe either.
 	 * Confusing? Just read the last paragraph again and check the respective toString() implementation.
 	 * 
 	 * 
 	 * This means, in effect, that logging would produce a usable output even if an ordinary System.out.println(o)
 	 * would produce a relatively hard-to-debug StackOverflowError.
 	 * 
 	 * @param o The object.
 	 * @return The String representation.
 	 */
 	static String deepToString(final Object o) {
 		if (o == null) {
 			return null;
 		}
 		// Check special types to avoid unnecessary StringBuilder usage
 		if (o instanceof String) {
 			return (String) o;
 		}
 		if (o instanceof Integer) {
 			return Integer.toString((Integer) o);
 		}
 		if (o instanceof Long) {
 			return Long.toString((Long) o);
 		}
 		if (o instanceof Double) {
 			return Double.toString((Double) o);
 		}
 		if (o instanceof Boolean) {
 			return Boolean.toString((Boolean) o);
 		}
 		if (o instanceof Character) {
 			return Character.toString((Character) o);
 		}
 		if (o instanceof Short) {
 			return Short.toString((Short) o);
 		}
 		if (o instanceof Float) {
 			return Float.toString((Float) o);
 		}
 		if (o instanceof Byte) {
 			return Byte.toString((Byte) o);
 		}
 		final StringBuilder str = new StringBuilder();
 		recursiveDeepToString(o, str, null);
 		return str.toString();
 	}
 
 	/**
 	 * This method performs a deep toString of the given Object.
 	 * Primitive arrays are converted using their respective Arrays.toString methods while
 	 * special handling is implemented for "container types", i.e. Object[], Map and Collection because those could
 	 * contain themselves.
 	 * 
 	 * dejaVu is used in case of those container types to prevent an endless recursion.
 	 * 
 	 * 
 	 * It should be noted that neither AbstractMap.toString() nor AbstractCollection.toString() implement such a
 	 * behavior.
 	 * They only check if the container is directly contained in itself, but not if a contained container contains the
 	 * original one. Because of that, Arrays.toString(Object[]) isn't safe either.
 	 * Confusing? Just read the last paragraph again and check the respective toString() implementation.
 	 * 
 	 * 
 	 * This means, in effect, that logging would produce a usable output even if an ordinary System.out.println(o)
 	 * would produce a relatively hard-to-debug StackOverflowError.
 	 * 
 	 *
 	 * @param o the Object to convert into a String
 	 * @param str the StringBuilder that o will be appended to
 	 * @param dejaVu a list of container identities that were already used.
 	 */
 	static void recursiveDeepToString(final Object o, final StringBuilder str, final Set<String> dejaVu) {
 		if (appendSpecialTypes(o, str)) {
 			return;
 		}
 		if (isMaybeRecursive(o)) {
 			appendPotentiallyRecursiveValue(o, str, dejaVu);
 		} else {
 			tryObjectToString(o, str);
 		}
 	}
 
 	private static boolean appendSpecialTypes(final Object o, final StringBuilder str) {
 		return StringBuilders.appendSpecificTypes(str, o) || appendDate(o, str);
 	}
 
 	private static boolean appendDate(final Object o, final StringBuilder str) {
 		if (!(o instanceof Date)) {
 			return false;
 		}
 		final Date date = (Date) o;
 		final SimpleDateFormat format = getSimpleDateFormat();
 		str.append(format.format(date));
 		return true;
 	}
 
 	private static SimpleDateFormat getSimpleDateFormat() {
 		SimpleDateFormat result = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSSZ");
 		return result;
 	}
 
 	/**
 	 * Returns {@code true} if the specified object is an array, a Map or a Collection.
 	 */
 	private static boolean isMaybeRecursive(final Object o) {
 		return o.getClass().isArray() || o instanceof Map || o instanceof Collection;
 	}
 
 	private static void appendPotentiallyRecursiveValue(final Object o, final StringBuilder str,
 														final Set<String> dejaVu) {
 		final Class<?> oClass = o.getClass();
 		if (oClass.isArray()) {
 			appendArray(o, str, dejaVu, oClass);
 		} else if (o instanceof Map) {
 			appendMap(o, str, dejaVu);
 		} else if (o instanceof Collection) {
 			appendCollection(o, str, dejaVu);
 		}
 	}
 
 	private static void appendArray(final Object o, final StringBuilder str, Set<String> dejaVu,
 									final Class<?> oClass) {
 		if (oClass == byte[].class) {
 			str.append(Arrays.toString((byte[]) o));
 		} else if (oClass == short[].class) {
 			str.append(Arrays.toString((short[]) o));
 		} else if (oClass == int[].class) {
 			str.append(Arrays.toString((int[]) o));
 		} else if (oClass == long[].class) {
 			str.append(Arrays.toString((long[]) o));
 		} else if (oClass == float[].class) {
 			str.append(Arrays.toString((float[]) o));
 		} else if (oClass == double[].class) {
 			str.append(Arrays.toString((double[]) o));
 		} else if (oClass == boolean[].class) {
 			str.append(Arrays.toString((boolean[]) o));
 		} else if (oClass == char[].class) {
 			str.append(Arrays.toString((char[]) o));
 		} else {
 			if (dejaVu == null) {
 				dejaVu = new HashSet<>();
 			}
 			// special handling of container Object[]
 			final String id = identityToString(o);
 			if (dejaVu.contains(id)) {
 				str.append(RECURSION_PREFIX).append(id).append(RECURSION_SUFFIX);
 			} else {
 				dejaVu.add(id);
 				final Object[] oArray = (Object[]) o;
 				str.append('[');
 				boolean first = true;
 				for (final Object current : oArray) {
 					if (first) {
 						first = false;
 					} else {
 						str.append(", ");
 					}
 					recursiveDeepToString(current, str, new HashSet<>(dejaVu));
 				}
 				str.append(']');
 			}
 			//str.append(Arrays.deepToString((Object[]) o));
 		}
 	}
 
 	private static void appendMap(final Object o, final StringBuilder str, Set<String> dejaVu) {
 		// special handling of container Map
 		if (dejaVu == null) {
 			dejaVu = new HashSet<>();
 		}
 		final String id = identityToString(o);
 		if (dejaVu.contains(id)) {
 			str.append(RECURSION_PREFIX).append(id).append(RECURSION_SUFFIX);
 		} else {
 			dejaVu.add(id);
 			final Map<?, ?> oMap = (Map<?, ?>) o;
 			str.append('{');
 			boolean isFirst = true;
 			for (final Object o1 : oMap.entrySet()) {
 				final Map.Entry<?, ?> current = (Map.Entry<?, ?>) o1;
 				if (isFirst) {
 					isFirst = false;
 				} else {
 					str.append(", ");
 				}
 				final Object key = current.getKey();
 				final Object value = current.getValue();
 				recursiveDeepToString(key, str, new HashSet<>(dejaVu));
 				str.append('=');
 				recursiveDeepToString(value, str, new HashSet<>(dejaVu));
 			}
 			str.append('}');
 		}
 	}
 
 	private static void appendCollection(final Object o, final StringBuilder str, Set<String> dejaVu) {
 		// special handling of container Collection
 		if (dejaVu == null) {
 			dejaVu = new HashSet<>();
 		}
 		final String id = identityToString(o);
 		if (dejaVu.contains(id)) {
 			str.append(RECURSION_PREFIX).append(id).append(RECURSION_SUFFIX);
 		} else {
 			dejaVu.add(id);
 			final Collection<?> oCol = (Collection<?>) o;
 			str.append('[');
 			boolean isFirst = true;
 			for (final Object anOCol : oCol) {
 				if (isFirst) {
 					isFirst = false;
 				} else {
 					str.append(", ");
 				}
 				recursiveDeepToString(anOCol, str, new HashSet<>(dejaVu));
 			}
 			str.append(']');
 		}
 	}
 
 	private static void tryObjectToString(final Object o, final StringBuilder str) {
 		// it's just some other Object, we can only use toString().
 		try {
 			str.append(o.toString());
 		} catch (final Throwable t) {
 			handleErrorInObjectToString(o, str, t);
 		}
 	}
 
 	private static void handleErrorInObjectToString(final Object o, final StringBuilder str, final Throwable t) {
 		str.append(ERROR_PREFIX);
 		str.append(identityToString(o));
 		str.append(ERROR_SEPARATOR);
 		final String msg = t.getMessage();
 		final String className = t.getClass().getName();
 		str.append(className);
 		if (!className.equals(msg)) {
 			str.append(ERROR_MSG_SEPARATOR);
 			str.append(msg);
 		}
 		str.append(ERROR_SUFFIX);
 	}
 
 	/**
 	 * This method returns the same as if Object.toString() would not have been
 	 * overridden in obj.
 	 * 
 	 * Note that this isn't 100% secure as collisions can always happen with hash codes.
 	 * 
 	 * 
 	 * Copied from Object.hashCode():
 	 * 
 	 * <blockquote>
 	 * As much as is reasonably practical, the hashCode method defined by
 	 * class {@code Object} does return distinct integers for distinct
 	 * objects. (This is typically implemented by converting the internal
 	 * address of the object into an integer, but this implementation
 	 * technique is not required by the Java™ programming language.)
 	 * </blockquote>
 	 *
 	 * @param obj the Object that is to be converted into an identity string.
 	 * @return the identity string as also defined in Object.toString()
 	 */
 	static String identityToString(final Object obj) {
 		if (obj == null) {
 			return null;
 		}
 		return obj.getClass().getName() + '@' + Integer.toHexString(System.identityHashCode(obj));
 	}
 
 }

## 3、Order.java ##

从 CSV 提取出关心的字段，这里主要关心订单 ID 以及 商户号。

> Order.java

@Data
    public class Order {
    
        private String merchantId;
    
        private String orderId;
    
    }

## 4、CSVProcessor.java ##

CSV 文件处理类，首先读取 CSV 文件把关心订单相关的数据设置到 Order 对象当中得到需要修改的 Order 对象集合。然后遍历集合替换占位符生成 SQL 并写入文件当中。

> CSVProcessor.java

public class CSVProcessor {
 
 String[] HEADERS = {"author", "title"};
 
 private static final String CSV_FILE_PATH = "/Users/carl/Desktop/order.csv";
 
 private static final String SQL_FILE_PATH = "/Users/carl/Desktop/order.sql";
 
 private static final String SQL_TEMPLATE = "update tb_order set status = '1',update_time = now(),extension = '{}' " +
 "where order_id = '{}' and merchant_id = '{}';\n";
 
 public static void main(String[] args) throws Exception {
 List<Order> orders = readCSV(CSV_FILE_PATH);
 JSONObject jo = new JSONObject();
 jo.put("reason", "订单状态订正-20200708");
 String extension = JSON.toJSONString(jo);
 for (Order order : orders) {
 String sql = ParameterFormatter.format(SQL_TEMPLATE, extension, order.getOrderId(), order.getMerchantId());
 writeFile(SQL_FILE_PATH, sql);
 }
 }
 
 private static List<Order> readCSV(String filePath) throws Exception {
 List<Order> result = new ArrayList<>();
 Reader fileReader = new FileReader(filePath);
 Iterable<CSVRecord> records = CSVFormat.DEFAULT
 // .withHeader(HEADERS)
 .withFirstRecordAsHeader()
 .parse(fileReader);
 for (CSVRecord record : records) {
 Order order = new Order();
 order.setMerchantId(record.get(2));
 order.setOrderId(record.get(3));
 result.add(order);
 }
 return result;
 }
 
 private static void writeFile(String targetFile, String content) {
 File file = new File(targetFile);
 try (FileWriter fileWriter = new FileWriter(file.getAbsoluteFile(), true);
 BufferedWriter bufferedWriter = new BufferedWriter(fileWriter)) {
 // if file doesnt exists, then create it
 if (!file.exists()) {
 file.createNewFile();
 }
 bufferedWriter.write(content);
 bufferedWriter.flush();
 } catch (IOException e) {
 e.printStackTrace();
 }
 }
 
 }

## 5、结果 ##

以下是生成 SQL 的部分截图。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI0MTA3MzM_size_16_color_FFFFFF_t_70]  
我们把 SQL 检测一下数据是否比对的上，然后通过 Mysql 客户端在非生产环境执行一下其中的一条 SQL 看是否满足 SQL 语法。最后就可以在生产环境上面执行上面生成文件中的 SQL 语句了。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI0MTA3MzM_size_16_color_FFFFFF_t_70]: /images/20230209/d490ac60bd6c4109b1b158c0b57f627f.png