JavaWeb学习Day3——数据库连接池以及JDBC练习

未来的技术大佬
58 阅读2分钟

携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第12天，点击查看活动详情

1.数据库连接池简介

  • 数据库连接池是个容器，负责分配、管理数据库连接(Connection)

  • 它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个；

  • 释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏

  • 好处

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

image.png

标准接口：==DataSource==

官方(SUN) 提供的数据库连接池标准接口，由第三方组织实现此接口。该接口提供了获取连接的功能：

Connection getConnection()

那么以后就不需要通过 DriverManager 对象获取 Connection 对象，而是通过连接池（DataSource）获取 Connection 对象。

  • 我们现在使用更多的是Druid，它的性能比其他两个会好一些。

  • Druid（德鲁伊）

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

Driud使用

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

编写配置文件如下：

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///test?useSSL=false&useServerPrepStmts=true
username=root
password=123456
#初始舒适连接数
initialSize=5
#最大连接数
maxActive=10
#最大等待时间
maxWait=3000

使用druid的代码Demo：

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

    }
}

案例实现

查询所有

public class BrandTest {
    @Test
    public void testSelectAll() throws Exception{
        //1.获取Connection对象
        Properties properties = new Properties();
        properties.load(new FileInputStream("src/druid.properties"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        Connection connection = dataSource.getConnection();
        //2.sql语句
        String sql = "select * from tb_brand";
        //3.获取PreparedStatement对象
        PreparedStatement pstmt = connection.prepareStatement(sql);
        //4.查询
        ResultSet rs = pstmt.executeQuery();
        List<Brand> list =new ArrayList<>();
        //5.对查询结果操作
        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.setBrandName(brandName);
            brand.setId(id);
            brand.setCompanyName(companyName);
            brand.setOrdered(ordered);
            brand.setDescription(description);
            brand.setStatus(status);
            list.add(brand);
        }
        System.out.println(list);
        //释放资源
        rs.close();
        pstmt.close();
        connection.close();

    }
}

添加数据

@Test
public void testAdd() throws Exception{
    String brandName = "香飘飘";
    String companyName = "香飘飘";
    int ordered = 1;
    String description = "绕地球一圈";
    int status = 1;
    //1.获取Connection对象
    Properties properties = new Properties();
    properties.load(new FileInputStream("src/druid.properties"));
    DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
    Connection connection = dataSource.getConnection();
    //2.sql语句
    String sql = "insert into tb_brand(brand_name, company_name, ordered, description, status) values(?,?,?,?,?);";
    //3.获取PreparedStatement对象
    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);
    //4.查询
    int count=pstmt.executeUpdate();

    //5.对查询结果操作
    if(count>0){
        System.out.println("添加成功");
    }else {
        System.out.println("添加失败");
    }

    //释放资源

    pstmt.close();
    connection.close();

}

修改数据

@Test
public void testUpdate() throws Exception{
    String brandName = "香飘飘";
    String companyName = "香飘飘";
    int ordered = 1000;
    String description = "绕地球三圈";
    int status = 1;
    int id = 4;

    //1.获取Connection对象
    Properties properties = new Properties();
    properties.load(new FileInputStream("src/druid.properties"));
    DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
    Connection connection = dataSource.getConnection();
    //2.sql语句
    String sql = " update tb_brand\n" +
            "         set brand_name  = ?,\n" +
            "         company_name= ?,\n" +
            "         ordered     = ?,\n" +
            "         description = ?,\n" +
            "         status      = ?\n" +
            "     where id = ?";
    //3.获取PreparedStatement对象
    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);
    //4.查询
    int count=pstmt.executeUpdate();

    //5.对查询结果操作
    if(count>0){
        System.out.println("修改成功");
    }else {
        System.out.println("修改失败");
    }

    //释放资源

    pstmt.close();
    connection.close();

}

删除数据

@Test
public void testDeleteById() throws Exception{
    int id = 4;

    //1.获取Connection对象
    Properties properties = new Properties();
    properties.load(new FileInputStream("src/druid.properties"));
    DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
    Connection connection = dataSource.getConnection();
    //2.sql语句
    String sql = "delete from tb_brand where id = ?";
    //3.获取PreparedStatement对象
    PreparedStatement pstmt = connection.prepareStatement(sql);
    pstmt.setInt(1,id);

    //4.查询
    int count=pstmt.executeUpdate();

    //5.对查询结果操作
    if(count>0){
        System.out.println("删除成功");
    }else {
        System.out.println("删除失败");
    }
    //释放资源
    pstmt.close();
    connection.close();
}
avatar
文章
阅读
粉丝