利用Druid实现对数据库的一些基本操作

文章目录

一、数据库表的基本信息

-- 删除tb_brand表
DROP TABLE IF EXISTS tb_brand;
-- 创建tb_brand表
CREATE TABLE tb_brand
(
    -- id 主键
    id           INT PRIMARY KEY AUTO_INCREMENT,
    -- 品牌名称
    brand_name   VARCHAR(20),
    -- 企业名称
    company_name VARCHAR(20),
    -- 排序字段
    ordered      INT,
    -- 描述信息
    description  VARCHAR(100),
    -- 状态：0：禁用  1：启用
    STATUS       INT
);
-- 添加数据
INSERT INTO tb_brand (brand_name, company_name, ordered, description, STATUS)
VALUES ('三只松鼠', '三只松鼠股份有限公司', 5, '好吃不上火', 0),
       ('华为', '华为技术有限公司', 100, '华为致力于把数字世界带入每个人、每个家庭、每个组织，构建万物互联的智能世界', 1),
       ('小米', '小米科技有限公司', 50, 'are you ok', 1);

二、使用步骤

1.导入jar包

2.配置环境

点击添加为库

选择级别为：模块库

 修改配置文件：

        修改url(即修改的数据库)

        自行修改username和password

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

3.java代码实现

Brand类

package cn.Subject;

/**
 * @author wzk
 */
public class Brand {
    // id 主键
    int id;
    // 品牌名称
    String brand_name;
    // 企业名称
    String company_name;
    // 排序字段
    int ordered;
    // 描述信息
    String description;
    // 状态：0：禁用  1：启用
    Integer STATUS;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getBrand_name() {
        return brand_name;
    }

    public void setBrand_name(String brand_name) {
        this.brand_name = brand_name;
    }

    public String getCompany_name() {
        return company_name;
    }

    public void setCompany_name(String company_name) {
        this.company_name = company_name;
    }

    public int getOrdered() {
        return ordered;
    }

    public void setOrdered(int ordered) {
        this.ordered = ordered;
    }

    public String getDescription() {
        return description;
    }

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

    public Integer getSTATUS() {
        return STATUS;
    }

    public void setSTATUS(Integer STATUS) {
        this.STATUS = STATUS;
    }

    @Override
    public String toString() {
        return "Brand{" +
                "id=" + id +
                ", brand_name='" + brand_name + '\'' +
                ", company_name='" + company_name + '\'' +
                ", ordered=" + ordered +
                ", description='" + description + '\'' +
                ", STATUS=" + STATUS +
                '}';
    }
}

实现功能

package cn.Druid;

import cn.Subject.Brand;
import com.alibaba.druid.pool.DruidDataSourceFactory;
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.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * @author wzk
 */
public class Druid_BrandTest {
    /**
    *   查询所有
    *   1.sql：select * from tb_brand
    *   2.参数：无
    *   3.结果 List<Brand>
    */

    @Test
    public void testSelectAll() throws Exception {
        Properties prop=new Properties();
        prop.load(new FileInputStream("E:\\java\\JDBC\\src\\druid.properties"));

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

        //获取数据库连接 Connection
        Connection conn = dataSource.getConnection();
        String sql="select * from tb_brand";
        PreparedStatement pstat = conn.prepareStatement(sql);

        ResultSet rs = pstat.executeQuery();

        Brand brand = null;
        List<Brand> list = new ArrayList<Brand>();
        while(rs.next()){
            //获取数据
            int id = rs.getInt("id");
            String brand_name = rs.getString("brand_name");
            String company_name = rs.getString("company_name");
            int ordered = rs.getInt("ordered");
            String description = rs.getString("description");
            int status = rs.getInt("status");

            //封装对象
            brand=new Brand();
            brand.setId(id);
            brand.setBrand_name(brand_name);
            brand.setCompany_name(company_name);
            brand.setOrdered(ordered);
            brand.setDescription(description);
            brand.setSTATUS(status);

            //纳入集合中
            list.add(brand);
        }

        System.out.println(list);

        rs.close();
        pstat.close();
        conn.close();
    }

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

    @Test
    public void testAdd() throws Exception {
        // 接收页面提交的参数
        String brandName = "香飘飘";
        String companyName = "香飘飘";
        int ordered = 1;
        String description = "绕地球一圈";
        int status = 1;


        Properties prop=new Properties();
        prop.load(new FileInputStream("E:\\java\\JDBC\\src\\druid.properties"));

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

        //获取数据库连接 Connection
        Connection conn = dataSource.getConnection();
        String sql = "insert into tb_brand(brand_name, company_name, ordered, description, status) values(?,?,?,?,?);";
        PreparedStatement pstat = conn.prepareStatement(sql);

        //4. 设置参数
        pstat.setString(1,brandName);
        pstat.setString(2,companyName);
        pstat.setInt(3,ordered);
        pstat.setString(4,description);
        pstat.setInt(5,status);

        Brand brand = null;

        //执行SQL
        int count = pstat.executeUpdate(); // 影响的行数
        //处理结果
        System.out.println(count > 0);


        //释放资源
        pstat.close();
        conn.close();
    }

    /**
     * 修改
     * 1. SQL：

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



     * 2. 参数：需要，所有数据
     * 3. 结果：boolean
     */

    @Test
    public void testUpdate() throws Exception {
        // 接收页面提交的参数
        String brandName = "香飘飘";
        String companyName = "香飘飘";
        int ordered = 1000;
        String description = "绕地球三圈";
        int status = 1;
        int id = 4;

        //加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("E:\\java\\JDBC\\src\\druid.properties"));

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

        //获取数据库连接 Connection
        Connection conn = dataSource.getConnection();

        //定义SQL
        String sql = " update tb_brand\n" +
                "         set brand_name  = ?,\n" +
                "         company_name= ?,\n" +
                "         ordered     = ?,\n" +
                "         description = ?,\n" +
                "         status      = ?\n" +
                "     where id = ?";

        //获取pstmt对象
        PreparedStatement pstmt = conn.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);

        //执行SQL
        int count = pstmt.executeUpdate(); // 影响的行数
        //处理结果
        System.out.println(count > 0);


        //释放资源
        pstmt.close();
        conn.close();

    }

    /**
     * 删除
     * 1. SQL：

     delete from tb_brand where id = ?

     * 2. 参数：需要，id
     * 3. 结果：boolean
     */

    @Test
    public void testDeleteById() throws Exception {
        // 接收页面提交的参数
        int id = 4;


        //1. 获取Connection
        //3. 加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("E:\\java\\JDBC\\src\\druid.properties"));
        //4. 获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);

        //5. 获取数据库连接 Connection
        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. 执行SQL
        int count = pstmt.executeUpdate(); // 影响的行数
        //6. 处理结果
        System.out.println(count > 0);


        //7. 释放资源
        pstmt.close();
        conn.close();
    }
}