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;
}
}
没有合适的资源?快使用搜索试试~ 我知道了~
利用JDBC备份和还原MYSQL数据库
共22个文件
class:11个
java:8个
project:1个
5星 · 超过95%的资源 需积分: 50 193 下载量 38 浏览量
2011-07-08
11:13:22
上传
评论 9
收藏 682KB RAR 举报
温馨提示
利用JDBC连接数据库,不依耐mysql的shell命令, 适合b/s 服务器备份
资源推荐
资源详情
资源评论
收起资源包目录
DbBackup.rar (22个子文件)
DbBackup
.project 384B
bin
com
keertech
util
publics
DbConfig.class 2KB
TableColumn.class 2KB
AppendMessage.class 2KB
DbConnection.class 1KB
PublicMethod.class 5KB
test
MySqlTest.class 1KB
backup
Db2Backup.class 302B
IBackup.class 254B
MySqlBackup.class 8KB
SqlServerBackup.class 320B
OracleBackup.class 311B
src
com
keertech
util
publics
PublicMethod.java 5KB
DbConnection.java 1KB
DbConfig.java 1KB
TableColumn.java 1KB
AppendMessage.java 2KB
test
MySqlTest.java 1KB
backup
MySqlBackup.java 7KB
IBackup.java 432B
mysql-connector-java-5.1.7-bin.jar 693KB
.classpath 383B
共 22 条
- 1
mrhao1991
- 粉丝: 1
- 资源: 2
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功
- 1
- 2
- 3
- 4
前往页