之前我们学习的时候，每一次，当我们需要向数据里获取信息的时候，我们都会先 向底层获取一个Connection对象 ，然后用这个对象向数据库里面申请资源，执行完sql语句之后又 关掉资源 。如此反复，这是相当费时的一件事情。
不如将其优化一下，先创建一个数据库连接池，里面放很多 连接对象 ，用户来访问数据库时，从容器中获取连接对象，用户访问完之后，会将连接对象归还给容器。
image.png

概念

其实就是一个容器（集合），存放数据库连接的容器
当系统初始化好后，容器被创建，容器中会申请一些连接对象，当用户来访问数据库时，从容器中获取连接对象，用户访问完之后，会将连接对象归还给容器

好处

  1. 节约资源

  2. 用户访问高效

    实现

    标准接口：DataSource

    java.sql 包下的接口

    方法

  3. 获取连接 ：getConnection()

  4. 归还连接 ：如果连接对象Connection是从连接池中获取的，那么调用Connection.close() 方法，则不会再关闭连接了。而是归还连接

    实现技术

    我们一般不去实现它，有数据库厂商来实现

  5. C3P0 ：数据库连接池技术

  6. Druid ：数据库连接池实现技术，由阿里巴巴提供（全球最棒！

    C3P0数据库连接技术

  7. 导入jar包 c3p0-0.9.5.5.jar mchange-commons-java-0.2.19.jar | image.png | 导入jar包 c3p0-0.9.5.5.jar mchange-commons-java-0.2.19.jar | | —- | —- | | image.png | 选中—> 右键—> Add Library |

  1. 定义配置文件
    • 名称：c3p0.properties 或者 c3p0-config.xml
    • 路径：直接将文件放在src目录下即可

image.png

  1. <c3p0-config>
  2.   <!-- 使用默认的配置读取连接池对象 -->
  3.   <default-config>
  4.       <!--  连接参数 -->
  5.     <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
  6.     <property name="jdbcUrl">jdbc:mysql://localhost:3306/myfirsttest?serverTimezone=Asia/Shanghai</property>
  7.     <property name="user">root</property>
  8.     <property name="password">123456</property>
  10.     <!-- 连接池参数 -->
  11.     <property name="initialPoolSize">5</property>
  12.     <property name="maxPoolSize">10</property>
  13. <!--    <property name="checkoutTimeout">3000</property>-->
  14.   </default-config>
  16.   <named-config name="otherc3p0"> 
  17.     <!--  连接参数 -->
  18.     <property name="driverClass">com.mysql.jdbc.Driver</property>
  19.     <property name="jdbcUrl">jdbc:mysql://localhost:3306/myfirsttest</property>
  20.     <property name="user">root</property>
  21.     <property name="password">123456</property>
  23.     <!-- 连接池参数 -->
  24.     <property name="initialPoolSize">5</property>
  25.     <property name="maxPoolSize">8</property>
  26. <!--    <property name="checkoutTimeout">1000</property>-->
  27.   </named-config>
  28. </c3p0-config>
  1. 创建核心对象 数据库连接池对象 ComboPooledDataSource
  2. 获取连接：getConnection

    基本使用

    ```java package cn.itcast.datasource.c3p0;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource; import java.sql.Connection; import java.sql.SQLException;

/**

  • c3p0的演示 */ public class C3P0Demo1 { public static void main(String[] args) throws SQLException {
    1.  //1.创建数据库连接池对象
    2.  DataSource ds = new ComboPooledDataSource();
    3.  //2.获取连接对象
    4.  Connection conn = ds.getConnection();
    5.  //3.打印
    6.  System.out.println(conn);
    } }

结果：

信息: MLog clients using java 1.4+ standard logging. 一月 21, 2021 12:11:21 下午 com.mchange.v2.c3p0.C3P0Registry 信息: Initializing c3p0-0.9.5.5 [built 11-December-2019 22:07:46 -0800; debug? true; trace: 10] 一月 21, 2021 12:11:21 下午 com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource 信息: Initializing c3p0 pool… com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 1hge0z3af9qzzzl14qzxxk|6659c656, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.cj.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hge0z3af9qzzzl14qzxxk|6659c656, idleConnectionTestPeriod -> 0, initialPoolSize -> 5, jdbcUrl -> jdbc:mysql://localhost:3306/myfirsttest?serverTimezone=Asia/Shanghai, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 10, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {user=**, password=**}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ] com.mchange.v2.c3p0.impl.NewProxyConnection@c818063 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@3f0ee7cb]

Process finished with exit code 0

  1. <a name="iSn7b"></a>
  2. ## 基本验证
  3. <a name="jFwxr"></a>
  4. ### 验证maxPoolSize（最大连接数
  5. ![image.png](https://cdn.nlark.com/yuque/0/2021/png/2211273/1611202716945-f60bb06b-2dbe-490b-aeb1-c1a64108cad7.png#align=left&display=inline&height=218&margin=%5Bobject%20Object%5D&name=image.png&originHeight=303&originWidth=780&size=29946&status=done&style=none&width=561)<br />从c3p0-config.xml文件里可以看到最大连接数量是10个，咱们可以验证一下，for循环打印10个地址
  6. ```java
  7. package cn.itcast.datasource.c3p0;
  9. import com.mchange.v2.c3p0.ComboPooledDataSource;
  11. import javax.sql.DataSource;
  12. import java.sql.Connection;
  13. import java.sql.SQLException;
  15. /**
  16.  * 连接池参数验证
  17.  */
  18. public class C3P0Demo2 {
  19.     public static void main(String[] args) throws SQLException {
  20.         //获取DataSource
  21.         DataSource ds = new ComboPooledDataSource();
  22.         //2.获取连接
  23.         for (int i = 1; i <=10 ; i++) {
  24.             Connection conn = ds.getConnection();
  25.             System.out.println(i+":"+conn);
  26.         }
  27.     }
  28. }
  30. 结果：
  32. "C:\Program Files\Java\jdk1.8.0_151\bin\java.exe" "-javaagent:C:\Program Files\JetBrains\IntelliJ IDEA 2019.3.3\lib\idea_rt.jar=51391:C:\Program Files\JetBrains\IntelliJ IDEA 2019.3.3\bin" -Dfile.encoding=UTF-8 -classpath "C:\Program Files\Java\jdk1.8.0_151\jre\lib\charsets.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\deploy.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\access-bridge-64.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\cldrdata.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\dnsns.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\jaccess.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\jfxrt.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\localedata.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\nashorn.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\sunec.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\sunjce_provider.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\sunmscapi.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\sunpkcs11.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\zipfs.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\javaws.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\jce.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\jfr.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\jfxswt.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\jsse.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\management-agent.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\plugin.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\resources.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\rt.jar;C:\Users\DYQ\IdeaProjects\workplace\out\production\dateSource_jdbcTemplate;C:\Users\DYQ\IdeaProjects\workplace\dataSource_jdbcTemplate\libs\c3p0-0.9.5.5.jar;C:\Users\DYQ\IdeaProjects\workplace\dataSource_jdbcTemplate\libs\mchange-commons-java-0.2.19.jar;C:\Users\DYQ\IdeaProjects\workplace\dataSource_jdbcTemplate\libs\mysql-connector-java-8.0.21.jar" cn.itcast.datasource.c3p0.C3P0Demo2
  33. 一月 21, 2021 12:19:59 下午 com.mchange.v2.log.MLog 
  34. 信息: MLog clients using java 1.4+ standard logging.
  35. 一月 21, 2021 12:19:59 下午 com.mchange.v2.c3p0.C3P0Registry 
  36. 信息: Initializing c3p0-0.9.5.5 [built 11-December-2019 22:07:46 -0800; debug? true; trace: 10]
  37. 一月 21, 2021 12:19:59 下午 com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource 
  38. 信息: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 3000, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 1hge0z3af9rb3mk107yr36|6659c656, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.cj.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hge0z3af9rb3mk107yr36|6659c656, idleConnectionTestPeriod -> 0, initialPoolSize -> 5, jdbcUrl -> jdbc:mysql://localhost:3306/myfirsttest?serverTimezone=Asia/Shanghai, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 10, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
  39. 1:com.mchange.v2.c3p0.impl.NewProxyConnection@c818063 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@3f0ee7cb]
  40. 2:com.mchange.v2.c3p0.impl.NewProxyConnection@7d417077 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@7dc36524]
  41. 3:com.mchange.v2.c3p0.impl.NewProxyConnection@2c8d66b2 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@5a39699c]
  42. 4:com.mchange.v2.c3p0.impl.NewProxyConnection@56cbfb61 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@1134affc]
  43. 5:com.mchange.v2.c3p0.impl.NewProxyConnection@129a8472 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@1b0375b3]
  44. 6:com.mchange.v2.c3p0.impl.NewProxyConnection@2d209079 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@6bdf28bb]
  45. 7:com.mchange.v2.c3p0.impl.NewProxyConnection@2752f6e2 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@e580929]
  46. 8:com.mchange.v2.c3p0.impl.NewProxyConnection@7c75222b [wrapping: com.mysql.cj.jdbc.ConnectionImpl@4c203ea1]
  47. 9:com.mchange.v2.c3p0.impl.NewProxyConnection@1d251891 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@48140564]
  48. 10:com.mchange.v2.c3p0.impl.NewProxyConnection@7c30a502 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@49e4cb85]
  50. Process finished with exit code 0

可以观察，是10个不同的地址
那么循环11次如何？
image.png
先是打印出10个地址，然后等待3秒开始报错

  • 等待3秒是因为xml文件里面有一个超时配置，最多不超过3秒

image.png
所以说，如果多余10个人 同时 过来从池子里拿连接（10个连接都拿走的同时还在申请），就会造成如上报错情况。
当然，因为我们取完没有归还连接，所以到第11个人来取依旧会报错（池子里没有了，
此时就要考虑：

  • 取完后 归还操作
  • 要根据实际情况可以改变xml里面maxPoolSize的值。

    验证归还操作close()方法

    ```java package cn.itcast.datasource.c3p0;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource; import java.sql.Connection; import java.sql.SQLException;

/**

  • 连接池参数验证 */ public class C3P0Demo2 { public static void main(String[] args) throws SQLException {

    1.  //获取DataSource
    2.  DataSource ds = new ComboPooledDataSource();
    3.  //2.获取连接
    4.  for (int i = 1; i <=11 ; i++) {
    5.      Connection conn = ds.getConnection();
    6.      System.out.println(i+":"+conn);
    8.      if (i==5){
    9.          conn.close(); //归还连接到连接池中
    10.      }
    11.  }

    } }

结果：

“C:\Program Files\Java\jdk1.8.0_151\bin\java.exe” “-javaagent:C:\Program Files\JetBrains\IntelliJ IDEA 2019.3.3\lib\idea_rt.jar=51440:C:\Program Files\JetBrains\IntelliJ IDEA 2019.3.3\bin” -Dfile.encoding=UTF-8 -classpath “C:\Program Files\Java\jdk1.8.0_151\jre\lib\charsets.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\deploy.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\access-bridge-64.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\cldrdata.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\dnsns.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\jaccess.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\jfxrt.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\localedata.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\nashorn.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\sunec.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\sunjce_provider.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\sunmscapi.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\sunpkcs11.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\zipfs.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\javaws.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\jce.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\jfr.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\jfxswt.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\jsse.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\management-agent.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\plugin.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\resources.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\rt.jar;C:\Users\DYQ\IdeaProjects\workplace\out\production\dateSource_jdbcTemplate;C:\Users\DYQ\IdeaProjects\workplace\dataSource_jdbcTemplate\libs\c3p0-0.9.5.5.jar;C:\Users\DYQ\IdeaProjects\workplace\dataSource_jdbcTemplate\libs\mchange-commons-java-0.2.19.jar;C:\Users\DYQ\IdeaProjects\workplace\dataSource_jdbcTemplate\libs\mysql-connector-java-8.0.21.jar” cn.itcast.datasource.c3p0.C3P0Demo2 一月 21, 2021 12:26:43 下午 com.mchange.v2.log.MLog 信息: MLog clients using java 1.4+ standard logging. 一月 21, 2021 12:26:43 下午 com.mchange.v2.c3p0.C3P0Registry 信息: Initializing c3p0-0.9.5.5 [built 11-December-2019 22:07:46 -0800; debug? true; trace: 10] 一月 21, 2021 12:26:43 下午 com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource 信息: Initializing c3p0 pool… com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 3000, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 1hge0z3af9rjrd82y0i88|6659c656, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.cj.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hge0z3af9rjrd82y0i88|6659c656, idleConnectionTestPeriod -> 0, initialPoolSize -> 5, jdbcUrl -> jdbc:mysql://localhost:3306/myfirsttest?serverTimezone=Asia/Shanghai, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 10, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {user=**, password=**}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ] 1:com.mchange.v2.c3p0.impl.NewProxyConnection@c818063 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@3f0ee7cb] 2:com.mchange.v2.c3p0.impl.NewProxyConnection@7d417077 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@7dc36524] 3:com.mchange.v2.c3p0.impl.NewProxyConnection@2c8d66b2 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@5a39699c] 4:com.mchange.v2.c3p0.impl.NewProxyConnection@56cbfb61 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@1134affc] 5:com.mchange.v2.c3p0.impl.NewProxyConnection@129a8472 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@1b0375b3] 6:com.mchange.v2.c3p0.impl.NewProxyConnection@6b71769e [wrapping: com.mysql.cj.jdbc.ConnectionImpl@1b0375b3] 7:com.mchange.v2.c3p0.impl.NewProxyConnection@e580929 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@1cd072a9] 8:com.mchange.v2.c3p0.impl.NewProxyConnection@4c203ea1 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@27f674d] 9:com.mchange.v2.c3p0.impl.NewProxyConnection@48140564 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@58ceff1] 10:com.mchange.v2.c3p0.impl.NewProxyConnection@49e4cb85 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@2133c8f8] 11:com.mchange.v2.c3p0.impl.NewProxyConnection@3ac3fd8b [wrapping: com.mysql.cj.jdbc.ConnectionImpl@5594a1b5]

Process finished with exit code 0

  1. 如上，从第五个人就开始归还连接了。所以第十一个可以从池子里拿到连接。地址相应也有重复的部分
  2. <a name="L0mwU"></a>
  3. ### 配置多个condig
  4. ![image.png](https://cdn.nlark.com/yuque/0/2021/png/2211273/1611218622162-12a45925-f93a-4afb-87c2-4e0ed48243e6.png#align=left&display=inline&height=240&margin=%5Bobject%20Object%5D&name=image.png&originHeight=254&originWidth=547&size=17957&status=done&style=none&width=516)<br />如图，和上这些块元素之后你就会发现，这里有两个config，一个默认default，一个命名named<br />展开来看，感觉内容大多也是相同的，只有个别不一样，那这些是干嘛用的？
  6. - 可以通过设置多个config，通过一个文件调用多个不同的数据源，操作不同的数据库
  7. - 如果你定义的字符串什么都不指定，什么都没传，则默认调用default-config。若指定name=""对应的值，则调用相应的named-config
  9. ![image.png](https://cdn.nlark.com/yuque/0/2021/png/2211273/1611219701486-4802ba65-d5a9-4c7b-96ca-62fc78dfd04c.png#align=left&display=inline&height=229&margin=%5Bobject%20Object%5D&name=image.png&originHeight=458&originWidth=1335&size=62925&status=done&style=none&width=667.5)<br />![image.png](https://cdn.nlark.com/yuque/0/2021/png/2211273/1611219635886-2c9b6061-ee8d-478a-b1b6-767aa2430ab8.png#align=left&display=inline&height=288&margin=%5Bobject%20Object%5D&name=image.png&originHeight=410&originWidth=941&size=39641&status=done&style=none&width=661)<br />我这里循环打印10次，但最大申请量只有8个，运行后如果到第9个报错，那么说明调用的就是名为otherc3p0的config。<br />果不其然。爷成功了✌
  10. ```java
  11. package cn.itcast.datasource.c3p0;
  13. import com.mchange.v2.c3p0.ComboPooledDataSource;
  15. import javax.sql.DataSource;
  16. import java.sql.Connection;
  17. import java.sql.SQLException;
  19. public class C3P0Demo2_1 {
  20.     public static void main(String[] args) throws SQLException {
  21.         //1.获取DataSource，使用默认配置config
  22.         DataSource ds = new ComboPooledDataSource("otherc3p0");
  24.         //2.获取连接
  25.         for (int i = 1; i <=10 ; i++) {
  26.             Connection conn = ds.getConnection();
  27.             System.out.println(i+":"+conn);
  28.         }
  29.     }
  30. }
  32. 结果：
  34. "C:\Program Files\Java\jdk1.8.0_151\bin\java.exe" "-javaagent:C:\Program Files\JetBrains\IntelliJ IDEA 2019.3.3\lib\idea_rt.jar=51995:C:\Program Files\JetBrains\IntelliJ IDEA 2019.3.3\bin" -Dfile.encoding=UTF-8 -classpath "C:\Program Files\Java\jdk1.8.0_151\jre\lib\charsets.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\deploy.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\access-bridge-64.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\cldrdata.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\dnsns.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\jaccess.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\jfxrt.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\localedata.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\nashorn.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\sunec.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\sunjce_provider.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\sunmscapi.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\sunpkcs11.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\zipfs.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\javaws.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\jce.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\jfr.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\jfxswt.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\jsse.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\management-agent.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\plugin.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\resources.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\rt.jar;C:\Users\DYQ\IdeaProjects\workplace\out\production\dateSource_jdbcTemplate;C:\Users\DYQ\IdeaProjects\workplace\dataSource_jdbcTemplate\libs\c3p0-0.9.5.5.jar;C:\Users\DYQ\IdeaProjects\workplace\dataSource_jdbcTemplate\libs\mchange-commons-java-0.2.19.jar;C:\Users\DYQ\IdeaProjects\workplace\dataSource_jdbcTemplate\libs\mysql-connector-java-8.0.21.jar" cn.itcast.datasource.c3p0.C3P0Demo2_1
  35. 一月 21, 2021 4:59:30 下午 com.mchange.v2.log.MLog 
  36. 信息: MLog clients using java 1.4+ standard logging.
  37. 一月 21, 2021 4:59:31 下午 com.mchange.v2.c3p0.C3P0Registry 
  38. 信息: Initializing c3p0-0.9.5.5 [built 11-December-2019 22:07:46 -0800; debug? true; trace: 10]
  39. 一月 21, 2021 4:59:31 下午 com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource 
  40. 信息: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 1000, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> otherc3p0, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hge9huafa1akvzzqedaf|6659c656, idleConnectionTestPeriod -> 0, initialPoolSize -> 5, jdbcUrl -> jdbc:mysql://localhost:3306/myfirsttest?serverTimezone=Asia/Shanghai, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 8, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
  41. Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
  42. 1:com.mchange.v2.c3p0.impl.NewProxyConnection@c818063 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@3f0ee7cb]
  43. 2:com.mchange.v2.c3p0.impl.NewProxyConnection@7d417077 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@7dc36524]
  44. 3:com.mchange.v2.c3p0.impl.NewProxyConnection@2c8d66b2 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@5a39699c]
  45. 4:com.mchange.v2.c3p0.impl.NewProxyConnection@56cbfb61 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@1134affc]
  46. 5:com.mchange.v2.c3p0.impl.NewProxyConnection@129a8472 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@1b0375b3]
  47. 6:com.mchange.v2.c3p0.impl.NewProxyConnection@2d209079 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@6bdf28bb]
  48. 7:com.mchange.v2.c3p0.impl.NewProxyConnection@2752f6e2 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@e580929]
  49. 8:com.mchange.v2.c3p0.impl.NewProxyConnection@7c75222b [wrapping: com.mysql.cj.jdbc.ConnectionImpl@4c203ea1]
  50. Exception in thread "main" java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.
  51.     at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118)
  52.     at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:77)
  53.     at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:690)
  54.     at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:140)
  55.     at cn.itcast.datasource.c3p0.C3P0Demo2_1.main(C3P0Demo2_1.java:16)
  56. Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@7e0babb1 -- timeout at awaitAvailable()
  57.     at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1505)
  58.     at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:644)
  59.     at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:554)
  60.     at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:758)
  61.     at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:685)
  62.     ... 2 more
  64. Process finished with exit code 1

Druid数据库连接池

数据库连接池实现技术，由阿里巴巴提供，全球最好！

配置环境

  1. 导入druid-1.0.9.jar
  2. 定义配置文件：
    • 是 .properties 形式的
    • 可以叫任意名称，可以放在任意目录下
  3. 加载配置文件 Properties
  4. 获取数据库连接池对象
    • 通过工厂类来获取 ：DruidDataSourceFactory
  5. 获取连接：getConnection

    druid.properties

    ```html driverClassName=com.mysql.cj.jdbc.Driver url=jdbc:mysql:///myfirsttest?serverTimezone=Asia/Shanghai username=root password=123456

    初始化连接数量

    initialSize=5

    最大连接数

    maxActive=10

    最大等待时间

    maxWait=3000

    maxIdle=8

    minIdle=3

validationQuery:SELECT 1 testWhileIdle:true testOnBorrow:false testOnReturn:false

  1. <a name="WVIci"></a>
  2. ### 测试
  3. ```java
  4. package cn.itcast.datasource.druid;
  6. import com.alibaba.druid.pool.DruidDataSourceFactory;
  8. import javax.sql.DataSource;
  9. import java.io.InputStream;
  10. import java.sql.Connection;
  11. import java.util.Properties;
  13. /**
  14.  * Druid基本演示
  15.  */
  16. public class DruidDemo {
  17.     public static void main(String[] args) throws Exception {
  18.         //1.导入jar包
  19.         //2.定义配置文件
  20.         //3.加载配置文件
  21.         Properties pro = new Properties();
  22.         InputStream is = DruidDemo.class.getClassLoader().getResourceAsStream("druid.properties");
  23.         pro.load(is);
  24.         //4.获取连接池对象
  25.         DataSource ds = DruidDataSourceFactory.createDataSource(pro);
  27.         //5.获取连接
  28.         Connection conn = ds.getConnection();
  29.         System.out.println(conn);
  30.     }
  31. }
  33. 结果：
  35. "C:\Program Files\Java\jdk1.8.0_151\bin\java.exe" "-javaagent:C:\Program Files\JetBrains\IntelliJ IDEA 2019.3.3\lib\idea_rt.jar=53834:C:\Program Files\JetBrains\IntelliJ IDEA 2019.3.3\bin" -Dfile.encoding=UTF-8 -classpath "C:\Program Files\Java\jdk1.8.0_151\jre\lib\charsets.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\deploy.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\access-bridge-64.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\cldrdata.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\dnsns.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\jaccess.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\jfxrt.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\localedata.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\nashorn.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\sunec.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\sunjce_provider.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\sunmscapi.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\sunpkcs11.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\zipfs.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\javaws.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\jce.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\jfr.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\jfxswt.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\jsse.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\management-agent.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\plugin.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\resources.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\rt.jar;C:\Users\DYQ\IdeaProjects\workplace\out\production\dateSource_jdbcTemplate;C:\Users\DYQ\IdeaProjects\workplace\dataSource_jdbcTemplate\libs\c3p0-0.9.5.5.jar;C:\Users\DYQ\IdeaProjects\workplace\dataSource_jdbcTemplate\libs\mchange-commons-java-0.2.19.jar;C:\Users\DYQ\IdeaProjects\workplace\dataSource_jdbcTemplate\libs\mysql-connector-java-8.0.21.jar;C:\Users\DYQ\IdeaProjects\workplace\dataSource_jdbcTemplate\libs\druid-1.0.9.jar" cn.itcast.datasource.druid.DruidDemo
  36. 一月 21, 2021 9:45:40 下午 com.alibaba.druid.pool.DruidDataSource info
  37. 信息: {dataSource-1} inited
  38. com.mysql.cj.jdbc.ConnectionImpl@55a1c291
  40. Process finished with exit code 0

定义工具类

  1. 定义一个类JDBCUtils
  2. 提供静态代码块加载配置文件，初始化连接池对象
  3. 提供方法
    1. 获取连接方法：通过数据库连接池获取连接
    2. 释放资源
    3. 获取连接池的方法

image.pngimage.png

  1. package cn.itcast.utils;
  3. import com.alibaba.druid.pool.DruidDataSourceFactory;
  4. import com.alibaba.druid.util.JdbcUtils;
  6. import javax.sql.DataSource;
  7. import java.io.IOException;
  8. import java.sql.Connection;
  9. import java.sql.ResultSet;
  10. import java.sql.SQLException;
  11. import java.sql.Statement;
  12. import java.util.Properties;
  14. public class JDBCUtils {
  16.     //1.定义一个成员变量DataSource
  17.     private static DataSource ds;
  18.     static {
  19.         try {
  20.             //1.加载配置文件
  21.             Properties pro = new Properties();
  22.             pro.load(JdbcUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
  23.             //2.获取DataSource
  24.             ds = DruidDataSourceFactory.createDataSource(pro);
  25.         } catch (IOException e) {
  26.             e.printStackTrace();
  27.         } catch (Exception e) {
  28.             e.printStackTrace();
  29.         }
  31.     }
  32.     /**
  33.      * 获取连接
  34.      */
  35.     public static Connection getConnection() throws SQLException {
  36.         return ds.getConnection();
  37.     }
  38.     /**
  39.      * 释放资源  方法的重载*2
  40.      */
  41.     public static void close(ResultSet rs ,Statement stmt, Connection conn){
  42.         if (rs!=null){
  43.             try {
  44.                 rs.close();
  45.             } catch (SQLException e) {
  46.                 e.printStackTrace();
  47.             }
  48.         }
  49.         if (stmt!=null){
  50.             try {
  51.                 stmt.close();
  52.             } catch (SQLException e) {
  53.                 e.printStackTrace();
  54.             }
  55.         }
  56.         if (conn!=null){
  57.             try {
  58.                 conn.close();
  59.             } catch (SQLException e) {
  60.                 e.printStackTrace();
  61.             }
  62.         }
  64.     }
  66.     public static void close(Statement stmt,Connection conn){
  67.         //简化代码
  68.         close(null,stmt,conn);
  69.     }
  71.     /**
  72.      * 获取连接池的方法
  73.      */
  74.     public static DataSource getDataSource(){
  75.         return ds;
  76.     }
  77. }

测试工具类

使用新的工具类
完成添加操作，给account表去添加一条记录

  1. package cn.itcast.datasource.druid;
  3. import cn.itcast.utils.JDBCUtils;
  5. import java.sql.Connection;
  6. import java.sql.PreparedStatement;
  7. import java.sql.SQLException;
  9. /**
  10.  * 使用新的工具类
  11.  * 完成添加操作，给account表去添加一条记录
  12.  */
  13. public class DuridDemo2 {
  14.     public static void main(String[] args) {
  15.         Connection conn = null;
  16.         PreparedStatement pstmt = null;
  17.         try {
  18.             //1.获取连接
  19.             conn = JDBCUtils.getConnection();
  20.             //2.定义sql
  21.             String sql = "insert into account values(null,?,?)";
  22.             //3.获取pstm对象
  23.             pstmt = conn.prepareStatement(sql);
  24.             //4.给？赋值
  25.             pstmt.setString(1,"王五");
  26.             pstmt.setDouble(2,3000);
  27.             //5.执行sql
  28.             int count = pstmt.executeUpdate();
  29.             System.out.println(count);
  30.         } catch (SQLException e) {
  31.             e.printStackTrace();
  32.         }finally {
  33.             //6.释放资源
  34.             JDBCUtils.close(pstmt,conn);
  35.         }
  37.     }
  38. }
  40. 结果：
  41. "C:\Program Files\Java\jdk1.8.0_151\bin\java.exe" "-javaagent:C:\Program Files\JetBrains\IntelliJ IDEA 2019.3.3\lib\idea_rt.jar=50885:C:\Program Files\JetBrains\IntelliJ IDEA 2019.3.3\bin" -Dfile.encoding=UTF-8 -classpath "C:\Program Files\Java\jdk1.8.0_151\jre\lib\charsets.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\deploy.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\access-bridge-64.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\cldrdata.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\dnsns.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\jaccess.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\jfxrt.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\localedata.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\nashorn.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\sunec.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\sunjce_provider.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\sunmscapi.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\sunpkcs11.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\ext\zipfs.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\javaws.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\jce.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\jfr.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\jfxswt.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\jsse.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\management-agent.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\plugin.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\resources.jar;C:\Program Files\Java\jdk1.8.0_151\jre\lib\rt.jar;C:\Users\DYQ\IdeaProjects\workplace\out\production\dateSource_jdbcTemplate;C:\Users\DYQ\IdeaProjects\workplace\dataSource_jdbcTemplate\libs\c3p0-0.9.5.5.jar;C:\Users\DYQ\IdeaProjects\workplace\dataSource_jdbcTemplate\libs\mchange-commons-java-0.2.19.jar;C:\Users\DYQ\IdeaProjects\workplace\dataSource_jdbcTemplate\libs\mysql-connector-java-8.0.21.jar;C:\Users\DYQ\IdeaProjects\workplace\dataSource_jdbcTemplate\libs\druid-1.0.9.jar" cn.itcast.datasource.druid.DuridDemo2
  42. 一月 22, 2021 4:39:57 下午 com.alibaba.druid.pool.DruidDataSource info
  43. 信息: {dataSource-1} inited
  44. 1
  46. Process finished with exit code 0

返回“1”，说明成功改变一条数据
数据库成功添加
image.png