数据库连接池

Java连接数据库时，需要构建连接，执行操作，最后释放连接。但是如果在执行操作上非常简便，那么构建连接和释放连接非常的损耗性能

• Connection是Java和数据库俩个平行系统的桥梁
• 桥梁构建不易，成本很高，单次使用成本贵昂贵
• 运用共享技术来实现数据库连接池（享元模式）
  – 降低系统中数据库连接Connection对象数量
  – 降低数据库服务器的连接响应消耗
  – 提高Connection获取的响应速度

享元模式

• 经典23个设计模式的一种，属于结构型模式
• 一个系统存在大量相同的对象（由于这类对象的大量使用，会造成系统内存的耗费，可以使用享元模式来减少系统中对象的数量）

数据库连接池

• 池Pool的概念
  – 初始量（一开始连接池中的数量），最大数（最大的数量），增量（增加连接的数量），超时时间（超过多少时间空闲，释放掉连接）等参数
• 常用的数据库连接池
  – DBCP
  – C3P0
  – Druid

数据库连接池不能代替sql连接，而是管理着这些连接

public class SelectTest {
    public static void main(String[] args){    	
    	  
        Connection conn = null;
        try {
        //四种办法获取连接
        	//从c3p0获取
            //conn = C3p0Factory1.getConnection();
            //conn = C3p0Factory2.getConnection();
            
            //从Druid获取
            //conn = DruidFactory1.getConnection();
            conn = DruidFactory2.getConnection();
            
            //构建数据库执行者
            Statement stmt = conn.createStatement(); 
            System.out.println("创建Statement成功！");      
            
            //执行SQL语句并返回结果到ResultSet
            ResultSet rs = stmt.executeQuery("select bookid, bookname, price from t_book order by bookid");
                        
            //开始遍历ResultSet数据
            while(rs.next())
            {
            	System.out.println(rs.getInt(1) + "," + rs.getString(2) + "," + rs.getInt("price"));
            }
            
            rs.close();
            stmt.close();
            
        } catch (Exception e){
            e.printStackTrace();
        } finally {
        	try	{
        		if(null != conn) {
            		conn.close();
            	}
        	} catch (SQLException e){
                e.printStackTrace();
        	}        	
        }
    }
}



public class C3p0Factory1 {
	
	private static ComboPooledDataSource dataSource = null;

	public static void init() throws Exception {
		
		dataSource = new ComboPooledDataSource();
		dataSource.setDriverClass( "com.mysql.jdbc.Driver" );            
		dataSource.setJdbcUrl( "jdbc:mysql://localhost:3306/test" );
		dataSource.setUser("root");                                  
		dataSource.setPassword("123456");                                  
			
		// the settings below are optional -- c3p0 can work with defaults
		dataSource.setMinPoolSize(5);                                     
		dataSource.setAcquireIncrement(5);
		dataSource.setMaxPoolSize(20);
			
		// The DataSource dataSource is now a fully configured and usable pooled DataSource

	}
	
	public static Connection getConnection() throws Exception {
		if(null == dataSource)
		{
			init();
		}
        return dataSource.getConnection();
    }

}

public class C3p0Factory2 {
	
	private static ComboPooledDataSource dataSource = null;

	public static void init() throws Exception {
		
		dataSource = new ComboPooledDataSource();
		//dataSource 自动加载c3p0-config.xml文件	
		
		// The DataSource dataSource is now a fully configured and usable pooled DataSource

	}
	
	public static Connection getConnection() throws Exception {
		if(null == dataSource)
		{
			init();
		}
        return dataSource.getConnection();
    }
}
c3p0-config.xml文件	
<?xml version="1.0" encoding="UTF-8"?>
-<c3p0-config>
-<default-config>
<!-- 默认配置 -->
<property name="driverClass">com.mysql.jdbc.Driver</property>

<property name="jdbcUrl">jdbc:mysql://localhost:3306/test</property>

<property name="user">root</property>

<property name="password">123456</property>

<property name="initialPoolSize">5</property>

<property name="maxPoolSize">20</property>

</default-config>

</c3p0-config>


public class DruidFactory1 {
	private static DruidDataSource dataSource = null;

	public static void init() throws Exception {
		
		dataSource = new DruidDataSource();
		dataSource.setDriverClassName("com.mysql.jdbc.Driver"); 
		dataSource.setUsername("root");
		dataSource.setPassword("123456");
		dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/test"); 
		dataSource.setInitialSize(5);
		dataSource.setMinIdle(1); 
		dataSource.setMaxActive(10); 
		// 启用监控统计功能 dataSource.setFilters("stat");// 
	}
	
	public static Connection getConnection() throws Exception {
		if(null == dataSource)
		{
			init();
		}
        return dataSource.getConnection();
    }
}


public class DruidFactory2 {
	private static DruidDataSource dataSource = null;

	public static void init() throws Exception {
		Properties properties = new Properties();
		
		InputStream in = DruidFactory2.class.getClassLoader().getResourceAsStream("druid.properties");  
		properties.load(in); 		
		dataSource = (DruidDataSource)DruidDataSourceFactory.createDataSource(properties);		
		
		in.close();
	}
	
	public static Connection getConnection() throws Exception {
		if(null == dataSource)
		{
			init();
		}
        return dataSource.getConnection();
    }
}