package com;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DbHelper {
private Connection conn = null;
private PreparedStatement ps = null;
private ResultSet rs = null;

// 建立连接,00000
public Connection getConnection(String DRIVER, String url, String username, String password) {
	try {
		Class.forName(DRIVER);
		conn = DriverManager.getConnection(url, username, password);
	} catch (Exception e) {
		e.printStackTrace();
	}
	return conn;
}

// 建立一个叫做testdatabase的数据库,00000

// 查询专用方法
public ResultSet executeQuery(String DRIVER, String sql, String url, String username, String password,
		Object... params) {
	this.conn = getConnection(DRIVER, url, username, password);
	try {
		ps = conn.prepareStatement(sql);
		if (params != null) {
			for (int i = 0; i < params.length; i++) {
				ps.setObject(i + 1, params[i]);
			}
		}
		rs = ps.executeQuery();
	} catch (Exception e) {
		e.printStackTrace();
	}
	return rs;
}

// 获得所有表的列表
public List<String> getAllTableName(String DRIVER, String url, String username, String password) {
	DatabaseMetaData metaData;
	List<String> colNamesList = new ArrayList<String>();
	try {
		metaData = getConnection(DRIVER, url, username, password).getMetaData();
		ResultSet tables = metaData.getTables(null, null, "%", new String[] { "TABLE" });
		// 表名称列表
		while (tables.next()) {
			colNamesList.add(tables.getString("TABLE_NAME"));
		}
	} catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	// 获取数据库下面所有表
	closeConnection();
	return colNamesList;
}

// 获得ddl语句
public List<String> Aachievddl(String DRIVER, String url, String username, String password, String table) {
	String tableName = table;
	Connection conn = getConnection(DRIVER, url, username, password);
	String sql = String.format("SHOW CREATE TABLE %s", tableName);// 查询sql
	// String sql = "SHOW CREATE TABLE ?";
	List<String> list = new ArrayList<>();
	PreparedStatement ps = null;
	try {
		ps = conn.prepareStatement(sql);
		// ps.setString(1, tableName);
		ResultSet resultSet = ps.executeQuery();
		while (resultSet.next()) {
			/// 第一个参数获取的是tableName
			list.add(resultSet.getString(2));
		}
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		if (null != ps) {
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (null != conn) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	return list;
}

// 执行ddl语句
public int copyddltable(String DRIVER, String url, String username, String password, String url2, String username2,
		String password2) {
	Statement stsm = null;
	List<String> tablename = getAllTableName(DRIVER, url, username, password);
	for (String table : tablename) {
		List<String> list = Aachievddl(DRIVER, url, username, password, table);
		try {
			Class.forName(DRIVER);
			Connection conns = getConnection(DRIVER, url2, username2, password2);
			stsm = conns.createStatement();
			int result = stsm.executeUpdate(StringUtils.strip(list.toString(), "[]"));
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	return 0;

}

// 执行数据库复制操作
// 查询表的字段信息
public List<String> getTablefields(String DRIVER, String url, String username, String password, String table) {
	List<String> tablelist = new ArrayList<String>();
	try {
		getConnection(DRIVER, url, username, password);
		ps = conn.prepareStatement("select * from " + table + " where 1=2");
		ResultSetMetaData rsd = ps.executeQuery().getMetaData();
		for (int i = 0; i < rsd.getColumnCount(); i++) {
			tablelist.add((rsd.getColumnName(i + 1) + " " + rsd.getColumnTypeName(i + 1) + "("
					+ rsd.getColumnDisplaySize(i + 1) + ")"));
		}
	} catch (SQLException e) {
		e.printStackTrace();
	}
	return tablelist;
}

// 进行字段对比
public int ComparisonField(String DRIVER, String url, String username, String password, String UrlSecond,
		String userSecond, String passSecond, String Tablename) {
	// 查询所有的表名称
	List<String> tableNameList = getTablefields(DRIVER, UrlSecond, userSecond, passSecond, Tablename);
	// 查询表的字段
	List<String> ComparisonList = getTablefields(DRIVER, url, username, password, Tablename);
	// 查询表的字段
	List<String> list = new ArrayList<>(tableNameList);
	// 取出不同的值
	for (int i = 0; i < ComparisonList.size(); i++) {
		// 如果第三个List已经存在，则不添加，如果不存在就添加
		if (!list.contains(ComparisonList.get(i))) {
			// contains是包含的意思，这个if加了！,意思是不包含
			list.add(ComparisonList.get(i));
		}
	}
	// 取出不同的值
	for (int i = 0; i < list.size(); i++) {
		// 将第一，第二个List和第三个比较，如果第一第二个都有的，则从第三个删除
		if (tableNameList.contains(list.get(i)) && ComparisonList.contains(list.get(i))) {
			list.remove(i);
			i--;// 如果删除了一个元素，就继续从这个数组下标开始比较
		}
	}
	// 把值传入字段添加里面
	int num = UpdateComparison(DRIVER, UrlSecond, userSecond, passSecond, list, Tablename);
	// 执行修改字段的方法
	closeConnection();
	return num;
}

// 执行修改字段的方法
public int UpdateComparison(String DRIVER, String UrlSecond, String userSecond, String passSecond,
		List<String> list, String Tablename) {
	getConnection(DRIVER, UrlSecond, userSecond, passSecond);
	for (String nameL : list) {
		String sql = "alter table " + Tablename + " add " + nameL + "";
		System.out.println(sql);
		try {
			conn.createStatement().executeUpdate(sql);

		} catch (Exception e) {
			e.printStackTrace();
		}

	}
	closeConnection();
	return 1;
}

// 执行表数据复制
public int Copytabledata(String DRIVER, String url, String username, String password, String UrlSecond,
		String userSecond, String passSecond) {
	emp es = new emp();
	List<String> list = getAllTableName(DRIVER, url, username, passSecond);
	for (String tablu : list) {
		List<String> emplist = new ArrayList<String>();
		String sql = "select*from " + tablu + "";
		try {
			Connection iteconn = getConnection(DRIVER, url, username, passSecond);
			Statement itestmt = iteconn.createStatement();
			ResultSet iters = itestmt.executeQuery(sql);
			int size = iters.getMetaData().getColumnCount();
			StringBuffer sbf = new StringBuffer();
			sbf.append("insert into " + tablu + " values (");
			String link = "";
			for (int i = 0; i < size; i++) {
				sbf.append(link).append("?");
				link = ",";
			}
			sbf.append(")");
			Connection mysqlconn = getConnection(DRIVER, UrlSecond, userSecond, passSecond);
			PreparedStatement mysqlpstmt = mysqlconn.prepareStatement(sbf.toString());

			// 取出结果集并向MySQL数据库插入数据 ( 使用批处理 )
			// 完成条数
			int count = 0;
			int num = 0;
			// 取消事务(不写入日志)
			mysqlconn.setAutoCommit(false);
			long start = System.currentTimeMillis();
			while (iters.next()) {
				++count;
				for (int i = 1; i <= size; i++) {
					mysqlpstmt.setObject(i, iters.getObject(i));
				}

				// 将预先语句存储起来，这里还没有向数据库插入
				mysqlpstmt.addBatch();
				// 当count 到达 20000条时 向数据库提交
				if (count % 20000 == 0) {
					++num;
					mysqlpstmt.executeBatch();
					System.out.println("第" + num + "次提交,耗时:" + (System.currentTimeMillis() - start) / 1000.0 + "s");
				}
			}
			// 防止有数据未提交
			mysqlpstmt.executeBatch();
			// 提交
			mysqlconn.commit();
			System.out.println("完成 " + count + " 条数据,耗时:" + (System.currentTimeMillis() - start) / 1000.0 + "s");
			// 恢复事务
			// mysqlconn.setAutoCommit(true);

			// 关闭资源

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	closeConnection();
	return 1;
}

// 关闭连接
public void closeConnection() {
	try {
		if (rs != null) {
			rs.close();
		}
		if (ps != null) {
			ps.close();
		}
		if (conn != null) {
			conn.close();
		}
	} catch (SQLException e) {
		e.printStackTrace();
	}
}

public static void main(String[] args) throws Exception {
	DbHelper dbHelper = new DbHelper();
	String DRIVER = "com.mysql.jdbc.Driver";
	// 取值的数据库
	String Url = "";
	String username = "";
	String password = "";
	// 复制到数据库

	String UrlSecond = "jdbc:mysql://localhost:3306/dv_db_gzdev?useUnicode=true&characterEncoding=UTF-8";
	String userSecond = "root";
	String passSecond = "root";
	// 复制数据库表结构
	int num = dbHelper.copyddltable(DRIVER, Url, username, password, UrlSecond, userSecond, passSecond);
	// 字段进行对比
                              //传入需要修改的表
                            String Tablename = "authorize";
	 int nums =dbHelper.ComparisonField(DRIVER, Url, username, password, UrlSecond,
	 userSecond, passSecond, Tablename);
	 System.out.println(num);
	//
	 int emp=dbHelper.Copytabledata(DRIVER,Url, username, password, UrlSecond, userSecond, passSecond);

}

}