JAVA Web学习笔记9 JDBC练习

Brand对象

package pojo;

public class Brand {
    // id 主键
    //int有默认值为0 所以最好用integer（由于是对象所以默认值为null）
    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 +
                '}';
    }
}

SQL：

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


SELECT * FROM tb_brand;

查询数据

public class brandTest {
    @Test
    public void testSelectAll() throws Exception {

        //获取数据库连接conn
        Properties prop=new Properties();
        prop.load(new FileInputStream("src/druid.properties"));
        DataSource dataSource= DruidDataSourceFactory.createDataSource(prop);
        Connection conn=dataSource.getConnection();

        //定义SQL字符串
        String sql="select * from tb_brand;";

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

        //设置参数

        //执行SQL
        ResultSet rs=pstmt.executeQuery();

        //处理结果list<Brand> 封装Brand对象，装载list集合
        Brand brand=null;//放在while外节省内存空间
        List<Brand> brands=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");
            //封装对象
            brand =new Brand();
            brand.setId(id);
            brand.setBrandName(brand_name);
            brand.setCompanyName(company_name);
            brand.setDescription(description);
            brand.setStatus(status);
            brand.setOrdered(ordered);
            //装载到集合中
            brands.add(brand);
        }

        System.out.println(brands);
        //释放资源
        rs.close();
        pstmt.close();
        conn.close();

    }
}

插入新数据

public class brandTest {
    @Test
    public void testSelectAll() throws Exception {
        //页面提交的参数
        String brandName="香飘飘";
        String companyName="香飘飘";
        int ordered=1;
        String description="绕地球一周";
        int status=1;

        //获取数据库连接conn
        Properties prop=new Properties();
        prop.load(new FileInputStream("src/druid.properties"));
        DataSource dataSource= DruidDataSourceFactory.createDataSource(prop);
        Connection conn=dataSource.getConnection();

        //定义SQL字符串
        String sql="insert into tb_brand(brand_name,company_name,ordered,description,status) values(?,?,?,?,?)";

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

        //设置参数
        pstmt.setString(1,brandName);
        pstmt.setString(2,companyName);
        pstmt.setInt(3,ordered);
        pstmt.setString(4,description);
        pstmt.setInt(5,status);

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

        //处理结果
        System.out.println(count>0);

        //释放资源

        pstmt.close();
        conn.close();

    }
}

public class brandTest {

        @Test
        public void testSelectAll() throws Exception {
            //页面提交的参数
            String brandName="香飘飘";
            String companyName="香飘飘";
            int ordered=1;
            String description="绕地球一周";
            int status=1;

            //获取数据库连接conn
            Properties prop=new Properties();
            prop.load(new FileInputStream("src/druid.properties"));
            DataSource dataSource= DruidDataSourceFactory.createDataSource(prop);
            Connection conn=dataSource.getConnection();

            //定义SQL字符串
            String sql="insert into tb_brand(brand_name,company_name,ordered,description,status) values(?,?,?,?,?)";

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

            //设置参数
            pstmt.setString(1,brandName);
            pstmt.setString(2,companyName);
            pstmt.setInt(3,ordered);
            pstmt.setString(4,description);
            pstmt.setInt(5,status);

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

            //处理结果
            System.out.println(count>0);

            //释放资源

            pstmt.close();
            conn.close();

        }
    /*
    update tb_brand
        set brand_name=?,
        company_name=?,
        ordered=?,
        description=?,
        status=?
    where id=?
     */
    @Test
    public void testUpdate() throws Exception {
        //页面提交的参数
        String brandName="香飘飘";
        String companyName="香飘飘";
        int ordered=1000;
        String description="绕地球三周";
        int status=1;
        int id=4;
        //获取数据库连接conn
        Properties prop=new Properties();
        prop.load(new FileInputStream("src/druid.properties"));
        DataSource dataSource= DruidDataSourceFactory.createDataSource(prop);
        Connection conn=dataSource.getConnection();

        //定义SQL字符串
        String sql="update tb_brand\n" +
                "        set brand_name=?,\n" +
                "        company_name=?,\n" +
                "        ordered=?,\n" +
                "        description=?,\n" +
                "        status=?\n" +
                "    where id=?";

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

        //设置参数
        pstmt.setString(1,brandName);
        pstmt.setString(2,companyName);
        pstmt.setInt(3,ordered);
        pstmt.setString(4,description);
        pstmt.setInt(5,status);
        pstmt.setInt(6,id);
        //执行SQL
        int  count=pstmt.executeUpdate();//sql影响的行数

        //处理结果
        System.out.println(count>0);

        //释放资源

        pstmt.close();
        conn.close();

    }
    /*
    delete from tb_brand where id = ?
     */
    @Test
    public void testDeleteById() throws Exception {
        //页面提交的参数
        int id=4;
        //获取数据库连接conn
        Properties prop=new Properties();
        prop.load(new FileInputStream("src/druid.properties"));
        DataSource dataSource= DruidDataSourceFactory.createDataSource(prop);
        Connection conn=dataSource.getConnection();

        //定义SQL字符串
        String sql="delete from tb_brand where id = ?";

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

        //设置参数
        pstmt.setInt(1,id);

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

        //处理结果
        System.out.println(count>0);

        //释放资源

        pstmt.close();
        conn.close();

    }
    }