JDBC初次总结案例练习

一. 需求

• 查询：查询所有数据

• 添加：添加品牌

• 修改：根据id修改

• 删除：根据id删除

二. 环境准备

1. 数据库表 tb_brand(商标表)

-- 删除tb_brand表
drop table if EXISTS 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,'时长两年半，玩的是花样',1),
('华为', '华为技术有限公司', 100,
 '华为致力于把数字世界带入每个人、每个家庭、每个组织，构建万物互联的智能世界', 1),
('小米', '小米科技有限公司', 50, 'are you ok',1);

结果图：

2. 在pojo包下实体类 Brand

package Web.pojo;

public class tb_brand {
    //id 主键
    public Integer id;

    //品牌名称
    public String brand_name;

    //企业名称
    public String company_name;

    //排序字段
    public Integer ordered;

    //描述信息
    public String description;

    //状态：0：禁用 1：启用
    public int status;

    public tb_brand() {
    }

    public tb_brand(Integer id, String brand_name, String company_name, Integer ordered, String description, int status) {
        this.id = id;
        this.brand_name = brand_name;
        this.company_name = company_name;
        this.ordered = ordered;
        this.description = description;
        this.status = status;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer 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 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 int getStatus() {
        return status;
    }

    public void setStatus(int status) {
        this.status = status;
    }

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

结果图：

3. 查询所有

 @Test
    public void select_Brand() throws Exception {
        //1. 配置jar和druid.properties文件,获取数据池,并请求连接
        Properties ppt = new Properties();
        ppt.load(new FileInputStream("C://myLife//src//druid.properties"));

        DataSource dataSource = DruidDataSourceFactory.createDataSource(ppt);

        Connection conn = dataSource.getConnection();

        //要查询的语句,进行预编译
        PreparedStatement pst = conn.prepareStatement("select *from tb_brand");

        //设置参数

        //执行SQL,并查看结果集

        //建立List<tb_brand>集合 存储数据
        List<Tb_brand> tb_brands =new ArrayList<Tb_brand>();
         Tb_brand brand=null;

        ResultSet resultSet = pst.executeQuery();
        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=new Tb_brand(id,brand_name,company_name,ordered,description,status);

            tb_brands.add(brand);
        }
        System.out.println(tb_brands);
        //关闭流
        pst.close();
        pst.close();
        conn.close();
    }

结果：

4. 添加数据

 @Test
    public void add_Brand()throws Exception {
        //1. 配置jar和druid.properties文件,获取数据池,并请求连接
        Properties ppt = new Properties();
        ppt.load(new FileInputStream("C://myLife//src//druid.properties"));

        DataSource dataSource = DruidDataSourceFactory.createDataSource(ppt);

        Connection conn = dataSource.getConnection();

        //要查询的语句,进行预编译
        PreparedStatement pst = conn.prepareStatement("insert into tb_brand(brand_name,company_name, ordered, description, status) values (?,?,?,?,?)");

        //设置参数
        pst.setString(1,"菜徐琨");
        pst.setString(2,"爱篮球有限公司");
        pst.setInt(3,8);
        pst.setString(4,"music，唱跳rap");
        pst.setInt(5,0);

        //执行SQL
        int i = pst.executeUpdate();

        System.out.println(i>0);

        //关闭流
        pst.close();
        conn.close();


    }

结果：

5. 修改数据

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

        //1. 获取Connection
        //3. 加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("C://myLife//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();
    }
}

结果

6. 删除数据

 @Test
    public void Delete_Brand() throws Exception {
        // 接收页面提交的参数

        //1. 获取Connection
        //3. 加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("C://myLife//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,6);

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


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

结果：