什么是數(shù)據(jù)庫連接池

連接池是創(chuàng)建和管理一個連接的緩沖池的技術，這些連接準備好被任何需要它們的線程使用

數(shù)據(jù)庫連接池有什么用

數(shù)據(jù)庫連接池負責分配，管理和釋放數(shù)據(jù)庫連接，
它允許應用程序重復使用一個現(xiàn)有的數(shù)據(jù)庫連接，
而不是在重新建立一個，
釋放空間時間超過最大空閑時間的數(shù)據(jù)庫連接來避免因為沒有釋放數(shù)據(jù)庫連接而引起的數(shù)據(jù)庫鏈接遺漏。
這項技術明顯提高對數(shù)據(jù)庫操作的性能。

有哪些可用的連接池

• 現(xiàn)在很多web服務器都提供了DataSoruce的實現(xiàn)，即連接池的實現(xiàn)，
  通常我們把DataSource的實現(xiàn)，，數(shù)據(jù)源中包含了數(shù)據(jù)庫連接池的實現(xiàn)
• DBCP數(shù)據(jù)庫連接池
• C3P0 數(shù)據(jù)庫連接池

DBCP數(shù)據(jù)庫連接池

• DBCP 是 Apache 軟件基金組織下的開源連接池實現(xiàn)，使用DBCP數(shù)據(jù)源，應用程序應在系統(tǒng)中增加如下兩個 jar 文件
  • Commons-dbcp.jar：連接池的實現(xiàn)
  • Commons-pool.jar：連接池實現(xiàn)的依賴庫

DBCP 實例代碼

1，src 添加文件dbcpconfig.properties

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/demo
username=root
password=

initialSize=10

maxActive=50

maxIdle=20

minIdle=5

maxWait=60000


connectionProperties=useUnicode=true;characterEncoding=utf8

defaultAutoCommit=true

defaultReadOnly=

defaultTransactionIsolation=REPEATABLE_READ

2，DBCPUtil 封裝

public class DBCPUtil {
    private static DataSource ds = null;
    static {
        Properties prop = new Properties();
        try {
            prop.load(DBCPUtil.class.getClassLoader().getResourceAsStream("dbcpconfig.properties"));
            ds = BasicDataSourceFactory.createDataSource(prop);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        try {
            return ds.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("服務器忙");
        }

    }

    public static void release(Connection conn, Statement stmt, ResultSet rs) {

        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            rs = null;
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            stmt = null;
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            conn = null;
        }
    }

}   

3，DBCPUtil 使用

Connection conn = DBCPUtil.getConnection();
PreparedStatement ps = null;
ResultSet rs = null;
try {
    ps = conn.prepareStatement("select * from user");
    rs = ps.executeQuery();
    while (rs.next()) {
        System.out.println(rs.getString(1));
        System.out.println(rs.getString(2));
        System.out.println(rs.getString(3));
        System.out.println(rs.getString(4));
        System.out.println(rs.getString(5));
        System.out.println(rs.getString(6));
        System.out.println(rs.getString(7));
    }
} catch (SQLException e) {
    e.printStackTrace();
} finally {
    DBCPUtil.release(conn, ps, rs);
}

C3P0數(shù)據(jù)庫連接池

• 使用C3P0需要添加 c3p0-0.9.1.2.jar

1，src 添加配置文件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/demo?useUnicode=true&characterEncoding=UTF-8</property>
    <property name="user">root</property>
    <property name="password"></property>
    <property name="initialPoolSize">10</property>
    <property name="maxIdleTime">30</property>
    <property name="maxPoolSize">100</property>
    <property name="minPoolSize">10</property>

  </default-config>
</c3p0-config>  

C3P0Util 封裝

public class C3P0Util {
//得到一個數(shù)據(jù)源
private static DataSource dataSource = new ComboPooledDataSource();
public static Connection getConnection(){
    try {
        return dataSource.getConnection();
    } catch (SQLException e) {
        e.printStackTrace();
        throw new RuntimeException("服務器錯誤");
    }
}

public static DataSource getDataSource() {
    return dataSource;
}

public static void setDataSource(DataSource dataSource) {
    C3P0Util.dataSource = dataSource;
}

public static void release(Connection conn, Statement stmt, ResultSet rs) {

    if (rs != null) {
        try {
            rs.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        rs = null;
    }
    if (stmt != null) {
        try {
            stmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        stmt = null;
    }
    if (conn != null) {
        try {
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        conn = null;
    }
}

}

C3P0Util 使用

PreparedStatement ps = null;
    ResultSet rs = null;
    Connection conn = C3P0Util.getConnection();
    try {
        ps = conn.prepareStatement("select * from user");
        rs = ps.executeQuery();
        while (rs.next()) {
            System.out.println(rs.getString(1));
            System.out.println(rs.getString(2));
            System.out.println(rs.getString(3));
            System.out.println(rs.getString(4));
            System.out.println(rs.getString(5));
            System.out.println(rs.getString(6));
            System.out.println(rs.getString(7));
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        C3P0Util.release(conn, ps, rs);
    }

優(yōu)化jdbc

• 從連接數(shù)據(jù)庫優(yōu)化
• 查詢輸出結果優(yōu)化

2016.10.29