package DataBase;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import Method.About_tb_track;
import Method.BaoWenShow;
import PbuliClass.GetTxt;
import PbuliClass.ShowMessage;
import zhuce.ZhuCeMan;
@SuppressWarnings("rawtypes")
public class DatabaseManagement {//Êý¾Ý¿â¹ÜÀíÀà

	protected static String adressandport =GetTxt.getTxt("databaseadress","system")+":"+GetTxt.getTxt("databaseport","system");	
	protected static String dbClassName = "com.mysql.jdbc.Driver";// MySQLÊý¾Ý¿âÇý¶¯ÀàµÄÃû³Æ
	protected static String dbUrl = "jdbc:mysql://"+adressandport+"/hxzkflow";// ·ÃÎÊMySQLÊý¾Ý¿âµÄ·¾¶
	protected static String dbUser;// ·ÃÎÊMySQLÊý¾Ý¿âµÄÓû§Ãû
	protected static String dbPwd;// ·ÃÎÊMySQLÊý¾Ý¿âµÄÃÜÂë
	protected static String dbName = "hxzkflow";// ·ÃÎÊMySQLÊý¾Ý¿âÖеÄʵÀýhxzk)	
	protected static String second = null;//
	public static Connection conn = null;// MySQLÊý¾Ý¿âµÄÁ¬½Ó¶ÔÏó
	public static PreparedStatement sql;//ÉêÃ÷Ô¤´¦Àí¶ÔÏó


	static {// ¾²Ì¬³õʼ»¯Àà
		try {

			dbUser=GetTxt.getTxt("dbName","system");		
			dbPwd=GetTxt.getTxt("dbPass","system");				
			if (conn == null) {
				Class.forName(dbClassName).newInstance();// ʵÀý»¯MySQLÊý¾Ý¿âµÄÇý¶¯
				try {conn = DriverManager.getConnection(dbUrl+"?useUnicode=true&characterEncoding=utf-8&useSSL=false",dbUser,dbPwd);// Á¬½ÓMySQLÊý¾Ý¿â
				}catch(SQLException e) {
					ShowMessage.zidingyi("Êý¾Ý¿âÁ¬½Ó³ö´í,Çë¼ì²éÊý¾Ý¿âÃÜÂë");
				}

			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			ShowMessage.zidingyi("Ç뽫MySQLµÄJDBCÇý¶¯°ü¸´ÖƵ½libÎļþ¼ÐÖС£");// ²¶»ñÒì³£ºó£¬µ¯³öÌáʾ¿ò
			System.exit(-1);// ϵͳֹͣÔËÐÐ
		} catch (Exception e) {
			e.printStackTrace();
		}
	}



	/**»ñÊý¾Ý¿âÖÐij¸ö±íµÄËùÓеÄÊý¾Ý
	 * @param Êý¾Ý¿âÖÐij¸ö±íµÄÃû³Æ*/

	public static List get_alldate_indatabase(String database_name) {
		List list = findForList("select * from "+database_name);
		return list;

	}

	/**ËÑË÷ij¸ö×Ö¶Î*/
	public static List get_moudata_indatabase(String sql) {
		List list = findForList(sql);
		return list;

	}

	/**Êý¾Ý°´ÕÕÉýÐò»òÕß½µÐòÅÅÁÐ
	 * @param DESC½µÐòÅÅÁÐ
	 * @param ASCÉýÐòÅÅÁÐ
	 * @param tablenameÊý¾Ý±íµÄÃû³Æ*/
	public static List table_AandD(String tablename,String sj) {
		List list = findForList("select * from "+tablename+" order by id "+sj);
		return list;
	}

	/**Êý¾Ý°´ÕÕÉýÐò»òÕß½µÐòÅÅÁÐ
	 * @param DESC½µÐòÅÅÁÐ
	 * @param ASCÉýÐòÅÅÁÐ
	 * @param tablenameÊý¾Ý±íµÄÃû³Æ*/
	public static List table_base(String tablename,String sj,String ziduan) {
		List list = findForList("select * from "+tablename+" order by "+ziduan+" "+sj);
		return list;
	}


	/**ÐÅÏ¢²éѯ*/
	public static List serch(String serch) {
		List list = findForList(serch);
		return list;		
	}


	// Ö´ÐÐÖ¸¶¨²éѯ
	public static ResultSet query(String QueryStr) {
		ResultSet set = findForResultSet(QueryStr);
		return set;
	}

	// Ö´ÐÐɾ³ý
	public static int delete(String sql) {
		BaoWenShow.show(sql, "Êý¾Ý¿âÊý","Ñ¡Ôñ±êÇ©");
		return update(sql);
	}


	/**Ö´ÐÐSQL²éѯÓï¾ä·µ»Ø½á¹û¼¯*/
	public static ResultSet findForResultSet(String sql) {
		if (conn == null) {
			ShowMessage.zidingyi("Êý¾ÝûÓÐÁ¬½Ó³É¹¦...");
			return null;
		}
		ResultSet rs = null;
		try {

			/**TYPE_SCROLL_INSENSITIVE,½á¹û¼¯µÄÓαê¿ÉÒÔÉÏÏÂÒƶ¯£¬µ±Êý¾Ý¿â±ä»¯Ê±£¬µ±Ç°½á¹û¼¯²»±ä¡£
			 * CONCUR_READ_ONLY ²»ÄÜÓýá¹û¼¯¸üÐÂÊý¾Ý¿âÖÐµÄ±í¡£*/
			Statement stmt= conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rs;
	}


	// Ìí¼ÓÊý¾Ý
	public static boolean insert(String sql) {
		BaoWenShow.show(sql, "Êý¾Ý¿âÊý","Ñ¡Ôñ±êÇ©");
		boolean result = false;
		try {
			Statement stmt = conn.createStatement();
			result = stmt.execute(sql);
			conn.commit();  // Ìá½»ÊÂÎñ
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}


	/**¸üÐÂÊý¾Ý*/ 
	public static int  update(String sql) {	
		BaoWenShow.show(sql, "Êý¾Ý¿âÊý","Ñ¡Ôñ±êÇ©");
		int result = 0;
		try {
			Statement stmt = conn.createStatement();
			conn.setAutoCommit(false);
			result = stmt.executeUpdate(sql);
			conn.commit();
			stmt.close();

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

		return result;
	}



	public static List findForList(String sql) {
		List<List> list = new ArrayList<List>();
		ResultSet rs = findForResultSet(sql);
		try {
			ResultSetMetaData metaData = rs.getMetaData();//´ÓÔªÊý¾ÝÖлñµÃÁÐÊý 
			int colCount = metaData.getColumnCount();//±í×ܵÄÁÐÊý
			while (rs.next()) {
				List<String> row = new ArrayList<String>();
				for (int i = 1; i <= colCount; i++) {
					String str = rs.getString(i);
					if (str != null && !str.isEmpty())
						str = str.trim();//ºöÂÔÇ°ÃæºÍºóÃæ¿Õ°×
					row.add(str);
				}
				list.add(row);
			}
		} catch (Exception e) {
			ShowMessage.zidingyi_24(sql+"Êý¾Ý¿â²Ù×÷³ö´í£¡");// ²¶»ñÒì³£ºó£¬µ¯³öÌáʾ¿ò

		}
		try {
			rs.close();
		} catch (SQLException e) {
			// TODO ×Ô¶¯Éú³ÉµÄ catch ¿é
			e.printStackTrace();
		}

		return list;
	}

	/**»ñÈ¡Êý¾Ý¿â·µ»ØµÄ½á¹û¼¯*/
	public static String[]  get_columname(String dataname) {

		String[] columname=null;
		ResultSet rs = findForResultSet("select * from "+dataname);
		ResultSetMetaData data=null;;
		try {
			data = rs.getMetaData();
			int colCount = data.getColumnCount();//±í×ܵÄÁÐÊý
			columname=new String[colCount];
			for (int i =1; i <= colCount; i++) {
				// »ñµÃÖ¸¶¨ÁеÄÁÐÃû
				String columnName = data.getColumnName(i);
				columname[i-1]=columnName;
			}
		} catch (SQLException e) {
			ShowMessage.zidingyi_24("get table column err...");
		}//´ÓÔªÊý¾ÝÖлñµÃÁÐÊý 

		try {
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return columname;
	}


	/**ʹÓÃÔ¤´¦ÀíÓï¾ä¿ìËÙÏòÊý¾Ý¿â²åÈëÊý¾Ý
	 * @param tbnameÊý¾Ý¿âÃû³Æ
	 * @param ziduan×Ö¶Î
	 * @param zhi×ֶζÔÓ¦µÄÖµ */
	public static boolean  insertfast(String tbname,String[] ziduan,String[] zhi) {
		boolean succ=false;
		int result=0;
		PreparedStatement ptmt=null;
		int lenth=ziduan.length;

		StringBuffer sql=new StringBuffer("INSERT " +tbname+" ( ");
		for(int i=0;i<lenth;i++) {

			if(i==(lenth-1)) {
				sql.append(ziduan[i]+") VALUES ( ");
			}else {
				sql.append(ziduan[i]+",");
			}
		}

		for(int i=0;i<lenth;i++) {

			if(i==(lenth-1)) {
				sql.append("? )");
			}else {
				sql.append("?,");
			}
		}

		BaoWenShow.show(sql.toString(), "Êý¾Ý¿âÊý","Ñ¡Ôñ±êÇ©");
		try {
			ptmt = conn.prepareStatement(sql.toString());

			//¹Ø±Õ×Ô¶¯Ìá½»ÊÂÎñ
			conn.setAutoCommit(false);
		} catch (SQLException e1) {
			e1.printStackTrace();
		}		



		for(int i=0;i<lenth;i++) {
			try {
				ptmt.setString(i+1, zhi[i]);
			} catch (SQLException e) {
				e.printStackTrace();
			}			
		}
		try {
			result=ptmt. executeUpdate();
			//×Ô¶¯Ìá½»ÊÂÎñ
			conn.commit();
			ptmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		if(result !=0) {
			succ=true;	
		}

		sql=new StringBuffer();
		return succ;
	}


	/**¿ìËÙÐÞ¸ÄÊý¾Ý¿âÊý¾Ý*/
	//String sql = "update sort set sname=? where sid=?";
	public static  boolean fast_alert_dbase(String tbname,String[] ziduan,String[] zhi) {

		int result=0;
		boolean succ=false;
		PreparedStatement ptmt=null;
		int lenth=ziduan.length;

		StringBuffer sql=new StringBuffer("update " +tbname+" set ");
		for(int i=0;i<lenth;i++) {

			if(i==(lenth-1)) {
				sql.append(" where "+ziduan[i]+"=? ");
			}else if(i==(lenth-2)) {
				sql.append(ziduan[i]+"=?");
			}else {
				sql.append(ziduan[i]+"=?, ");
			}
		}
		if(ZhuCeMan.isGood()) {
			BaoWenShow.show(sql.toString(), "Êý¾Ý¿âÊý","Ñ¡Ôñ±êÇ©");
		}
		try {
			ptmt = conn.prepareStatement(sql.toString());
			//¹Ø±Õ×Ô¶¯Ìá½»ÊÂÎñ
			conn.setAutoCommit(false);
		} catch (SQLException e1) {
			e1.printStackTrace();
		}		



		for(int i=0;i<lenth;i++) {
			try {
				ptmt.setString(i+1, zhi[i]);
			} catch (SQLException e) {
				e.printStackTrace();
			}			
		}
		try {
			result=ptmt.executeUpdate();
			conn.commit();  // Ìá½»ÊÂÎñ
			ptmt.close();
			if(result !=0) {
				succ=true;
			}

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

		sql=new StringBuffer();
		return succ;
	}


	public static void insert_fast(String sqls,int datafrom) {
		BaoWenShow.show(sqls, "Êý¾Ý¿âÊý","Ñ¡Ôñ±êÇ©");
		PreparedStatement pst;
		try {
			conn.setAutoCommit(false);
			pst = conn.prepareStatement("");
			pst.addBatch(sqls);  // Ìí¼ÓÖ´ÐÐsql  
			pst.executeBatch(); // Ö´ÐвÙ×÷   
			conn.commit();  // Ìá½»ÊÂÎñ
			pst.close();
		} catch (SQLException e) {
			if(datafrom==1) {
				About_tb_track.create_tb_track();
			}
			ShowMessage.zidingyi("Êý¾ÝдÈëÊý¾Ý¿â³ö´í"+sqls);
		} // ÉèÖÃÊÂÎñΪ·Ç×Ô¶¯Ìá½» 
	}
}