淺談常用Java數(shù)據(jù)庫(kù)連接池(小結(jié))

<dependencies>
  <!-- JDBC驅(qū)動(dòng) -->
  <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.42</version>
  </dependency>
  
  <!-- c3p0連接池 -->
  <dependency>
    <groupId>com.mchange</groupId>
    <artifactId>c3p0</artifactId>
    <version>0.9.5.2</version>
  </dependency>

  <!-- 日志組件 -->
  <dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-api</artifactId>
    <version>1.7.25</version>
  </dependency>
  <dependency>
    <groupId>ch.qos.logback</groupId>
    <artifactId>logback-core</artifactId>
    <version>1.2.3</version>
  </dependency>
  <dependency>
    <groupId>ch.qos.logback</groupId>
    <artifactId>logback-classic</artifactId>
    <version>1.2.3</version>
  </dependency>
</dependencies>

ComboPooledDataSource cpds = new ComboPooledDataSource();
cpds.setDriverClass( "com.mysql.jdbc.Driver" ); //loads the jdbc driver      
cpds.setJdbcUrl("jdbc:mysql://host:port/db");
cpds.setUser("username");                 
cpds.setPassword("password");                 
  
cpds.setMinPoolSize(5);                   
cpds.setMaxPoolSize(20);
cpds.setAcquireIncrement(5);

// 直接從連接池中獲取連接
Connection conn = cpds.getConnection();
query(conn);

// 關(guān)閉連接池
// cpds.close();

c3p0.driverClass=com.mysql.jdbc.Driver
c3p0.jdbcUrl=jdbc:mysql://host:port/db
c3p0.user=root
c3p0.password=
c3p0.minPoolSize=5
c3p0.maxPoolSize=20
c3p0.acquireIncrement=5

ComboPooledDataSource cpds = new ComboPooledDataSource();
Connection conn = cpds.getConnection();
query(conn);
cpds.close();

<c3p0-config>
  <!-- 默認(rèn)數(shù)據(jù)源 -->
  <default-config>
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://host:port/db</property>
    <property name="user">username</property>
    <property name="password">password</property>
    <property name="minPoolSize">5</property>
    <property name="maxPoolSize">20</property>
    <property name="acquireIncrement">5</property>
  </default-config>

  <!-- 定義帶名稱的數(shù)據(jù)源 -->
  <named-config name="myDataSource">
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://localhost:3306/test_jdbc</property>
    <property name="user">root</property>
    <property name="password"></property>
    <property name="minPoolSize">5</property>
    <property name="maxPoolSize">20</property>
    <property name="acquireIncrement">5</property>
  </named-config>
</c3p0-config>

// 使用默認(rèn)數(shù)據(jù)源
// ComboPooledDataSource cpds = new ComboPooledDataSource();

// 使用指定名稱的數(shù)據(jù)源
ComboPooledDataSource cpds = new ComboPooledDataSource("myDataSource");
Connection conn = cpds.getConnection();
query(conn);
cpds.close();

<c3p0-config>
  <default-config>
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://host:port/db</property>
    <property name="user">username</property>
    <property name="password">password</property>

    <!-- 連接池初始化時(shí)創(chuàng)建的連接數(shù),默認(rèn)值: 3 -->
    <property name="initialPoolSize">3</property>
    <!-- 連接池保持的最小連接數(shù),默認(rèn)值: 3 -->
    <property name="minPoolSize">3</property>
    <!-- 連接池中擁有的最大連接數(shù)，如果獲得新連接時(shí)會(huì)使連接總數(shù)超過(guò)這個(gè)值則不會(huì)再獲取新連接，而是等待其他連接釋放。 -->
    <property name="maxPoolSize">15</property>
    <!-- 連接池在無(wú)空閑連接可用時(shí)一次性創(chuàng)建的新數(shù)據(jù)庫(kù)連接數(shù),默認(rèn)值: 3 -->
    <property name="acquireIncrement">3</property>
    <!-- 連接的最大空閑時(shí)間，如果超過(guò)這個(gè)時(shí)間，某個(gè)數(shù)據(jù)庫(kù)連接還沒(méi)有被使用，則會(huì)斷開(kāi)掉這個(gè)連接。為0，則永遠(yuǎn)不會(huì)斷開(kāi)連接。默認(rèn)值: 0,單位: 秒 -->
    <property name="maxIdleTime">0</property>
    <!-- 連接測(cè)試語(yǔ)句 -->
    <property name="preferredTestQuery">select 1</property>
    <!-- 用來(lái)配置測(cè)試空閑連接的間隔時(shí)間?？梢杂脕?lái)解決MySQL 8小時(shí)斷開(kāi)連接的問(wèn)題。因?yàn)樗ＷC連接池會(huì)每隔一定時(shí)間對(duì)空閑連接進(jìn)行一次測(cè)試，從而保證有效的空閑連接能每隔一定時(shí)間訪問(wèn)一次數(shù)據(jù)庫(kù)，將MySQL8小時(shí)無(wú)會(huì)話的狀態(tài)打破。為0則不測(cè)試。默認(rèn)值:0,單位: 秒 -->
    <property name="idleConnectionTestPeriod">30</property>
    <!-- 連接池在獲得新連接失敗時(shí)重試的次數(shù)，如果小于等于0則無(wú)限重試直至連接獲得成功。默認(rèn)值: 30 -->
    <property name="acquireRetryAttempts">30</property>
    <!-- 連接池在獲得新連接時(shí)的間隔時(shí)間,默認(rèn)值: 1000,單位: 毫秒 -->
    <property name="acquireRetryDelay">1000</property>
  </default-config>
</c3p0-config>