一、数据库连接池简介

二、数据库连接池基本实现

下载地址：https://repo1.maven.org/maven2/com/alibaba/druid/

连接配置文件

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://192.168.5.222:3306/test?useServerPrepStmts=true
username=root
password=123456
# 初始化连接数
initialSize=5
# 最大连接数
maxActive=10
# 超时时间
maxWait=3000

连接测试

package Driud;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.util.Properties;


//import 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/src/druid.properties"));
        //4、获取连接池对象
        DataSource dataSource= DruidDataSourceFactory.createDataSource(prop);
        //5.获取对象的数据库链接
        Connection connection = dataSource.getConnection();
        System.out.println(connection);

    }

}

实操训练

建立数据表 tb_brand

CREATE TABLE `test`.`tb_brand`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `brand_name` varchar(20) NULL,
  `company_name` varchar(20) NULL,
  `ordered` int NULL,
  `description` varchar(100) NULL,
  `status` int NULL,
  PRIMARY KEY (`id`)
)

插入数据

INSERT INTO `test`.`tb_brand` (`brand_name`, `company_name`, `ordered`, `description`, `status`) VALUES ('三只松鼠', '三只松鼠有限公司', 5, '好吃u不上火', 0)
INSERT INTO `test`.`tb_brand` (`brand_name`, `company_name`, `ordered`, `description`, `status`) VALUES ('华为', '华为技术有限公司', 100, '华为致力于。。。。。。。。。', 1)
INSERT INTO `test`.`tb_brand` (`brand_name`, `company_name`, `ordered`, `description`, `status`) VALUES ('小米', '小米科技有限公司', 50, 'are you ok!', 1)

创建实体类Brand

public class Brand {
    private Integer  id;
    private String brandName;
    private String companyName;
    private Integer ordered ;
    private String description;
    private Integer ststus ;

    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 String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public Integer getStstus() {
        return ststus;
    }

    public void setStstus(Integer ststus) {
        this.ststus = ststus;
    }

    @Override
    public String toString() {
        return "Brand{" +
                "id=" + id +
                ", brandName='" + brandName + ''' +
                ", companyName='" + companyName + ''' +
                ", ordered=" + ordered +
                ", description='" + description + ''' +
                ", ststus=" + ststus +
                '}';
    }
}

建立测试类进行品牌数据的增删改查操作

public class BrandTest {
}

一、练习查询所有数据

package example;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.testng.annotations.Test;
import pojo.Brand;

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;


public class BrandTest {
    
    @Test
    public void TestSelectAll() 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 = dataSource.getConnection();

        //2.定义sql语句
        String sql="Select * from tb_brand;";

        //3.获取pstmt对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        //4.设置参数

        //5.执行sql
        ResultSet resultSet = preparedStatement.executeQuery();

        //6.处理结果存到list
        List brandlist=new ArrayList();
        Brand brand = new Brand();
        while (resultSet.next()){
            //获取数据
            int id= resultSet.getInt("id");
            String brand_name = resultSet.getString("brand_name");
            String company_name = resultSet.getString("company_name");
            int ordered = resultSet.getInt("ordered");
            String description = resultSet.getString("description");
            int status = resultSet.getInt("status");
            //封装Brand对象

            brand.setBrandName(brand_name);
            brand.setCompanyName(company_name);
            brand.setOrdered(ordered);
            brand.setDescription(description);
            brand.setStstus(status);
            //装载集合
            brandlist.add(brand);
        }
        System.out.println(brandlist);
        //7.释放资源
        resultSet.close();
        preparedStatement.close();

    }
}

运行结果

6月 25, 2022 6:56:50 下午 com.alibaba.druid.pool.DruidDataSource info
信息: {dataSource-1} inited
[Brand{id=null, brandName='小米', companyName='小米科技有限公司', ordered=50, description='are you ok!', ststus=1}, Brand{id=null, brandName='小米', companyName='小米科技有限公司', ordered=50, description='are you ok!', ststus=1}, Brand{id=null, brandName='小米', companyName='小米科技有限公司', ordered=50, description='are you ok!', ststus=1}]

===============================================
Default Suite
Total tests run: 1, Passes: 1, Failures: 0, Skips: 0
===============================================


进程已结束,退出代码0

进阶联系增删改查

插入

INSERT INTO `test`.`tb_brand` (`brand_name`, `company_name`, `ordered`, `description`, `status`) VALUES (?, ?, ?, ?, ?);

@Test
    
    public void TestSelectADD() throws Exception {

        
        String brandName="香飘飘";
        String companyName="香飘飘";
        int ordered=1;
        String description="绕地球一圈";
        int status=1;
        //1.获取数据库连接connection
        //3、加载配置文件
        Properties prop=new Properties();
        prop.load(new FileInputStream("src/druid.properties"));
        //4、获取连接池对象
        DataSource dataSource= DruidDataSourceFactory.createDataSource(prop);
        //5.获取对象的数据库链接
        Connection connection = dataSource.getConnection();

        //2.定义sql语句
        String sql="INSERT INTO `test`.`tb_brand` (`brand_name`, `company_name`, `ordered`, `description`, `status`) VALUES (?, ?, ?, ?, ?);";

        //3.获取pstmt对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        //4.设置参数
        preparedStatement.setString(1,brandName);
        preparedStatement.setString(2,companyName);
        preparedStatement.setInt(3,ordered);
        preparedStatement.setString(4,description);
        preparedStatement.setInt(5,status);
        //5.执行sql
        int i = preparedStatement.executeUpdate();//影响行数
        //6.处理结果
        if (i>0){
            System.out.println("插入陈工");
        }else {
            System.out.println("插入失败");
        }
        //7.释放资源

        preparedStatement.close();
        connection.close();

    }

执行结果

6月 25, 2022 8:08:03 下午 com.alibaba.druid.pool.DruidDataSource info
信息: {dataSource-1} inited
插入陈工

===============================================
Default Suite
Total tests run: 1, Passes: 1, Failures: 0, Skips: 0
===============================================


进程已结束,退出代码0

修改

update tb_brand set brand_name=?,company_name?,ordered=?,description=?,status=? where id=?;

@Test
    
    public void TestSelectupdate() throws Exception {

        
        String brandName="香飘飘";
        String companyName="香飘飘";
        int ordered=1;
        String description="绕地球一圈";
        int status=1;
        //1.获取数据库连接connection
        //3、加载配置文件
        Properties prop=new Properties();
        prop.load(new FileInputStream("src/druid.properties"));
        //4、获取连接池对象
        DataSource dataSource= DruidDataSourceFactory.createDataSource(prop);
        //5.获取对象的数据库链接
        Connection connection = dataSource.getConnection();

        //2.定义sql语句
        String sql="update tb_brand set brand_name=?,company_name=?,ordered=?,description=?,status=? where id=?;";

        //3.获取pstmt对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        //4.设置参数
        preparedStatement.setString(1,brandName);
        preparedStatement.setString(2,companyName);
        preparedStatement.setInt(3,ordered);
        preparedStatement.setString(4,description);
        preparedStatement.setInt(5,status);
        preparedStatement.setInt(6,2);
        //5.执行sql
        int i = preparedStatement.executeUpdate();//影响行数
        //6.处理结果
        if (i>0){
            System.out.println("插入陈工");
        }else {
            System.out.println("插入失败");
        }
        //7.释放资源

        preparedStatement.close();
        connection.close();

    }

从实战学习Druid(德鲁伊）数据库连接池图文教程，大量学习源码到手即用

一、数据库连接池简介

Java相关栏目本月热门文章