11.3 基于JDBC实现的商品CRUD案例

1. 案例需求

MySQL数据库创建商品表，有主键、商品名称、商品价格信息。

使用JDBC实现商品信息的新增、修改、删除、查询所有功能，并实现MySQL数据库的操作。

2. 数据初始化

（1）创建goods数据库

create database goods default charset utf8 collate utf8_general_ci;

（2）创建t_goods表

create table t_goods  (
    id varchar(32) not null,
    name varchar(255),
    price double
);

（3）初始化数据

insert into t_goods values("1","电脑",5000); 
insert into t_goods values("2","手机",3000);

3. 导入相关jar包

导入mysql-connector-java-8.0.27.jar包。

导入junit-4.6.jar包。

4. 创建Goods实体类

public class Goods {

    private String id;
    private String name;
    private double price;

    public String getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public double getPrice() {
        return price;
    }

    public void setPrice(double price) {
        this.price = price;
    }

    @Override
    public String toString() {
        return "Goods{" +
                "id='" + id + '\'' +
                ", name='" + name + '\'' +
                ", price=" + price +
                '}';
    }
}

5. 创建GoodsController类

public class GoodsController {

    private GoodsService goodsService;

    /**
     * 新增商品信息
     * @param goods
     */
    public void addGoods(Goods goods){
        try{
            goodsService = new GoodsServiceImpl();
            goodsService.addGoods(goods);
        }catch (Exception e){
            System.out.println(e.getMessage());
        }
    }

    /**
     * 修改商品信息
     * @param goods
     */
    public void updateGoods(Goods goods){
        try{
            goodsService = new GoodsServiceImpl();
            goodsService.updateGoods(goods);
        }catch (Exception e){
            System.out.println(e.getMessage());
        }
    }

    /**
     * 删除商品信息
     * @param id
     */
    public void delGoods(String id){
        try{
            goodsService = new GoodsServiceImpl();
            goodsService.delGoods(id);
        }catch (Exception e){
            System.out.println(e.getMessage());
        }
    }

    /**
     * 查询商品信息
     * @return
     */
    public List<Goods> queryGoods(){
        List<Goods> list = new ArrayList<>();
        try{
            goodsService = new GoodsServiceImpl();
            list = goodsService.queryGoods();
        }catch (Exception e){
            System.out.println(e.getMessage());
        }
        return list;
    }

}

6. 创建GoodsService接口

public interface GoodsService {

    public void addGoods(Goods goods)throws Exception;
    public void updateGoods(Goods goods)throws Exception;
    public void delGoods(String id)throws Exception;
    public List<Goods> queryGoods()throws Exception;

}

7. 创建GoodsServiceImpl类

public class GoodsServiceImpl implements GoodsService{

    private GoodsDao goodsDao;
    /**
     * 新增商品信息
     * @param goods
     * @throws Exception
     */
    @Override
    public void addGoods(Goods goods) throws Exception {
        goodsDao = new GoodsDaoImpl();
        goodsDao.addGoods(goods);
        System.out.println("新增商品成功！");
    }
    /**
     * 修改商品信息
     * @param goods
     * @throws Exception
     */
    @Override
    public void updateGoods(Goods goods) throws Exception {
        goodsDao = new GoodsDaoImpl();
        goodsDao.updateGoods(goods);
        System.out.println("修改商品成功！");
    }

    /**
     * 删除商品信息
     * @param id
     * @throws Exception
     */
    @Override
    public void delGoods(String id) throws Exception {
        goodsDao = new GoodsDaoImpl();
        goodsDao.delGoods(id);
        System.out.println("删除商品成功！");
    }

    /**
     * 查询商品信息
     * @return
     * @throws Exception
     */
    @Override
    public List<Goods> queryGoods() throws Exception {
        goodsDao = new GoodsDaoImpl();
        return goodsDao.queryGoods();
    }
}

8. 创建Dao类

public class Dao {

    public Connection getConnection() throws Exception{
        // 1.加载驱动程序
        Class.forName("com.mysql.cj.jdbc.Driver");
        // 2.创建连接对象
        String dburl = "jdbc:mysql://127.0.0.1:3306/goods?useUnicode=true&characterEncoding=utf-8";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(dburl,username,password);
        return conn;
    }

}

9. 创建GoodsDao接口

public interface GoodsDao {

    public void addGoods(Goods goods)throws Exception;
    public void updateGoods(Goods goods)throws Exception;
    public void delGoods(String id)throws Exception;
    public List<Goods> queryGoods()throws Exception;

}

10. 创建GoodsDaoImpl实现类

public class GoodsDaoImpl extends Dao implements GoodsDao{

    /**
     * 新增商品信息
     * @param goods
     * @throws Exception
     */
    @Override
    public void addGoods(Goods goods) throws Exception {
        Connection conn = getConnection();
        String sql = "insert into t_goods values(?,?,?)";
        // 3.通过Connection对象获取Statement对象
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1,goods.getId());
        ps.setString(2,goods.getName());
        ps.setDouble(3,goods.getPrice());
        // 4. 使用Statement执行SQL语句
        ps.executeUpdate();
        // 6. 关闭连接，释放资源
        ps.close();
        conn.close();
    }

    /**
     * 修改商品信息
     * @param goods
     * @throws Exception
     */
    @Override
    public void updateGoods(Goods goods) throws Exception {
        Connection conn = getConnection();
        String sql = "update t_goods t set t.name = ? ,t.price = ? where t.id = ?";
        // 3.通过Connection对象获取Statement对象
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1,goods.getName());
        ps.setDouble(2,goods.getPrice());
        ps.setString(3,goods.getId());
        // 4.使用Statement执行SQL语句
        ps.executeUpdate();
        // 6.关闭连接，释放资源
        ps.close();
        conn.close();
    }

    /**
     * 删除商品信息
     * @param id
     * @throws Exception
     */
    @Override
    public void delGoods(String id) throws Exception {
        Connection conn = getConnection();
        String sql = "delete from t_goods t where t.id = ? ";
        // 3.通过Connection对象获取Statement对象
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, id);
        // 4.使用Statement执行SQL语句
        ps.executeUpdate();
        // 6.关闭连接，释放资源
        ps.close();
        conn.close();
    }

    /**
     * 查询商品信息
     * @return
     * @throws Exception
     */
    @Override
    public List<Goods> queryGoods() throws Exception {
        Connection conn = getConnection();
        String sql = "select * from t_goods ";
        // 3.通过Connection对象获取Statement对象
        PreparedStatement ps = conn.prepareStatement(sql);
        // 4.使用Statement执行SQL语句
        ResultSet rs = ps.executeQuery();
        List<Goods> list = new ArrayList<Goods>();
        // 5. 操作结果集
        while(rs.next()) {
            Goods goods = new Goods();
            goods.setId(rs.getString("id"));
            goods.setName(rs.getString("name"));
            goods.setPrice(rs.getDouble("price"));
            list.add(goods);
        }
        // 6.关闭连接，释放资源
        rs.close();
        ps.close();
        conn.close();
        return list;
    }
}

11. 创建测试类

public class TestGoods {

    private GoodsController goodsController;

    @Before
    public void init(){
        goodsController = new GoodsController();
    }

    /**
     * 测试商品新增
     */
    @Test
    public void testAdd(){
        Goods goods = new Goods();
        goods.setId("3");
        goods.setName("平板");
        goods.setPrice(6000);
        goodsController.addGoods(goods);
    }

    /**
     * 测试商品修改
     */
    @Test
    public void testUpdate(){
        Goods goods = new Goods();
        goods.setId("3");
        goods.setName("华为平板");
        goods.setPrice(8000);
        goodsController.updateGoods(goods);
    }

    /**
     * 测试商品删除
     */
    @Test
    public void testDel(){
        goodsController.delGoods("3");
    }

    /**
     * 测试商品查询
     */
    @Test
    public void testQuery(){
        List<Goods> list = goodsController.queryGoods();
        list.forEach(good -> System.out.println(good));
    }
}