java mysql数据库的备份与还原
1.底层数据库操作封装package com.db;import java.sql.*;import java.util.*;/** ** @author zhaozhi3758 * @desc 底层数据库操作封装 */public class JdbcExcute {/** * 获取链接 * @return */public Connection getCon(){Connection conn = null;try {Class.forName("com.mysql.jdbc.Driver");// 加载驱动String url = "jdbc:mysql://localhost:3306/libraryv1?useUnicode=true&characterEncoding=utf-8&useOldAliasMetadataBehavior=true";conn = DriverManager.getConnection(url, "root", "123");// 建立连接conn.setAutoCommit(false);} catch (ClassNotFoundException e) {e.printStackTrace();}catch (SQLException e) {e.printStackTrace();}return conn;}/** * 得到查询结果集的字段结构 * @param rs * @return 字段结果集 *//*public HashMap getDataColl(ResultSet rs){HashMap mapcoll = new HashMap();try {ResultSetMetaData rsmd = rs.getMetaData();for(int i=1;i<=rsmd.getColumnCount();i++){ String filedName=rsmd.getColumnName(i); System.out.println("*******"+filedName); //String fileType = rsmd.getColumnTypeName(i); mapcoll.put(filedName, "");}} catch (Exception e) {e.printStackTrace();}return mapcoll;}*//** * 获得查询数据,使用HashMap进行封装 * @param sqlstr 查询的SQL语句 * @return */public ArrayList selQuery(String sqlstr){Connection connetcion=null;Statement stm=null;try {connetcion = getCon();connetcion.setAutoCommit(true);} catch (Exception e1) {e1.printStackTrace();}ArrayList datalist = new ArrayList();ResultSet rs = null;if(sqlstr.equals(""))return null;try {stm=connetcion.createStatement();rs = stm.executeQuery(sqlstr);ResultSetMetaData rsmd = rs.getMetaData();int colum=rsmd.getColumnCount();//HashMap map = getDataColl(rs);//Object[] keys = map.keySet().toArray();while(rs.next()){HashMap dataSet = new HashMap();for(int i=1;i<=colum;i++){String strFieldName = rsmd.getColumnName(i).toLowerCase();// if(keys.toString().equals("COLUMN_NAME"))keys="Field";// if(keys.toString().equals("COLUMN_TYPE"))keys="Type";// if(keys.toString().equals("IS_NULLABLE"))keys="Null";// if(keys.toString().equals("COLUMN_KEY"))keys="Key";// if(keys.toString().equals("COLUMN_DEFAULT"))keys="Default";// if(keys.toString().equals("EXTRA"))keys="Extra"; dataSet.put(strFieldName, rs.getObject(i));}datalist.add(dataSet);}} catch (Exception e) {e.printStackTrace();}finally{try {if(stm!=null)stm.close();if(connetcion!=null)connetcion.close();} catch (SQLException e) {e.printStackTrace();}}return datalist;}/** * 执行数据库增删改操作 * @param sqlstr 增删改的SQL语句 * @return 成功:0 失败:-1 */public int saveOrUpdate(String sqlstr){Connection connetcion=null;try {connetcion = getCon();connetcion.setAutoCommit(true);} catch (Exception e1) {e1.printStackTrace();}try {connetcion.createStatement().execute(sqlstr);return 0;} catch (Exception e) {e.printStackTrace();return -1;}finally{try {if(!connetcion.isClosed()){connetcion.close();}} catch (SQLException e) {e.printStackTrace();}}} public static void main(String[] args) { JdbcExcute dbExcute=new JdbcExcute(); System.out.println(dbExcute.selQuery("SELECT * FROM reader"));}}
2.操作业务类
package com.db;import java.io.*;import java.text.SimpleDateFormat;import java.util.*;public class DbOper {private String tableName;private String dbName="libraryv1";private String _username = "root";private String _password = "123";private String _host = "localhost";private String _port="3306";public String getTableName() {return tableName;}public void setTableName(String tableName) {this.tableName = tableName;}public String getDbName() {return dbName;}public void setDbName(String dbName) {this.dbName = dbName;}public String get_username() {return _username;}public void set_username(String _username) {this._username = _username;}public String get_password() {return _password;}public void set_password(String _password) {this._password = _password;}public String get_host() {return _host;}public void set_host(String _host) {this._host = _host;}public String get_port() {return _port;}public void set_port(String _port) {this._port = _port;}/** * 查询指定数据库的所有表名 * @param dbName 数据库名称 * @return ArrayList */public ArrayList selAllTableInfo(String dbName){//返回的结果集ArrayList<HashMap> resultList = new ArrayList<HashMap>();JdbcExcute exec = new JdbcExcute();String sql = "select table_name, table_type,engine from information_schema.tables where table_schema='"+this.dbName+"' order by table_name asc";resultList = exec.selQuery(sql);return resultList;}/** * 查询指定表的表结构 * @param tableName 表名 * @return ArrayList */public ArrayList selTableStructure(String tableName){ArrayList<HashMap> resultList = new ArrayList<HashMap>();JdbcExcute exec = new JdbcExcute();String sql = "desc "+tableName;resultList = exec.selQuery(sql);return resultList;}/** * 根据路径生成备份数据库的Shell字符串 * @param targetName 要备份的对象名：只能为表名和数据库名称 * @return 实际执行的shell命令 */public String getBackupShellString(String targetName){ String basepath=Thread.currentThread().getContextClassLoader().getResource("").toString();String backFile = "";String database_tools_path=basepath.substring(6, basepath.length()-4)+"dbtools/";//备份工具路径if(targetName.equals(this.dbName)){//若要备份整个数据库SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");backFile = "F:/database/"+targetName+"_"+sdf.format(new Date())+".sql";//要备份的文件targetName = "";}else{backFile = "F:/tables/"+targetName+".sql";}String OSType = System.getProperty("os.name");String shellStr = "";if(OSType.indexOf("Windows")!=-1){shellStr = database_tools_path+"mysqldumpwin.exe -h "+this._host+" -P"+this._port+" -u"+this._username+" -p"+this._password+" --result-file="+backFile+" --default-character-set=gbk "+this.dbName+" "+targetName;}else{shellStr = database_tools_path+"mysqldump -h "+this._host+" -P"+this._port+" -u"+this._username+" -p"+this._password+" --result-file="+backFile+" --default-character-set=gbk "+this.dbName+" "+targetName;}System.out.print("##############"+shellStr);return shellStr;}/** * 备份数据库 * @param targetName 要备份的对象名：只能为表名和数据库名称 * @return 成功:TRUE 失败:FALSE * 备份表直接备份在指定文件夹，备份库则按日期备份到指定的文件夹 * */public boolean backup(String targetName){String backFilePath = "";SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");String backDirString = "F:/database/";//默认备份库try {if(!targetName.equals(this.dbName)){//备份表File tableDir = new File("F:/tables/");if(!tableDir.exists()){//存放表的文件夹不存在tableDir.mkdir();System.out.println("--------->"+tableDir);}backFilePath ="F:/tables/"+targetName+".sql";//要备份的文件}else {//备份库backFilePath ="F:/database/"+targetName+"_"+sdf.format(new Date())+".sql";//要备份的文件File backDir = new File(backDirString);if(!backDir.exists()){//存放库的文件夹不存在backDir.mkdir();}}//判断要备份的文件是否已存在File backFile = new File(backFilePath);if(backFile.exists()){backFile.delete();}Runtime runt = Runtime.getRuntime();//Process proc = runt.exec("D:/myec6_tomcat/webapps/cms/dbtools/mysqldumpwin.exe -h 127.0.0.1 -P3306 -uroot -p123 --result-file=F:/tables/menuinfo.sql --default-character-set=gbk bizoss_cms menuinfo");Process proc = runt.exec(getBackupShellString(targetName));int tag = proc.waitFor();//等待进程终止if(tag==0){return true;}else{return false;}} catch (Exception e) {e.printStackTrace();return false;}}/** * 恢复数据库 * @param targetName 要备份的对象名：只能为表名和数据库名称 * @return 成功:TRUE 失败:FALSE */public boolean restore(String targetName){try {Runtime runt = Runtime.getRuntime();Process proc;String cmdtext = this.getRestoreShellString(targetName);if(System.getProperty("os.name").indexOf("Windows")!=-1){String[] cmd= { "cmd", "/c", cmdtext};proc= runt.exec(cmd);}else{String[] cmd= { "sh","-c",cmdtext};proc = runt.exec(cmd);} System.out.println(cmdtext);int tag = proc.waitFor();//等待进程终止System.out.println("进程返回值为tag:"+tag);if(tag==0){return true;}else{return false;}} catch (Exception e) {e.printStackTrace();}return false;}/** * 根据路径生成恢复数据库的Shell字符串 * @param targetName targetName 要还原的对象名：只能为表名和数据库名称 * @return 恢复数据时实际执行的shell */public String getRestoreShellString(String targetName){String basepath=Thread.currentThread().getContextClassLoader().getResource("").toString();String database_tools_path=basepath.substring(6, basepath.length()-4)+"dbtools/";//备份工具路径String backFile = "";//已备份的文件if(targetName.indexOf(this.dbName) == -1){//还原表backFile = "f:/tables/"+targetName+".sql";}else{//还原库backFile ="f:/database/"+targetName;}String OSType = System.getProperty("os.name");String shellStr = "";if(OSType.indexOf("Windows")!=-1){shellStr = database_tools_path+"mysqlwin.exe -h "+this._host+" -P"+this._port+" -u"+this._username+" -p"+this._password+" --default-character-set=gbk "+this.dbName +" < "+backFile;}else{shellStr = database_tools_path+"mysql -h "+this._host+" -P"+this._port+" -u"+this._username+" -p"+this._password+" --default-character-set=gbk "+this.dbName+" < "+backFile;}return shellStr;}public static void main(String[] args) {DbOper db=new DbOper(); // System.out.println(db.selAllTableInfo("libraryv1")); System.out.println(db.selTableStructure("bookinfo"));//System.out.println(db.backup("t"));// System.out.println(System.getProperty("os.name"));//System.out.println(db.restore("t")); }}
页:
[1]