Title: 数据表字段比较工具
Date: 2017-11-28 21:14
Category: 技术博客
Modified: 2017-11-28 21:14
Tags: 数据库
Slug: DB-compare
Authors: Victor Lv
Summary: 在数据表中使用两种不同的方法（可通过某些特殊字段的数据区分）插入一条数据，比较这两条数据所有字段是否一致，同时可以设置白名单，在白名单中的字段允许不一致。
一、需求描述：
在数据表中使用两种不同的方法（可通过某些特殊字段的数据区分）插入一条数据，比较这两条数据所有字段是否一致，同时可以设置白名单，在白名单中的字段允许不一致。
二、设计分析：
功能很简单，如果表字段很少，直接用肉眼比较很快。但如果数据表的字段非常多，用肉眼一个个去比较就太费劲了。另一种不需要写代码的方法就是，把该两条数据都select出来，然后复制整行，通过诸如UltraEdit Compare等工具自动比较两个数据是否全一致，但这种方法有一个工作量就是：不一致的字段是哪些没法提示，并且也没法自动过滤白名单。既然如此，那就写个小工具程序吧。
三、程序架构：
根据以上设计，容易引申出程序需要实现的几大功能：首先需要把两条数据从数据表中select出来；其次，需要逐个检查数据表的每个字段，是否一致；并且，对于不一致的字段，是否是在白名单中，如果不一致且又不在白名单中的话，则需要提示该字段不一致。笔者的主程序正是包含了三个主要函数去实现这三个点：queryRecord()函数用于把记录select出来，compare()函数用于比较两条记录，printResult()函数用于输出结果和提示。那么，先上主程序（以下程序在Java8下测试通过）：
Compare.java :
package comparator;
/**
* @ClassName: Compare
*
* @Description: A tool to compare the segment data of mysql database.
* Function description：
* 1. Self-defined primary keys.
* 2. Self-defined filter white list.
*
* @Author: Victor Lv (http://langlv.me)
*
* @Email: langlv@qq.com
*
* @Date: Nov 28, 2017 12:58:02 PM
*
* @Version: V1.0
*
*/
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
public class Compare {
private final static String CONNECT_URL = "jdbc:mysql://localhost:3306/test?" +"autoReconnect=true&useUnicode=true&characterEncoding=UTF-8";
private final static String USER = "root";
private final static String PWD = "";
private final static String TABLE_NAME = "victor";
private static PreparedStatement psment = null;
private static Connection conn = JDBCUtil.getConnection(JDBCUtil.MYSQL_DRIVER, CONNECT_URL, USER, PWD);
/**
* All column names of the table which are arranged in real order.
*/
public final static String[] columnNames = new String[]{
"pri_key",
"userName",
"phoneNumber",
"remark"
};
/**
* White list for filtering, please add on demand.
*/
public final static String[] filterList = new String[]{
"pri_key",
"remark"
};
/**
* Query record using self-defined primary keys.
* @param segm1
* @param segm2
* @return found record resultSet
* @throws SQLException
*/
public static ResultSet queryRecord(String segm1, String segm2) throws SQLException{
ResultSet result = null;
String sql = "select * from " + TABLE_NAME + " where userName=? and remark=?";
System.out.println("Query sql: " + sql);
psment = conn.prepareStatement(sql);
psment.setString(1, StringUtil.sqlFilter(segm1));
psment.setString(2, StringUtil.sqlFilter(segm2));
try {
result = psment.executeQuery();
}catch (Exception e) {
e.printStackTrace();
}
return result;
}
public static ArrayList<String> compare(ResultSet rs1, ResultSet rs2) {
ArrayList<String> notEqualList = new ArrayList<String>();
try {
if(rs1.next() && rs2.next()) {
for(int i =0; i < columnNames.length; i++) {
String columnName = columnNames[i];
/*compare specific segments of two results ,
* and store into list if not equal and not in white list
*/
if (StringUtil.notEqual(rs1.getString(columnName), rs2.getString(columnName)) && StringUtil.notIn(columnName,filterList))
notEqualList.add(columnName);
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return notEqualList;
}
public static void printResult(ArrayList<String> list) {
if (!list.isEmpty()) {
System.out.println("The different segments: ");
int count = 1;
for (String str : list){
System.out.println(count + ": " + str);
count++;
}
}
else {
System.out.println("No different segments found outside the white list.");
}
}
public static void comparator(String segm1) throws SQLException {
ResultSet rSet1 = queryRecord(segm1, "lvlang1");
ResultSet rSet2 = queryRecord(segm1, "lvlang2");
if((rSet1 != null) && (rSet2 != null))
printResult(compare(rSet1, rSet2));
else
System.out.println("Not all the records were queried.");
try {
JDBCUtil.closeConnection(rSet1, psment, conn);
JDBCUtil.closeConnection(rSet2, psment, conn);
} catch (Exception e) {
e.printStackTrace();
}
}
public static void main(String[] args) throws SQLException {
comparator("tony");
}
} 作为示例，本文使用了一个很简单的数据表做示范，只有四个字段：pri_key, userName, phoneNumber, remark，pri_key作为主键允许不一致，remark是我用来专门区分两种造数据方法的，所以也列入白名单，同时因为数据表中存在各种途径造的数据，所以需要用一个字段判别是我们当前要测试的方法造的数据，这个字段就是userName，所以userName这个字段在两条数据中都应该是一致的，作为key之一用于查找数据，其他的字段（这里只剩下phoneNumber）则要作为检查目标，检查是否一致，如果不一致，则需要报错。
数据表搜寻的全字段和白名单，这里都是通过一个String数组来存储，注意全字段数据需要按照数据表的实际字段顺序来排，因为程序比较字段时是通过字段顺序的index来判断当前比较的是哪个名称的字段。白名单数组的使用则只需要将检查出来的不一致的字段去和白名单里面的既定字段比对，是否存在于白名单中，即可。
数据表数据如下:
这里顺便推荐下wampserver自带的phpmyadmin mysql数据库管理后台，可以很方便地图形化管理mysql数据库：
运行效果如下图：
另外，我将一些可复用的功能抽取出来作为两个工具辅助类：
JDBCUtil.java :
package comparator;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @ClassName: JDBCUtil
*
* @Description: A tool to connection database ( mysql or db2 ..) use JDBC.
*
* @Author: Victor Lv (http://langlv.me)
*
* @Email: langlv@qq.com
*
* @Date: Nov 28, 2017 12:58:02 PM
*
* @Version: V1.0
*
*/
public class JDBCUtil {
public final static String MYSQL_DRIVER = "com.mysql.jdbc.Driver"; //need to import mysql-connector-java-*-bin.jar manually
public final static String DB2_DRIVER = "com.ibm.db2.jcc.DB2Driver"; //need to import db2-jdbc-jar manually
/**
* Create the database connection and return it.
* @param DRIVER
* @param URL
* @param USER
* @param PWD
* @return connection object
*/
public static Connection getConnection(String driver, String url, String user, String pwd) {
Connection conn = null;
try {
Class.forName(driver).newInstance();
conn = DriverManager.getConnection(url, user, pwd);
} catch (Exception e) {
e.printStackTrace();
}
System.out.println("Database connected.");
return conn;
}
/**
* Close the connection / resulSet / preparedStatement.
* @param rSet
* @param psment
* @param conn
* @throws Exception
*/
public static void closeConnection(ResultSet rSet, PreparedStatement psment, Connection conn) throws Exception {
try {
if (rSet != null) {
rSet.close();
rSet = null;
}
if (psment != null) {
psment.close();
psment = null;
}
if (conn != null && !conn.isClosed()) {
conn.close();
conn = null;
}
} catch (SQLException e) {
throw new Exception("Fail to close ResultSet / PreParedStatment / Connection !");
}
}
}StringUtil.java :
package comparator;
/**
* @ClassName: StringUtil
*
* @Description: A tool for String dealing
*
* @Author: Victor Lv (http://langlv.me)
*
* @Email: langlv@qq.com
*
* @Date: Nov 28, 2017 12:58:02 PM
*
* @Version: V1.0
*
*/
public class StringUtil {
/**
* Judge whether two Strings are equal (trim the space of head and tail)
* @param str1
* @param str2
* @return return true if equal and return false conversely
*/
public static boolean equal(String str1, String str2) {
if (str1 == null & str2 == null)
return true;
if ((str1 == null && str2 != null)
|| (str1 != null & str2 ==null))
return false;
return str1.trim().equals(str2.trim());
}
public static boolean notEqual(String str1, String str2) {
return !equal(str1, str2);
}
/**
* Judge whether the string is in the array.
* @param str
* @param array
* @return return true if in and return false conversely
*/
public static boolean isIn(String str, String[] array) {
for (String s : array) {
if (equal(str, s))
return true;
}
return false;
}
public static boolean notIn(String str, String[] array) {
return !isIn(str, array);
}
/**
* sql injection filter
*
* Blog of java how to prevent sql injection：
*
*
* @param String to filter
* @return filtered String
*/
public static String sqlFilter(String str) {
if (str == null)
return null;
return str.replaceAll(".*([';]+|(--)+).*", "");
}
} 这里的程序我后期额外添加了sql注入过滤器，纯粹是为了学习下，关于java 防止sql注入的方法可以参考一下两个博客：
java程序防止sql注入的方法Java防止SQL注入（转）
本文组合使用了通过prepareStatement.setString()拼接字符串和正则表达式替换特殊字符这两种方法。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
