周六 jdbc练习 商品品牌数据增删改查

先把练习用的表建立出来

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


SELECT * FROM tb_brand;

然后是主类Brand

package pojo;

/**
 * 品牌
 */
public class Brand {
    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 String getBrandName() {
        return brandName;
    }

    public String getCompanyName() {
        return companyName;
    }

    public Integer getOrdered() {
        return ordered;
    }

    public String getDescription() {
        return description;
    }

    public Integer getStatus() {
        return status;
    }

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

    public void setBrandName(String brandName) {
        this.brandName = brandName;
    }

    public void setCompanyName(String companyName) {
        this.companyName = companyName;
    }

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

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

    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 +
                '}';
    }
}

之后为了方便测试，整了一下junit，然后就可以敲主要内容了

package example.example;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.jupiter.api.Test;
import pojo.Brand;

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.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.ResourceBundle;

/**
 * 品牌数据增删改查
 */
public class Brandtest {
    /**
     * 查询所有
     * 1.sql:select * from tb_brand
     * 2.参数：不需要
     * 3.结果：list<Brand>
     */
    @Test
    public void testSelectall() throws Exception {
        //1.获取connection
        Properties prop=new Properties();
        prop.load(new FileInputStream("D:\\66\\jdbc_demo\\src\\druid.properties"));
        DataSource dataSource= DruidDataSourceFactory.createDataSource(prop);
        Connection conn=dataSource.getConnection();
        //2.定义sql
        String sql="select * from tb_brand";
        PreparedStatement psmt =conn.prepareStatement(sql);
        //4.设置参数
        //5.执行sql
        ResultSet rs =psmt.executeQuery();
        //6.处理结果 list<Brand>
        Brand brand=new Brand();
        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.setId(id);
            brand.setBrandName(brandName);
            brand.setCompanyName(companyName);
            brand.setDescription(description);
            brand.setStatus(status);
            brand.setOrdered(ordered);
            //装载集合
            brands.add(brand);
        }
        //7.释放资源
        rs.close();
        psmt.close();
        conn.close();

        System.out.println(brands);

    }


    /**
     * 添加
     * 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="theshy";
        String companyName="wbg";
        int ordered=1;
        String  description="别吃，别吃";
        int status=1;
        //1.获取connection
        Properties prop=new Properties();
        prop.load(new FileInputStream("D:\\66\\jdbc_demo\\src\\druid.properties"));
        DataSource dataSource= DruidDataSourceFactory.createDataSource(prop);
        Connection conn=dataSource.getConnection();
        //2.定义sql
        String sql="insert into tb_brand(brand_name, company_name, ordered, description, status) values (?,?,?,?,?);";
        PreparedStatement psmt =conn.prepareStatement(sql);
        //4.设置参数
        psmt.setString(1,brandName);
        psmt.setString(2,companyName);
        psmt.setInt(3,ordered);
        psmt.setString(4,description);
        psmt.setInt(5,status);
        //5.执行sql
        int count=psmt.executeUpdate();//影响的行数
        //6.处理结果
        System.out.println(count>0);

        //7.释放资源
        psmt.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="theshy";
        String companyName="wbg";
        int ordered=1000;
        String  description="别吃，别吃";
        int status=1;
        int id=4;
        //1.获取connection
        Properties prop=new Properties();
        prop.load(new FileInputStream("D:\\66\\jdbc_demo\\src\\druid.properties"));
        DataSource dataSource= DruidDataSourceFactory.createDataSource(prop);
        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=?;";
        PreparedStatement psmt =conn.prepareStatement(sql);
        //4.设置参数
        psmt.setString(1,brandName);
        psmt.setString(2,companyName);
        psmt.setInt(3,ordered);
        psmt.setString(4,description);
        psmt.setInt(5,status);
        psmt.setInt(6,id);
        //5.执行sql
        int count=psmt.executeUpdate();//影响的行数
        //6.处理结果
        System.out.println(count>0);

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


    }



    /**
     * 删除
     * 1.sql:delete from tb_brand where id=?;
     * 2.参数：需要id
     * 3.结果：boolean
     */
    @Test
    public void testdelete() throws Exception {
        int id=4;
        //1.获取connection
        Properties prop=new Properties();
        prop.load(new FileInputStream("D:\\66\\jdbc_demo\\src\\druid.properties"));
        DataSource dataSource= DruidDataSourceFactory.createDataSource(prop);
        Connection conn=dataSource.getConnection();
        //2.定义sql
        String sql="delete from tb_brand where id=?;";
        PreparedStatement psmt =conn.prepareStatement(sql);
        //4.设置参数
        psmt.setInt(1,id);
        //5.执行sql
        int count=psmt.executeUpdate();//影响的行数
        //6.处理结果
        System.out.println(count>0);

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


    }





}