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