Connection getConnection()
                  throws SQLException

package dao;

import pojo.Brand;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.Properties;

/**
 * 品牌数据的增删改查操作
 */
public class BrandDAO {

    private static DataSource dataSource;

    // 获取Connection:
    static {
        try {
            // - 导入jar包 & 定义配置文件
            // - 加载配置文件
            Properties prop = new Properties();
            prop.load(new FileInputStream("demo/src/druid.properties"));
            // - 获取连接池对象
            dataSource = DruidDataSourceFactory.createDataSource(prop);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 查询所有
     * 1. SQL: SELECT * FROM tb_brand
     * 2. 参数: 不需要
     * 3. 结果: ArrayList<Brand>
     */
    public ArrayList<Brand> selectAll() throws Exception{
        // 1. 获取数据库连接Connection
        Connection conn = dataSource.getConnection();

        // *2. 定义SQL
        String sql = "SELECT * FROM tb_brand";

        // 3. 获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
      
        // *4. 设置参数
      
        // 5. 执行SQL
        ResultSet rs = pstmt.executeQuery();

        // *6. 处理结果: ArrayList<Brand>
        Brand brand = null;
        ArrayList<Brand> brands = new ArrayList<>();
        // - 遍历结果集
        while (rs.next()) {
            // 1. 获取数据
            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");

            // 2. 封装Brand对象
            brand = new Brand();
            brand.setId(id);
            brand.setCompanyName(companyName);
            brand.setOrdered(ordered);
            brand.setDescription(description);
            brand.setStatus(status);

            // 3. 装载ArrayList集合
            brands.add(brand);
        }

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

        // 8. 返回结果
        return brands;
    }

    /**
     * 添加
     * 1. SQL:
         INSERT INTO tb_brand(
            brand_name,
            company_name,
            ordered,
            description,
            status)
         VALUES(?,?,?,?,?)
     * 2. 参数: 除了id之外的所有参数信息
     * 3. 结果: boolean
     */
    public boolean add(Brand brand) throws Exception{
        // 1. 获取数据库连接Connection
        Connection conn = dataSource.getConnection();

        // *2. 定义SQL
        String sql = """
                INSERT INTO tb_brand(
                    brand_name,
                    company_name,
                    ordered,
                    description,
                    status)
                VALUES(?,?,?,?,?)""";

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

        // *4. 设置参数
        pstmt.setString(1, brand.getBrandName());
        pstmt.setString(2, brand.getCompanyName());
        pstmt.setInt(3, brand.getOrdered());
        pstmt.setString(4, brand.getDescription());
        pstmt.setInt(5, brand.getStatus());

        // 5. 执行SQL
        int count = pstmt.executeUpdate(); // 返回影响的行数

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

        // 7. 返回结果
        return count > 0;
    }

    /**
     * 修改
     * 1. SQL:
         UPDATE tb_brand
         SET brand_name=?,
            company_name=?,
            ordered=?,
            description=?,
            status=?
         WHERE id=?
     * 2. 参数: 所有
     * 3. 结果: boolean
     */
    public boolean update(Brand brand) throws Exception{
        // 1. 获取数据库连接Connection
        Connection conn = dataSource.getConnection();

        // *2. 定义SQL
        String sql = """
                UPDATE tb_brand
                SET brand_name=?,
                    company_name=?,
                    ordered=?,
                    description=?,
                    status=?
                WHERE id=?""";

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

        // *4. 设置参数
        pstmt.setString(1, brand.getBrandName());
        pstmt.setString(2, brand.getCompanyName());
        pstmt.setInt(3, brand.getOrdered());
        pstmt.setString(4, brand.getDescription());
        pstmt.setInt(5, brand.getStatus());
        pstmt.setInt(6, brand.getId());

        // 5. 执行SQL
        int count = pstmt.executeUpdate(); // 返回影响的行数

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

        // 8. 返回结果
        return count > 0;
    }

    /**
     * 删除
     * 1. SQL:DELETE FROM tb_brand WHERE id=?
     * 2. 参数: id
     * 3. 结果: boolean
     */
    public boolean deleteById(int id) throws Exception{
        // 1. 获取数据库连接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. 释放资源
        pstmt.close();
        conn.close();

        // 7. 返回结果
        return count > 0;
    }
}