1.需求

完成商品品牌数据的增删改查操作。

• 查询：查询所有数据

• 添加：添加品牌

• 修改：根据 id修改

• 删除：根据 id删除

2.环境搭建

•准备环境：1.创建项目 2.导入druid 和mysql驱动包 3.在src下面创建连接数据库的配置文件

Ø数据库表 tb_brand

Ø实体类 Brand

Ø测试用例

3.查询所有数据

1.获取Connection

2.定义SQL：select *** from tb_brand;

3.获取 PreparedStatement对象

4.设置参数：不需要

5.执行SQL

6.处理结果：List

7.释放资源

 @Test
    public void query() throws Exception{
        Properties p = new Properties();
        p.load(new FileInputStream("src\\druid.properties"));
        DataSource ds = DruidDataSourceFactory.createDataSource(p);
        //获取Connection
        Connection conn = ds.getConnection();
        //定义SQL：select * from tb_brand;
        String sql = "select * from tb_brand";
        //获取 PreparedStatement对象
        PreparedStatement pst = conn.prepareStatement(sql);
        //设置参数：不需要
        //执行SQL
        ResultSet rs = pst.executeQuery();
        //处理结果：List<Brand>
        ArrayList<Brand> list = 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);
            list.add(brand);
        }
        System.out.println("list = " + list);
        //释放资源
        rs.close();
        pst.close();
        conn.close();
    }

4.添加

@Test
    public void add() throws Exception{
        Properties p = new Properties();
        p.load(new FileInputStream("src\\druid.properties"));
        DataSource ds = DruidDataSourceFactory.createDataSource(p);
        //获取Connection
        Connection conn = ds.getConnection();
        String sql = "insert into tb_brand values(null,?,?,?,?,?)";
        //获取 PreparedStatement对象
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1,"苹果");
        pst.setString(2,"apple");
        pst.setInt(3,1);
        pst.setString(4,"贵");
        pst.setInt(5,0);
        int count = pst.executeUpdate();
        System.out.println("count = " + count);
        //释放资源
        pst.close();
        conn.close();
    }

5.修改

 @Test
    public void update() throws Exception{
        Properties p = new Properties();
        p.load(new FileInputStream("src\\druid.properties"));
        DataSource ds = DruidDataSourceFactory.createDataSource(p);
        //获取Connection
        Connection conn = ds.getConnection();
        String sql = "update tb_brand set brand_name  = ?,company_name= ?,ordered= ?,description = ?,status= ? where id = ?";
        //获取 PreparedStatement对象
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1,"OPPO");
        pst.setString(2,"OPPO");
        pst.setInt(3,1);
        pst.setString(4,"贵");
        pst.setInt(5,0);
        pst.setInt(6,3);
        int count = pst.executeUpdate();
        System.out.println("count = " + count);
        //释放资源
        pst.close();
        conn.close();
    }

6.删除

 @Test
    public void delete() throws Exception{
        Properties p = new Properties();
        p.load(new FileInputStream("src\\druid.properties"));
        DataSource ds = DruidDataSourceFactory.createDataSource(p);
        //获取Connection
        Connection conn = ds.getConnection();
        String sql = "delete from tb_brand where id = ?";
        //获取 PreparedStatement对象
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setInt(1,4);
        int count = pst.executeUpdate();
        System.out.println("count = " + count);
        //释放资源
        pst.close();
        conn.close();
    }

至此 JDBC的综合练习就完了… 增删改查就是 这么简单!~