导入导出MYSQL数据库到SQL文件

zhouer

自己工作中要用这个功能，就自己写了一个。 自己也测试了，没有发现问题，现在发上来，看看别人有没有人用得着的。
导出测试方法：
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<Object> 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, 大家都知道通常会用到一些配置文件，还有一些第三方连接池，提供出来有点乱。这个写起来应该不是什么难事，就自己写吧。