数据库导入导出工具类

只在MySQL中测试过，可将数据库中的数据导出到指定文件，也可从包含insert语句的文件中导入数据到数据库。

 

去掉导出的方法exportDatas()中的注释可将表结构等也导出。

 

package cn.com.kingkit.rmdbs.comm.util; import java.io.BufferedReader; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStreamWriter; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import org.apache.commons.lang.StringUtils; /** * 数据库导入导出工具 * * @author 黄庆勇 * @version 1.0 * @date 2009-12-10 * */ public class MysqlTool { /** * 将数据导出到文件 * * @param backupFile * 导出文件的绝对路径 * @param driver * 连接数据库驱动名称，如："com.mysql.jdbc.Driver" * @param url * 连接数据库地址，如："jdbc:mysql://localhost:3306/rmdbs" * @param username * 登录数据库的用户名 * @param password * 登录数据库的密码 */ public static void exportDatas(String backupFile, String driver, String url, String username, String password) { Connection conn = null; ResultSet rs = null; Statement stmt = null; OutputStreamWriter osw = null; try { Class.forName(driver); conn = DriverManager.getConnection(url, username, password); // Get tables DatabaseMetaData dmd = conn.getMetaData(); rs = dmd.getTables(null, null, "%", null); ArrayList<String> tables = new ArrayList<String>(); while (rs.next()) { tables.add(rs.getString(3)); } rs.close(); String resultString = ""; // resultString += "/*/n"; // resultString += "MySQL Data Transfer/n"; // resultString += "url: " + url + "/n"; // resultString += "Date: " + new Date() + "/n"; // resultString += "*//n/n"; resultString += "SET FOREIGN_KEY_CHECKS=0;/n/n"; // ResultSetMetaData rsmd = null; stmt = conn.createStatement(); // // for (String table : tables) { // resultString += "-- ----------------------------/n"; // resultString += "-- Table structure for " + table + "/n"; // resultString += "-- ----------------------------/n"; // resultString += "DROP TABLE " + table + ";/n"; // rs = stmt.executeQuery("SHOW CREATE TABLE " + table); // rsmd = rs.getMetaData(); // while (rs.next()) { // // JDBC is 1-based, Java is not !? // resultString += rs.getString(2) + ";/n/n"; // } // rs.close(); // } // // resultString += "-- ----------------------------/n"; // resultString += "-- Records/n"; // resultString += "-- ----------------------------/n"; for (String table : tables) { // System.out.println("Dumping data for table " + table // + "..."); resultString += "DELETE FROM " + table + ";/n"; rs = stmt.executeQuery("SELECT * FROM " + table); rsmd = rs.getMetaData(); while (rs.next()) { resultString += "INSERT INTO " + table + " VALUES("; // JDBC is 1-based, Java is not ! for (int col = 1; col <= rsmd.getColumnCount(); col++) { resultString += "'"; if (rs.getString(col) == null) resultString += ""; else resultString += rs.getString(col).replaceAll( "[//r]?[//n]", "rn"); if (col == rsmd.getColumnCount()) resultString += "'"; else resultString += "', "; } resultString += ");/n"; } resultString += "/n"; rs.close(); } stmt.close(); osw = new OutputStreamWriter(new FileOutputStream(backupFile), "UTF-8"); osw.append(resultString); osw.flush(); // System.out.println("backup is complete!!!"); } catch (Exception e) { e.printStackTrace(); } finally { try { osw.close(); } catch (IOException e1) { e1.printStackTrace(); } try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } /** * 导入数据到数据库 * <p> * <b>注意：该方法是针对本工具类中MysqlTool.exportDatas()方法导出的文件进行恢复数据而开发，不保证通用与正确性！</b> * </p> * * @param input * 数据所在文件的输入流 * @param driver * 连接数据库驱动名称，如："com.mysql.jdbc.Driver" * @param url * 连接数据库地址，如："jdbc:mysql://localhost:3306/rmdbs" * @param username * 登录数据库的用户名 * @param password * 登录数据库的密码 */ public static void importDatas(BufferedReader input, String driver, String url, String username, String password) { Connection conn = null; Statement stmt = null; try { Class.forName(driver); conn = DriverManager.getConnection(url, username, password); stmt = conn.createStatement(); String sql = ""; do { sql = input.readLine(); if (StringUtils.isBlank(sql)) { sql = input.readLine(); } if (StringUtils.isBlank(sql)) { sql = input.readLine(); } if (StringUtils.isBlank(sql)) { sql = input.readLine(); } else { System.out.println(sql); stmt.execute(sql.trim()); } } while (!StringUtils.isBlank(sql)); stmt.close(); } catch (Exception e) { e.printStackTrace(); } finally { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } /** * @param args */ public static void main(String[] args) { String backupFile = "d://rmdb.sql"; String driver = "com.mysql.jdbc.Driver"; String url = "jdbc:mysql://localhost:3306/rmdbs"; String username = "root"; String password = "kingkit"; MysqlTool.exportDatas(backupFile, driver, url, username, password); } }