java mysql数据库备份，列表，还原，删除
（java mysql数据库备份，列表，还原，删除，备份sql，记录文件存储，按时间展示）
界面效果：
备份
/**
* 判断当前是否存在数据库操作
*/
public static boolean ISDBOPER = false;
@Override
public ResponseResult backupKeySnData(HttpServletRequest request) {
// 判断是否有其他备份操作
if (ISDBOPER) {
return new ResponseResult(201, "备份数据库失败，其他任务在执行");
}
ISDBOPER = true;
// 获取备份文件路径
String backupPath = getPathName(request);
// 实例化文件
File file = new File(backupPath);
RandomAccessFile randomFile = null;
StringBuffer sqlBuf = new StringBuffer();
// 排除备份表
List<String> excludeBack = Arrays.asList(
new String[]{"tb_app_log", "tb_log", "tb_seclogon_log"});
// 如果文件不存在，则创建文件路径
if (!file.exists())
file.mkdirs();
// 获取数据库表
List<Map<String, String>> tableList = sysParamsMapper.getDataBaseTableList();
for (Map<String, String> tableRecord : tableList) {
String tableName = tableRecord.get("tablename");
if (excludeBack.contains(tableName)) {
continue;
}
// 获取删除数据库数据sql
String deleteSql = "DELETE FROM " + tableName + ";\r\n";
sqlBuf.append(deleteSql);
// 有数据才进行备份操作
if (sysParamsMapper.getCount(tableName) > 0) {
// 获取插入数据库数据sql
String insertSql = this.getInsertSqlByTable(tableName);
sqlBuf.append(insertSql);
}
}
// 获取生成的sql文件名称
String fileName = backupPath + "db_" + (new SimpleDateFormat("yyyy_MM_dd_HH_mm_ss")).format(new Date())
+ ".sql";
try {
// 实例化随机访问文件对象
randomFile = new RandomAccessFile(fileName, "rw");
// 写入文件使用Base64加密
randomFile.write(Base64Util.encode(sqlBuf.toString().getBytes("UTF-8")));
randomFile.close();
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
ISDBOPER = false;
return new ResponseResult(200, "备份成功");
}
@NotNull
private String getPathName(HttpServletRequest request) {
return Constants.DATA_BACKUP_PATH + File.separator;
}
/**
* @param tableName
* @return String
* @throws
* @Title: getInsertSqlByTable
* @Description: 根据数据库表获取插入语句
*/
private String getInsertSqlByTable(String tableName) {
Connection conn = null;
ResultSet rs = null;
PreparedStatement pst = null;
String sql = "SELECT * FROM " + tableName;
String resultStr = "";
StringBuffer columnNameBuf = new StringBuffer();
StringBuffer columnDataBuf = new StringBuffer();
try {
// 获取数据库连接
conn = dataSource.getConnection();
pst = conn.prepareStatement(sql);
rs = pst.executeQuery();
// 获取表字段数量
int colAmount = rs.getMetaData().getColumnCount();
while (rs.next()) {
columnNameBuf = new StringBuffer();
columnDataBuf = new StringBuffer();
for (int j = 0; j < colAmount; j++) {
// 获取表字段名称
String columnName = rs.getMetaData().getColumnName(j + 1);
if ("GROUP".equalsIgnoreCase(columnName))
columnName = "`".concat(columnName).concat("`");
columnNameBuf.append(columnName).append(',');
// 获取表字段类型
String columnType = rs.getMetaData().getColumnTypeName(j + 1).toUpperCase(Locale.US);
// 获取表字段数据
String columnData = (rs.getObject(j + 1) == null) ? null : rs.getObject(j + 1).toString();
// 判断表字段类型
if (("VARCHAR".equals(columnType) || columnType.indexOf("BLOB") > -1
|| "TIMESTAMP".equals(columnType) || "DATETIME".equals(columnType)
|| "CHAR".equals(columnType) || "TEXT".equals(columnType)) && columnData != null) {
columnDataBuf.append("'");
columnData = columnData.replace("\\", "\\\\");
columnData = columnData.replace("'", "''");
columnDataBuf.append(columnData);
columnDataBuf.append("'");
columnDataBuf.append(',');
} else if ("VARBINARY".equals(columnType)) {
boolean bData = rs.getBoolean(j + 1);
columnDataBuf.append(bData).append(',');
} else {
columnDataBuf.append(columnData).append(',');
}
}
String allColumnName = columnNameBuf.toString();
allColumnName = allColumnName.substring(0, allColumnName.length() - 1);
String allColumnData = columnDataBuf.toString();
allColumnData = allColumnData.substring(0, allColumnData.length() - 1);
// 获取插入语句
String insertSql = "INSERT INTO " + tableName + "(" + allColumnName + ") VALUES(" + allColumnData
+ ");\r\n";
resultStr = resultStr + insertSql;
}
return resultStr;
} catch (Exception e) {
LOG.error("getInsertSqlByTable error:" + e.getMessage());
return "";
} finally {
if (rs != null)
try {
rs.close();
} catch (SQLException e) {
}
if (pst != null)
try {
pst.close();
} catch (SQLException e) {
}
if (conn != null)
try {
conn.close();
} catch (SQLException e) {
}
}
}
列表
@Override
public ResponseResult getKeySnBackupBook(Map<String, Object> map, HttpServletRequest request) {
Page<Map<String, String>> page = new Page<Map<String, String>>(Integer.valueOf(map.get("page").toString()), Integer.valueOf(map.get("pageSize").toString()));
// 获取备份文件路径
File file = new File(getPathName(request));
// 查询备份文件
File[] files = file.listFiles(new FileFilter() {
public boolean accept(File pathname) {
return pathname.getName().endsWith(".sql");
}
});
List<Map<String, String>> list = new ArrayList<Map<String, String>>();
if (files != null && files.length > 0) {
for (int i = 0; i < files.length; i++) {
Map<String, String> record = new HashMap<String, String>();
record.put("path", files[i].getName());
String dateString = files[i].getName().substring(files[i].getName().indexOf("_") + 1,
files[i].getName().lastIndexOf("."));
SimpleDateFormat sdf = new SimpleDateFormat("yyyy_MM_dd_HH_mm_ss");
try {
Date parse = sdf.parse(dateString);
record.put("time", String.valueOf(parse.getTime() / 1000));
} catch (ParseException e) {
e.printStackTrace();
}
list.add(record);
}
}
// 对结果集按照创建时间降序排列
list.sort(new Comparator<Map<String, String>>() {
@Override
public int compare(Map<String, String> o1, Map<String, String> o2) {
Integer date = Integer.parseInt(o1.get("time"));
Integer date1 = Integer.parseInt(o2.get("time"));
// 进行时间比较
if (date > date1) {
return -1;
} else {
return 1;
}
}
});
// 查询当前页码
int pageNumber = (int) page.getCurrent();
// 查询每页条数
int pageSize = (int) page.getSize();
// 获取查询偏移量
int offset = pageSize * (pageNumber - 1);
List<Map<String, String>> fileList = new ArrayList<Map<String, String>>();
for (int i = offset; i < offset + pageSize && list.size() > i; i++) {
fileList.add(list.get(i));
}
// 实例化分页对象
Page<Map<String, String>> pageList = new Page<Map<String, String>>();
// 设置当前页
pageList.setCurrent(page.getCurrent());
// 设置每页数量
pageList.setSize(page.getSize());
// 设置总条数
pageList.setTotal(files != null ? files.length : 0);
// 设置数据
pageList.setRecords(fileList);
return new ResponseResult(200, "查询成功", pageList);
}
还原
@Override
public ResponseResult rollbackKeySnData(HttpServletRequest request, Map<String, Object> map) {
String filePath = map.get("filePath").toString();
// 判断是否有其他备份或还原操作
if (ISDBOPER) {
return new ResponseResult(201, "有其他任务正在执行数据库备份或还原操作，请稍后再试");
}
ISDBOPER = true;
// 获取还原的sql文件路径
String backupPath = getPathName(request) + filePath;
RandomAccessFile randomFile = null;
StringBuffer sqlBuf = new StringBuffer();
String result = "success";
try {
randomFile = new RandomAccessFile(backupPath, "rw");
String lineString;
// 读取sql文件
while ((lineString = randomFile.readLine()) != null) {
sqlBuf.append(lineString);
}
String strSqls = new String(Base64Util.decode(sqlBuf.toString().getBytes("UTF-8")), "UTF-8");
// 获取sql列表
List<String> sqlList = new ArrayList<String>(Arrays.asList(strSqls.split(";\r\n")));
// 执行还原操作
result = this.processSqlList(sqlList);
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
// 关闭流，解决备份还原后文件无法删除问题
if(randomFile!=null){
try {
randomFile.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
ISDBOPER = false;
if (result.equals("success")) {
return new ResponseResult(200, "还原数据库成功");
}
return new ResponseResult(201, "还原数据库失败");
}
/**
* @param @param sqlList
* @param @return 参数
* @return String 返回类型
* @throws
* @Title: processSqlList
* @Description: 恢复数据库
*/
private String processSqlList(List<String> sqlList) {
String result = "success";
Connection conn = null;
Statement stmt = null;
try {
// 获取数据库连接
conn = dataSource.getConnection();
stmt = conn.createStatement();
// 不检查外键约束关系
stmt.addBatch("SET FOREIGN_KEY_CHECKS=0;");
// 添加sql文件sql
for (String sql : sqlList) {
stmt.addBatch(sql);
}
stmt.addBatch("SET FOREIGN_KEY_CHECKS=1;");
// 批量执行sql语句
stmt.executeBatch();
} catch (SQLException e) {
result = "fail";
} finally {
try {
if (stmt != null)
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
// result = printSqlException("3", e.getErrorCode(), "+ e.getMessage());
}
try {
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
// result = printSqlException("3", e.getErrorCode(), "+ e.getMessage());
}
}
return result;
}
删除
@Override
public ResponseResult deleteKeySnBackup(HttpServletRequest request, Map<String, Object> map) {
String filePath = map.get("filePath").toString();
// 获取备份文件路径
File file = new File(
getPathName(request) + filePath);
// 执行删除操作
if (!file.exists() || !file.delete()) {
return new ResponseResult(201, "删除失败");
}
return new ResponseResult(200, "删除成功");
}
以上！
上面有个坑点，还原后文件无法删除，重启服务可以删除，面向百度后排查 发现还原文件流未关闭；
1968
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
