导入导出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());
    }

}

代码比较长，也不想加附件了。 :wink: 有什么问题，大家说一下。谢谢。

其他的代码：

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, 大家都知道通常会用到一些配置文件，还有一些第三方连接池，提供出来有点乱。这个写起来应该不是什么难事，就自己写吧。