JDBC增删改查案例讲解

快来打我* 2024-03-31 11:23 101阅读 0赞

## JDBC增删改查案例讲解 ##

**简介**：这是一个网上非常常见的，JDBC的练习题，系统大家通过本文的讲解，熟悉JDBC的增删改查。

**推荐学习路线**：[JDBC数据库的连接][JDBC]\->[Connection（数据库连接对象）][Connection]\->[Driud数据库连接池的使用][Driud]\->[ResultSet][]\->[通过PreparedStatement预防SQL注入][PreparedStatement_SQL]\->[JDBC增删改查案例讲解][JDBC 1] 大家跟着敲完基本就可以JDBC基础毕业了。

题目要求为：  
完成商品品牌数据的增删改查操作，把每个操作封装成为对应函数。

*  查询：查询所有数据
 *  添加：添加品牌
 *  修改：根据id修改
 *  删除：根据id删除

### 环境准备 ###

*  数据库表 `tb_brand`  
    ![在这里插入图片描述][57864ea127fa495b9619bfd0a1d2c8e9.png]  
    ![在这里插入图片描述][c741cd02f62c4508b49717baceb13f59.png]  
    ![在这里插入图片描述][b6fedac6bbf64e2484202e577018d6cc.png]

**数据库建表语句**：

-- 删除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);

*  创建工具包`pojo`  
    ![在这里插入图片描述][1a2305497c9a4a0e8b214783c8ba2f96.png]  
    ![在这里插入图片描述][4a4e1831d803437eba3380601894c98a.png]  
    **Brand类**

package com.study.pojo;
    
    /**
     * 品牌类
     * alt + 鼠标左键：整列编辑
     * 在实体类中，基本数据类型建议使用其对应的包装类型
     */
    public class Brand {
        
        // id 主键
        private Integer id;
        // 品牌名称
        private String brandName;
        // 企业名称
        private String companyName;
        // 排序字段
        private Integer ordered;
        // 描述信息
        private String description;
        // 状态：0：禁用  1：启用
        private Integer status;
    
        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 getStatus() {
        
            return status;
        }
    
        public void setStatus(Integer status) {
        
            this.status = status;
        }
    
        @Override
        public String toString() {
        
            return "Brand{" +
                    "id=" + id +
                    ", brandName='" + brandName + '\'' +
                    ", companyName='" + companyName + '\'' +
                    ", ordered=" + ordered +
                    ", description='" + description + '\'' +
                    ", status=" + status +
                    '}';
        }
    }

### 代码讲解 ###

package com.study.jdbc;
    
    
    import com.alibaba.druid.pool.DruidDataSourceFactory;
    import com.study.pojo.Brand;
    
    import javax.sql.DataSource;
    import java.io.FileInputStream;
    import java.sql.*;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Properties;
    
    
    public class JDBCDemo {
        
        /**
         * 查询所有
         * 1. SQL：select * from tb_brand;
         * 2. 参数：不需要
         * 3. 结果：List<Brand>
         */
        public static void testSelectAll(Connection conn) throws Exception {
        
            //2. 定义SQL
            String sql = "select * from tb_brand;";
            //3. 获取pstmt对象
            PreparedStatement pstmt = conn.prepareStatement(sql);
            //4. 设置参数
            //5. 执行SQL
            ResultSet rs = pstmt.executeQuery();
            //6. 处理结果 List<Brand> 封装Brand对象，装载List集合
            Brand brand = null;
            List<Brand> brands = new ArrayList<>();
            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.setId(id);
                brand.setBrandName(brandName);
                brand.setCompanyName(companyName);
                brand.setOrdered(ordered);
                brand.setDescription(description);
                brand.setStatus(status);
    
                //装载集合
                brands.add(brand);
            }
            System.out.println(brands);
            //7. 释放资源
            rs.close();
            pstmt.close();
        }
    
        /**
         * 添加
         * 1. SQL：insert into tb_brand(brand_name, company_name, ordered, description, status) values(?,?,?,?,?);
         * 2. 参数：需要，除了id之外的所有参数信息
         * 3. 结果：boolean
         */
        public static void testAdd(Connection conn) throws Exception {
        
            // 接收页面提交的参数
            String brandName = "香飘飘";
            String companyName = "香飘飘";
            int ordered = 1;
            String description = "绕地球一圈";
            int status = 1;
    
    
            //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,brandName);
            pstmt.setString(2,companyName);
            pstmt.setInt(3,ordered);
            pstmt.setString(4,description);
            pstmt.setInt(5,status);
    
            //5. 执行SQL
            int count = pstmt.executeUpdate(); // 影响的行数
            //6. 处理结果
            System.out.println(count > 0);
    
            //7. 释放资源
            pstmt.close();
        }
    
        /**
         * 修改
         * 1. SQL：
    
         update tb_brand
         set brand_name  = ?,
         company_name= ?,
         ordered     = ?,
         description = ?,
         status      = ?
         where id = ?
    
         * 2. 参数：需要，所有数据
         * 3. 结果：boolean
         */
    
        public static void testUpdate(Connection conn) throws Exception {
        
            // 接收页面提交的参数
            String brandName = "香飘飘";
            String companyName = "香飘飘";
            int ordered = 1000;
            String description = "绕地球三圈";
            int status = 1;
            int id = 4;
    
            //2. 定义SQL
            String sql = " update tb_brand\n" +
                    "         set brand_name  = ?,\n" +
                    "         company_name= ?,\n" +
                    "         ordered     = ?,\n" +
                    "         description = ?,\n" +
                    "         status      = ?\n" +
                    "     where id = ?";
    
            //3. 获取pstmt对象
            PreparedStatement pstmt = conn.prepareStatement(sql);
    
            //4. 设置参数
            pstmt.setString(1,brandName);
            pstmt.setString(2,companyName);
            pstmt.setInt(3,ordered);
            pstmt.setString(4,description);
            pstmt.setInt(5,status);
            pstmt.setInt(6,id);
    
            //5. 执行SQL
            int count = pstmt.executeUpdate(); // 影响的行数
            //6. 处理结果
            System.out.println(count > 0);
    
            //7. 释放资源
            pstmt.close();
        }
    
        /**
         * 删除
         * 1. SQL：
         delete from tb_brand where id = ?
         * 2. 参数：需要，id
         * 3. 结果：boolean
         */
    
        public static void testDeleteById(Connection conn) throws Exception {
        
            // 接收页面提交的参数
            int id = 4;
    
            //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();
        }
    
        public static void main(String[] args) throws Exception {
        
            Properties prop = new Properties();
            prop.load(new FileInputStream("src/com/study/druid.properties"));
            //4. 获取连接池对象
            DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
    
            //5. 获取数据库连接 Connection
            Connection conn = dataSource.getConnection();
    
            // 测试查询全部
            testSelectAll(conn);
    
            // 测试添加
            testAdd(conn);
    
            // 测试更新
            testUpdate(conn);
    
            // 测试删除
            testDeleteById(conn);
    
            conn.close();  // 统一删除
        }
    }

**运行结果**：  
![在这里插入图片描述][6aa03af9f30945a8a5d309f34cbedcf5.png]

### 分模块演示 ###

#### 查询所有 ####

public void testSelectAll() throws Exception {
        
        //1. 获取Connection
        //3. 加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("jdbc-demo/src/druid.properties"));
        //4. 获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
    
        //5. 获取数据库连接 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. 处理结果 List<Brand> 封装Brand对象，装载List集合
        Brand brand = null;
        List<Brand> brands = new ArrayList<>();
        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.setId(id);
            brand.setBrandName(brandName);
            brand.setCompanyName(companyName);
            brand.setOrdered(ordered);
            brand.setDescription(description);
            brand.setStatus(status);
    
            //装载集合
            brands.add(brand);
        }
        System.out.println(brands);
        //7. 释放资源
        rs.close();
        pstmt.close();
        conn.close();
    }

#### 添加数据 ####

/**
      * 添加
      * 1. SQL：insert into tb_brand(brand_name, company_name, ordered, description, status) values(?,?,?,?,?);
      * 2. 参数：需要，除了id之外的所有参数信息
      * 3. 结果：boolean
      */
    public void testAdd() 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("jdbc-demo/src/druid.properties"));
        //4. 获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        //5. 获取数据库连接 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,brandName);
        pstmt.setString(2,companyName);
        pstmt.setInt(3,ordered);
        pstmt.setString(4,description);
        pstmt.setInt(5,status);
    
        //5. 执行SQL
        int count = pstmt.executeUpdate(); // 影响的行数
        //6. 处理结果
        System.out.println(count > 0);
    
        //7. 释放资源
        pstmt.close();
        conn.close();
    }

#### 修改数据 ####

/**
      * 修改
      * 1. SQL：
    
         update tb_brand
             set brand_name  = ?,
             company_name= ?,
             ordered     = ?,
             description = ?,
             status      = ?
         where id = ?
    
       * 2. 参数：需要，所有数据
       * 3. 结果：boolean
       */
    
    public void testUpdate() throws Exception {
        
        // 接收页面提交的参数
        String brandName = "香飘飘";
        String companyName = "香飘飘";
        int ordered = 1000;
        String description = "绕地球三圈";
        int status = 1;
        int id = 4;
    
        //1. 获取Connection
        //3. 加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("jdbc-demo/src/druid.properties"));
        //4. 获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        //5. 获取数据库连接 Connection
        Connection conn = 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. 获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
    
        //4. 设置参数
        pstmt.setString(1,brandName);
        pstmt.setString(2,companyName);
        pstmt.setInt(3,ordered);
        pstmt.setString(4,description);
        pstmt.setInt(5,status);
        pstmt.setInt(6,id);
    
        //5. 执行SQL
        int count = pstmt.executeUpdate(); // 影响的行数
        //6. 处理结果
        System.out.println(count > 0);
    
        //7. 释放资源
        pstmt.close();
        conn.close();
    }

#### 删除数据 ####

/**
      * 删除
      * 1. SQL：
                delete from tb_brand where id = ?
      * 2. 参数：需要，id
      * 3. 结果：boolean
      */
    public void testDeleteById() throws Exception {
        
        // 接收页面提交的参数
        int id = 4;
        //1. 获取Connection
        //3. 加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("jdbc-demo/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();
    }

[JDBC]: https://blog.csdn.net/qq_51447496/article/details/128018451
[Connection]: https://blog.csdn.net/qq_51447496/article/details/128026554
[Driud]: https://blog.csdn.net/qq_51447496/article/details/128027478
[ResultSet]: https://blog.csdn.net/qq_51447496/article/details/128027846
[PreparedStatement_SQL]: https://blog.csdn.net/qq_51447496/article/details/128032906
[JDBC 1]: https://blog.csdn.net/qq_51447496/article/details/128036297
[57864ea127fa495b9619bfd0a1d2c8e9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/0ce23d6129dd408a87a402da326fef51.png
[c741cd02f62c4508b49717baceb13f59.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/a5e2ed522fe44e01bbeaf6091a761e40.png
[b6fedac6bbf64e2484202e577018d6cc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/c896a8745f4e4b82a7a7e75568a92f15.png
[1a2305497c9a4a0e8b214783c8ba2f96.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/608e1189c0b74404b570023236a615ac.png
[4a4e1831d803437eba3380601894c98a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/12b44f4ecd924145b7c15b3863fb3d0c.png
[6aa03af9f30945a8a5d309f34cbedcf5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/e5a4fe25adf148bd878c1f34945a4efc.png