自用数据库软件（3-3程序执行前后db变化比较）

思路：参照备忘录模式，程序执行前保存db状态，和执行后的db状态比较，输出在两sheet中，用颜色表示具体变化。

具体代码参照如下：

public static void compareTableObject(List<TableObject> bfTblObjLst,List<TableObject> afTblObjLst,String xlsName) throws Exception {HSSFWorkbook workbook = new HSSFWorkbook();String tableName;HashMap hsMap;Set<Map.Entry> set;for (int i=0; i<bfTblObjLst.size(); i++) {TableObject bfTblObj = bfTblObjLst.get(i);TableObject afTblObj = afTblObjLst.get(i);writeTblObj(bfTblObj,workbook,"bf_");writeTblObj(afTblObj,workbook,"af_");tableName = bfTblObj.getTableName();HSSFSheet bfSheet = workbook.getSheet("bf_" + tableName);HSSFSheet afSheet = workbook.getSheet("af_" + tableName);List<String> tblKeyList = ConnectionHandler.getKeyByTable(tableName);if (tblKeyList.size() > 0) {compareSheet(bfSheet,afSheet,tblKeyList,ConnectionHandler.getIsTblRemarks().get(tableName));}}FileOutputStream fOut = new FileOutputStream(xlsName);workbook.write(fOut);fOut.flush();fOut.close();}

private static void compareSheet(HSSFSheet bfSheet,HSSFSheet afSheet,List<String> tblKeyList,boolean isTblRemarks) {HSSFRow bfRow = bfSheet.getRow(0);int stRow;int maxCol = bfRow.getLastCellNum();int maxBfRow = bfSheet.getLastRowNum();int maxAfRow = afSheet.getLastRowNum();intPryKeyCol.clear();handleRowList.clear();// 获取主键列'for(int i=0; i<maxCol; i++) {HSSFCell cell = bfRow.getCell(i);String strVal = cell.getStringCellValue();if (tblKeyList.contains(strVal)) {intPryKeyCol.add(i);}}for (int i : intPryKeyCol) {System.out.print(i + " " + "\t");}System.out.println();// 获取起始行if (isTblRemarks) {stRow = 2;} else {stRow = 1;}System.out.println("maxBfRow  " + maxBfRow);// bfSheet内容行获取int afStRow = stRow;for (int intRow=stRow; intRow<=maxBfRow; intRow++ ) {HSSFRow bfSltRow = bfSheet.getRow(intRow);// 和afSheet内容行进行判断boolean isExists = false;for(int compareRow=afStRow; compareRow<=maxAfRow; compareRow++) {if (handleRowList.contains(compareRow )) {isExists = false;continue;}isExists = true;HSSFRow afSltRow = afSheet.getRow(compareRow);// 主键列存在判断(被删除的数据)for (int keycol : intPryKeyCol) {HSSFCell bfCell = bfSltRow.getCell(keycol);HSSFCell afCell = afSltRow.getCell(keycol);String bfStr = bfCell.getStringCellValue()+"";String afStr = afCell.getStringCellValue()+"";//System.out.println("bfStr " + bfStr + " afStr " + afStr);if (!bfStr.equals(afStr)) {isExists = false;break;}} // end for3if(isExists) {handleRowList.add(compareRow);// 列更新数据判断compareRowData(bfSltRow,afSltRow);break;}}// end for2// 对应主键列不存在，设置row样式(删除)if(!isExists) {setRowStyle(bfSltRow,greyStyle);continue;}}// end for1// 剩下的afSheet未处理行，都是增加的for (int i=stRow; i<=maxAfRow; i++) {if (handleRowList.contains(i )) {continue;}HSSFRow afSltRow = afSheet.getRow(i);setRowStyle(afSltRow,orangeStyle);}}

private static void compareRowData(HSSFRow bfSltRow,HSSFRow afSltRow) {int maxCol = bfSltRow.getLastCellNum();for(int i=0; i<maxCol; i++) {HSSFCell bfCell = bfSltRow.getCell(i);HSSFCell afCell = afSltRow.getCell(i);String bfStr = bfCell.getStringCellValue()+"";String afStr = afCell.getStringCellValue()+"";if (!bfStr.equals(afStr)) {bfCell.setCellStyle(yellowStyle);afCell.setCellStyle(yellowStyle);}}}

最后打算抽时间把以上功能界面化，方便平时的使用。