package com.ithings.util.database.mysql;
import com.ithings.util.CommUtils;
import com.ithings.util.database.entity.BackupParam;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
*
* 数据库操作工具类
* 用于备份和恢复数据、运行sql文件（触发器、存储过程等）
* 2012-08-25
* 吴辉
*/
public class MySqlUtil {
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException ex) {
Logger.getLogger(MySqlUtil.class.getName()).log(Level.SEVERE, null, ex);
}
}
/**
* 获取jdbc连接
* @param ip
* @param port
* @param dbName
* @param userName
* @param pwd
* @return
*/
public static Connection getConn(String ip, String port, String dbName, String userName, String pwd) {
Connection conn = null;
try {
StringBuilder sb = new StringBuilder("jdbc:mysql://");
sb.append(ip).append(":").append(port).append("/");
sb.append(dbName);
conn = DriverManager.getConnection(sb.toString(), userName, pwd);
} catch (SQLException ex) {
Logger.getLogger(MySqlUtil.class.getName()).log(Level.SEVERE, null, ex);
}
return conn;
}
/**
* 备份数据
* @param backupParam
* @return
* @throws IOException
*/
public static String backupDb(BackupParam backupParam) throws IOException {
String resultPath = null;
String ip = CommUtils.isNull(backupParam.getServerIp()) ? "127.0.0.1" : backupParam.getServerIp();
String port = CommUtils.isNull(backupParam.getServerPort()) ? "3306" : backupParam.getServerPort();
String binDir = backupParam.getBinDir();
String backupDir = backupParam.getBackupDir();
String dbName = backupParam.getDbName();
String userName = backupParam.getUserName();
String pwd = CommUtils.isNull(backupParam.getPwd()) ? "" : backupParam.getPwd();
String tableStr = backupParam.getTables();
if (CommUtils.isNull(dbName)
|| CommUtils.isNull(userName)
|| CommUtils.isNull(backupParam.getTables())) {
throw new RuntimeException("备份命令参数必输项不能为空。");
}
boolean flag = false;
try {
flag = checkedConnection(ip, port, dbName, userName, pwd);
} catch (SQLException ex) {
Logger.getLogger(MySqlUtil.class.getName()).log(Level.SEVERE, null, ex);
}
if (!flag) {
return resultPath;
}
String[] tableArr = backupParam.getTables().split(",");
StringBuilder tables = new StringBuilder();
for (String table : tableArr) {
if (!CommUtils.isNull(table)) {
tables.append(table.trim()).append(" ");
}
}
StringBuilder sb = new StringBuilder(binDir);
sb.append("mysqldump ");
sb.append("-h").append(ip).append(" -P").append(port);
sb.append(" -u").append(userName);
if (!CommUtils.isNull(pwd)) {
sb.append(" -p").append(pwd);
}
sb.append(" -c -t ").append(dbName).append(" ").append(tables);
System.out.println(sb.toString());
String command = sb.toString();
InputStream in = null;
InputStreamReader isr = null;
BufferedReader br = null;
OutputStreamWriter writer = null;
FileOutputStream fout = null;
Integer fileSize = 0;
try {
Runtime rt = Runtime.getRuntime();
Process child = rt.exec(command);
in = child.getInputStream();// 控制台的输出信息作为输入流
isr = new InputStreamReader(in, "utf8");// 设置输出流编码为utf8。这里必须是utf8，否则从流中读入的是乱码
// 组合控制台输出信息字符串
br = new BufferedReader(isr);
backupDir = backupParam.getBackupDir();
backupDir = backupDir.replaceAll("//", "/");
//创建备份目录
File file = new File(backupDir);
if (!file.exists()) {
file.mkdirs();
}
file = null;
//备份目录+文件名为保存是文件地址
backupDir += backupParam.getFileName();
fout = new FileOutputStream(backupDir);
writer = new OutputStreamWriter(fout, "utf8");
String line;
while ((line = br.readLine()) != null) {
line += "\r\n";
writer.write(line);
//累计数据大小
fileSize += line.getBytes().length;
}
// 注：这里如果用缓冲方式写入文件的话，会导致中文乱码，用flush()方法则可以避免
writer.flush();
resultPath = backupDir + "; 大小=" + fileSize;
} catch (UnsupportedEncodingException ex) {
Logger.getLogger(MySqlUtil.class.getName()).log(Level.SEVERE, null, ex);
} catch (IOException ex) {
Logger.getLogger(MySqlUtil.class.getName()).log(Level.SEVERE, null, ex);
} finally {
MySqlUtil.closeStream(in, isr, br, writer, fout);
return resultPath;
}
}
/**
* 导入sql文件（批量sql语句、存储过程、触发器等）
* @param backupParam
* @throws IOException
*/
public static void importSql(BackupParam backupParam) throws IOException {
String ip = CommUtils.isNull(backupParam.getServerIp()) ? "127.0.0.1" : backupParam.getServerIp();
String port = CommUtils.isNull(backupParam.getServerPort()) ? "3306" : backupParam.getServerPort();
String binDir = backupParam.getBinDir();
String dbName = backupParam.getDbName();
String userName = backupParam.getUserName();
String pwd = CommUtils.isNull(backupParam.getPwd()) ? "" : backupParam.getPwd();
boolean flag = false;
try {
flag = checkedConnection(ip, port, dbName, userName, pwd);
} catch (SQLException ex) {
Logger.getLogger(MySqlUtil.class.getName()).log(Level.SEVERE, null, ex);
}
if (!flag) {
return;
}
StringBuilder sb = new StringBuilder(binDir);
sb.append("mysql");
sb.append(" -h").append(ip).append(" -P").append(port);
sb.append(" -u").append(userName);
if (!CommUtils.isNull(pwd)) {
sb.append(" -p").append(pwd);
}
sb.append(" ").append(dbName);
BufferedReader br = null;
OutputStreamWriter writer = null;
FileOutputStream fout = null;
try {
Runtime rt = Runtime.getRuntime();
Process process = rt.exec(sb.toString());
OutputStream out = process.getOutputStream();
sb.delete(0, sb.length());
br = new BufferedReader(new FileReader(new File(backupParam.getRevertDir())));
String line = null;
while ((line = br.readLine()) != null) {
sb.append(line).append("\r\n");
}
writer = new OutputStreamWriter(out, "utf8");
writer.write(sb.toString());
writer.flush();
out.close();
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
- 1
- 2
- 3
前往页