本次实现是使用了MySQL数据库本身提供的备份命令mysqldump和恢复命令mysql，在java代码中通过从命令行调用这两条命令来实现备份和恢复。备份和恢复所使用的文件都是sql文件。
本代码是参照网上某网友提供的源码完成的。
[java] view plaincopy
package xxx.utils
import java.io.BufferedReader
import java.io.File
import java.io.FileInputStream
import java.io.FileNotFoundException
import java.io.FileOutputStream
import java.io.IOException
import java.io.InputStream
import java.io.InputStreamReader
import java.io.OutputStream
import java.io.OutputStreamWriter
import java.io.PrintWriter
import java.io.UnsupportedEncodingException
/**
* MySQL数据库的备份与恢复 缺陷：可能会被杀毒软件拦截
*
* @author xxx
* @version xxx
*/
public class DatabaseBackup {
/** MySQL安装目录的Bin目录的绝对路径 */
private String mysqlBinPath
/** 访问MySQL数据库的用户名 */
private String username
/** 访问MySQL数据库的密码 */
private String password
public String getMysqlBinPath() {
return mysqlBinPath
}
public void setMysqlBinPath(String mysqlBinPath) {
this.mysqlBinPath = mysqlBinPath
}
public String getUsername() {
return username
}
public void setUsername(String username) {
this.username = username
}
public String getPassword() {
return password
}
public void setPassword(String password) {
this.password = password
}
public DatabaseBackup(String mysqlBinPath, String username, String password) {
if (!mysqlBinPath.endsWith(File.separator)) {
mysqlBinPath = mysqlBinPath + File.separator
}
this.mysqlBinPath = mysqlBinPath
this.username = username
this.password = password
}
/**
* 备份数据库
*
* @param output
*输出流
* @param dbname
*要备份的数据库名
*/
public void backup(OutputStream output, String dbname) {
String command = "cmd /c " + mysqlBinPath + "mysqldump -u" + username
+ " -p" + password + " --set-charset=utf8 " + dbname
PrintWriter p = null
BufferedReader reader = null
try {
p = new PrintWriter(new OutputStreamWriter(output, "utf8"))
Process process = Runtime.getRuntime().exec(command)
InputStreamReader inputStreamReader = new InputStreamReader(process
.getInputStream(), "utf8")
reader = new BufferedReader(inputStreamReader)
String line = null
while ((line = reader.readLine()) != null) {
p.println(line)
}
p.flush()
} catch (UnsupportedEncodingException e) {
e.printStackTrace()
} catch (IOException e) {
e.printStackTrace()
} finally {
try {
if (reader != null) {
reader.close()
}
if (p != null) {
p.close()
}
} catch (IOException e) {
e.printStackTrace()
}
}
}
/**
* 备份数据库，如果指定路径的文件不存在会自动生成
*
* @param dest
*备份文件的路径
* @param dbname
*要备份的数据库
*/
public void backup(String dest, String dbname) {
try {
OutputStream out = new FileOutputStream(dest)
backup(out, dbname)
} catch (FileNotFoundException e) {
e.printStackTrace()
}
}
/**
* 恢复数据库
*
* @param input
*输入流
* @param dbname
*数据库名
*/
public void restore(InputStream input, String dbname) {
String command = "cmd /c " + mysqlBinPath + "mysql -u" + username
+ " -p" + password + " " + dbname
try {
Process process = Runtime.getRuntime().exec(command)
OutputStream out = process.getOutputStream()
String line = null
String outStr = null
StringBuffer sb = new StringBuffer("")
BufferedReader br = new BufferedReader(new InputStreamReader(input,
"utf8"))
while ((line = br.readLine()) != null) {
sb.append(line + "/r/n")
}
outStr = sb.toString()
OutputStreamWriter writer = new OutputStreamWriter(out, "utf8")
writer.write(outStr)
writer.flush()
out.close()
br.close()
writer.close()
} catch (UnsupportedEncodingException e) {
e.printStackTrace()
} catch (IOException e) {
e.printStackTrace()
}
}
/**
* 恢复数据库
*
* @param dest
*备份文件的路径
* @param dbname
*数据库名
*/
public void restore(String dest, String dbname) {
try {
InputStream input = new FileInputStream(dest)
restore(input, dbname)
} catch (FileNotFoundException e) {
e.printStackTrace()
}
}
public static void main(String[] args) {
Configuration config = HibernateSessionFactory.getConfiguration()
String binPath = config.getProperty("mysql.binpath")
String userName = config.getProperty("connection.username")
String pwd = config.getProperty("connection.password")
DatabaseBackup bak = new DatabaseBackup(binPath, userName, pwd)
bak.backup("c:/ttt.sql", "ttt")
bak.restore("c:/ttt.sql", "ttt")
}
}
最后的main方法只是一个简单的使用方法的示例代码。
本人所做的项目是使用了hibernate的，而这里需要提供MySQL的bin路径和用户名、密码，而hibernate.cfg.xml中本身就是需要配置数据库的用户名和密码，所以我把MySQL的bin路径也直接配置到了这个文件里面，也不需要创建专门的配置文件，不需要写读取配置文件的接口了。
如果不明白，可以去看hibernate.cfg.xml的说明，里面是可以配置其他的property的
package com.ly520.dbimport java.sql.Connection
import java.sql.DriverManager
import java.sql.PreparedStatement
import java.sql.ResultSet
import java.sql.ResultSetMetaData
import java.sql.SQLException
import java.sql.Statement
import java.util.Enumeration
import java.util.Hashtable
import java.util.List
import java.util.Vector
import javax.naming.Context
import javax.naming.InitialContext
import javax.sql.DataSource
/**
* mysql数据库 *** 作类。
*
* @author Ryoma
*
*/
public class MySqlOperate implements SqlOperate {
private Connection con
private String DBname
private String sql
/**
* Mysql数据库初始化包名
*/
private String INIT_SQL_CTX = "org.gjt.mm.mysql.Driver"
/**
* MYSQL数据库服务参数:服务器IP地址和端口
*/
private String SQL_SERVICE = "jdbc:mysql://127.0.0.1:3306?autoReconnect=true&useUnicode=true&characterEncoding=utf-8&mysqlEncoding=utf8"
/**
* MYSQL数据库参数:系统管理用户
*/
private String SQL_SYSTEM_USER = "root"
/**
* MYSQL数据库参数:系统管理密码
*/
private String SQL_SYSTEM_PASSWORD = "123456"
/**
* 初始化链接方法。此方法不需要再使用use Dbname
*/
public MySqlOperate() {
try {
Context ctx = new InitialContext()
if (ctx == null)
throw new Exception("Boom - No Context")
// java:comp/env/jdbc/imabled_mysql 为配置的连接池的jndi名称。
DataSource ds = (DataSource) ctx
.lookup("java:comp/env/jdbc/imabled_mysql")
con = ds.getConnection()
} catch (Exception ex) {
ex.printStackTrace()
}
}
/**
* 指定库名的连接方法，此方法没有使用连接池，不赞成使用。
*
* @param DBn
*/
public MySqlOperate(String DBn) {
try {
Class.forName(this.INIT_SQL_CTX)
} catch (java.lang.ClassNotFoundException e) {
e.printStackTrace()
}
try {
con = DriverManager.getConnection(this.SQL_SERVICE + "/" + DBn,
this.SQL_SYSTEM_USER, this.SQL_SYSTEM_PASSWORD)
DBname = DBn
} catch (SQLException ex) {
ex.printStackTrace()
}
}
public boolean add(String tablename, Hashtable searchdetail) {
Statement stmt = null
Enumeration key_values = searchdetail.keys()
String key, value
String temp = (String) key_values.nextElement()
key = temp
value = "'" + searchdetail.get(temp) + "'"
while (key_values.hasMoreElements()) {
temp = (String) key_values.nextElement()
key = key + "," + temp
value = value + "," + "'" + searchdetail.get(temp) + "'"
}
try {
useDB()
stmt = con.createStatement()
sql = "insert into " + tablename + " (" + key + ") " + " VALUES ("
+ value + ")"
stmt.executeUpdate(sql)
return true
} catch (SQLException ex) {
System.out.println("执行的出错的sql语句:" + sql)
ex.printStackTrace()
return false
} finally {
closeStmt(stmt)
}
}
public boolean batchImport(String tablename, List list, String[] fields)
throws SQLException {
PreparedStatement ps = null
String key, value
key = toString(fields)
String[] values = new String[fields.length]
for (int i = 0i <values.lengthi++) {
values[i] = "?"
}
value = toString(values)
try {
useDB()
con.setAutoCommit(false)
System.out.println("insert into " + tablename + " (" + key + ") "
+ " VALUES (" + value + ")")
ps = con.prepareStatement("insert into " + tablename + " (" + key
+ ") " + " VALUES (" + value + ")")
String[] tmpData = null
for (int i = 0i <list.size()i++) {
tmpData = (String[]) list.get(i)
for (int j = 0j <fields.length &&j <tmpData.lengthj++) {
ps.setString(j + 1, tmpData[j])
}
ps.addBatch()// 添加执行的语句。
}
int[] count = ps.executeBatch()// 批量执行
con.commit()
return true
} catch (SQLException ex) {
throw ex
} finally {
try {
if (ps != null) {
ps.clearParameters()
ps.close()
ps = null
}
} catch (SQLException e) {
throw e
}
}
}
public boolean delete(String tablename, String filter) {
Statement stmt = null
String value
try {
useDB()
stmt = con.createStatement()
sql = "delete from " + tablename + " where " + filter
stmt.executeUpdate(sql)
return true
} catch (SQLException e) {
System.out.println("执行的出错的sql语句:" + sql)
e.printStackTrace()
return false
} finally {
closeStmt(stmt)
}
}
public Hashtable list(String sql) {
Hashtable[] infoList = search(sql)
if (infoList == null || infoList.length <1)
return null
return infoList[0]
}
public Hashtable list(String tablename, String id) {
String sql = "select * from " + tablename + " where id ='" + id + "'"
return list(sql)
}
public boolean modify(String tablename, Hashtable setdetail, String filter) {
Enumeration key_values = setdetail.keys()
Statement stmt = null
String value
String temp = (String) key_values.nextElement()
value = temp + "='" + setdetail.get(temp) + "'"
while (key_values.hasMoreElements()) {
temp = (String) key_values.nextElement()
value = value + "," + temp + "='" + setdetail.get(temp) + "'"
}
try {
useDB()
stmt = con.createStatement()
sql = "update " + tablename + " set " + value + " where " + filter
int tag = stmt.executeUpdate(sql)
if (tag == 0)
return false
else
return true
} catch (SQLException e) {
System.out.println("执行的出错的sql语句:" + sql)
e.printStackTrace()
return false
} finally {
closeStmt(stmt)
}
}
public Hashtable[] search(String sql) {
ResultSet rs
Statement stmt = null
try {
useDB()
stmt = con.createStatement()
rs = stmt.executeQuery(sql)
return toHashtableArray(rs)
} catch (SQLException ex) {
System.out.println("执行的出错的sql语句:" + sql)
ex.printStackTrace()
return null
} finally {
closeStmt(stmt)
}
}
public Hashtable[] search(String tablename, String[] fieldname,
String filter) {
return search(tablename, fieldname, filter, "")
}
public Hashtable[] search(String tablename, String[] fieldname,
String filter, String ordergroup) {
ResultSet rs
Statement stmt = null
String colname = fieldname[0]
for (int i = 1i <fieldname.lengthi++) {
colname += "," + fieldname[i]
}
String queryString = "select " + colname + " from " + tablename
if (!filter.equals("")) {
queryString = queryString + " where " + filter
}
if (!ordergroup.equals("")) {
queryString = queryString + " " + ordergroup
}
return search(sql)
}
/**
* @return the con
*/
public Connection getCon() {
return con
}
public void close() {
try {
if (con != null)
con.close()
} catch (SQLException e) {
e.printStackTrace()
}
}
/**
* 返回使用的数据库
*
* @return the dBname
*/
public String getDBname() {
return DBname
}
/**
* 设置使用的数据库
*
* @param bname
*the dBname to set
*/
public void setDBname(String bname) {
DBname = bname
}
/**
* 返回执行的sql语句
*
* @return the sql
*/
public String getSql() {
return sql
}
/**
* 本方法是为了再没有使用连接池的情况下，首先选择使用的数据库。
*/
private void useDB() {
if (DBname != null &&DBname.equals("")) {
String query = "use " + DBname
Statement stmt = null
try {
stmt = con.createStatement()
stmt.execute(query)
} catch (SQLException e) {
e.printStackTrace()
} finally {
if (stmt != null) {
try {
stmt.close()
} catch (SQLException e) {
e.printStackTrace()
}
}
}
}
}
/**
* 关闭Statement
*
* @param stmt
*/
private void closeStmt(Statement stmt) {
if (stmt != null) {
try {
stmt.close()
} catch (SQLException e) {
e.printStackTrace()
}
}
}
/**
* @param ss
* @return
*/
private String toString(String[] ss) {
String s = ""
for (int i = 0i <ss.lengthi++) {
s += ss[i] + ","
}
if (s.endsWith(","))
s = s.substring(0, s.length() - 1)
return s
}
/**
* 把ResultSet转换成Hashtable数组 java.util.Arrays.asList 可以把数组转换成List
*
* @param rs
* @return
*/
private Hashtable[] toHashtableArray(ResultSet rs) {
Vector searchresult = new Vector()
try {
ResultSetMetaData rsmd = rs.getMetaData()
int column = rsmd.getColumnCount()
while (rs.next()) {
Hashtable onerow = new Hashtable()
for (int i = 1i <= columni++) {
try {
String columnName = rsmd.getColumnName(i)
String columnValue = rs.getString(columnName)
onerow.put(columnName, columnValue)
} catch (Exception e) {
}
}
searchresult.add(onerow)
}
Hashtable[] searchset = new Hashtable[searchresult.size()]
searchresult.toArray(searchset)
return searchset
} catch (SQLException e) {
e.printStackTrace()
return null
}
}
}
备份MySQL数据库的方法：import java.io.File
import java.io.IOException
/**
* MySQL数据库备份
*
* @author GaoHuanjie
*/
public class MySQLDatabaseBackup {
/**
* Java代码实现MySQL数据库导出
*
* @author GaoHuanjie
* @param hostIP MySQL数据库所在服务器地址IP
* @param userName 进入数据库所需要的用户名
* @param password 进入数据库所需要的密码
* @param savePath 数据库导出文件保存路径
* @param fileName 数据库导出文件文件名
* @param databaseName 要导出的数据库名
* @return 返回true表示导出成功，否则返回false。
*/
public static boolean exportDatabaseTool(String hostIP, String userName, String password, String savePath, String fileName, String databaseName) {
File saveFile = new File(savePath)
if (!saveFile.exists()) {// 如果目录不存在
saveFile.mkdirs()// 创建文件夹
}
if (!savePath.endsWith(File.separator)) {
savePath = savePath + File.separator
}
StringBuilder stringBuilder = new StringBuilder()
stringBuilder.append("mysqldump").append(" --opt").append(" -h").append(hostIP)
stringBuilder.append(" --user=").append(userName) .append(" --password=").append(password).append(" --lock-all-tables=true")
stringBuilder.append(" --result-file=").append(savePath + fileName).append(" --default-character-set=utf8 ").append(databaseName)
try {
Process process = Runtime.getRuntime().exec(stringBuilder.toString())
if (process.waitFor() == 0) {// 0 表示线程正常终止。
return true
}
} catch (IOException e) {
e.printStackTrace()
} catch (InterruptedException e) {
e.printStackTrace()
}
return false
}
public static void main(String[] args) throws InterruptedException {
if (exportDatabaseTool("172.16.0.127", "root", "123456", "D:/backupDatabase", "2014-10-14.sql", "test")) {
System.out.println("数据库备份成功！！！")
} else {
System.out.println("数据库备份失败！！！")
}
}
}
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）