Oracle 备份和还原
大家好，为了方便数据库备份还原，往往会做成一个小功能，供后台维护人员使用；
我简单记录一下我demo的实现吧；
工具：eclipse + pl/sql Oracle 数据库 ，使用的是mybatis框架 来实现指定数据表备份；
第一步：首先画一个简单的不能在简单的一面；
<%@ page contentType="text/html;charset=UTF-8"%>
<%@ include file="/WEB-INF/views/include/taglib.jsp"%>
<html>
<head>
<title>数据备份管理</title>
<script type="text/javascript">
</script>
</head>
<body>
<div>
<form action="${ctx}/backup/backTables/exportDatabase" method="post">
<button type="submit">备份数据</button>
</form>
</div>
<div>
<form action="${ctx}/backup/backTables/importDatabase" method="post">
<button type="submit">还原数据</button>
</form>
</div>
</body>
</html>
第二步：写一个配置文件： databaseBackUp.properties
DbUserName=数据库名称(例：root)
DbPassword=数据库密码(例：root)
DbSID=IP:端口号(例：192.168.1.1:1521)
DbServiceName=tns监听名称（例：orcl）
fileName=d:/backup7.dpm
tables=要备份导出的表(例：TEST_DATA,TEST_DATA_MAIN）
第三步：创建一个controller ，写清楚映射
@Controller
@RequestMapping(value = "${adminPath}/backup/backTables")
public class BackUpController extends BaseController {
接下来的代码，写在这里面；
}
第四步：注入service ，跳转页面、获取配置文件的内容及实体
@Autowired
private BackUpService backUpService;
/**
* 跳转页面
*/
@RequestMapping(value = "backup")
public String backup() throws InterruptedException {
return "modules/backup/backup";
}
/**
* 读取配置文件
*
* @return
*/
private static Properties properties() {
InputStream in = PropertyUtil.class.getClassLoader().getResourceAsStream("databasebackup.properties");
Properties properties = new Properties();
try {
properties.load(in);
} catch (Exception e1) {
e1.printStackTrace();
}
return properties;
}
public class OracleDBBackUp {
//数据库名称
private String DbUserName;
//数据库密码
private String DbPassword;
//连接地址：192.168.x.x
private String DbSID;
//服务名称 orcl
private String DbServiceName;
//导入文件名称
private String fileName;
//导入导出表
private List<String> tables;
省略get,set 及toString方法
}
第五步：备份前，获取配置文件中的数据
/**
* 备份数据前，准备信息
*/
@RequestMapping(value = "exportDatabase")
public void exportDatabase() throws Exception {
OracleDBBackUp dod = new OracleDBBackUp();
Properties properties = properties();
List<String> list = new ArrayList<>();
String tables = properties.getProperty("tables");
String[] str = tables.split(",");
list.addAll(Arrays.asList(str));
dod.setTables(list);
dod.setFileName(properties.getProperty("fileName"));
dod.setDbUserName(properties.getProperty("DbUserName"));
dod.setDbPassword(properties.getProperty("DbPassword"));
dod.setDbSID(properties.getProperty("DbSID"));
dod.setDbServiceName(properties.getProperty("DbServiceName"));
//调用备份方法
exportDB(dod);
}
/**
* 导出数据
*
* @param oracleDbBackUp
* @return
*/
public static String exportDB(OracleDBBackUp oracleDbBackUp) {
StringBuffer commandBuf = new StringBuffer("exp ");
commandBuf.append(oracleDbBackUp.getDbUserName() + "/");
commandBuf.append(oracleDbBackUp.getDbPassword() + "@");
commandBuf.append(oracleDbBackUp.getDbSID() + "/");
commandBuf.append(oracleDbBackUp.getDbServiceName());
commandBuf.append(" file=" + oracleDbBackUp.getFileName() + " tables=(");
List<String> tabNameList = oracleDbBackUp.getTables();
if (null != tabNameList && tabNameList.size() > 0) {
for (int i = 0; i < tabNameList.size(); i++) {
commandBuf.append(oracleDbBackUp.getTables().get(i));
commandBuf.append(",");
}
commandBuf.deleteCharAt(commandBuf.length() - 1);
} else {
return "导出失败,没有导出的表名";
}
commandBuf.append(")");
String[] cmds = { "cmd", "/C", commandBuf.toString() };
Joiner joiner = Joiner.on(" ").skipNulls();
System.out.println(joiner.join(cmds));
Process process = null;
try {
process = Runtime.getRuntime().exec(joiner.join(cmds));
if (process.waitFor() == 0) {// 0 表示线程正常终止。
return "数据库成功备份！！！";
} else {
return "数据库备份失败！！！";
}
} catch (Exception e) {
e.printStackTrace();
return "导出失败";
}
}
第六步：导入数据库信息
/**
* 导入功能
*/
@RequestMapping(value = "importDatabase")
public void importDatabase() {
OracleDBBackUp dod = new OracleDBBackUp();
Properties properties = properties();
List<String> list = new ArrayList<>();
String tables = properties.getProperty("tables");
String[] str = tables.split(",");
list.addAll(Arrays.asList(str));
dod.setTables(list);
dod.setFileName(properties.getProperty("fileName"));
dod.setDbUserName(properties.getProperty("DbUserName"));
dod.setDbPassword(properties.getProperty("DbPassword"));
dod.setDbSID(properties.getProperty("DbSID"));
dod.setDbServiceName(properties.getProperty("DbServiceName"));
System.out.print(importDB(dod));
}
public String importDB(OracleDBBackUp oracleDbBackUp) {
StringBuffer commandBuf = new StringBuffer("imp ");
commandBuf.append(oracleDbBackUp.getDbUserName() + "/");
commandBuf.append(oracleDbBackUp.getDbPassword() + "@");
commandBuf.append(oracleDbBackUp.getDbSID() + "/");
commandBuf.append(oracleDbBackUp.getDbServiceName());
commandBuf.append(" file=" + oracleDbBackUp.getFileName());
List<String> tabNameList = oracleDbBackUp.getTables();
if (null != tabNameList && tabNameList.size() > 0) {
commandBuf.append(" tables=(");
for (int i = 0; i < tabNameList.size(); i++) {
commandBuf.append(oracleDbBackUp.getTables().get(i));
commandBuf.append(",");
}
commandBuf.deleteCharAt(commandBuf.length() - 1);
commandBuf.append(")");
}
String[] cmds = { "cmd", "/C", commandBuf.toString() };
Joiner joiner = Joiner.on(" ").skipNulls();
System.out.println(joiner.join(cmds));
Process process = null;
try {
// 导入前，先删除表结构
List<String> tables = oracleDbBackUp.getTables();
dropTables(tables);
process = Runtime.getRuntime().exec(cmds);
if (process.waitFor() == 0) {// 0 表示线程正常终止。
return "导入数据库成功！！！";
} else {
return "导入数据库失败！！！";
}
} catch (Exception e) {
e.printStackTrace();
return "导入失败";
}
}
/**
* 导入前，先将表删除
*
* @param tables
*/
private void dropTables(List<String> tables) {
for (String tabName : tables) {
// 导入数据前先删除数据表
backUpService.dropTable(tabName);
}
};
第七步：创建service 、dao层代码及mapper.xml
@Service
@Transactional(readOnly = true)
public class BackUpService extends BaseService{
@Autowired
private BackUpDao backUpDao;
/**
* drop table
*/
@Transactional(readOnly = false)
public void dropTable(String tabName) {
// TODO Auto-generated method stub
backUpDao.dropTable(tabName);
}
}
-- dao层代码
@MyBatisDao
public interface BackUpDao extends CrudDao<OracleDBBackUp>{
/**
* 删除数据表
*/
void dropTable(@Param(value="tabName") String tabName);
}
-- mapper.xml 文件
<?xml version="1.0" encoding="UTF-8" ?>
<mapper namespace="dao的全路径名">
<update id="dropTable">
drop table ${tabName}
</update>
</mapper>
至此，Java实现Oracle的备份还原实现了；
第一次写这个，如果有不对的地方，欢迎指出，共同学习，共同进步；
posted on 2020-09-03 11:56 Spring-Boot-Cloud 阅读(360) 评论(0) 收藏 举报
浙公网安备 33010602011771号