在系統開發上線過程中，難免會遇到在測試過程中沒有覆蓋到的場景，而且還有可能生成臟數據。這個時候就需要對錯誤數據進行一一修改了。如果遇到需要修改的數據量比較少，那么可以進行手動的編輯 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 loopfinal char curChar = messagePattern.charAt(i);if (curChar == ESCAPE_CHAR) {escapeCounter++;} else {if (isDelimPair(curChar, messagePattern, i)) { // looks ahead one chari++;// write escaped escape charswriteEscapedEscapeChars(escapeCounter, buffer);if (isOdd(escapeCounter)) {// i.e. escaped: write escaped escape charswriteDelimPair(buffer);} else {// unescapedwriteArgOrDelimPair(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-1096private 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-1096private 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-1096private 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-1096private 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 charswriteUnescapedEscapeChars(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-1096private 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-1096private 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 <em>pair</em> 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-1096private static void writeEscapedEscapeChars(final int escapeCounter, final StringBuilder buffer) {final int escapedEscapes = escapeCounter >> 1; // divide by twowriteUnescapedEscapeChars(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-1096private 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-1096private 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.* <p>* 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.* </p>* <p>* 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.* </p>* @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 usageif (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.* <p>* dejaVu is used in case of those container types to prevent an endless recursion.* </p>* <p>* 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.* </p>* <p>* 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.* </p>** @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 Mapif (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 Collectionif (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.* <p>* Note that this isn't 100% secure as collisions can always happen with hash codes.* </p>* <p>* Copied from Object.hashCode():* </p>* <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&#8482; 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 itif (!file.exists()) {file.createNewFile();}bufferedWriter.write(content);bufferedWriter.flush();} catch (IOException e) {e.printStackTrace();}}}

5、結果

以下是生成 SQL 的部分截圖。

我們把 SQL 檢測一下數據是否比對的上，然后通過 Mysql 客戶端在非生產環境執行一下其中的一條 SQL 看是否滿足 SQL 語法。最后就可以在生產環境上面執行上面生成文件中的 SQL 語句了。

總結

以上是生活随笔為你收集整理的优雅的进行线上数据订正的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。