數據庫常用連接池總結

數據庫常用連接池總結

一、為什么需要連接池

數據庫連接是一種關鍵的有限的昂貴的資源，這一點在多用戶的網頁應用程序中體現得尤為突出。? 一個數據庫連接對象均對應一個物理數據庫連接，每次操作都打開一個物理連接，使用完都關閉連接，這樣造成系統的 性能低下。
數據庫連接池的解決方案是在應用程序啟動時建立足夠的數據庫連接，并講這些連接組成一個連接池，由應用程序動態地對池中的連接進行申請、使用和釋放。
對于多于連接池中連接數的并發請求，應該在請求隊列中排隊等待。并且應用程序可以根據池中連接的使用率，動態增加或減少池中的連接數。
?連接池技術盡可能多地重用了消耗內存地資源，大大節省了內存，提高了服務器地服務效率，能夠支持更多的客戶服務。通過使用連接池，將大大提高程序運行效率，同時，我們可以通過其自身的管理機制來監視數據庫連接的數量、使用情況等。

二、定義

數據庫連接池負責分配、管理和釋放數據庫連接，它允許應用程序重復使用一個現有的數據庫連接，而不是再重新建立一個。

三、工作原理

連接池的工作原理主要由三部分組成，分別為連接池的建立、連接池中連接的使用管理、連接池的關閉。
第一、連接池的建立。一般在系統初始化時，連接池會根據系統配置建立，并在池中創建了幾個連接對象，以便使用時能從連接池中獲取。連接池中的連接不能隨意創建和關閉，這樣避免了連接隨意建立和關閉造成的系統開銷。Java中提供了很多容器類可以方便的構建連接池，例如Vector、Stack等。
第二、連接池的管理。連接池管理策略是連接池機制的核心，連接池內連接的分配和釋放對系統的性能有很大的影響。其管理策略是： 當客戶請求數據庫連接時，首先查看連接池中是否有空閑連接，如果存在空閑連接，則將連接分配給客戶使用；如果沒有空閑連接，則查看當前所開的連接數是否已經達到最大連接數，如果沒達到就重新創建一個連接給請求的客戶；如果達到就按設定的最大等待時間進行等待，如果超出最大等待時間，則拋出異常給客戶。
當客戶釋放數據庫連接時，先判斷該連接的引用次數是否超過了規定值，如果超過就從連接池中刪除該連接，否則保留為其他客戶服務。
該策略保證了數據庫連接的有效復用，避免頻繁的建立、釋放連接所帶來的系統資源開銷。
?第三、連接池的關閉。當應用程序退出時，關閉連接池中所有的連接，釋放連接池相關的資源，該過程正好與創建相反。

四、傳統數據庫連接對比數據庫連接池

不使用數據庫連接池的步驟：
TCP建立連接的三次握手
MySQL認證的三次握手
真正的SQL執行
MySQL的關閉
TCP的四次握手關閉
可以看到，為了執行一條SQL，卻多了非常多我們不關心的網絡交互。

優點：實現簡單
缺點：

1. 網絡IO較多
2. 數據庫的負載較高
3. 響應時間較長及QPS較低
4. 應用頻繁的創建連接和關閉連接，導致臨時對象較多，GC頻繁
5. 在關閉連接后，會出現大量TIME_WAIT 的TCP狀態（在2個MSL之后關閉）

使用數據庫連接池的步驟：

第一次訪問的時候，需要建立連接。 但是之后的訪問，均會復用之前創建的連接，直接執行SQL語句。

優點：

較少了網絡開銷
系統的性能會有一個實質的提升
沒了麻煩的TIME_WAIT狀態

五、常用連接池對比
目前存在多個開源的java數據庫連接池，這些連接池都是在java.sql基礎上編寫而成。

建數據庫test，然后在創建表

CREATE TABLE `user` (
  `userid` INT(11) NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(255) DEFAULT NULL,
  `password` VARCHAR(255) DEFAULT NULL,
  `email` VARCHAR(255) DEFAULT NULL,
  `phone` VARCHAR(255) DEFAULT NULL,
  `status` VARCHAR(255) NOT NULL DEFAULT '0',
  `code` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`userid`)
) ENGINE=INNODB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

DBCP連接池
DBCP 是 Apache 軟件基金組織下的開源連接池實現，使用DBCP數據源，應用程序應在系統中增加如下兩個 jar 文件：
Commons-dbcp.jar：連接池的實現
Commons-pool.jar：連接池實現的依賴庫
Tomcat 的連接池正是采用該連接池來實現的。該數據庫連接池既可以與應用服務器整合使用，也可由應用程序獨立使用。
核心類：org.apache.commons.dbcp2.BasicDataSource extends Object implements DataSource

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.tomcat.dbcp.dbcp2.BasicDataSource;
import org.apache.tomcat.dbcp.dbcp2.BasicDataSourceFactory;
import org.junit.Test;

import com.alibaba.druid.pool.DruidDataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;

public class SqlDriverManage {
    private final String driverClassName = "com.mysql.cj.jdbc.Driver";
    private final String url = "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC";
    private final String userName = "root";
    private final String userPassword = "123456";
    Connection con=null;
    PreparedStatement pst=null;
    ResultSet rs=null;
    /*
     * 使用java.sql.DriverManager類----傳統方式，沒有使用連接池，需要引入mysql驅動包
     */
    @Test
    public void test1(){
        try {
            //1.驅動注冊程序
            Class.forName(driverClassName);
            //2.獲取連接對象
            con= DriverManager.getConnection(url,userName, userPassword);
            //3.準備sql
            String sql="select * from user";
            //4.創建prepareStatement
            pst=con.prepareStatement(sql);
            //5.執行sql語句,得到返回結果
            rs= pst.executeQuery();
            //6.遍歷結果，索引從1開始
            while(rs.next()){
                System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            //7.關閉連接(順序:后打開的先關閉)
            if(rs!=null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            if(pst!=null){
                try {
                    pst.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            if(con!=null){
                try {
                    con.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    }
    }

/*
     * 第一種方式使用實現了javax.sql.DataSource接口的子類--DBCP連接池硬編碼
     * 在上述代碼中添加單元測試代碼
     */  
    @Test
    public void test2(){

        try {
            //1.驅動注冊程序
            BasicDataSource bds=new BasicDataSource();
            bds.setDriverClassName(driverClassName);
            bds.setUrl(url);
            bds.setUsername(userName);
            bds.setPassword(userPassword);
            //2.獲取連接對象
            con=bds.getConnection();
            //3.準備sql語句
            String sql="select * from user";
            //4.創建prepareStatement
            pst=con.prepareStatement(sql);
            //5.執行sql語句,得到返回結果
            rs= pst.executeQuery();
            //6.遍歷結果，索引從1開始
            while(rs.next()){
                System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3));
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

/*
     *第二種方式使用實現了javax.sql.DataSource接口的子類--DBCP連接池軟編碼
     */
    @Test
    public void test3(){
        try {
            //1.驅動注冊程序
            Properties pro=new Properties();
            BasicDataSource bds=null;
            InputStream inStream=SqlDriverManage.class.getResourceAsStream("dbcp.properties");
            pro.load(inStream);
            bds=new BasicDataSourceFactory().createDataSource(pro);
            //2.獲取連接對象
            con=bds.getConnection();
            //3.準備sql語句
            String sql="select * from user";
            //4.創建prepareStatement
            pst=con.prepareStatement(sql);
            //5.執行sql語句,得到返回結果
            rs= pst.executeQuery();
            //6.遍歷結果，索引從1開始
            while(rs.next()){
                System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3));
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

#dbcp.properties
driverClassName=com.mysql.cj.jdbc.Driver
username=root
password=123456
url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC

在此過程中出現的問題

解決方法 添加tomcat斌目錄下的tomcat-juli.jar

解決方法：

1. DBCP連接池配置文件中的key與BasicDataSouce中的屬性一樣
2. URl后追加allowPublicKeyRetrieval=true

C3P0連接池：
最常用的連接池技術！Spring框架，默認支持C3P0連接池技術！
需要引入c3p0-0.9.1.2.jar
核心類：com.mchange.v2.c3p0.ComboPooledDataSource implements PooledDataSource extends DataSource

/*
     * 使用實現了javax.sql.DataSource接口的子類--c3p0連接池硬編碼
     */
    @Test
    public void test4(){
        try {
            //1.驅動注冊程序
            ComboPooledDataSource cpd=new ComboPooledDataSource();
            cpd.setDriverClass(driverClassName);
            cpd.setJdbcUrl(url);
            cpd.setUser(userName);
            cpd.setPassword(userPassword);
            //2.獲取連接對象
            con=cpd.getConnection();
            //3.準備sql語句
            String sql="select * from user";
            //4.創建prepareStatement
            pst=con.prepareStatement(sql);
            //5.執行sql語句,得到返回結果
            rs= pst.executeQuery();
            //6.遍歷結果，索引從1開始
            while(rs.next()){
                System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3));
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

/*
     * 使用實現了javax.sql.DataSource接口的子類--c3p0連接池配置文件xml
     */
    @Test
    public void test5(){
        try {
            //1.驅動注冊程序
            ComboPooledDataSource cpd=new ComboPooledDataSource("mysql-config");
            //ComboPooledDataSource cpd=new ComboPooledDataSource();
            //2.獲取連接對象
            con=cpd.getConnection();
            //3.準備sql語句
            String sql="select * from user";
            //4.創建prepareStatement
            pst=con.prepareStatement(sql);
            //5.執行sql語句,得到返回結果
            rs= pst.executeQuery();
            //6.遍歷結果，索引從1開始
            while(rs.next()){
                System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3));
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

<!--自動加載src下c3p0的配置文件【c3p0-config.xml】，必須是放在src下。-->
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <default-config>
        <property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC</property>
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="user">root</property>
        <property name="password">123456</property>
    </default-config>
    <named-config name="mysql-config">
        <property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC</property>
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="user">root</property>
        <property name="password">123456</property>
    </named-config>
</c3p0-config>

在此過程中出現的問題

DRUID簡介
Druid是阿里巴巴開發的號稱為監控而生的數據庫連接池。可以監控數據庫訪問性能，Druid內置提供了一個功能強大的StatView插件，能夠詳細統計SQL的執行性能，這對于線上分析數據庫訪問性能有幫助。
核心類 com.alibaba.druid.pool.DruidDataSource extends DruidAbstractDataSource implements DataSource

    /*
     * 使用實現了javax.sql.DataSource接口的子類--druid連接池硬編碼
     */
    @Test
    public void test6(){
        try {
            //1.驅動注冊程序
            DruidDataSource dds=new DruidDataSource();
            dds.setDriverClassName(driverClassName);
            dds.setUrl(url);
            dds.setUsername(userName);
            dds.setPassword(userPassword);
            //2.獲取連接對象
            con=dds.getConnection();
            //3.準備sql語句
            String sql="select * from user";
            //4.創建prepareStatement
            pst=con.prepareStatement(sql);
            //5.執行sql語句,得到返回結果
            rs= pst.executeQuery();
            //6.遍歷結果，索引從1開始
            while(rs.next()){
                System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3));
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

/*
     * 使用實現了javax.sql.DataSource接口的子類--druid連接池軟編碼
     */
    @Test
    public void test7(){
        try {
            //1.驅動注冊程序
            Properties pro=new Properties();
            DataSource dds=null;
            InputStream inStream=SqlDriverManage.class.getResourceAsStream("druid.properties");
            pro.load(inStream);
            dds=com.alibaba.druid.pool.DruidDataSourceFactory.createDataSource(pro);

            //2.獲取連接對象
            con=dds.getConnection();
            //3.準備sql語句
            String sql="select * from user";
            //4.創建prepareStatement
            pst=con.prepareStatement(sql);
            //5.執行sql語句,得到返回結果
            rs= pst.executeQuery();
            //6.遍歷結果，索引從1開始
            while(rs.next()){
                System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3));
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

#druid.properties
driverClassName=com.mysql.cj.jdbc.Driver
username=root
password=123456
url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=truecharacterEncoding=UTF-8&useSSL=false&serverTimezone=UTC