Java_MyConnectionPool

package com.tangguoqiang;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.util.Vector;import com.mysql.jdbc.Driver;import com.mysql.jdbc.Statement;/** * 数据库连接池简单实现 * 内部类实现单例模式维护数据库连接 * @author 唐国强 * */public class MyConnectionPool {private static MyPool pool = PoolManager.getInstance();public synchronized static void selectDate(){PooledConnection connection = pool.getConnection();ResultSet rs = connection.queryBySql("select * from items");//输出当前线程名System.out.println(Thread.currentThread().getName());try {while(rs.next()){try {System.out.print(rs.getString("ID")+"\t");} catch (SQLException e) {e.printStackTrace();}System.out.print(rs.getString("NAME")+"\t");System.out.print(rs.getString("PRICE")+"\t");System.out.println();}//需要释放数据库连接，设置为空闲rs.close();connection.close();} catch (SQLException e) {e.printStackTrace();}}public static void main(String[] args) {selectDate();//多线程并发访问数据库连接池for(int i = 0; i < 2000; i++){new Thread(new Runnable(){@Overridepublic void run() {selectDate();}}).start();}//结束标识System.out.println("运行结束");}}class PoolManager{/** * 内部类 * 多个线程在加载内部类的时候线程一定是互斥的， * 所以用单例模式内部类的方式来避免线程混乱 * */private static class createPool{private static MyPool pool = new MyPool();}public static MyPool getInstance(){return createPool.pool;}}//连接池管理class MyPool{//读取配置文件，此处直接写死了private static String jdbcDriver="com.mysql.jdbc.Driver";private static String jdbcUrl="jdbc:mysql://localhost:3306/test";private static String userName="root";private static String password="521847";//初始化连接管道数private static int    initCount=10;//步进数量private static int    stepSize=4;//最大数量private static int    poolMaxSize=150;//管道缓存，需要线程安全的结构private static Vector<PooledConnection> pooledConnections = new Vector<PooledConnection>();//构造函数实现初始化public MyPool(){//生产环境应该读取配置文件初始化连接池//init();//加载驱动try {Driver driver = (Driver)Class.forName(jdbcDriver).newInstance();//注册驱动DriverManager.registerDriver(driver);} catch (InstantiationException | IllegalAccessException | ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}//创建数据库连接createConnections(initCount);}//读取配置文件//private void init(){////流的形式读取配置文件//InputStream is = MyPool.class.getClassLoader().getResourceAsStream("***");////将管道内的字节数据key-value化//Properties properties = new Properties();//try{//properties.load(is);//}catch(IOException e){//e.printStackTrace();//}//jdbcDriver = properties.getProperty("jdbcDriver");//jdbcUrl = properties.getProperty("jdbcUrl");//userName = properties.getProperty("userName");//password = properties.getProperty("passWord");//initCount = Integer.valueOf(properties.getProperty("initCount"));//stepSize = Integer.valueOf(properties.getProperty("stepSize")); //poolMaxSize = Integer.valueOf(properties.getProperty("poolMaxSize"));////}//创建连接_生产者public void createConnections(int count){if(count>0 && pooledConnections.size()+count > poolMaxSize){System.out.println("创建数据库连接失败，超过连接池上限");throw new RuntimeException("创建数据库连接失败，超过连接池上限");}for(int i=0;i<count;i++){try {Connection conn = DriverManager.getConnection(jdbcUrl,userName,password);//把创建的管道封装到自定义管道中，并标记为未被占用PooledConnection pooledConn = new PooledConnection(conn,false);//缓存管道pooledConnections.add(pooledConn);} catch (SQLException e) {e.printStackTrace();}}}//获取连接_消费者public PooledConnection getConnection(){//判断是否有管道if(pooledConnections.size()<=0){System.out.println("连接池内没有数据库管道对象");//工业上需要打印logger.error();输出throw new RuntimeException("连接池内没有数据库管道对象");}//需要判断管道对象是否被占用？即使没被占用但是存放太久了失效？PooledConnection connection = getRealConnection();//并发状态新建的连接可能被抢走，再次创建连接while(connection==null){//如果没有可用管道，自动扩容createConnections(stepSize);connection = getRealConnection();//等待一下try{Thread.sleep(300);}catch(InterruptedException e){e.printStackTrace();}}return connection;}//获取一个真实有效的管道对象：空闲——占用——失效private synchronized PooledConnection getRealConnection(){//判断管道对象是否为空闲for(PooledConnection conn:pooledConnections){//是不是被占用if(!conn.isBusy()){Connection connection = conn.getConnection();//检测管道是否有效try {if(!connection.isValid(2000)){//管道失效就创建一个有效的管道Connection validConn = DriverManager.getConnection(jdbcUrl,userName,password);conn.setConn(validConn);}} catch (SQLException e) {e.printStackTrace();}}conn.setBusy(true);return conn;}return null;}}//连接池内部管道class PooledConnection{//里面封装的是真正的物理连接对象private Connection conn;//定义一个标识，表示是否繁忙private boolean isBusy = false;public PooledConnection(Connection coon,boolean isBusy){this.conn = coon;this.isBusy = isBusy;}//关闭连接public void close(){this.isBusy = false;}public Connection getConnection() {return conn;}public void setConn(Connection conn) {this.conn = conn;}public boolean isBusy() {return isBusy;}public void setBusy(boolean isBusy) {this.isBusy = isBusy;}//自定义查询方法public ResultSet queryBySql(String sql){Statement sm = null;ResultSet rs = null;try {sm = (Statement) conn.createStatement();rs = sm.executeQuery(sql);} catch (SQLException e) {e.printStackTrace();}return rs;}}