JDBC简单实操(Druid连接池)

目录结构

数据库

-- 删除 tb_brand表
drop table if exists tb_brand;
-- 创建 tb_brand表
create table tb_brand
(
  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),
       ('小米', '小米科技有限公司', 51, 'are you ok', 1);

配置文件

实体类pojo

package com.xiaobo.pojo;

/*
实体类
*/
public class Brand {
    private Integer id;
    private String brandName;
    private String companyName;
    private Integer ordered;
    private String description;
    private Integer status;


    public Brand() {
    }

    public Brand(Integer id, String brandName, String companyName, Integer ordered, String description, Integer status) {
        this.id = id;
        this.brandName = brandName;
        this.companyName = companyName;
        this.ordered = ordered;
        this.description = description;
        this.status = status;
    }

    /**
     * 获取
     * @return id
     */
    public Integer getId() {
        return id;
    }

    /**
     * 设置
     * @param id
     */
    public void setId(Integer id) {
        this.id = id;
    }

    /**
     * 获取
     * @return brandName
     */
    public String getBrandName() {
        return brandName;
    }

    /**
     * 设置
     * @param brandName
     */
    public void setBrandName(String brandName) {
        this.brandName = brandName;
    }

    /**
     * 获取
     * @return companyName
     */
    public String getCompanyName() {
        return companyName;
    }

    /**
     * 设置
     * @param companyName
     */
    public void setCompanyName(String companyName) {
        this.companyName = companyName;
    }

    /**
     * 获取
     * @return ordered
     */
    public Integer getOrdered() {
        return ordered;
    }

    /**
     * 设置
     * @param ordered
     */
    public void setOrdered(Integer ordered) {
        this.ordered = ordered;
    }

    /**
     * 获取
     * @return description
     */
    public String getDescription() {
        return description;
    }

    /**
     * 设置
     * @param description
     */
    public void setDescription(String description) {
        this.description = description;
    }

    /**
     * 获取
     * @return status
     */
    public Integer getStatus() {
        return status;
    }

    /**
     * 设置
     * @param status
     */
    public void setStatus(Integer status) {
        this.status = status;
    }

    public String toString() {
        return "Brand{id = " + id + ", brandName = " + brandName + ", companyName = " + companyName + ", ordered = " + ordered + ", description = " + description + ", status = " + status + "}";
    }
}

测试类BrandTest

package com.xiaobo.test;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.xiaobo.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.Properties;
import java.util.Scanner;

public class BrandTest {
    private static Scanner sc = new Scanner(System.in);

    public static void main(String[] args) throws Exception {
    	// 随便测测
        queryAllBrands();
        deleteBrand();
        queryAllBrands();
    }

    // 查询所有品牌
    private static void queryAllBrands() throws Exception {
        // 创建List集合对象存储Brand对象
        ArrayList<Brand> list = new ArrayList<>();

        // 1 创建数据库连接池对象
        Properties p = new Properties();
        p.load(new FileInputStream("src\\druid.properties"));// 关联配置文件
        DataSource ds = DruidDataSourceFactory.createDataSource(p); // 数据库连接池对象
        // 2 获取连接
        Connection conn = ds.getConnection();
        // 3 获取预编译对象
        PreparedStatement pst = conn.prepareStatement("select * from tb_brand order by ordered");
        // 4 执行sql
        ResultSet rs = pst.executeQuery(); // 执行查询语句
        // 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(id, brandName, companyName, ordered, description, status);
            list.add(brand);
        }
        // 遍历结果
        for (Brand brand : list) {
            System.out.println(brand);
        }

        // 6 释放资源
        rs.close();
        pst.close();
        conn.close();
    }

    // 添加品牌
    private static void addBrand() throws Exception {

        System.out.println("请输入品牌名称:");
        String brandName = sc.next();

        System.out.println("请输入公司名称:");
        String companyName = sc.next();

        System.out.println("请输入排序优先级(1-100 越小越优先):");
        int ordered = sc.nextInt();

        System.out.println("请输入品牌描述:");
        String description = sc.next();

        System.out.println("请输入状态 0:禁用 1:启用");
        int status = sc.nextInt();

        Properties p = new Properties();
        p.load(new FileInputStream("src\\druid.properties"));
        DataSource ds = DruidDataSourceFactory.createDataSource(p);

        Connection conn = ds.getConnection();

        PreparedStatement pst = conn.prepareStatement("insert into tb_brand(brand_name, company_name, ordered, description, status) " +
                "values(?, ?, ?, ?, ?)");

        pst.setString(1, brandName);
        pst.setString(2, companyName);
        pst.setInt(3, ordered);
        pst.setString(4, description);
        pst.setInt(5, status);

        int r = pst.executeUpdate(); // 执行增删改语句

        System.out.printf("影响的行数: %d\n", r);

        pst.close();
        conn.close();
    }

    // 修改品牌 排序优先级+1
    private static void updateBrand() throws Exception {

        System.out.println("请输入您要修改的品牌名:");
        String brandName = sc.next();

        Properties p = new Properties();
        p.load(new FileInputStream("src\\druid.properties"));
        DataSource ds = DruidDataSourceFactory.createDataSource(p);

        Connection conn = ds.getConnection();

        PreparedStatement pst = conn.prepareStatement("update tb_brand set ordered = ordered + 1 where brand_name = ?");

        pst.setString(1, brandName);

        int r = pst.executeUpdate();
        System.out.printf("影响的行数: %d\n", r);

        pst.close();
        conn.close();
    }

    // 删除品牌
    private static void deleteBrand() throws Exception {
        System.out.println("请输入您要删除的品牌名:");
        String brandName = sc.next();

        Properties p = new Properties();
        p.load(new FileInputStream("src\\druid.properties"));
        DataSource ds = DruidDataSourceFactory.createDataSource(p);

        Connection conn = ds.getConnection();

        PreparedStatement pst = conn.prepareStatement("delete from tb_brand where brand_name = ?");

        pst.setString(1, brandName);

        int r = pst.executeUpdate();
        System.out.printf("影响的行数: %d\n", r);

        pst.close();
        conn.close();
    }
}