使用Java以.sql文件形式保存和恢复数据库指定表的文件
保存数据库文件(.sql)到本地文件夹
String[] command = new String[3];
//传入指定的数据库表的名称
@Override
public void backUpTable(String tableName) throws Exception{
StringBuilder command = new StringBuilder();
Runtime rt = Runtime.getRuntime();
Process pro = null;
command.append("mysqldump -u" + "root" + " -p" + "123456" + " dbName");
//username为数据库用户名 password为密码 dbName为数据库名
//.sql文件命名格式,以创建时间为纬度
String time = new SimpleDateFormat("yyyy-MM-dd_HH-mm-ss").format(new Date());
String fileName ="E://" +tableName + "/" + time + ".sql";//放在E盘下
command.append(tableName);
command.append(" ");
command.append("-w \" 1=1 ORDER BY id DESC");
//command.append(number);
command.append("\"");
command.append(" ");
command.append("--result-file=" + fileName);
pro = rt.exec(command.toString());
//命令执行出错则打印相关错误信息
BufferedReader br = new BufferedReader(new InputStreamReader( pro.getErrorStream()));
String errorLine = null;
while ((errorLine = br.readLine()) != null) {
System.out.println(errorLine);
}
br.close();
}
读取.sql文件恢复表数据
@Override
public void restoreTable(String sqlPackage,String sqlName) throws Exception{
initOSCommand();
//mysql -uusername -ppassword testdb < E://test.sql
StringBuilder commandStr = new StringBuilder();
Runtime rt = Runtime.getRuntime();
//读取的文件路径
String fileName = "E://"+"onlineSql/"+ sqlPackage + "/" + sqlName;
//test为数据库名
commandStr.append("mysql -u" + "root" + " -p" + "123456" + " test < ");
commandStr.append(fileName);
command[2] = commandStr.toString();
Process pro = rt.exec(command);
//命令执行出错则打印相关错误信息
BufferedReader br = new BufferedReader(new InputStreamReader( pro.getErrorStream()));
String errorLine = null;
while ((errorLine = br.readLine()) != null) {
System.out.println(errorLine);
}
br.close();
}
public void initOSCommand(){
String osName = System.getProperty("os.name");
osName = osName.toLowerCase();
if(osName.indexOf("win")>-1){
command[0] = "cmd";
command[1] = "/c";
}else if(osName.indexOf("linux")>-1){
command[0] = "/bin/bash";
command[1] = "-c";
}else{
throw new MyException(20001,"当前系统环境不支持数据库还原操作");
}
}