使用Java以.sql文件形式保存和恢复数据库指定表的文件

使用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,"当前系统环境不支持数据库还原操作");
        }
    }