程序导出mysql数据库文件_导入导出MYSQL数据库到SQL文件

public static void main(String[] args) throws SQLException, IOException {

Export export = new ExportMySQL();

export.exportData("c:/sql.sql", "userrole;method;policy;audit", ConnectionManager.getConnection(), true);

}

exportData方法如下：

public void exportData(String fileName, String tables, Connection conn, boolean withDeleteSQL) throws SQLException, IOException;

参数说明:

fileName: 导出到哪个文件；

tables: 要导出哪些数据表，多个数据表名以分号(;)分隔；

conn: 数据库连接；

withDeleteSQL: true,表示要生成delete所有数据的SQL, 如果为false, 则不生成。

导入测试方法：

public static void main(String[] args) throws SQLException, IOException {

Import import_ = new ImportMySQL();

import_.importFile("c:/sql.sql", ConnectionManager.getConnection());

}

importFile方法如下：

public void importFile(String fileName, Connection conn) throws IOException;

参数说明:

fileName: 导入的文件；

conn: 数据库连接；

代码如下:

import java.io.IOException;

import java.sql.Connection;

import java.sql.SQLException;

public interface Export {

public void exportData(String fileName, String tables, Connection conn, boolean withDeleteSQL) throws SQLException, IOException;

}

import java.io.IOException;

import java.sql.Connection;

public interface Import {

public void importFile(String fileName, Connection conn) throws IOException;

}

import java.io.FileWriter;

import java.io.IOException;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.sql.Statement;

import java.sql.Types;

import java.util.Formatter;

import java.util.StringTokenizer;

public class ExportMySQL implements Export {

private Connection conn;

private final String SELECT_TABLE = "SELECT * FROM %s;";

private final String DELETE_TABLE = "DELETE FROM %s;\n";

private final String INSERT_TABLE = "INSERT INTO `%s`(%s) VALUES\n%s;\n";

private final String COUNT_TABLE = "SELECT COUNT(*) as totalcount FROM %s;";

public void exportData(String fileName, String tables, Connection conn, boolean withDeleteSQL) throws SQLException, IOException {

checkNull(fileName, "fileName");

checkNull(tables, "tables");

checkString(fileName, "fileName");

checkString(tables, "tables");

checkNull(conn, "conn");

this.conn = conn;

try {

StringBuffer allsql = new StringBuffer();

StringTokenizer s = new StringTokenizer(tables, ";");

while (s.hasMoreTokens()) {

String table = s.nextToken();

Formatter formatter = new Formatter();

formatter.format(COUNT_TABLE, table);

ResultSet rs = conn.createStatement().executeQuery(formatter.toString());

int count = 0;

if (rs.next()) {

count = rs.getInt(1);

}

if (count > 0) {

if (withDeleteSQL) {

formatter = new Formatter();

formatter.format(DELETE_TABLE, table);

allsql.append(formatter.toString());

}

allsql.append(generateInsertSQL(table));

}

}

if (!isEmpty(allsql.toString().trim())) {

FileWriter fw = null;

try {

fw = new FileWriter(fileName);

fw.write(allsql.toString());

} finally {

if (null != fw)

fw.close();

}

}

} finally {

DBUtil.closeConnection(conn);

}

}

private String generateInsertSQL(String table) {

Formatter formatter = new Formatter();

formatter.format(SELECT_TABLE, table);

ResultSet rs = null;

Statement stmt = null;

try {

stmt = conn.createStatement();

rs = stmt.executeQuery(formatter.toString());

ResultSetMetaData rsmd = rs.getMetaData();

int cols = rsmd.getColumnCount();

if (0 == cols) {

return "";

}

String fields = getFields(rsmd);

String allvalue = getValues(rs);

formatter = new Formatter();

formatter.format(INSERT_TABLE, table, fields, allvalue);

String result = formatter.toString();

return result;

} catch (SQLException ex) {

ex.printStackTrace(System.out);

return "";

} finally {

DBUtil.closeResultSet(rs);

DBUtil.closeStatement(stmt);

}

}

private String getValues(ResultSet rs) {

checkNull(rs, "rs");

try {

ResultSetMetaData rsmd = rs.getMetaData();

int cols = rsmd.getColumnCount();

if (0 == cols) {

return "";

}

StringBuffer values = new StringBuffer();

boolean isFirst = true;

while (rs.next()) {

StringBuffer value = new StringBuffer();

for (int i = 1; i <= cols; i++) {

if (null != rs.getString(i))

if (rsmd.getColumnType(i) == Types.VARCHAR || rsmd.getColumnType(i) == Types.DATE || rsmd.getColumnType(i) == Types.TIME

|| rsmd.getColumnType(i) == Types.TIMESTAMP || rsmd.getColumnType(i) == Types.CHAR

|| rsmd.getColumnType(i) == Types.LONGVARCHAR || rsmd.getColumnType(i) == Types.NCHAR

|| rsmd.getColumnType(i) == Types.NVARCHAR || rsmd.getColumnType(i) == Types.LONGNVARCHAR

|| rsmd.getColumnType(i) == Types.SQLXML)

value.append("'" + rs.getString(i) + "'");

else

value.append(rs.getString(i));

else

value.append("NULL");

if (i < cols) {

value.append(",");

}

}

if (isFirst) {

values.append("(" + value + ")");

isFirst = false;

} else

values.append(",\n(" + value + ")");

}

return values.toString();

} catch (SQLException ex) {

ex.printStackTrace(System.out);

return "";

}

}

private String getFields(ResultSetMetaData rsmd) {

try {

int cols = rsmd.getColumnCount();

if (0 == cols) {

return "";

}

StringBuffer fields = new StringBuffer();

for (int i = 1; i <= cols; i++) {

if (i == cols)

fields.append(rsmd.getColumnName(i));

else

fields.append(rsmd.getColumnName(i) + ",");

}

return fields.toString();

} catch (SQLException ex) {

ex.printStackTrace(System.out);

return "";

}

}

public static void main(String[] args) throws SQLException, IOException {

Export export = new ExportMySQL();

export.exportData("c:/sql.sql", "userrole;method;policy;audit", ConnectionManager.getConnection(), true);

}

}

import java.io.BufferedReader;

import java.io.FileReader;

import java.io.IOException;

import java.sql.Connection;

import java.sql.SQLException;

public class ImportMySQL implements Import {

public void importFile(String fileName, Connection conn) throws IOException {

checkNull(fileName, "fileName");

checkNull(conn, "conn");

StringBuffer buffer = new StringBuffer();

BufferedReader br = null;

try {

br = new BufferedReader(new FileReader(fileName));

String next = "";

while (null != next ) {

next = br.readLine();

buffer.append(next);

if (null != next && next.endsWith(";")) {// just means a whole sql

DBUtil.executeSQL(buffer.toString(), null);

buffer = new StringBuffer();

}

}

} finally {

if (null != br)

br.close();

DBUtil.closeConnection(conn);

}

}

public static void main(String[] args) throws SQLException, IOException {

Import import_ = new ImportMySQL();

import_.importFile("c:/sql.sql", ConnectionManager.getConnection());

}

}

代码比较长，也不想加附件了。

  有什么问题，大家说一下。谢谢。

其他的代码：

import java.security.InvalidParameterException;

import java.text.MessageFormat;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

import java.util.ResourceBundle;

import java.util.UUID;

public class StringUtil {

static ResourceBundle resourceBundle = DaoResourceBundle.getResourceBundle();

public static String changeNull(String n) {

if (null == n) {

return "";

}

return n.trim();

}

public static void checkString(String string, String name) {

if (StringUtil.isEmpty(string)) {

String pattern = resourceBundle.getString("dao.exception.invalidparameterexception");

String message = MessageFormat.format(pattern, new String(name));

throw new InvalidParameterException(message);

}

}

public static void checkNull(Object object, String name) {

if (null == object) {

String pattern = resourceBundle.getString("dao.exception.nullpointerexception");//需要一个properties文件，也可以直接hardcode

String message = MessageFormat.format(pattern, new String(name));

throw new NullPointerException(message);

}

}

}

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.List;

import org.apache.commons.logging.Log;

import org.apache.commons.logging.LogFactory;

public final class DBUtil {

static Log logger = LogFactory.getLog(DBUtil.class);

public static void executeSQL(String sql, final List paramsList) {

Connection conn = ConnectionManager.getConnection();

PreparedStatement stmt = null;

try {

stmt = conn.prepareStatement(sql);

//setParameters(paramsList, stmt);//在这个功能里应该用不到，所以就尽量少的提供代码。

stmt.executeUpdate();

} catch (SQLException ex) {

logger.error("ERROR_CAUSE" + ex.getCause() + " MSG: " + ex.getMessage());

//throw new DBException(ex);

} finally {

closeStatement(stmt);

}

}

public static void closeStatement(final Statement stmt) {

if (stmt != null) {

try {

stmt.close();

} catch (SQLException ex) {

logger.error("ERROR_CAUSE" + ex.getCause() + " MSG: " + ex.getMessage());

//throw new DBException(ex);

}

}

}

public static void closeResultSet(final ResultSet rs) {

if (rs != null) {

try {

rs.close();

} catch (SQLException ex) {

logger.error("ERROR_CAUSE" + ex.getCause() + " MSG: " + ex.getMessage());

//throw new DBException(ex);

}

}

}

public static void closeConnection(final Connection conn) {

if (conn != null) {

try {

conn.close();

} catch (SQLException ex) {

logger.error("ERROR_CAUSE" + ex.getCause() + " MSG: " + ex.getMessage());

//throw new DBException(ex);

}

}

}

}

其中DBUtil里用到CoonectionManager, 大家都知道通常会用到一些配置文件，还有一些第三方连接池，提供出来有点乱。这个写起来应该不是什么难事，就自己写吧。

分享到：

2009-07-23 15:01

浏览 2701

评论

5 楼

every

2009-12-01

我还以为是可以到处表结构的呢……

4 楼

grandboy

2009-12-01

every 写道

checkNull(fileName, "fileName");

checkNull(tables, "tables");

checkString(fileName, "fileName");

checkString(tables, "tables");

checkNull(conn, "conn");

checkNull(rs, "rs");

这两个函数

以及数据库连接池

DBUtil

我提供了剩下的类，但是你还得自己写一个ConnectionMananger, 这个很容易，一般应用程序里都有，直接考一个就行了。希望你顺利。

3 楼

every

2009-11-30

checkNull(fileName, "fileName");

checkNull(tables, "tables");

checkString(fileName, "fileName");

checkString(tables, "tables");

checkNull(conn, "conn");

checkNull(rs, "rs");

这两个函数

以及数据库连接池

DBUtil

2 楼

grandboy

2009-11-26

every 写道

看到这篇日志，感觉很受用，代码中应用了其它工具类，能否也共享一下，谢谢

这就是完整的代码。 没有用到其他的工具类啊，如果你编译不过去的话，可以把详细情况说一下。

1 楼

every

2009-11-26

看到这篇日志，感觉很受用，代码中应用了其它工具类，能否也共享一下，谢谢