JAVAWEB学习笔记--Day5

目录

数据库连接池

Druid使用步骤

练习：完成游戏品牌数据的增删改查操作

数据库连接池

数据库连接池简介

• 数据库连接池是个容器，负责分配、管理数据库连接(Connection)
• 它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个
• 释放空间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏
• 好处：

1. 资源重用
2. 提升系统响应速度
3. 避免数据库连接遗漏

数据库连接池实现

标准接口：DataSource

• 官方（SUN）提供的数据库连接池标准接口，由第三方组织实现此接口
• 功能：获取连接

Connection getConnection()

常见的数据库连接池：

• DBCP
• C3P0
• Druid

Druid（德鲁伊）

• Druid连接池时阿里巴巴开源的数据库连接池项目
• 功能强大，性能优越，时Java语言最好的数据库连接池之一

Druid使用步骤

/*
* Druid 数据库连接池演示
* */
public class DruidDemo {public static void main(String[] args) throws Exception {//1.导入jar包//2.定义配置文件//3.加载配置文件Properties prop = new Properties();prop.load(new FileInputStream("JDBC_demo/src/druid.properties"));//4.获取连接池对象DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);//5.获取对应地数据库连接ConnectionConnection connection = dataSource.getConnection();System.out.println(connection);}
}

德鲁伊连接池jar包下载地址：https://repo1.maven.org/maven2/com/alibaba/druid/

练习：完成游戏品牌数据的增删改查操作

• 查询： 查询所有数据

• 添加：添加品牌

• 修改：根据id修改

• 删除：根据id删除

准备环境

1.创建一个数据库表tb_brand

 2.实体类Brand

public class Brand {private Integer id;private String brandName;private String companyName;private Integer ordered;private  Integer score;public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getBrandName() {return brandName;}public void setBrandName(String brandName) {this.brandName = brandName;}public String getCompanyName() {return companyName;}public void setCompanyName(String companyName) {this.companyName = companyName;}public Integer getOrdered() {return ordered;}public void setOrdered(Integer ordered) {this.ordered = ordered;}public Integer getSocre() {return socre;}public void setScore(Integer score) {this.socre = score;}@Overridepublic String toString() {return "Brand{" +"id=" + id +", brandName='" + brandName + '\'' +", companyName='" + companyName + '\'' +", ordered=" + ordered +", score=" + score +'}';}
}

3.测试用例

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.jdbc.pojo.Brand;
import org.junit.jupiter.api.Test;import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;/*
* 品牌数据的增删改查
* */
public class BrandTest {/** 查询所有* 1.SQL：select * from tb_brand* 2.参数：不需要* 3.返回结果：List<Brand>* */@Testpublic void testSelectAll() throws Exception {//1.获取Connection对象Properties prop = new Properties();prop.load(new FileInputStream("src/druid.properties"));DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);Connection conn = dataSource.getConnection();//2.定义SQL语句String sql = "select * from tb_brand";//3.获取pstmt对象PreparedStatement pstmt = conn.prepareStatement(sql);//4.设置参数//5.执行sqlResultSet rs = pstmt.executeQuery();//6.处理结果List<Brand> 封装Brand对象，装载List集合Brand brand = null;List<Brand> 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");int score = rs.getInt("score");//封装Brand对象brand = new Brand();brand.setId(id);brand.setBrandName(brandName);brand.setCompanyName(companyName);brand.setOrdered(ordered);brand.setSocre(score);//封装集合brands.add(brand);}System.out.println(brands);//7.释放资源rs.close();pstmt.close();conn.close();}
}

执行结果

增删改查练习

添加数据：

/** 添加* 1.SQL:insert into tb_brand(brand_name,company_name,ordered,score) values(?,?,?,?)* 2.需要参数，除了id之外的所有参数* 3.结果：boolean* */@Testpublic void testAdd() throws Exception {//接受页面提交的参数String brandName = "Assassin's Creed Unity";String companyName = "育碧娱乐软件公司";int ordered = 1;int score = 90;//1.获取Connection对象Properties prop = new Properties();prop.load(new FileInputStream("src/druid.properties"));DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);Connection conn = dataSource.getConnection();//2.定义SQL语句String sql = "insert into tb_brand(brand_name,company_name,ordered,score) values(?,?,?,?)";//3.获取pstmt对象PreparedStatement pstmt = conn.prepareStatement(sql);//4.设置参数pstmt.setString(1,brandName);pstmt.setString(2,companyName);pstmt.setInt(3,ordered);pstmt.setInt(4,score);//5.执行sqlint count = pstmt.executeUpdate(); //影响的行数//6.处理结果System.out.println(count > 0);//7.释放资源pstmt.close();conn.close();}

修改：根据id修改

/** 修改* 1.SQL：update tb_brand set brand_name = ?,company_name = ?,ordered= ?,score = ? where id = ?* 2.参数：需要* 3.结果：boolean* */@Testpublic void testUpdate() throws Exception {//接受页面提交的参数String brandName = "Assassin's Creed Unity";String companyName = "育碧娱乐软件公司";int ordered = 10;int score = 90;int id = 4;//1.获取Connection对象Properties prop = new Properties();prop.load(new FileInputStream("src/druid.properties"));DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);Connection conn = dataSource.getConnection();//2.定义SQL语句String sql = "update tb_brand set brand_name = ?,company_name = ?,ordered= ?,score = ? where id = ?";//3.获取pstmt对象PreparedStatement pstmt = conn.prepareStatement(sql);//4.设置参数pstmt.setString(1,brandName);pstmt.setString(2,companyName);pstmt.setInt(3,ordered);pstmt.setInt(4,score);pstmt.setInt(5,id);//5.执行sqlint count = pstmt.executeUpdate(); //影响的行数//6.处理结果System.out.println(count > 0);//7.释放资源pstmt.close();conn.close();}

删除：根据id删除

/** 修改* 1.SQL：delete from tb_brand where id = ?* 2.参数：需要* 3.结果：boolean* */@Testpublic void testDelete() throws Exception {//接受页面提交的参数String brandName = "Assassin's Creed Unity";String companyName = "育碧娱乐软件公司";int ordered = 10;int score = 90;int id = 4;//1.获取Connection对象Properties prop = new Properties();prop.load(new FileInputStream("src/druid.properties"));DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);Connection conn = dataSource.getConnection();//2.定义SQL语句String sql = "delete from tb_brand where id = ?";//3.获取pstmt对象PreparedStatement pstmt = conn.prepareStatement(sql);//4.设置参数pstmt.setInt(1,id);//5.执行sqlint count = pstmt.executeUpdate(); //影响的行数//6.处理结果System.out.println(count > 0);//7.释放资源pstmt.close();conn.close();}