自用数据库软件（3-2备份还原）

    用jdbc技术支持多数据库，备份还原准备使用两种方案实现，1：java序列化 2：poi + excel方式。

一：java序列化

       1:  DbToDisk    

public static void DbToDisk(String filePath,List<String> tableNameList) throws Exception {List<TableObject> tblObjList = creatTtblObjList(tableNameList);FileUtils.writeObjList(filePath,tblObjList);}

public static List<TableObject> creatTtblObjList(List<String> tableNameList) throws Exception {Connection con = DbUtils.getConn();Statement stmt = con.createStatement();ResultSet rs = null;try {TableObject tblObj;List<TableObject> tblObjList = new ArrayList<TableObject>();Iterator itTblName = tableNameList.iterator();while(itTblName.hasNext()) {String tableName = (String)itTblName.next();rs = stmt.executeQuery("select * from " + tableName);tblObj = new TableObject();tblObj.setTableName(tableName.toUpperCase());DbDao.rsToList(rs, tblObj);tblObjList.add(tblObj);DbUtils.close(rs);}return tblObjList;} catch(Exception e) {throw e;} finally {DbUtils.closeQuietly(con,stmt,rs);}}

public static void writeObjList(String filePath,List<TableObject> tblObjList)  throws Exception {try {ObjectOutputStream oos = new ObjectOutputStream(new FileOutputStream(            filePath));oos.writeObject(tblObjList);} catch (Exception ex) {throw ex;}}

       2:  DiskToDb

public static void DiskToDb(String filePath) throws Exception {Connection con = DbUtils.getConn();con.setAutoCommit(false);try {List objLst = FileUtils.readObjList(filePath);deleteTables(objLst,con);ListToDb(objLst,con);DbUtils.commitAndClose(con);}  catch(Exception ex) {DbUtils.rollbacQuietly(con);throw ex;} finally {DbUtils.closeQuietly(con);}}

private static void ListToDb(List tblObjLst,Connection con) throws SQLException {StringBuffer sqlKey = new StringBuffer();StringBuffer sqlValue = new StringBuffer();StringBuffer sql = new StringBuffer();PreparedStatement prep = null;String tblName;boolean isBatchSupport = ConnectionHandler.isBatchSupport();if (isBatchSupport) {System.out.println ("*****支持批处理********");} else {System.out.println ("*****不支持批处理********");}try {Iterator itTblObjLst = tblObjLst.iterator();while(itTblObjLst.hasNext()) {TableObject tblObj = (TableObject)itTblObjLst.next();List tblRowLst = tblObj.getTableRow();tblName = tblObj.getTableName();Iterator itRows = tblRowLst.iterator();// 生成sql语句并准备if (0 < tblRowLst.size()) {sqlKey.setLength(0);sqlValue.setLength(0);sql.setLength(0);sqlKey.append(" insert into " + tblName + " (");HashMap hsMap = (HashMap) tblRowLst.get(0);// 表列名行Set<Map.Entry> set = hsMap.entrySet();for(Map.Entry entry  : set) {sqlKey.append(entry.getKey() + ",");sqlValue.append(" ? ,");}// 去除多余的逗号sqlKey.replace(sqlKey.lastIndexOf(","),sqlKey.length(),"  ) values ( ");sqlValue.replace(sqlValue.lastIndexOf(","),sqlValue.length(),"  ) ");sql.append(sqlKey).append(sqlValue);prep = con.prepareStatement(sql.toString());}// 执行插入sql语句int intRow = 1;while(itRows.hasNext()) {HashMap hsMap = (HashMap) itRows.next();Set<Map.Entry> set = hsMap.entrySet();int sqlIndex = 1;for(Map.Entry entry  : set) {prep.setObject(sqlIndex++,entry.getValue());}if (isBatchSupport) {prep.addBatch();if(intRow++ %1000 == 0) {prep.executeBatch();}} else {prep.execute();}}if (isBatchSupport && prep != null) {prep.executeBatch();}}// end whileif (isBatchSupport && prep != null) {prep.clearBatch();}} catch(SQLException e) {throw e;} finally {DbUtils.closeQuietly(prep);}}

二：poi + excel（表需有主键）
       1:  tableObjToExcel

public static void TableObjectListToExcel(List<TableObject> tblObjLst,String xlsName) throws Exception {HSSFWorkbook workbook = new HSSFWorkbook();Iterator itTblObjLst = tblObjLst.iterator();while(itTblObjLst.hasNext()) {writeTblObj((TableObject)itTblObjLst.next(),workbook,"");}FileOutputStream fOut = new FileOutputStream(xlsName);workbook.write(fOut);fOut.flush();fOut.close();}

private static void writeTblObj(TableObject tblObj,HSSFWorkbook workbook,String prefix) {listCol.clear();setStyle(workbook);String tableName;HashMap hsMap;Set<Map.Entry> set;List tblRowLst = tblObj.getTableRow();tableName = tblObj.getTableName();HSSFSheet sheet = workbook.createSheet(prefix + tableName);Iterator itRows = tblRowLst.iterator();HSSFRow row= sheet.createRow((short)0);;HSSFCell cell;if(tblRowLst.size() <= 0) return;hsMap = (HashMap) tblRowLst.get(0);set = hsMap.entrySet();for(Map.Entry entry  : set) {listCol.add(entry.getKey().toString());}// 获取主键列List<String> listPryKey = ConnectionHandler.getKeyByTable(tableName);//写入各个字段的名称for(int i=1; i<=listCol.size(); i++) {cell = row.createCell((short)(i-1));cell.setCellType(HSSFCell.CELL_TYPE_STRING);cell.setCellValue(listCol.get(i-1));// 设置主键列颜色if (listPryKey.contains(listCol.get(i-1))) {cell.setCellStyle(greenStyle);}}/************ 字段内容和注释 *****************/int iRow=1;// 写入列名的注释hsMap =  ConnectionHandler.getRmksColByTable(tableName);if (ConnectionHandler.getIsTblRemarks().get(tableName)) {int nRarmks = hsMap.size();row= sheet.createRow((short)iRow);for(int intRarmks=1; intRarmks<=nRarmks; intRarmks++) {cell = row.createCell((short)(intRarmks-1));cell.setCellType(HSSFCell.CELL_TYPE_STRING);cell.setCellValue(hsMap.get(listCol.get(intRarmks-1))+"");}iRow++;}//写入各条记录，每条记录对应Excel中的一行while(itRows.hasNext()) {hsMap = (HashMap) itRows.next();set = hsMap.entrySet();int intIndex = 1;row= sheet.createRow((short)iRow);for(Map.Entry entry  : set) {cell = row.createCell((short)(intIndex-1));cell.setCellType(HSSFCell.CELL_TYPE_STRING);cell.setCellValue(entry.getValue()+"");intIndex++;}iRow++;}}

       2:  excelToDb

public static void ExcelToDb(String xlsName,Connection con) throws Exception {con.setAutoCommit(false);HSSFWorkbook workBook= FileUtils.readExcel(xlsName);HSSFRow row;HSSFSheet sheet;int stRow;PreparedStatement prep = null;PreparedStatement prepInsrt = null;PreparedStatement prepUpdate = null;ResultSet rsSlt = null;boolean isBatchSupport = ConnectionHandler.isBatchSupport();try {for (int i=0; i<workBook.getNumberOfSheets(); i++) {sheet = workBook.getSheetAt(i);intPryKeyCol.clear();String tableName = sheet.getSheetName().toUpperCase();List<String> strKeyList = ConnectionHandler.getKeyByTable(sheet.getSheetName());row = sheet.getRow(0);int maxCol = row.getLastCellNum();int maxRow = sheet.getLastRowNum();HashMap<String,Integer> hsColType = DbUtils.getColTypeByTbl(con,tableName);if (strKeyList.size() > 0) {sqlKey.setLength(0);sqlInsert.setLength(0);sqlUpdate.setLength(0);sql1Key.setLength(0);sql1Value.setLength(0);sqlUpdateKey.setLength(0);sqlUpdateValue.setLength(0);sqlUpdateValue.append(" where ");sqlKey.append("select * from " + tableName + " where ");sqlInsert.append(" insert into " + tableName + " (");sqlUpdate.append(" update " + tableName + " set ");// 获取主键列for(int j=0; j<maxCol; j++) {HSSFCell cell = row.getCell(j);String strVal = cell.getStringCellValue();if (strKeyList.contains(strVal)) {intPryKeyCol.add(j);sqlKey.append( strVal + " = ? and " );sqlUpdateValue.append(strVal + " = ? and " );} else {sqlUpdateKey.append(strVal + " = ? , " );}sql1Key.append(strVal + ",");sql1Value.append(" ? ,");}// end for2// 去除多余的逗号String sltSql = sqlKey.replace(sqlKey.lastIndexOf("and"),sqlKey.length(),"").toString();sql1Key.replace(sql1Key.lastIndexOf(","),sql1Key.length(),"  ) values ( ");sql1Value.replace(sql1Value.lastIndexOf(","),sql1Value.length(),"  ) ");sqlUpdateKey.replace(sqlUpdateKey.lastIndexOf(","),sqlUpdateKey.length(),"").toString();sqlUpdateValue.replace(sqlUpdateValue.lastIndexOf("and"),sqlUpdateValue.length(),"").toString();String insertSql = sqlInsert.append(sql1Key).append(sql1Value).toString();String updateSql = sqlUpdate.append(sqlUpdateKey).append(sqlUpdateValue).toString();System.out.println(updateSql);System.out.println(insertSql);prepInsrt = con.prepareStatement(insertSql);prepUpdate = con.prepareStatement(updateSql);// 获取起始行if (ConnectionHandler.getIsTblRemarks().get(tableName)) {stRow = 2;} else {stRow = 1;}prep = con.prepareStatement(sltSql);// 开始生成sqlint intInsrtRow = 1;int intUpdateRow = 1;for (int intRow=stRow; intRow<=maxRow; intRow++ ) {HSSFRow sltRow = sheet.getRow(intRow);// 数据库存在数据判断int sqlIndex = 1;for (int keycol : intPryKeyCol) {HSSFCell cell = sltRow.getCell(keycol);String cellStr;if (cell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC ) {cellStr = (long)cell.getNumericCellValue()+"";prep.setObject(sqlIndex++,cellStr);} else {cellStr = cell.getStringCellValue()+"";prep.setObject(sqlIndex++,cellStr);}} // end for3System.out.println(sltSql);rsSlt = prep.executeQuery();sqlIndex = 1;if (rsSlt.next()) {for(int j=0; j<maxCol; j++) {if (intPryKeyCol.contains(j)) continue;HSSFCell cell = sltRow.getCell(j);String cellStr;int colType = hsColType.get((j+1)+"");System.out.println(cell + " " + cell.getCellType());if (cell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC ) {cellStr = (long)cell.getNumericCellValue()+"";setPrepDate(prepUpdate,colType,sqlIndex++,cellStr);} else {cellStr = cell.getStringCellValue()+"";setPrepDate(prepUpdate,colType,sqlIndex++,cellStr);}}// WHERE条件设定for (int keycol : intPryKeyCol) {HSSFCell cell = sltRow.getCell(keycol);String cellStr;if (cell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC ) {cellStr = (long)cell.getNumericCellValue()+"";prepUpdate.setObject(sqlIndex++,cellStr);} else {cellStr = cell.getStringCellValue()+"";prepUpdate.setObject(sqlIndex++,cellStr);}} // end for3if (isBatchSupport) {prepUpdate.addBatch();if(intUpdateRow++ %1000 == 0) {prepUpdate.executeBatch();}} else {prepUpdate.execute();}} else {for(int j=0; j<maxCol; j++) {HSSFCell cell = sltRow.getCell(j);String cellStr;int colType = hsColType.get((j+1)+"");if (cell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC ) {cellStr = (long)cell.getNumericCellValue()+"";setPrepDate(prepInsrt,colType,sqlIndex++,cellStr);} else {cellStr = cell.getStringCellValue()+"";setPrepDate(prepInsrt,colType,sqlIndex++,cellStr);}}if (isBatchSupport) {prepInsrt.addBatch();if(intInsrtRow++ %1000 == 0) {prepInsrt.executeBatch();}} else {prepInsrt.execute();}}if (isBatchSupport) {prepUpdate.executeBatch();prepInsrt.executeBatch();}} // end for2}}// end for1} catch (Exception ex) {throw ex;} finally {DbUtils.closeQuietly(rsSlt);DbUtils.closeQuietly(prep);DbUtils.closeQuietly(prepInsrt);DbUtils.closeQuietly(prepUpdate);}}

对日期类型参数要特别处理，请注意

public static void setPrepDate(PreparedStatement prep,int sqlType,int index,String value)  throws Exception{SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");switch(sqlType) {case Types.TIME:case Types.DATE:java.sql.Date date = new java.sql.Date(sdf.parse(value).getTime());prep.setObject(index,date);break;case Types.TIMESTAMP:Timestamp tamp = Timestamp.valueOf(value);prep.setObject(index,tamp);break;default:if (StringUtils.isEmpty(value) || value.equals("null"))// "null" 解决db导出excel是null的问题{prep.setNull(index,sqlType);}else{prep.setObject(index,value);}break;}}

下一节将实现excel中两Sheet页发生变化的内容用颜色区分的功能（程序执行前后db变化比较）