JAVA数据库连接池

1. 数据库连接池

1.1. 原理

数据库操作的构建连接与释放连接浪费资源，所以应该通过运用共享技术实现数据库连接池（享元模式）

• 降低系统中数据库连接Connection对象的数量
• 将数据库服务器的连接响应消耗
• 提高Connection获取的响应速度

享元模式：经典设计模式的一种，当一个系统中存在大量相同的对象时，由于这类对象的大量使用，会造成系统内存的浪费，可以使用享元模式来减少系统中对象的数量。

1.2. 基本属性

• 初始数：初始数据库连接的数量
• 最大数：最大数据库连接的数量
• 增量：用完后增加的数量
• 超时时间：

1.3. C3P0连接池

import java.sql.Connection;

import com.mchange.v2.c3p0.ComboPooledDataSource;

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("1234567");
            
        // the settings below are optional -- c3p0 can work with defaults
        dataSource.setMinPoolSize(5);//最小元素为5个
        dataSource.setAcquireIncrement(5);//增量为5个
        dataSource.setMaxPoolSize(20);//最大连接池数量为5个
            
        // 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();
    }

}

import java.sql.Connection;

import com.mchange.v2.c3p0.ComboPooledDataSource;

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();
    }
}

<?xml version="1.0" encoding="UTF-8"?>//名字必须为c3p0-config.xml
<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>

1.4. Druid连接池

import java.sql.Connection;

import com.alibaba.druid.pool.DruidDataSource;


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();
    }
}

import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;


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();
    }
}

//druid.properties
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/test
username=root
password=123456
filters=stat
initialSize=2
maxActive=300
maxWait=60000
timeBetweenEvictionRunsMillis=60000
minEvictableIdleTimeMillis=300000
validationQuery=SELECT 1
testWhileIdle=true
testOnBorrow=false
testOnReturn=false
poolPreparedStatements=false
maxPoolPreparedStatementPerConnectionSize=200

1.5. 测试类

import java.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();
            }           
        }
    }
}