用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 while
if (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);
// 开始生成sql
int 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 for3
System.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 for3
if (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变化比较）
1971
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
