如何用java实现mysql数据库的导入导出

如何用java实现mysql数据库的导入导出

要使用CMD命令窗口首先的将MYSQL环境变量设置到操作系统中,这是第一步,
导出MYSQL数据的命令如下:
mysqldump -u root [-p password] database [table] > x.sql
注:root的登录数据库的用户名；database为使用到的数据库；table为表名；x.sql为导出的sql脚本
这样导出为一个SQL脚本文件,里面包括所有的建表语句与数据插入语句,
而数据导入也差不多,给传入一个SQL脚本,语句如下:
source d:/mysql.sql;

mysql的一些前台工具是有备份恢复功能的，可是如何在我们的应用程序中实现这一功能呢？本文提供了示例代码来说明如何使用java代码实现mysql数据库的备份恢复。 本次实现是使用了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; public class databasebackup { private string mysqlbinpath; private string username; 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; } 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(); } } } public void backup(string dest, string dbname) { try { outputstream out = new fileoutputstream(dest); backup(out, dbname); } catch (filenotfoundexception e) { e.printstacktrace(); } } 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(); } } 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的