世上沒(méi)有從天而降的英雄,只有挺身而出的凡人匆背。
——致敬呼伸，那些在疫情中為我們挺身而出的人。
運(yùn)行環(huán)境
JDK8 + IntelliJ IDEA 2018.3
優(yōu)點(diǎn)：
使用連接池的最主要的優(yōu)點(diǎn)是性能钝尸。創(chuàng)建一個(gè)新的數(shù)據(jù)庫(kù)連接所耗費(fèi)的時(shí)間主要取決于網(wǎng)絡(luò)的速
度以及應(yīng)用程序和數(shù)據(jù)庫(kù)服務(wù)器的(網(wǎng)絡(luò))距離括享，而且這個(gè)過(guò)程通常是一個(gè)很耗時(shí)的過(guò)程。而采用
數(shù)據(jù)庫(kù)連接池后珍促，數(shù)據(jù)庫(kù)連接請(qǐng)求可以直接通過(guò)連接池滿足而不需要為該請(qǐng)求重新連接铃辖、認(rèn)證到
數(shù)據(jù)庫(kù)服務(wù)器，這樣就節(jié)省了時(shí)間猪叙。
缺點(diǎn)：
數(shù)據(jù)庫(kù)連接池中可能存在著多個(gè)沒(méi)有被使用的連接一直連接著數(shù)據(jù)庫(kù)(這意味著資源的浪費(fèi))
一娇斩、導(dǎo)包
xml文件:
1 <?xml version="1.0" encoding="UTF-8"?>
2 <project xmlns="http://maven.apache.org/POM/4.0.0"
3 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
4 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
5 <modelVersion>4.0.0</modelVersion>
6
7 <groupId>com.papercy</groupId>
8 <artifactId>DBCP</artifactId>
9 <version>1.0-SNAPSHOT</version>
10 <dependencies>
11 <dependency>
12 <groupId>mysql</groupId>
13 <artifactId>mysql-connector-java</artifactId>
14 <version>5.1.48</version>
15 </dependency>
16
17 <!--C3P0-->
18 <dependency>
19 <groupId>c3p0</groupId>
20 <artifactId>c3p0</artifactId>
21 <version>0.9.1.1</version>
22 </dependency>
23 <!--DBCP -->
24 <dependency>
25 <groupId>commons-dbcp</groupId>
26 <artifactId>commons-dbcp</artifactId>
27 <version>1.4</version>
28 </dependency>
29
30 <dependency>
31 <groupId>commons-pool</groupId>
32 <artifactId>commons-pool</artifactId>
33 <version>1.5.7</version>
34 </dependency>
35
36 <dependency>
37 <groupId>junit</groupId>
38 <artifactId>junit</artifactId>
39 <version>4.13</version>
40 </dependency>
41
42 <!-- https://mvnrepository.com/artifact/com.mchange/mchange-commons-java -->
43 <dependency>
44 <groupId>com.mchange</groupId>
45 <artifactId>mchange-commons-java</artifactId>
46 <version>0.2.15</version>
47 </dependency>
48 </dependencies>
49
50 </project>
二、DBCP
DBUtil 類
1 package com.papercy;
2
3 import org.apache.commons.dbcp.BasicDataSource;
4
5 public class DBCPUtils {
6 private static String DRIVER="com.mysql.jdbc.Driver";
7 private static String URL="jdbc:mysql://localhost/lob?useUnicode=true&characterEncoding=utf8";
8 private static String USERNAME="root";
9 private static String PASSWORD="123456";
10
11 //獲取BasicDataSource并配置穴翩，開(kāi)始....
12 private static BasicDataSource basicDataSource=new BasicDataSource();
13 static {
14 basicDataSource.setUrl(URL);
15 basicDataSource.setUsername(USERNAME);
16 basicDataSource.setPassword(PASSWORD);
17 basicDataSource.setInitialSize(10);//初始化創(chuàng)建十個(gè)鏈接
18 basicDataSource.setMaxActive(10);//允許同時(shí)10個(gè)活動(dòng)連接數(shù)
19 basicDataSource.setMaxIdle(8);//最大空閑連接數(shù)
20 basicDataSource.setMinIdle(1);//最小空閑連接數(shù)
21
22 }
23
24 public static BasicDataSource getBasicDataSource(){
25 return basicDataSource;
26 }
27 }
測(cè)試類
1 package com.papercy;
2
3 import java.sql.Connection;
4 import java.sql.PreparedStatement;
5 import java.sql.SQLException;
6
7 public class TestDBUtil {
8 public static void main(String[] args) {
9 try {
10 Connection conn=DbcpDBUtil.getDataSource().getConnection();
11 String sql="INSERT INTO student(id,`name`,age) VALUES(NULL,?,?)";
12 PreparedStatement preparedStatement=conn.prepareStatement(sql);
13 preparedStatement.setString(1,"feifeiye");
14 preparedStatement.setInt(2,88);
15 int count=preparedStatement.executeUpdate();
16
17 if (count>0){
18 System.out.println("插入成功");
19 }else{
20 System.out.println("插入失敗");
21 }
22
23 } catch (SQLException e) {
24 e.printStackTrace();
25 }
26 }
27 }
注:其中的Close方法不會(huì)真正的將連接關(guān)閉犬第，而是將其放回到連接池中，對(duì)于所有的數(shù)據(jù)源一般都會(huì)改寫(xiě)此方法（使用修飾）芒帕。
方式二：使用BasicDataSourceFactory +配置文件
配置文件dbcp.properties：
1 diverClassName=com.mysql.jdbc.Driver
2 url=jdbc:mysql://localhost:3306/lob?useUnicode=true&characterEncoding=utf8
3 user=root
4 pwd=123456
1 package com.papercy;
2
3 import org.apache.commons.dbcp.BasicDataSourceFactory;
4
5
6 import javax.sql.DataSource;
7 import java.io.FileInputStream;
8 import java.io.FileNotFoundException;
9 import java.io.IOException;
10 import java.io.InputStream;
11 import java.util.Properties;
12
13 public class DbcpDBUtil {
14 public static DataSource getDataSource(){
15 Properties properties=new Properties();
16 String path="src/dbcp.properties";
17 try {
18 InputStream inputStream=new FileInputStream(path);
19 properties.load(inputStream);
20 } catch (FileNotFoundException e) {
21 e.printStackTrace();
22 } catch (IOException e) {
23 e.printStackTrace();
24 }
25
26
27 DataSource dataSource=null;
28 try {
29 dataSource= new BasicDataSourceFactory().createDataSource(properties);
30 } catch (Exception e) {
31 e.printStackTrace();
32 }
33 return dataSource;
34 }
35 }
三歉嗓、C3P0
C3P0Util類
1 package com.papercy.c3p0;
2
3 import com.mchange.v2.c3p0.ComboPooledDataSource;
4
5 import javax.sql.DataSource;
6 import java.sql.Connection;
7 import java.sql.PreparedStatement;
8 import java.sql.ResultSet;
9 import java.sql.SQLException;
10
11 /**
12 * C3P0工具類
13 */
14 public class C3p0Utils {
15 //定義數(shù)據(jù)源對(duì)象
16 private static DataSource dataSource;
17 //獲取數(shù)據(jù)源
18 static {
19 dataSource=new ComboPooledDataSource();
20 }
21 //通過(guò)數(shù)據(jù)源獲取數(shù)據(jù)庫(kù)連接
22 public static Connection getConnection(){
23 try {
24 return dataSource.getConnection();
25 } catch (SQLException e) {
26 e.printStackTrace();
27 return null;
28 }
29 }
30 //關(guān)閉預(yù)處理對(duì)象和結(jié)果集對(duì)象
31 public static void close(PreparedStatement preparedStatement, ResultSet resultSet){
32 if (resultSet!=null){
33 try {
34 resultSet.close();
35 } catch (SQLException e) {
36 e.printStackTrace();
37 }
38 }
39
40 if (preparedStatement!=null){
41 try {
42 preparedStatement.close();
43 } catch (SQLException e) {
44 e.printStackTrace();
45 }
46 }
47
48 }
49 }
C3P0測(cè)試類
1 package com.papercy.c3p0;
2
3 import com.sun.scenario.effect.impl.sw.sse.SSEBlend_SRC_OUTPeer;
4
5 import java.sql.Connection;
6 import java.sql.PreparedStatement;
7 import java.sql.ResultSet;
8 import java.sql.SQLException;
9
10 /**
11 * 測(cè)試C3P0
12 */
13 public class TestC3p0Pools {
14 public static void main(String[] args) {
15 Connection conn=C3p0Utils.getConnection();
16 String sql="SELECT * FROM EMPLOYEES";
17 PreparedStatement preparedStatement=null;
18 ResultSet resultSet=null;
19 try {
20 preparedStatement=conn.prepareStatement(sql);
21 resultSet=preparedStatement.executeQuery();
22 while(resultSet.next()){
23 int id=resultSet.getInt("EID");
24 String firstName=resultSet.getString("FIRSTNAME");
25 String lastName=resultSet.getString("LASTNAME");
26 int age=resultSet.getInt("AGE");
27 System.out.println(id+"\t"+firstName+"\t"+lastName+"\t"+age);
28 }
29 } catch (SQLException e) {
30 e.printStackTrace();
31 }finally {
32 C3p0Utils.close(preparedStatement,resultSet);
33 }
34 }
35 }
注:
- acquireIncrement： 聲明當(dāng)連接池中連接耗盡時(shí)再一次新生成多少個(gè)連接，默認(rèn)為3個(gè)
- initialPoolSize： 當(dāng)連接池啟動(dòng)時(shí)背蟆，初始化連接的個(gè)數(shù)鉴分，必須在minPoolSize~maxPoolSize之間哮幢，默認(rèn)為3
- minPoolSize： 任何時(shí)間連接池中保存的最小連接數(shù)，默認(rèn)3
- maxPoolSize： 在任何時(shí)間連接池中所能擁有的最大連接數(shù)冠场，默認(rèn)15
- maxIdleTime： 超過(guò)多長(zhǎng)時(shí)間連接自動(dòng)銷毀家浇，默認(rèn)為0，即永遠(yuǎn)不會(huì)自動(dòng)銷毀
PS:
如果碴裙，您希望更容易地發(fā)現(xiàn)我的新博客，不妨點(diǎn)擊一下關(guān)注点额。
如果你覺(jué)得本篇文章對(duì)你有所幫助舔株，請(qǐng)給予我更多的鼓勵(lì)，
因?yàn)榛估猓业膶?xiě)作熱情也離不開(kāi)您的肯定支持载慈，感謝您的閱讀，我是【肥肥也】珍手！
