package com.table_sql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class MySqlPool {
	private ComboPooledDataSource mysql_ds = new ComboPooledDataSource();//Êý¾ÝÔ´±»¹Ø±Õºó²»ÄÜÔÙµ÷ÓÃgetconn£¨£©·½·¨
    private int mSqlPort = 3360;
    //private String mSqlIp="118.89.139.230";
    //private String mSqlIp="127.0.0.1";
    private String mSqlIp="192.168.10.79";
    private String user="root";
    private String upassword="lmx8688139";
    private int conncount_max=200;
    private int conncount_min=2;
    private int MaxStatements=50;
    private int MaxIdleTime=60;
    private int InitialPoolSize=2;
    
	public ComboPooledDataSource getMysql_ds() {
		return mysql_ds;
	}
	public MySqlPool() {
		//System.out.println("mSqlIp:"+mSqlIp);
		
		try {
			//Thread.sleep(1000*60);
			mysql_ds.setDriverClass("com.mysql.jdbc.Driver");
			mysql_ds.setJdbcUrl("jdbc:mysql://" + mSqlIp + ":" + mSqlPort);
			mysql_ds.setUser(user);
			mysql_ds.setPassword(upassword);
			mysql_ds.setMaxPoolSize(conncount_max);
			mysql_ds.setMinPoolSize(conncount_min);
			mysql_ds.setIdleConnectionTestPeriod(60);
			// ÉèÖÃÁ¬½Ó³ØÖеÄ×î´óStatementsÊýÁ¿£¡  
			mysql_ds.setMaxStatements(MaxStatements);  
            // ÉèÖÃÁ¬½Ó³ØµÄ×î´ó¿ÕÏÐʱ¼ä£¡  
			mysql_ds.setMaxIdleTime(MaxIdleTime); 
			 // ÉèÖóõʼÁ¬½Ó³ØµÄ´óС£¡  
			mysql_ds.setInitialPoolSize(InitialPoolSize);
			//System.out.println("11111111111111111111111111");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
    public Connection getConn(){
    	Connection con = null;
    	try {
			con=mysql_ds.getConnection();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return con;
    }
    
    public static boolean makeManualCommit(Connection mysql_con,ArrayList<String> al_sql_strs) 
	{
		boolean exe_res = true;
		try {
			mysql_con.setAutoCommit(false);
			
			for(int n=0; n<al_sql_strs.size(); n++) {
				if(true == exe_res) {
					exe_res = executeCreateTableNoclose(mysql_con,al_sql_strs.get(n));
				} else {
					break;
				}
			}
			
			if(true == exe_res) {
				mysql_con.commit();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			exe_res = false;
		} finally {
			try {
				if(false == exe_res) {
					mysql_con.rollback();
				}
				mysql_con.setAutoCommit(true);
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}finally {
				if(mysql_con!=null)
					try {
						mysql_con.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
			}
		}
		
		return exe_res;
	}
    //Ö´ÐÐsqlÓï¾äwith no colse
  	public static boolean executeCreateTableNoclose(Connection mysql_con,String sql_str)
  	{  
  		PreparedStatement ps=null;
  	
  		boolean rest = true;
  		try
      	{
  		    ps = mysql_con.prepareStatement(sql_str);
  			ps.setQueryTimeout(600);
  			ps.execute();
      	}
      	catch(SQLException ex)
  		{
      		ex.printStackTrace();
      		rest = false;
      		
  		}finally {
  			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
  		return rest;
  	}

	//¸ù¾ÝsqlÓï¾äÖ´ÐÐsql²éѯÓï¾ä
	public ResultSet sqlMysqlQuery(Connection mysql_con,String sql_str)
	{
		ResultSet res = null;
		try
    	{
			Statement sql = mysql_con.createStatement();
			sql.setQueryTimeout(30);
			String query = sql_str;
			res =  sql.executeQuery(query);
    	}
    	catch(SQLException ex)
		{
			System.err.println("SQLException:" + ex.getMessage());
		}
		
		return res;
	}

	//¸ù¾ÝsqlÓï¾äÖ´ÐÐsql¸üÐÂÓï¾ä
	public boolean sqlMysqlUpdate(Connection mysql_con,String sql_str)
	{   boolean bl=true;
	    Statement sql=null;
		try
    	{
			sql= mysql_con.createStatement();
			sql.setQueryTimeout(30);
			String query = sql_str;
			bl=sql.execute(query);
    	}
    	catch(SQLException ex)
		{   
    		bl=false;
			System.out.println("SQLException:" + ex.getMessage());
		}finally {
			try {
				if(sql!=null)sql.close();
				if(mysql_con!=null)mysql_con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return bl;
	}
	//¹Ø±ÕÁ¬½Ó
	public void close_con(Connection mysql_con)
	{
		try {
			mysql_con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
    public static void main(String[] args) {
		MySqlPool mPool=new MySqlPool();
		Connection conn=mPool.getConn();
		System.out.println("conn:"+conn);
		String sql="show databases";
		//boolean bl=mPool.executeUpdate(sql, conn);
		
		
	}
}