java 操作数据库备份

java 操作数据库备份

    /**用于执行某表的备份(内部类)线程
     * @from    fhadmin.cn
     * Callable 有返回值的线程接口
     */
    class DbBackUpCallable implements Callable<Object>{
        String tableName = null;
        public DbBackUpCallable(String tableName){
            this.tableName = tableName;
        }
        @Override
        public Object call() {
            try {
                String remoteDB = pros.getProperty("remoteDB");            //是否远程备份数据库 yes or no
                String DBSeverport = pros.getProperty("DBSeverport");    //远程服务器备份程序端口
                String dbtype = pros.getProperty("dbtype");                //数据库类型
                String username = pros.getProperty("username");            //用户名
                String password = pros.getProperty("password");            //密码
                String address = pros.getProperty("dbAddress");            //数据库连接地址
                String databaseName = pros.getProperty("databaseName");    //数据库名
                String dbpath = pros.getProperty("dbpath");            //数据库的安装路径
                String sqlpath = pros.getProperty("sqlFilePath");        //存储路径
                String ffilename = DateUtil.getSdfTimes();
                String commandStr = "";

                if(!"sqlserver".equals(dbtype)){
                    sqlpath = sqlpath+DateUtil.getDays()+"/";            //日期当路径分支
                    if("yes".equals(remoteDB)){//数据库另外一台服务器上(和tomcat不在同一台服务器上)
                        commandStr = DbFH.getExecStr(dbtype,dbpath,"localhost",username,password,sqlpath,tableName,databaseName,ffilename); //命令语句
                        Socket ss = null;
                        DataOutputStream bb = null;
                        DataInputStream dat = null;
                        ss = new Socket(address, Integer.parseInt(DBSeverport));//连接远程服务器数据库备份程序
                        bb = new DataOutputStream(ss.getOutputStream());
                        dat = new DataInputStream(ss.getInputStream());
                        bb.writeUTF("FHQ313596790"+commandStr+"FH313596790"+sqlpath);    //发送指令给服务端
                        bb.flush();
                        Boolean llm = true;
                        while(llm){
                            String returnstr = dat.readUTF();
                            if("errer".equals(returnstr)){
                                return returnstr;    //远程服务器备份失败或超时
                            }
                            llm = false;
                            ss.close();
                            bb.close();
                            dat.close();
                        }
                    }else{                            //数据库在本地(和tomcat在同一台服务器上)
                        FileUtil.createDir(sqlpath+"/fh.fh");
                        commandStr = DbFH.getExecStr(dbtype,dbpath,address,username,password,sqlpath,tableName,databaseName,ffilename); //命令语句
                        Runtime cmd = Runtime.getRuntime();
                        Process p = cmd.exec(commandStr);
                        p.waitFor();                 // 该语句用于标记,如果备份没有完成,则该线程持续等待
                    }
                }else{//当数据库为sqlserver时 只能备份整库,不能单表备份
                    String spath = sqlpath + databaseName + "_"+ffilename + ".bak";// name文件名  
                    String bakSQL = "backup database "+databaseName+" to disk=? with init";// SQL语句  
                    PreparedStatement bak = DbFH.getFHCon().prepareStatement(bakSQL);  
                    bak.setString(1, spath);// path必须是绝对路径  
                    bak.execute();             // 备份数据库  
                    bak.close(); 
                }
                String fileType=".bak";
                if("mysql".equals(dbtype)){
                    fileType=".sql";
                }else if("oracle".equals(dbtype)){
                    fileType=".DMP";
                }
                if("".equals(tableName)){
                    return sqlpath+databaseName+"_"+ffilename+fileType;
                }else{
                    return sqlpath+tableName+"_"+ffilename+fileType;
                }
            } catch (Exception e) {
                logger.error("备份操作出现问题", e);
                return "errer";
            }finally{
                backUpTableList.remove(tableName); // 最终都将解除
            }
        }
    }
    
    /**数据库备份命令字符串
     * @param dbtype 数据库类型
     * @param dbpaths 数据库的路径
     * @param address 数据库连接地址
     * @param username 用户名
     * @param password 密码
     * @param sqlpath 存储路径
     * @param tableName 表名
     * @param databaseName 数据库名
     * @param ffilename 日期当路径和保存文件名的后半部分
     * @return 完整的命令字符串
     */
    public static String getExecStr(String dbtype,String dbpath,String address,String username,String password,String sqlpath,String tableName,String databaseName,String ffilename){
        StringBuffer sb = new StringBuffer();
        if("mysql".equals(dbtype)){
            address = "localhost";
            sb.append(dbpath);
            sb.append("mysqldump ");
            sb.append("--opt ");
            sb.append("-h ");
            sb.append(address);
            sb.append(" ");
            sb.append("--user=");
            sb.append(username);
            sb.append(" ");
            sb.append("--password=");
            sb.append(password);
            sb.append(" ");
            sb.append("--lock-all-tables=true ");
            sb.append("--result-file=");
            sb.append(sqlpath);
            sb.append(("".equals(tableName)?databaseName+"_"+ffilename:tableName+"_"+ffilename)+".sql");
            sb.append(" ");
            sb.append("--default-character-set=utf8 ");
            sb.append(databaseName);
            sb.append(" ");
            sb.append(tableName);//当tableName为“”时,备份整库
        }else if("oracle".equals(dbtype)){
            if("".equals(tableName)){//备份整库
                sb.append("EXP "+username+"/"+password+" BUFFER=880000 FILE="+sqlpath+username+"_"+ffilename+".DMP LOG="+sqlpath+username+"_"+ffilename+".LOG OWNER="+username);
            }else{//备份单表
                sb.append("EXP "+username+"/"+password+" BUFFER=880000 FILE="+sqlpath+tableName+"_"+ffilename+".DMP LOG="+sqlpath+tableName+"_"+ffilename+".LOG TABLES=("+username+"."+tableName+")");
            }
        }
        return sb.toString();
    }
 
hmoban主题是根据ripro二开的主题,极致后台体验,无插件,集成会员系统
自学咖网 » java 操作数据库备份