数据库链接池（「JavaWeb学习记录08」JDBC（2）数据库连接池与案例）java教程 / Java Web应用程序中的数据库连接池管理...

#头条创作挑战赛#3.4 数据库连接池3.4.1 简介数据库连接池是一个容器，负责分配、管理数据库连接（Connection）允许应用程序重复使用一个现有的数据库连接，而不是重新建立一个释放空闲空间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏

好处资源重用提升系统响应速度避免数据库连接遗漏创建Connection对象并将其销毁的过程特别耗费计算机性能和时间数据库使用数据库连接池，达到Connection对象的重复使用

连接池是在一开始就创建好了一些连接（Connection）对象存储起来用户需要连接数据库时，不需要自己创建连接，而只需要从连接池中获取一个连接进行使用，使用完毕后再将连接对象归还给连接池；这样就可以起到资源重用，也节省了频繁创建连接销毁连接所花费的时间，从而提升了系统响应的速度。

3.4.2 数据连接池的实现1. 标准接口：DataSource提供了获取连接的功能ConnectiongetConnection() 不需要通过 DriverManager 对象获取 Connection

对象，而是通过连接池 DataSource 获取 Connection 对象2. 常见数据库连接池DBCPC3P0Druid3. DruidDruid 连接池，alibaba开源的数据库连接池项目功能强大，性能优秀，Java语言最好的数据库连接池之一

3.4.3 Druid 使用导入jar包 druid-1.1.12.jar定义配置文件加载配置文件获取数据库连接池对象获取连接

druid.properties 配置文件如下：driverClassName=com.mysql.jdbc.Driverurl=jdbc:mysql:///itcast?useSSL=false&useServerPrepStmts=true

username=rootpassword=chenmo # 初始化连接数量initialSize=5 # 最大连接数maxActive=10 # 最大等待时间maxWait=3000使用druid的代码如下：

package druid; import com.alibaba.druid.pool.DruidDataSourceFactory; import javax.sql.DataSource;

import java.io.FileInputStream; import java.sql.Connection; import java.util.Properties; /** * @Author 晨默 * @Date 2022/8/28 7:57 */

publicclassDruidDemo{ publicstatic void main(String[] args) throwsException { // 1. 导入jar包

// 2. 定义配置文件// 3. 加载配置文件Properties prop = new Properties(); prop.load(new FileInputStream("src/druid.properties"

)); // 4. 获取连接池对象DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);

// 5. 获取数据库连接 ConnectionConnection connection = dataSource.getConnection(); System.out.println

(connection); // 获取到了连接后就可以继续做其他操作了System.out.println(System.getProperty("user.dir")); } }结果如下：

3.5 JDBC案例1. 数据库表 tb_rand2. 实体类 Brandpackage pojo; import java.io.InputStream; /** * @Author 晨默 *

@Date 2022/8/28 8:27 */publicclassBrand{ // id 主键private Integer id; // 品牌名称private String brandName;

// 企业名称private String companyName; // 排序字段private Integer ordered; // 描述信息private String description;

// 状态：0：禁用 1：启用private Integer status; public Integer getId(){ return id; }

publicvoidsetId(Integer id){ this.id = id; } public String getBrandName(){

return brandName; } publicvoidsetBrandName(String brandName){ this.brandName = brandName; }

public String getCompanyName(){ return companyName; } publicvoidsetCompanyName(String companyName)

{ this.companyName = companyName; } public Integer getOrdered(){ return

ordered; } publicvoidsetOrdered(Integer ordered){ this.ordered = ordered; }

public String getDescription(){ return description; } publicvoidsetDescription(String description)

{ this.description = description; } public Integer getStatus(){ return status; }

publicvoidsetStatus(Integer status){ this.status = status; } @Overridepublic String

toString(){ return"Brand{" + "id=" + id + ", brandName=" + brandName +

\ + ", companyName=" + companyName + \ + ", ordered=" + ordered +

", description=" + description + \ + ", status=" + status + }; } }

3. 查询所有信息package pojo; import com.alibaba.druid.pool.DruidDataSourceFactory; import javax.sql.DataSource;

import java.io.FileInputStream; import java.sql.Connection; import java.sql.PreparedStatement; import

java.sql.ResultSet; import java.util.ArrayList; import java.util.List; import java.util.Properties;

/** * @Author 晨默 * @Date 2022/8/28 8:30 *//** * 查询所有数据 * 1. SQL：select * from tb_brand * 2. 参数：不需要 * 3. 结果：List */

publicclasstestSelectAll{ publicstaticvoidmain(String[] args)throws Exception { // 1. 获取Connection

// 3. 加载配置文件 Properties prop = new Properties(); prop.load(new FileInputStream("src/druid.properties"

)); // 4. 获取连接池对象 DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);

// 5. 获取数据库连接 Connection Connection conn = dataSource.getConnection(); // 2. 定义SQL String sql =

"select * from tb_rand"; // 3. 获取pstmt对象 PreparedStatement pstmt = conn.prepareStatement(sql);

// 4. 设置参数// 5. 执行SQL ResultSet rs = pstmt.executeQuery(); // 6. 处理结果 List 封装Brand对象，装载List集合

Brand brand = null; List brands = new ArrayList<>(); while(rs.next()){

// 获取数据int id = rs.getInt("id"); String brandName = rs.getString("brand_name"); String companyName = rs.getString(

"company_name"); int ordered = rs.getInt("ordered"); String description = rs.getString(

"description"); int status = rs.getInt("status"); // 封装Brand对象 brand =

new Brand(); brand.setId(id); brand.setBrandName(brandName); brand.setCompanyName(companyName); brand.setOrdered(ordered); brand.setDescription(description); brand.setStatus(status);

// 装载集合 brands.add(brand); } System.out.println(brands); // 7. 释放资源

rs.close(); pstmt.close(); conn.close(); } }// 打印结果为： [Brand{id=1, brandName=

三只松鼠, companyName=三只松鼠股份有限公司, ordered=5, description=好吃不上火, status=0}, Brand{id=2, brandName=华为, companyName=

华为技术有限公司, ordered=100, description=华为致力于把数字世界带入每个人、每个家庭、每个组织，构建万物互联的智能世界, status=1}, Brand{id=3, brandName=

小米, companyName=小米科技有限公司, ordered=50, description=are you OK, status=1}] 4. 添加数据 package pojo; /** * @Author 晨默 * @Date 2022/8/28 9:03 */

import com.alibaba.druid.pool.DruidDataSource; import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource; import java.io.FileInputStream; import java.io.FileNotFoundException;

import java.io.IOException; import java.sql.Connection; import java.sql.PreparedStatement; import java.util.Properties;

/** * 添加数据 * 1. SQL：insert into tb_brand(brand_name, company_name, ordered, description, status) values(?,?,?,?,?); * 2. 参数：需要，除了id之外的所有参数信息 * 3. 结果：boolean * */

publicclasstestAdd{ publicstatic void main(String[] args) throwsException { Properties prop = new

Properties(); prop.load(new FileInputStream("src/druid.properties")); DataSource dataSource =

DruidDataSourceFactory.createDataSource(prop); Connection connection = dataSource.getConnection();

// 接收页面提交的参数String brandName = "香飘飘"; String companyName = "香飘飘"; int ordered = 1;

String description = "绕地球一圈"; int status = 1; String sql = "insert into tb_rand(brand_name, company_name, ordered, description, status) values(?,?,?,?,?);"

; PreparedStatement pstmt = connection.prepareStatement(sql); pstmt.setString(1,brandName); pstmt.setString(

2,companyName); pstmt.setInt(3,ordered); pstmt.setString(4,description); pstmt.setInt(

5,status); int count = pstmt.executeUpdate(); System.out.println(count > 0); // true

pstmt.close(); connection.close(); } }5. 修改数据 package pojo; /** * @Author 晨默 * @Date 2022/8/28 9:37 */

import com.alibaba.druid.pool.DruidDataSource; import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource; import java.io.FileInputStream; import java.io.FileNotFoundException;

import java.io.IOException; import java.sql.Connection; import java.sql.PreparedStatement; import java.util.Properties;

/** * 修改数据 * 1. SQL： * update tb_rand * set brand_name = ?, * company_name = ?, * ordered = ?, * description = ?, * status = ? * where id = ?; * 2. 参数：需要，所有参数信息 * 3. 结果：boolean * */

publicclasstestUpdate{ publicstatic void main(String[] args) throwsException { Properties

prop = new Properties(); prop.load(new FileInputStream("src/druid.properties"));

DataSource dataSource = DruidDataSourceFactory.createDataSource(prop); Connection connection = dataSource.getConnection();

// 接收页面提交的参数String brandName = "香飘飘"; String companyName = "香飘飘"; int ordered = 1000

; String description = "绕地球一圈"; int status = 1; int id = 4; String

sql = """ update tb_rand set brand_name = ?, company_name = ?, ordered = ?, description = ?, status = ? where id = ?;"""

; PreparedStatement pstmt = connection.prepareStatement(sql); pstmt.setString(1,brandName); pstmt.setString(

2,companyName); pstmt.setInt(3,ordered); pstmt.setString(4,description); pstmt.setInt(

5,status); pstmt.setInt(6,id); int count = pstmt.executeUpdate(); System

.out.println(count > 0); // true pstmt.close(); connection.close(); } }6. 删除数据

package pojo; /** * @Author 晨默 * @Date 2022/8/28 9:48 */import com.alibaba.druid.pool.DruidDataSource;

import com.alibaba.druid.pool.DruidDataSourceFactory; import javax.sql.DataSource; import java.io.FileInputStream;

import java.io.FileNotFoundException; import java.io.IOException; import java.sql.Connection; import

java.sql.PreparedStatement; import java.sql.SQLException; import java.util.Properties; /** * 1. SQL:delete from tb_rand where id = ?; * 2. 参数： 需要，id * 3. 结果： boolean * */

publicclasstestDeleteById{ publicstatic void main(String[] args) throwsException { int id =

4; Properties prop = new Properties(); prop.load(new FileInputStream("src/druid.properties"

)); DataSource dataSource = DruidDataSourceFactory.createDataSource(prop); Connection

connection = dataSource.getConnection(); String sql = "delete from tb_rand where id = ?;"

; PreparedStatement pstmt = connection.prepareStatement(sql); pstmt.setInt(1,id); int

count = pstmt.executeUpdate(); System.out.println(count > 0); // true pstmt.close(); connection.close(); } }