今天接到一个任务，测试库和开发库不同步，领导让对比2个库的差异，对比原则表名、字段名、字段属性、字段长度、是否为空，刚接手任务，通过查询USER_TAB_COLUMNS表查询出测试库的表字段属性，之后在开发库新建一个表，把数据导入，之后通过为左右连接为null判断是否存在，一查右4000来条数据，之后要一条一条的比较，这根本就做不了，之后想到了写程序达到效果，思路：
demo路径：
1、分别查询到2个库的表名，表子段属性，分别存入MAP中（查询时最好按照表名排序）。
2、第二步，把一个库名做为key，表名做value，遍历它，如果另外一个map根据key查询不到值，表示此表没有在另外一个库中。
3、之后比较表子段属性，用表名和字段名做key，其余5个属性做值，传入2中查询到的list，过滤key在2中的，就表示2库中都存在表，但字段维度不同。
4、重复上诉步骤，把两个库替换。
代码为：
packagecom.sunline.util;
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* @author 熊浪
* @Email xiongl@sunline.cn
* @Time 2018年1月25日 @此类的作用：
*/
public class DatabaseUtil {
private final static Logger logger = LoggerFactory.getLogger(DatabaseUtil.class);
private static final String DRIVER = "oracle.jdbc.OracleDriver";
private static final String URL1 = "jdbc:oracle:thin:@10.22.20.12:1521/UATDEMODB";
private static final String URL2 = "jdbc:oracle:thin:@10.24.0.234:1521/DEVSITDB";
private static final String USERNAME1 = "uat_sungl";
private static final String PASSWORD1 = "uat_sungl";
private static final String USERNAME2 = "sit_sungl";
private static final String PASSWORD2 = "sit_sungl";
private static final String TABLE_INFO = "SELECT DISTINCT TABLE_NAME FROM USER_TABLES";
private static final String TABLE_NAME = "SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,NULLABLE FROM USER_TAB_COLUMNS";
private static final String SQL = "SELECT * FROM ";// 数据库操作
static {
try {
Class.forName(DRIVER);
} catch (ClassNotFoundException e) {
logger.error("can not load jdbc driver", e);
}
}
/**
* 获取数据库连接
*
* @return
*/
public static Connection getConnection(String url, String userName, String password) {
Connection conn = null;
try {
conn = DriverManager.getConnection(url, userName, password);
} catch (SQLException e) {
logger.error("get connection failure", e);
}
return conn;
}
/**
* 关闭数据库连接
*
* @param conn
*/
public static void closeConnection(Connection conn) {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
logger.error("close connection failure", e);
}
}
}
/**
* 获取数据库下的所有表名
*/
public static Map<String, String> getTableInfos(String url, String userName, String password) {
Map<String, String> tableNames = new HashMap<String, String>();
Connection conn = getConnection(url, userName, password);
PreparedStatement pst = null;
ResultSet rs = null;
try {
pst = conn.prepareStatement(TABLE_NAME);
rs = pst.executeQuery();
while (rs.next()) {
tableNames.put(rs.getString(1) + "=" + rs.getString(2), rs.getString(1) + "=" + rs.getString(2) + "=" + rs.getString(3) + "=" + rs.getString(4) + "=" + rs.getString(5) + "=" + rs.getString(1));
}
} catch (SQLException e) {
logger.error("getTableNames failure", e);
} finally {
try {
rs.close();
closeConnection(conn);
} catch (SQLException e) {
logger.error("close ResultSet failure", e);
}
}
return tableNames;
}
public static Map<String, String> getTableNames(String url, String userName, String password) {
Map<String, String> tableNames = new HashMap<String, String>();
Connection conn = getConnection(url, userName, password);
PreparedStatement pst = null;
ResultSet rs = null;
try {
pst = conn.prepareStatement(TABLE_INFO);
rs = pst.executeQuery();
while (rs.next()) {
tableNames.put(rs.getString(1), rs.getString(1));
}
} catch (SQLException e) {
logger.error("getTableNames failure", e);
} finally {
try {
rs.close();
closeConnection(conn);
} catch (SQLException e) {
logger.error("close ResultSet failure", e);
}
}
return tableNames;
}
/**
* 获取表中所有字段名称
*
* @param tableName 表名
* @return
*/
public static List<String> getColumnNames(String tableName, String url, String userName, String password) {
List<String> columnNames = new ArrayList<>();
// 与数据库的连接
Connection conn = getConnection(url, userName, password);
PreparedStatement pStemt = null;
String tableSql = SQL + tableName;
try {
pStemt = conn.prepareStatement(tableSql);
// 结果集元数据
ResultSetMetaData rsmd = pStemt.getMetaData();
// 表列数
int size = rsmd.getColumnCount();
for (int i = 0; i < size; i++) {
columnNames.add(rsmd.getColumnName(i + 1));
}
} catch (SQLException e) {
logger.error("getColumnNames failure", e);
} finally {
if (pStemt != null) {
try {
pStemt.close();
closeConnection(conn);
} catch (SQLException e) {
logger.error("getColumnNames close pstem and connection failure", e);
}
}
}
return columnNames;
}
/**
* 获取表中所有字段类型
*
* @param tableName
* @return
*/
public static List<String> getColumnTypes(String tableName, String url, String userName, String password) {
List<String> columnTypes = new ArrayList<>();
// 与数据库的连接
Connection conn = getConnection(url, userName, password);
PreparedStatement pStemt = null;
String tableSql = SQL + tableName;
try {
pStemt = conn.prepareStatement(tableSql);
// 结果集元数据
ResultSetMetaData rsmd = pStemt.getMetaData();
// 表列数
int size = rsmd.getColumnCount();
for (int i = 0; i < size; i++) {
columnTypes.add(rsmd.getColumnTypeName(i + 1));
}
Arrays.sort(columnTypes.toArray());
} catch (SQLException e) {
logger.error("getColumnTypes failure", e);
} finally {
if (pStemt != null) {
try {
pStemt.close();
closeConnection(conn);
} catch (SQLException e) {
logger.error("getColumnTypes close pstem and connection failure", e);
}
}
}
return columnTypes;
}
public static List<List<String>> selectTableInfo(Map<String, String> uat, Map<String, String> sit) {
Set<Entry<String, String>> uats = uat.entrySet();
Iterator<Entry<String, String>> it = uats.iterator();
Entry<String, String> infos = null;
String uatInfo = "", sitInfo = "";
List<List<String>> list = new ArrayList<List<String>>();
String[] uatArray = null, sitArray = null;
while (it.hasNext()) {
infos = it.next();
uatInfo = infos.getValue();
sitInfo = sit.get(infos.getKey());
if (!uatInfo.equals(sitInfo)) {
List<String> isDiffent = new ArrayList<String>();
uatArray = uatInfo.split("=");
for (int i = 0; i < uatArray.length; i++) {
isDiffent.add(uatArray[i]);
}
if (sitInfo != null) {
sitArray = sitInfo.split("=");
for (int i = 0; i < sitArray.length; i++) {
isDiffent.add(sitArray[i]);
}
} else {
for (int i = 0; i < uatArray.length; i++) {
isDiffent.add("");
}
}
list.add(isDiffent);
}
}
return list;
}
public static List<List<String>>selectTableInfo(Map<String, String> uat, Map<String, String> sit, String prefix) {
Set<Entry<String, String>> uats = uat.entrySet();
Iterator<Entry<String, String>> it = uats.iterator();
Entry<String, String> infos = null;
String uatInfo = "", sitInfo = "";
List<List<String>> list = new ArrayList<List<String>>();
String[] uatArray = null, sitArray = null;
int len=0;
while (it.hasNext()) {
infos = it.next();
uatInfo = infos.getValue();
sitInfo = sit.get(infos.getKey());
if (!uatInfo.equals(sitInfo)) {
uatArray = uatInfo.split("=");
if (prefix != null && prefix.contains(uatArray[uatArray.length - 1])) {
continue;
}
List<String> isDiffent = new ArrayList<String>();
if(prefix!=null)
len=uatArray.length-1;
else
len=uatArray.length;
for (int i = 0; i < len; i++) {
isDiffent.add(uatArray[i]);
}
if (sitInfo != null) {
sitArray = sitInfo.split("=");
for (int i = 0; i < len; i++) {
isDiffent.add(sitArray[i]);
}
} else {
for (int i = 0; i < len; i++) {
isDiffent.add("");
}
}
list.add(isDiffent);
}
}
return list;
}
public static void main(String[] args) {
Map<String, String> uatTable = getTableNames(URL1, USERNAME1, PASSWORD1);
Map<String, String> sitTable = getTableNames(URL2, USERNAME2, PASSWORD2);
Map<String, String> uatInfos = getTableInfos(URL1, USERNAME1, PASSWORD1);
Map<String, String> sitInfos = getTableInfos(URL2, USERNAME2, PASSWORD2);
String[] title = {"表名", "字段名", "字段类型", "字段长度", "是否为空", "表名", "字段名", "字段类型", "字段长度", "是否为空"};
String[] infos = {"表名", "字段名", "字段类型", "字段长度", "是否为空", "表名", "字段名", "字段类型", "字段长度", "是否为空"};
List<List<String>> uatTables = selectTableInfo(uatTable, sitTable, null);
List<List<String>> infoTables = selectTableInfo(sitTable, uatTable, null);
StringBuffer sbOne = new StringBuffer();
StringBuffer sbTwo = new StringBuffer();
for (List<String> list:uatTables)
sbOne.append(list.get(0)+",");
for (List<String> list:infoTables)
sbTwo.append(list.get(0)+",");
ExcelManage.writeInfoToExcel("UAT与SIT表名比较，前面现实UAT", "D:" + File.separator + "长亮", "UAT-SIT_TABLE", "xls", title, uatTables, TableInfoBean.class);
ExcelManage.writeInfoToExcel("UAT与UAT字段名比较，前面现实UAT", "D:" + File.separator + "长亮", "UAT-SIT_CLOUMN", "xls", infos, selectTableInfo(uatInfos, sitInfos, sbOne.toString()), SujuInfoBean.class);
ExcelManage.writeInfoToExcel("SIT与UAT表名比较，前面现实SIT", "D:" + File.separator + "长亮", "SIT-UAT_TABLE", "xls", title, infoTables, TableInfoBean.class);
ExcelManage.writeInfoToExcel("SIT与UAT字段名比较，前面现实SIT", "D:" + File.separator + "长亮", "SIT-UAT_CLOUMN", "xls", infos, selectTableInfo(sitInfos, uatInfos, sbTwo.toString()), SujuInfoBean.class);
}
}package com.sunline.util;
/**
* @author 熊浪
* @Email xiongl@sunline.cn
* @Time 2018/1/25
* @此类的作用：
*/
public class TableInfoBean {
private String tableName;
public String getTableName() {
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
}package com.sunline.util;
/**
* @author 熊浪
* @Email xiongl@sunline.cn
* @Time 2018年1月25日
* @此类的作用：
*/
public class SujuInfoBean {
private String tableNameOne;
private String cloumnOne;
private String typeOne;
private String cloumnLengthOne;
private String isOrNullOne;
private String tableNameTwo;
private String cloumnTwo;
private String typeTwo;
private String cloumnLengthTwo;
private String isOrNullTwo;
public String getTableNameOne() {
return tableNameOne;
}
public void setTableNameOne(String tableNameOne) {
this.tableNameOne = tableNameOne;
}
public String getCloumnOne() {
return cloumnOne;
}
public void setCloumnOne(String cloumnOne) {
this.cloumnOne = cloumnOne;
}
public String getTypeOne() {
return typeOne;
}
public void setTypeOne(String typeOne) {
this.typeOne = typeOne;
}
public String getCloumnLengthOne() {
return cloumnLengthOne;
}
public void setCloumnLengthOne(String cloumnLengthOne) {
this.cloumnLengthOne = cloumnLengthOne;
}
public String getIsOrNullOne() {
return isOrNullOne;
}
public void setIsOrNullOne(String isOrNullOne) {
this.isOrNullOne = isOrNullOne;
}
public String getTableNameTwo() {
return tableNameTwo;
}
public void setTableNameTwo(String tableNameTwo) {
this.tableNameTwo = tableNameTwo;
}
public String getCloumnTwo() {
return cloumnTwo;
}
public void setCloumnTwo(String cloumnTwo) {
this.cloumnTwo = cloumnTwo;
}
public String getTypeTwo() {
return typeTwo;
}
public void setTypeTwo(String typeTwo) {
this.typeTwo = typeTwo;
}
public String getCloumnLengthTwo() {
return cloumnLengthTwo;
}
public void setCloumnLengthTwo(String cloumnLengthTwo) {
this.cloumnLengthTwo = cloumnLengthTwo;
}
public String getIsOrNullTwo() {
return isOrNullTwo;
}
public void setIsOrNullTwo(String isOrNullTwo) {
this.isOrNullTwo = isOrNullTwo;
}
}package com.sunline.util;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* @author 熊浪
* @Email xiongl@sunline.cn
* @Time 2018年1月25日 @此类的作用：
*/
public class ExcelManage {
public static boolean writeInfoToExcel(String fileTitle, String path, String fileName, String fileType, String[] title, List<List<String>> list, Class<?> clazz) {
boolean flag = false;
try {
writer(fileTitle, path, fileName, fileType, insertInfoToBean(list, clazz), title);
flag = true;
} catch (Exception e) {
e.printStackTrace();
}
return flag;
}
private static List<Object> insertInfoToBean(List<List<String>> list, Class<?> clazz) throws Exception {
List<Object> objs = new ArrayList<Object>();
boolean flag = false;
Field[] fields = clazz.getDeclaredFields();
for (int i = 0, len = list.size(); i < len; i++) {
Object obj = clazz.newInstance();
for (int j = 0; j < fields.length; j++) {
flag = fields[j].isAccessible();
fields[j].setAccessible(true);
try {
fields[j].set(obj, list.get(i).get(j));
fields[j].setAccessible(flag);
} catch (IllegalArgumentException | IllegalAccessException e) {
e.printStackTrace();
}
}
objs.add(obj);
}
return objs;
}
private static void writer(String fileTitle, String path, String fileName, String fileType, List<Object> list, String titleRow[]) throws Exception {
Workbook wb = null;
String excelPath = path + File.separator + fileName + "." + fileType;
File file = new File(excelPath);
Sheet sheet = null;
// 创建工作文档对象
if (!file.exists()) {
if (fileType.equals("xls")) {
wb = new HSSFWorkbook();
} else if (fileType.equals("xlsx")) {
wb = new XSSFWorkbook();
} else {
throw new Exception("文件格式不正确");
}
// 创建sheet对象
sheet = (Sheet) wb.createSheet("sheet1");
OutputStream outputStream = new FileOutputStream(excelPath);
wb.write(outputStream);
outputStream.flush();
outputStream.close();
} else {
if (fileType.equals("xls")) {
wb = new HSSFWorkbook();
} else if (fileType.equals("xlsx")) {
wb = new XSSFWorkbook();
} else {
throw new Exception("文件格式不正确");
}
}
// 创建sheet对象
if (sheet == null) {
sheet = (Sheet) wb.createSheet("sheet1");
}
// 添加表头
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
row.setHeight((short) 540);
cell.setCellValue(fileTitle); // 创建第一行
CellStyle style = wb.createCellStyle(); // 样式对象
// 设置单元格的背景颜色为淡蓝色
style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直
style.setAlignment(CellStyle.ALIGN_CENTER);// 水平
style.setWrapText(true);// 指定当单元格内容显示不下时自动换行
cell.setCellStyle(style); // 样式，居中
Font font = wb.createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
font.setFontName("宋体");
font.setFontHeight((short) 280);
style.setFont(font);
// 单元格合并
// 四个参数分别是：起始行，起始列，结束行，结束列
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
sheet.autoSizeColumn(5200);
row = sheet.createRow(1); // 创建第二行
for (int i = 0; i < titleRow.length; i++) {
cell = row.createCell(i);
cell.setCellValue(titleRow[i]);
cell.setCellStyle(style); // 样式，居中
sheet.setColumnWidth(i, 20 * 256);
}
row.setHeight((short) 540);
Field[] fields = null;
if (!list.isEmpty())
fields = list.get(0).getClass().getDeclaredFields();
// 循环写入行数据
boolean flag = false;
for (int i = 0; i < list.size(); i++) {
row = (Row) sheet.createRow(i + 2);
row.setHeight((short) 500);
for (int j = 0; j < fields.length; j++) {
flag = fields[j].isAccessible();
fields[j].setAccessible(true);
try {
row.createCell(j).setCellValue("" + fields[j].get(list.get(i)));
} catch (IllegalArgumentException | IllegalAccessException e) {
throw new Exception(e.getMessage());
}
fields[j].setAccessible(flag);
}
}
// 创建文件流
OutputStream stream = new FileOutputStream(excelPath);
// 写入数据
wb.write(stream);
// 关闭文件流
stream.close();
}
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
