import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class Test {
	List<Connection> cs = new ArrayList<Connection>();
	
	int size;

	public Test(int size) {
		this.size = size;
		init();
	}

	public void init() {

		//这里恰恰不能使用try-with-resource的方式，因为这些连接都需要是"活"的，不要被自动关闭了
		try {
			Class.forName("com.mysql.jdbc.Driver");
			for (int i = 0; i < size; i++) {
				Connection c = DriverManager
						.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8", "root", "admin");

				cs.add(c);

			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public synchronized Connection getConnection() {
		while (cs.isEmpty()) {
			try {
				this.wait();
			} catch (InterruptedException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		Connection c = cs.remove(0);
		return c;
	}

	public synchronized void returnConnection(Connection c) {
		cs.add(c);
		this.notifyAll();
	}
	public static void main(String[] args) {
		Thread thread=null;
		
		long tic =System.currentTimeMillis();
		List<Thread> ts = new ArrayList<>();
		for(int i=0;i<100;i++)
		{
			thread =new Thread(){
				public void run(){
					
					try {
						Class.forName("com.mysql.jdbc.Driver");
					} catch (ClassNotFoundException e) {
						e.printStackTrace();
					}

					try (
							Connection c = DriverManager.getConnection
							("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8",
									"root", "admin");//a mistake may happen because of the limit connection that 
							//Mysql can offer!
							Statement s = c.createStatement();             
							)
					{
						String sql = "insert into hero values"
								+ "(null," + "'hero"+"'," + 313.0f + "," + 50 + ")";
						s.execute(sql);
						System.out.println("1 insert a data!");
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					
				}
			};
			thread.start();
			ts.add(thread);
		}
		for(Thread t:ts){//Wait until the thread go to an end!!!
			try{
				t.join();
			}catch(InterruptedException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
		}
		long toc=System.currentTimeMillis();
		System.out.println("The traditional method has cost "+
				(toc-tic)+" milliseconds!");
		//*************
		Test test=new Test(10);
		test.init();
		
		tic=System.currentTimeMillis();
		List<Thread> ts1 = new ArrayList<>();
		for(int i=0;i<100;i++)
		{
			

			thread =new Thread(){
				public void run()
				{
					Connection c=test.getConnection();
					
					String statement="insert into hero values"
							+ "(null," + "'hero"+"'," + 313.0f + "," + 50 + ")";
					try(Statement st=c.createStatement()){
						st.execute(statement.toString());
						System.out.println("2 insert a data!");
					}catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					test.returnConnection(c);
				}
			};
			thread.start();
			ts1.add(thread);
		}
		for (Thread t : ts1) {//Wait until the thread go to an end!!!
            try {
                t.join();
            } catch (InterruptedException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
		toc=System.currentTimeMillis();
		System.out.println("The connection pool cost "+(toc-tic)+" milliseconds!");
	}
	/*
		Test cp = new Test(3);
		for (int i = 0; i < 100; i++) {
			new WorkingThread("working thread" + i, cp).start();
		}*/



}
class WorkingThread extends Thread {
	private Test cp;

	public WorkingThread(String name, Test cp) {
		super(name);
		this.cp = cp;
	}

	public void run() {
		Connection c = cp.getConnection();
		System.out.println(this.getName()+ ":\t 获取了一根连接，并开始工作"  );
		try (Statement st = c.createStatement()){

			//模拟时耗１秒的数据库ＳＱＬ语句
			Thread.sleep(1000);
			st.execute("select * from hero");

		} catch (SQLException | InterruptedException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		cp.returnConnection(c);
	}
}

CREATE TABLE hero (
  id int(11) AUTO_INCREMENT,
  name varchar(30) ,
  hp float ,
  damage int(11) ,
  PRIMARY KEY (id)
)  DEFAULT CHARSET=utf8;