20220607_JDBC数据库增删改查练习

先在数据库my_temp建表如下

drop table if exists practice_brand;
create table practice_brand
(
    id      int primary key auto_increment,
    brand_name    varchar(20),
    company_name varchar(20),
    ordered int, -- 用于将来手动排序
    description varchar(100),
    status int -- 0 禁用 1 启用
);

insert into practice_brand(brand_name,company_name,ordered,description,status) values
('jia_duo_bao','herbal_tea_limited',5,'you do not burning inside',0),
 ('huawei','huawei_honor',10,'connecting something',1),
 ('xiaomi','lei_xiaomi',20,'are_you_ok',1);

select *
from practice_brand;

建一个brand类

public class Brand {
    private Integer id;
    private String brand_name;
    private String company_name;
    private Integer ordered;
    private String description;
    private Integer status;
    //数字类型成员变量不用基本类型int，而用包装类型Integer，因为后者默认值null，比前者0，更有利于实际开发


    public Brand() {
    }

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

    @Override
    public String toString() {
        return "Brand{" +
                "id=" + id +
                ", brand_name='" + brand_name + '\'' +
                ", company_name='" + company_name + '\'' +
                ", ordered=" + ordered +
                ", description='" + description + '\'' +
                ", 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 Integer getStatus() {
        return status;
    }

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

测试开始，建一个大类demo，里头用@Test注解测试方法

public class PracticeDemo {
    @Test
    public void testBrandShowAll() throws Exception {
        /**
         * JDBC操作三大思想
         * 第一，sql语句
         * 第二，要不要参数
         * 第三，返回结果怎么处理
         */
        //首先获取connection，加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("src\\druid.properties"));
        //用连接池生成连接
        DataSource ds = DruidDataSourceFactory.createDataSource(prop);
        Connection con = ds.getConnection();

        //定义sql语句
        String sql = "select * from practice_brand";
        //获取预编译对象
        PreparedStatement ppst = con.prepareStatement(sql);
        //传入参数，本例sql无需参数，那就执行吧
        ResultSet rs = ppst.executeQuery();
        //返回的rs是一个集合，把集合展示出来
        List<Brand> brs = new ArrayList<>();
        while (rs.next()){
            int id = rs.getInt("id");
            String brand_name = rs.getString("brand_name");
            String company_name = rs.getString("company_name");
            int ordered = rs.getInt("ordered");
            String description = rs.getString("description");
            int status = rs.getInt("status");

            brs.add(new Brand(id,brand_name,company_name,ordered,description,status));
        }
        //用for打印比较整齐
        for (Brand br : brs) {
            System.out.println(br);
        }
        rs.close();
        ppst.close();
        con.close();
    }
}

添加数据

    @Test
    public void testBrandAdd() throws Exception {

        //首先获取connection，加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("src\\druid.properties"));
        //用连接池生成连接
        DataSource ds = DruidDataSourceFactory.createDataSource(prop);
        Connection con = ds.getConnection();

        /**
         * 定义sql语句，此处不用有id，因为不可能让用户向数据库里添加id
         * ?占位符一定不要打单引号
         */
        String sql = "insert into practice_brand(brand_name,company_name,ordered,description,status) " +
                "values (?,?,?,?,?)";
        //获取预编译对象
        PreparedStatement ppst = con.prepareStatement(sql);
        //传入参数
        String bName = "lao_gan_ma";
        String cName = "tao_bi_hua";
        int ord = 30;
        String des = "burning over the world";
        int sta = 1;
        ppst.setString(1,bName);//这个排序按照sql语句里参数顺序排，不是按数据库里列顺序，所以不考虑id的序号
        ppst.setString(2,cName);
        ppst.setInt(3,ord);
        ppst.setString(4,des);
        ppst.setInt(5,sta);

        int count = ppst.executeUpdate();
        //返回的count是受影响的行数
        System.out.println(count > 0);
        
        ppst.close();
        con.close();
    }

修改

    @Test
    public void testBrandModify() throws Exception {

        //首先获取connection，加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("src\\druid.properties"));
        //用连接池生成连接
        DataSource ds = DruidDataSourceFactory.createDataSource(prop);
        Connection con = ds.getConnection();

        /**
         * 定义sql语句，通过数据唯一标识id修改数据，所以要用id
         * ?占位符一定不要打单引号
         */
        String sql = "update practice_brand\n" +
                "set brand_name   = ?,\n" +
                "    company_name = ?,\n" +
                "    ordered      = ?,\n" +
                "    description  = ?,\n" +
                "    status       = ?\n" +
                "where id = ?";
        //获取预编译对象
        PreparedStatement ppst = con.prepareStatement(sql);
        //传入参数
        String bName = "bili_bili";
        String cName = "bili";
        int ord = 100;
        String des = "parody is the best";
        int sta = 1;
        int id = 4;
        ppst.setString(1,bName);
        ppst.setString(2,cName);
        ppst.setInt(3,ord);
        ppst.setString(4,des);
        ppst.setInt(5,sta);
        ppst.setInt(6,id);//id在sql语句中是第六个？，所以这里参数索引为6

        int count = ppst.executeUpdate();
        //依然返回count
        System.out.println(count > 0);

        ppst.close();
        con.close();
    }

删除

    @Test
    public void testBrandDelete() throws Exception {

        //首先获取connection，加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("src\\druid.properties"));
        //用连接池生成连接
        DataSource ds = DruidDataSourceFactory.createDataSource(prop);
        Connection con = ds.getConnection();

        /**
         * 定义sql语句，通过数据唯一标识id删除数据，所以要用id
         * ?占位符一定不要打单引号
         */
        String sql = "delete\n" +
                "from practice_brand\n" +
                "where id = ?";
        //获取预编译对象
        PreparedStatement ppst = con.prepareStatement(sql);
        //传入参数
        int id = 2;

        ppst.setInt(1,id);

        int count = ppst.executeUpdate();
        //依然返回count
        System.out.println(count > 0);

        ppst.close();
        con.close();
    }

被删除的条目，id没了，其他条目id仍不变，如果变就要出大错了