数据库连接池---(一)

一般我们要建立一个数据连接池存放一些Connection连接，当然了，不要放太多，比方说我这里就只放了三个做例子而已。

每当我们需要使用Connection连接的时候就从中取出来，当他没有了的时候就等待，等有用完了欢回来了就再取。

当一个事务完成了就会将连接还回去。

具体示例：

配置文件jdbc.properties

#mysql
driver=com.mysql.jdbc.Driver
url=jdbc:mysql:///mydata?useUnicode=true&characterEncoding=utf-8
userName=root
password=

因为我没有设置密码，因此上面的密码我让他空了

连接池ConnsUtil.java

package cn.hncu.pool;

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import cn.hncu.utils.ConnectionFactory;

public class ConnsUtil {
	private static List<Connection> pool=new ArrayList<Connection>();
	private static int num=3;
	static {
		try {
			//读取配置文件
			Properties p=new Properties();
			p.load(ConnectionFactory.class.getClassLoader().getResourceAsStream("jdbc.properties"));
			String driver=p.getProperty("driver");
			String url=p.getProperty("url");
			String userName=p.getProperty("userName");
			String password=p.getProperty("password");
			Class.forName(driver);
			
			for (int i=0;i<num;i++){
				Connection con=DriverManager.getConnection(url, userName, password);
				pool.add(con);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public static synchronized Connection getConnection() throws Exception{
		if (pool.size()<=0){
			Thread.sleep(100);
			return getConnection();
		}
		return pool.remove(0);
	}
	public static void back(Connection con){
		pool.add(con);
		System.out.println("还回来一个连接");
	}
}

测试TestPool.java

package cn.hncu.pool;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public class TestPool {

	public static void main(String[] args) {
		Connection con=null;
		try {
			con=ConnsUtil.getConnection();
			con.setAutoCommit(false);
			new SecondThread(1).start();
			new SecondThread(2).start();
			new SecondThread(3).start();
			new SecondThread(4).start();
			new SecondThread(5).start();
			
			Statement st=con.createStatement();
			String sql="insert into stud values('p3001','凤姐',50)";
			st.execute(sql);
			sql="insert into stud values('p3002','芙蓉姐',40)";
			st.execute(sql);
			
			System.out.println("主线程准备提交");
			con.commit();
			System.out.println("主线程提交完毕");
		} catch (Exception e) {
			try {
				con.rollback();
				System.out.println("主线程回滚了");
			} catch (SQLException e1) {
				throw new RuntimeException("主线程事务回滚失败", e1);
			}
		} finally {
			try {
				if (con!=null){
					con.setAutoCommit(true);
					ConnsUtil.back(con);
					//con.close();//如果要把close内部的功能换成还连接，就需要我们以后的技术来实现
				}
			} catch (SQLException e) {
				throw new RuntimeException("主线程连接关闭失败", e);
			}
		}
	}
}

class SecondThread extends Thread{
	private int n;
	public SecondThread(int n) {
		this.n=n;
	}

	@Override
	public void run() {
		Connection con=null;
		try {
			con=ConnsUtil.getConnection();
			con.setAutoCommit(false);
			Statement st=con.createStatement();
			String sql="insert into stud values('p400"+n+"','凤姐2',50)";
			st.execute(sql);
			sql="insert into stud values('p410"+n+"','芙蓉姐2',40)";
			st.execute(sql);
			
			System.out.println("第"+n+"个线程准备提交");
			con.commit();
			System.out.println("第"+n+"个线程提交完毕");
		} catch (Exception e) {
			try {
				con.rollback();
				System.out.println("第"+n+"个线程回滚了");
			} catch (SQLException e1) {
				throw new RuntimeException("第"+n+"个事务回滚失败", e1);
			}
		} finally {
			try {
				if (con!=null){
					con.setAutoCommit(true);
					//con.close();
					ConnsUtil.back(con);
				}
			} catch (SQLException e) {
				throw new RuntimeException("第"+n+"个连接关闭失败", e);
			}
		}
	}
}