利用JDBC备份和还原MYSQL数据库

package com.keertech.util.backup; import java.io.File; import java.io.FileOutputStream; import java.io.OutputStreamWriter; import java.io.PrintWriter; import java.sql.Connection; import java.sql.ResultSet; import java.sql.Statement; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; import java.util.Vector; import com.keertech.util.publics.AppendMessage; import com.keertech.util.publics.DbConfig; import com.keertech.util.publics.DbConnection; import com.keertech.util.publics.PublicMethod; import com.keertech.util.publics.TableColumn; /** * MySql的备份和还原 * @author 吴浩 * @version 1.0 * 2011-6-29 */ @SuppressWarnings("serial") public class MySqlBackup implements IBackup{ private DbConfig dbConfig; public MySqlBackup(){ } public MySqlBackup(DbConfig dbConfig){ this.dbConfig = dbConfig; } /** * 创建mysql备份 */ public void createSqlScript(String filePath) throws Exception{ File file; File newFile; PrintWriter pwrite; try { file =new File(filePath); SimpleDateFormat smf = new SimpleDateFormat("yyyy:MM:dd"); newFile = new File(file.getPath() + "/" + dbConfig.getDbName() + "_" + smf.format(new Date()).replaceAll(" |:", "") + ".sql"); pwrite = new PrintWriter(new OutputStreamWriter(new FileOutputStream(newFile,false), "UTF8")); //写入头部信息 pwrite.println(AppendMessage.headerMessage(dbConfig)); pwrite.println("SET FOREIGN_KEY_CHECKS=0;" + "\n"); List<String> tablelists = PublicMethod.getAllTableName(dbConfig, "show tables"); for (String table : tablelists) { StringBuilder strBuilder =new StringBuilder(); strBuilder.append("show create table ") .append(table); List<String> list = PublicMethod.getAllColumns(dbConfig,strBuilder.toString()); for (String line : list) { //在建表前加说明 pwrite.println(AppendMessage.tableHeaderMessage(table)); //生成建表语句 pwrite.println("DROP TABLE IF EXISTS " + " `" + table + "`;"); pwrite.println(line + ";" + "\n"); } } pwrite.println(AppendMessage.insertHeaderMessage()); for (Object table : tablelists) { //生成insert语句 List<Vector<Object>> insertList = getAllDatas(dbConfig,table.toString()); for (int i = 0; i < insertList.size(); i++) { Vector<Object> vector = insertList.get(i); String tempStr = vector.toString(); tempStr =tempStr.substring(1, tempStr.length()-1); tempStr ="INSERT INTO " + "`"+ table + "`" + " VALUES(" + tempStr + ");"; pwrite.println(tempStr); } } pwrite.flush(); pwrite.close(); } catch (Exception e) { e.printStackTrace(); throw new Exception("出现错误,创建备份文件失败!"); } } /** * 还原mysql备份 */ public void executSqlScript(String filePath) throws Exception { Connection conn = null; Statement stmt = null; List<String> sqlStrList = null; try { sqlStrList = PublicMethod.loadSqlScript(filePath); conn = PublicMethod.getConnection(dbConfig); stmt = conn.createStatement(); // 禁止自动提交 conn.setAutoCommit(false); for (String sqlStr : sqlStrList) { int index = sqlStr.indexOf("INSERT"); if (-1 == index) { stmt.addBatch(sqlStr); } } stmt.executeBatch(); // INSERT语句跟建表语句分开执行，防止未建表先INSERT for (String sqlStr : sqlStrList) { int index = sqlStr.indexOf("INSERT"); if (-1 != index) { stmt.executeUpdate(sqlStr); } } stmt.executeBatch(); conn.commit(); } catch (Exception ex) { conn.rollback(); throw ex; }finally{ DbConnection.closeAll(conn, stmt, null); } } /** * 拼接连接字符串 * @param dbConfig * @return String * @throws Exception */ public static String getDbUrl(DbConfig dbConfig) throws Exception { StringBuilder urlStr = null; try { urlStr = new StringBuilder(); urlStr.append("jdbc:mysql://") .append(dbConfig.getIpNo()) .append(":") .append(dbConfig.getPortNo()) .append("/") .append(dbConfig.getDbName()); } catch (Exception e) { throw e; } return urlStr.toString(); } /** * 查询生成Insert语句所需的数据 * @param tableName * @return List<Vector<Object>> * @throws Exception */ public List<Vector<Object>> getAllDatas(DbConfig config,String tableName) throws Exception { List<Vector<Object>> list ; Vector<Object> vector; StringBuilder typeStr = null; List<TableColumn> columnList; StringBuilder sqlStr; ResultSet rs= null; StringBuilder columnsStr ; try { //生成查询语句 typeStr =new StringBuilder(); sqlStr =new StringBuilder(); columnsStr =new StringBuilder().append("describe ").append(tableName); columnList = PublicMethod.getDescribe(config,columnsStr.toString()); sqlStr.append("SELECT "); for (TableColumn bColumn : columnList) { //处理BLOB类型的数据 String columnsType = bColumn.getColumnsType(); if("longblob".equals(columnsType)||"blob".equals(columnsType)||"tinyblob".equals(columnsType)||"mediumblob".equals(columnsType)){ typeStr.append("hex(" + "`" + bColumn.getColumnsFiled() + "`" + ") as " + "`" + bColumn.getColumnsFiled() + "`" + " ,"); }else{ typeStr.append("`" + bColumn.getColumnsFiled() + "`" + " ,"); } } sqlStr.append(typeStr.substring(0,typeStr.length()-1)); sqlStr.append(" FROM ").append("`"+tableName + "`;"); //查询insert语句所需的数据 list = new ArrayList<Vector<Object>>(); rs = PublicMethod.queryResult(config, sqlStr.toString()); while (rs.next()) { vector = new Vector<Object>(); for (TableColumn dbColumn : columnList) { String columnsType = dbColumn.getColumnsType(); String columnsFile = dbColumn.getColumnsFiled(); if(null == rs.getString(columnsFile)){ vector.add( rs.getString(columnsFile)); //处理BIT类型的数据 }else if("bit".equals(columnsType.substring(0,3))){ vector.add(Integer.valueOf(rs.getString(columnsFile)).intValue()); }else if("bit".equals(columnsType.substring(0,3)) && 0 == Integer.valueOf(rs.getString(columnsFile)).intValue()){ vector.add("\'"+"\'"); }else if("longblob".equals(columnsType)||"blob".equals(columnsType)||"tinyblob".equals(columnsType)||"mediumblob".equals(columnsType)){ vector.add("0x"+rs.getString(columnsFile)); //处理 }else if("text".equals(columnsType)||"longtext".equals(columnsType)||"tinytext".equals(columnsType)||"mediumtext".equals(columnsType)){ String tempStr = rs.getString(columnsFile); tempStr = tempStr.replace("\'", "\\'"); tempStr = tempStr.replace("\"", "\\\""); vector.add("\'" + tempStr +"\'"); }else{ vector.add("\'" + rs.getString(columnsFile) + "\'"); } } list.add(vector); } } catch (Exception e) { throw e; } finally { DbConnection.closeAll(null, null, rs); } return list; } }