package com.example.demo;
import lombok.Data;
import org.apache.commons.collections4.ListUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.Stream;
/**
* 比较两个数据库中表结构的不同，并导出为excel(以链接一为标准)
*/
public class DatabaseInfo {
private static final short SEA_GREEN = IndexedColors.SEA_GREEN.getIndex();
private static final short LIGHT_GREEN = IndexedColors.LIGHT_GREEN.getIndex();
private static final short GOLD = IndexedColors.GOLD.getIndex();
public static void main(String[] args) {
String jdbcUrl1 = "jdbcUrl1 ";
String username1 = "username1 ";
String password1 = "password1 ";
String dataSourceName1 = "dataSourceName1 ";
String jdbcUrl2 = "jdbcUrl2 ";
String username2 = "username2 ";
String password2 = "password2 ";
String dataSourceName2 = "dataSourceName2 ";
String excelPath = "tables_diff.xlsx";
getDiffExcel(jdbcUrl1, username1, password1, dataSourceName1, jdbcUrl2, username2, password2, dataSourceName2, excelPath);
}
private static void getDiffExcel(String jdbcUrl1, String username1, String password1, String dataSourceName1, String jdbcUrl2, String username2, String password2, String dataSourceName2, String excelPath) {
List<TableEntity> dev = getTableStructure(jdbcUrl1, username1, password1, dataSourceName1);
List<TableEntity> test = getTableStructure(jdbcUrl2, username2, password2, dataSourceName2);
List<TableEntity> diff = getDifferentTables(dev, test);
toExcel(diff, excelPath);
}
/**
* 将表结构集合转成excel
* @param tableList 表结构集合
* @param excelPath 文件路径
*/
private static void toExcel(List<TableEntity> tableList, String excelPath) {
Workbook workbook = new XSSFWorkbook();
CellStyle secondStyle = createCellStyle(workbook, GOLD);
for (TableEntity table : tableList) {
if (table.getStatus() == 0) continue;
CellStyle headerStyle = createCellStyle(workbook, SEA_GREEN);
String tableName = table.getTableName();
if (tableName.matches(".*_[0-9]+")) continue;
String tableComment = table.getRemarks();
Sheet sheet = workbook.createSheet(tableName);
//设置列宽
sheet.setColumnWidth(0, 30 * 256);
sheet.setColumnWidth(1, 50 * 256);
sheet.setColumnWidth(2, 30 * 256);
sheet.setColumnWidth(3, 10 * 256);
sheet.setColumnWidth(4, 10 * 256);
sheet.setColumnWidth(5, 10 * 256);
sheet.setColumnWidth(6, 10 * 256);
// Create header row
Row headerRow = sheet.createRow(0);
// 设置第一行的样式和名称
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
Cell mergedCell = headerRow.createCell(0);
String title = tableName + "（" + tableComment + "）";
if (table.getStatus() == 2) {
setDeleteStyle(headerStyle, workbook);
title = title + "(已删除)";
} else if (table.getStatus() == 1) {
title = title + "(新增)";
}
mergedCell.setCellValue(title);
mergedCell.setCellStyle(headerStyle);
// 设置第二行的样式和每列的名称
Row fieldRow = sheet.createRow(1);
createCell(fieldRow, 0, "英文", secondStyle);
createCell(fieldRow, 1, "中文", secondStyle);
createCell(fieldRow, 2, "类型", secondStyle);
createCell(fieldRow, 3, "主键否", secondStyle);
createCell(fieldRow, 4, "能否为空", secondStyle);
createCell(fieldRow, 5, "自增", secondStyle);
createCell(fieldRow, 6, "索引", secondStyle);
createCell(fieldRow, 7, "存在修改", secondStyle);
int rowNumber = 2;
for (ColumnEntity column : table.getColumns()) {
CellStyle dataStyle = createCellStyle(workbook, (short) 1);
String fieldName = column.getColumnName();
String fieldComment = column.getColumnComment();
String fieldType = column.getColumnType();
String isPrimaryKey = column.isPrimaryKey() ? "主键" : "n";
String isNullable = column.isNullable() ? "y" : "不能为空";
String isAutoIncrement = column.isAutoIncrement() ? "自增" : "n";
String indexName = column.isIndex() ? "有索引" : "";
int status = column.getStatus();
String diff = "";
if (status == 1) {
dataStyle = createCellStyle(workbook, LIGHT_GREEN);
diff = "新增字段";
}
if (status == 2) {
setDeleteStyle(dataStyle, workbook);
diff = "删除字段";
}
if (status == 3) {
dataStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
diff = "类型修改";
}
Row row = sheet.createRow(rowNumber++);
createCell( row, 0, fieldName, dataStyle);
createCell( row, 1, fieldComment, dataStyle);
createCell( row, 2, fieldType, dataStyle);
createCell( row, 3, isPrimaryKey, dataStyle);
createCell( row, 4, isNullable, dataStyle);
createCell( row, 5, isAutoIncrement, dataStyle);
createCell( row, 6, indexName, dataStyle);
createCell( row, 7, diff, dataStyle);
}
try (FileOutputStream fileOut = new FileOutputStream(excelPath)) {
workbook.write(fileOut);
} catch (Exception e) {
System.out.println("e = " + e);
}
}
System.out.println("Excel file generated successfully.");
}
/**
* 创建单元格
* @param fieldRow 行对象
* @param row 列
* @param value 值
* @param style 风格
*/
private static void createCell(Row fieldRow, int row, String value, CellStyle style) {
Cell cell7 = fieldRow.createCell(row);
cell7.setCellStyle(style);
cell7.setCellValue(value);
}
/**
* 设置单元格风格未删除（颜色为灰色，加删除线）
* @param cellStyle
* @param workbook
*/
private static void setDeleteStyle(CellStyle cellStyle, Workbook workbook) {
Font font = workbook.createFont();
font.setStrikeout(true);
cellStyle.setFont(font);
cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
}
/**
* 获取存在不同的表的表结构
* @param master 标准表
* @param slave 对比表
* @return 存在不同的表的表结构
*/
private static List<TableEntity> getDifferentTables(List<TableEntity> master, List<TableEntity> slave) {
//获取新增或删除的表的表结构
List<TableEntity> tableDiff = getTableDiff(master, slave);
//获取新增或删除字段或字段类型存在不同的表的表结构
List<TableEntity> columnDiff = getColumnDiff(master, slave);
return ListUtils.union(tableDiff, columnDiff);
}
/**
* 获取新增或删除字段或字段类型存在不同的表的表结构
* @param master 标准表
* @param slave 对比表
* @return 新增或删除字段或字段类型存在不同的表的表结构
*/
private static List<TableEntity> getColumnDiff(List<TableEntity> master, List<TableEntity> slave) {
//在两个库中都存在的表，获取其在主库中的表结构
List<TableEntity> masterDiff = master.stream().filter(e -> {
List<String> tableNames = slave.stream().map(TableEntity::getTableName).collect(Collectors.toList());
return tableNames.contains(e.getTableName());
}).collect(Collectors.toList());
//在两个库中都存在的表，获取其在从库中的表结构
List<TableEntity> slaveDiff = slave.stream().filter(e -> {
List<String> tableNames = master.stream().map(TableEntity::getTableName).collect(Collectors.toList());
return tableNames.contains(e.getTableName());
}).collect(Collectors.toList());
for (TableEntity table : masterDiff) {
//主库中的表字段结构集合
List<ColumnEntity> masterColumns = table.getColumns();
//从库中的表字段结构集合
List<ColumnEntity> slaveColumns = slaveDiff.stream().filter(e -> e.getTableName().equals(table.getTableName())).collect(Collectors.toList()).get(0).getColumns();
//被删除的字段集合
List<ColumnEntity> deleteColumns = slaveColumns.stream().filter(e -> {
List<String> names = masterColumns.stream().map(ColumnEntity::getColumnName).collect(Collectors.toList());
return !names.contains(e.getColumnName());
}).collect(Collectors.toList());
deleteColumns.forEach(e -> e.setStatus(2));
//新增的字段集合
List<ColumnEntity> addColumns = masterColumns.stream().filter(e -> {
List<String> names = slaveColumns.stream().map(ColumnEntity::getColumnName).collect(Collectors.toList());
return !names.contains(e.getColumnName());
}).collect(Collectors.toList());
addColumns.forEach(e -> e.setStatus(1));
//类型被修改的字段集合
List<ColumnEntity> diffColumn = masterColumns.stream().filter(e -> {
List<String> names = slaveColumns.stream().map(ColumnEntity::getColumnName).collect(Collectors.toList());
return names.contains(e.getColumnName());
}).filter(e -> {
String slaveColumnType = slaveColumns.stream().filter(f -> f.getColumnName().equals(e.getColumnName())).collect(Collectors.toList()).get(0).getColumnType();
return !e.getColumnType().equals(slaveColumnType);
}).collect(Collectors.toList());
diffColumn.forEach(e -> {
e.setStatus(3);
String slaveColumnType = slaveColumns.stream().filter(f -> f.getColumnName().equals(e.getColumnName())).collect(Collectors.toList()).get(0).getColumnType();
e.setColumnType(slaveColumnType + " -> " + e.getColumnType());
});
//无修改的字段集合
List<ColumnEntity> noDiff = masterColumns.stream().filter(e -> {
List<String> names = slaveColumns.stream().map(ColumnEntity::getColumnName).collect(Collectors.toList());
return names.contains(e.getColumnName());
}).filter(e -> {
String slaveColumnType = slaveColumns.stream().filter(f -> f.getColumnName().equals(e.getColumnName())).collect(Collectors.toList()).get(0).getColumnType();
return e.getColumnType().equals(slaveColumnType);
}).collect(Collectors.toList());
//若无修改的字段集合长度与主表字段集合长度不同，则该表存在修改
if (noDiff.size() != masterColumns.size()) {
table.setStatus(3);
}
//合并新增字段集合、删除字段集合、修改字段集合和无修改字段集合，为表的字段结构集合
List<ColumnEntity> column = Stream.of(addColumns, deleteColumns, diffColumn, noDiff).flatMap(Collection::stream).collect(Collectors.toList());
table.setColumns(column);
}
return masterDiff;
}
/**
* 获取新增或删除的表的表结构
* @param master 标准表
* @param slave 对比表
* @return 新增或删除的表的表结构
*/
private static List<TableEntity> getTableDiff(List<TableEntity> master, List<TableEntity> slave) {
//已删除的表的表结构集合
List<TableEntity> delete = slave.stream().filter(e -> {
List<String> masterNames = master.stream().map(TableEntity::getTableName).collect(Collectors.toList());
return !masterNames.contains(e.getTableName());
}).collect(Collectors.toList());
//状态为2：已删除
delete.forEach(e -> e.setStatus(2));
//新增表的表结构集合
List<TableEntity> add = master.stream().filter(e -> {
List<String> slaveNames = slave.stream().map(TableEntity::getTableName).collect(Collectors.toList());
return !slaveNames.contains(e.getTableName());
}).collect(Collectors.toList());
//表状态为1：新增
add.forEach(e -> e.setStatus(1));
//合并两个集合
return ListUtils.union(delete, add);
}
/**
* 获取数据库中所有表的表结构
* @param jdbcUrl url
* @param username 用户名
* @param password 密码
* @param dataSourceName 数据库名
* @return 表结构
*/
private static List<TableEntity> getTableStructure(String jdbcUrl, String username, String password, String dataSourceName) {
List<TableEntity> tableList = new ArrayList<>();
try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password)) {
String query = "SELECT table_name, table_comment FROM information_schema.tables WHERE table_schema = '" + dataSourceName + "'";
Statement stmt = connection.createStatement();
ResultSet tables = stmt.executeQuery(query);
while (tables.next()) {
TableEntity table = new TableEntity();
String tableName = tables.getString("table_name");
String tableComment = tables.getString("table_comment");
table.setTableName(tableName);
table.setRemarks(tableComment);
String structureQuery = "SHOW FULL COLUMNS FROM " + tableName;
List<ColumnEntity> columnList = new ArrayList<>();
try (Statement structureStmt = connection.createStatement(); ResultSet columns = structureStmt.executeQuery(structureQuery)) {
while (columns.next()) {
//字段名
String columnName = columns.getString("Field");
//字段类型
String columnType = columns.getString("Type");
//字段注释
String columnComment = columns.getString("Comment");
//是否主键
boolean isPrimaryKey = columns.getString("Key").equals("PRI");
//能否为空
boolean isNullable = columns.getString("Null").equals("YES");
//是否自增
boolean isAutoIncrement = columns.getString("Extra").equals("auto_increment");
//是否有索引
boolean isIndex = columns.getString("Key").equals("MUL");
ColumnEntity column = new ColumnEntity();
column.setColumnName(columnName);
column.setColumnType(columnType);
column.setColumnComment(columnComment);
column.setPrimaryKey(isPrimaryKey);
column.setNullable(isNullable);
column.setAutoIncrement(isAutoIncrement);
column.setIndex(isIndex);
columnList.add(column);
}
}
table.setColumns(columnList);
tableList.add(table);
}
tables.close();
return tableList;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
private static CellStyle createCellStyle(Workbook workbook, short colorIndex) {
CellStyle style = workbook.createCellStyle();
if (colorIndex != 1) {
style.setFillForegroundColor(colorIndex);
}
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setAlignment(HorizontalAlignment.CENTER);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
return style;
}
}
@Data
class TableEntity {
private String tableName;
private String remarks;
List<ColumnEntity> columns;
/**
* 1：新增；2：删除，3：修改
*/
private int status = 0;
}
@Data
class ColumnEntity {
private String columnName;
private String columnType;
private String columnComment;
private boolean isPrimaryKey = false;
private boolean isNullable = true;
private boolean isAutoIncrement = false;
private boolean isIndex = false;
/**
* 1：新增；2：删除，3：修改
*/
private int status = 0;
}
年轻人的第一个车轱辘——比较两个数据库中表结构的不同并导出为Excel文件
发布时间 2023-10-16 14:21:11作者: Guardian0769