mysql通过jdbc的Statement做导出数据库的sql

package net.eicp.roomally.util.tool;

import java.io.BufferedWriter;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;

import net.eicp.roomally.util.JdbcManager;

/**
 * @author xxf
 */
public class MySqlUtils {
	public static final String enter = "\r\n";//windows txt文档的换行

	public static void main(String[] args) throws SQLException,
			FileNotFoundException, UnsupportedEncodingException, IOException {
		MySqlUtils mySqlUtils = new MySqlUtils();
		System.out.println(mySqlUtils.exportSqlString(new String[] {
				"ally_user", "keywords", "clauses" }));//要导出的表名,可多个
	}

	public void export(String[] args) throws SQLException,
			FileNotFoundException, UnsupportedEncodingException, IOException {
		String exportPath = MySqlUtils.class.getResource("/").getPath()//得到classpath路径
				+ getDateString("yyyy-MM-dd_hh-mm-ss") + ".sql";
		PATH = exportPath;
		String sql = exportSqlString(args);

		// FileWriter out = new FileWriter(exportPath);
		// 如果要写入全角字符，应该选择合适的编码再定义out
		FileOutputStream fos = new FileOutputStream(exportPath);
		OutputStreamWriter osw = new OutputStreamWriter(fos, "utf-8");
		BufferedWriter out = new BufferedWriter(osw);
		out.write(sql);//进行文件保存
		// out.write(new String(sql.getBytes(),"utf-8"));
		out.flush();
		out.close();
		this.sql = sql;
	}

	private String sql;

	public String getSql() {
		return sql;
	}

	public static String PATH;

	public static String getDateString(String format) {
		String dateStr = new SimpleDateFormat(format).format(new Date());
		return dateStr;
	}

	public String exportSqlString(String[] tableNames) throws SQLException {
		StringBuffer tablesql = new StringBuffer();
		for (int i = 0; i < tableNames.length; i++) {
			tablesql.append(exportSqlString(tableNames[i]) + enter + enter);
		}
		return tablesql.toString();
	}

	public String exportSqlString(String tableName) throws SQLException {
		String tablesql = "";
		String datasql = "";
		Connection con = null;
		Statement stat = null;
		try {
			con = JdbcManager.getConnection();//得到数据库连接
			stat = con.createStatement();//创建Statement对象
			ResultSet rs = stat.executeQuery("select * from " + tableName);//执行查询语句
			tablesql = getCreateTableSql(rs, tableName);//得到创建表的sql语句
			datasql = getTableDataSql(rs, tableName);//得到插入数据的sql语句
		} catch (SQLException e) {
			throw e;
		} finally {
			JdbcManager.free(stat, con);
		}
		return tablesql + enter + datasql;
	}

	public String[] getColumns(ResultSet rs) throws SQLException {//得到字段的名字，存放到一个数组里
		ResultSetMetaData rsmd = rs.getMetaData();
		int ccount = rsmd.getColumnCount();
		String[] args = new String[ccount];
		for (int i = 1; i <= ccount; i++) {
			String colName = rsmd.getColumnName(i);
			args[i - 1] = colName;
		}
		return args;
	}

	public String getColumnsString(String[] args) {//拼接所有字段名
		StringBuffer buffer = new StringBuffer();
		for (int i = 0; i < args.length; i++) {
			buffer.append("`" + args[i] + "`,");
		}
		return buffer.deleteCharAt(buffer.length() - 1).toString();
	}

	public String getCreateTableSql(ResultSet rs, String tableName)
			throws SQLException {
		ResultSetMetaData rsmd = rs.getMetaData();//主要的通过这个方法
		int ccount = rsmd.getColumnCount();
		StringBuffer columnBuffer = new StringBuffer("DROP TABLE IF EXISTS `"
				+ tableName + "`;" + enter);//为了方便，好多东西是写死的
		columnBuffer.append("CREATE TABLE `" + tableName + "` (" + enter);
		for (int i = 1; i <= ccount; i++) {
			int size = rsmd.getColumnDisplaySize(i);
			String colTypeName = rsmd.getColumnTypeName(i);
			// String colClassName = rsmd.getColumnClassName(i);
			String colName = rsmd.getColumnName(i);
			columnBuffer.append("`" + colName + "` ");
			columnBuffer.append(colTypeName);//在这儿我只做了一些简单的判断
			if (!"double".equalsIgnoreCase(colTypeName)
					&& !"date".equalsIgnoreCase(colTypeName)) {
				columnBuffer.append("(" + size + ") ");
			}
			columnBuffer.append(" DEFAULT NULL," + enter);
		}
		columnBuffer.delete(columnBuffer.length() - 3,
				columnBuffer.length() - 2);
		columnBuffer.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8;");
		return columnBuffer.toString();
	}

	public String getTableDataSql(ResultSet rs, String tableName)
			throws SQLException {
		String[] columns = getColumns(rs);
		StringBuffer columnBuffer = new StringBuffer();
		columnBuffer.append("INSERT INTO `" + tableName + "` ("
				+ getColumnsString(columns) + ") VALUES");
		while (rs.next()) {
			columnBuffer.append("(");
			for (int i = 0; i < columns.length; i++) {
				Object obj = rs.getObject(columns[i]);
				String typeName = "";
				if (obj == null) {
					obj = "";
				}
				if (obj.getClass() != null) {
					typeName = obj.getClass().getName();
				}//在这儿我只做了一些简单的判断
				if ("java.lang.String".equals(typeName)
						|| "java.sql.Date".equals(typeName)) {
					columnBuffer.append("'" + obj + "',");
				} else {
					columnBuffer.append(obj + ",");
				}
			}
			columnBuffer.deleteCharAt(columnBuffer.length() - 1);
			columnBuffer.append("),");
		}
		if (columnBuffer.toString().endsWith("VALUES"))
			return "";
		columnBuffer.deleteCharAt(columnBuffer.length() - 1).append(";");
		return columnBuffer.toString();
	}
}
 

package net.eicp.roomally.util;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import net.eicp.roomally.exception.RoomAllyJdbcException;
import net.eicp.roomally.util.tool.CryptUtil;

public class JdbcManager {

	private JdbcManager() {
	}

	private static String url = null;
	private static String driver = null;
	private static String username = null;
	private static String password = null;
	static {
		try {
			InputStream is = JdbcManager.class.getClassLoader()
					.getResourceAsStream("jdbc.properties");
			Properties properties = new Properties();
			properties.load(is);
			url = properties.getProperty("jdbc.url");
			driver = properties.getProperty("jdbc.driver");
			username = properties.getProperty("jdbc.username");
			password = properties.getProperty("jdbc.password");
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
		
		} catch (IOException e) {
		
		}
	}

	public static Connection getConnection() {
		try {
			return DriverManager.getConnection(url, username, password);
		} catch (SQLException e) {
		
		}
	}

	public static void free(Statement st, Connection conn) {
		try {
			if (st != null)
				st.close();
			if (conn != null)
				conn.close();
		} catch (SQLException e) {
			
		}
	}
}

 

在classpath下有jdbc.properties内容如下：

jdbc.url=jdbc:mysql://localhost:3310/roomally?useUnicode=true&characterEncoding=UTF-8
jdbc.driver=com.mysql.jdbc.Driver
jdbc.username=roomally
jdbc.password=039a44b295a8cfb62506b7fe2c23801a