package ay.db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.UUID;
import ay.util.SecurityEncryptUtil;

public class DbUtil_mysql {
	
	/**
	 * 用sql转库
	 * @param conn_ppas
	 * @param conn_mysql
	 * @param sql_insert
	 * @param tableName
	 * @param tfc_id
	 * @param ay_id
	 * @param out_field
	 * @param del_ay_id
	 * @param is_leave
	 */
	
	public void insert(Connection conn_ppas,Connection conn_mysql,String tableName,String sql_select,String sql_insert,String sql_del) {
		
		SimpleDateFormat df=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		System.out.println("/*****************************************"+String.format("%-20s", tableName)+"**************************************************************/");
		System.out.println("begin:"+df.format(new Date()));
		
//		sql_select=sql_select+" and rownum<10 ";
		
		System.out.println("\n---------sql_select:  "+sql_select);
						
		int ii=0;

		StringBuffer sb = new StringBuffer();
		String s_name = "", s_type = "";
		try {	
			Statement st_mysql = conn_mysql.createStatement();
			Statement st_ppas = conn_ppas.createStatement();
			ResultSet rs = st_ppas.executeQuery(sql_select);
			if(sql_del!=null)st_mysql.executeUpdate(sql_del);

			ii=0;
			while (rs.next()) {
				ii=ii+1;
				sb = new StringBuffer();				
				sb.append(sql_insert+" values(");
				
				for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
//					s_type = rs.getMetaData().getColumnTypeName(i).toUpperCase();
					s_type = rs.getMetaData().getColumnTypeName(i).toLowerCase();
//					System.out.println(s_type);
//					System.out.println(rs.getObject(i));
					if (rs.getObject(i) == null) {
						sb.append("null,");
					} else {
						String columnNameString = rs.getMetaData().getColumnName(i);
//						System.out.println(columnNameString);
						if (s_type.equals("varchar")) {
							sb.append("'" + ((String)rs.getObject(i)).replaceAll("\'", "’") + "',");
						}
						if (s_type.equals("datetime")) {
							sb.append("'" + rs.getObject(i) + "',");
						}
						if (s_type.equals("numeric")|| s_type.equals("int")) {
							sb.append(rs.getObject(i) + ",");
						}
						if (!s_type.equals("varchar")
								&& !s_type.equals("datetime")
								&& !s_type.equals("numeric")
								&& !s_type.equals("int")
								) {								
								sb.append("null,");
						}
						
					}
				}
//				System.out.println("delete from "+tableName+" where id = '"+sb.substring(sb.indexOf("(", sb.indexOf("values"))+1, sb.indexOf(",", sb.indexOf("values"))).trim()+"'");
//				st_mysql.executeUpdate("delete from "+tableName+" where id = '"+sb.substring(sb.indexOf("(", sb.indexOf("values"))+1, sb.indexOf(",", sb.indexOf("values"))).trim()+"'");
				
//				System.out.println(sb.substring(0, sb.length() - 1) + ")");
//				st_mysql.executeUpdate(sb.substring(0, sb.length() - 1) + ")");
					
				
				st_mysql.addBatch(sb.substring(0, sb.length() - 1) + ")");					
				if(ii%1000==0){
					st_mysql.executeBatch();
					System.out.println(ii);
				}	
					
				
			}			
			
			st_mysql.executeBatch();
			
			rs.close();
			st_ppas.close();

			st_mysql.close();			
			
			System.out.println("end:"+df.format(new Date())+"     "+ii );

		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}
	
	
	public ResultSet query(Connection con, String sql){
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			statement = con.createStatement();
			resultSet = statement.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return resultSet;
	}

	
	public void update(Connection con, String sql){
		Statement statement = null;
		try {
//			System.out.println("\n=====update  sql: "+sql);
			statement = con.createStatement();
			statement.executeUpdate(sql);			
		} catch (SQLException e) {
//			Scanner reader=new Scanner(System.in);
//			System.out.println("输入任意字符继续回车继续");
//			String str = reader.next();
//			if(str.equals("Y")){
//				update(con, sql);
//			}
			e.printStackTrace();
		}
	}
}