综合案例

最新推荐文章于 2024-07-22 23:37:14 发布

卿...

最新推荐文章于 2024-07-22 23:37:14 发布

阅读量236

点赞数

文章标签： mysql java

本文链接：https://blog.csdn.net/weixin_45845391/article/details/125992339

版权

商城案例表设计

通过对商城项目的部分表关系进行分析，完成数据库表的设计

表关系分析

在这里插入图片描述

建库建表

创建名为store的数据库，对应商城项目

-- 用户表
create table user(
   uid VARCHAR(32) PRIMARY KEY, -- 用户id
	 username VARCHAR(20), -- 用户名
	 password VARCHAR(20),  -- 密码
	 telephone VARCHAR(20),  -- 电话
	 birthday DATE ,   -- 生日
	 sex VARCHAR(10) -- 性别
	 );
	 
insert into user
values
('001','渣渣辉','123456','13511112222','2015-11-04','男'),
('002','药水哥','123456','13533334444','1990-02-01','男'),
('003','大明白','123456','13544445555','2015-11-03','男'),
('004','长海','123456','13566667777','2000-02-01','男'),
('005','乔杉','123456','13588889999','2000-02-01','男');

-- 订单表
CREATE TABLE orders (
  oid varchar(32)   PRIMARY KEY, -- 订单id
  ordertime datetime ,	-- 下单时间 
  total double , -- 总金额
  name varchar(20), -- 收货人姓名
  telephone varchar(20) , -- 电话
  address varchar(30) , -- 地址
  state int(11) ,  -- 订单状态
  uid varchar(32), -- 外键字段 对应用户表id
  CONSTRAINT ofk_0001 FOREIGN KEY (uid) REFERENCES user (uid)
);

-- 插入一条订单数据
INSERT INTO orders 
VALUES('order001','2019-10-11',5500,'乔杉','15512342345','皇家洗浴',0,'001');

-- 商品分类表
CREATE TABLE category (
  cid varchar(32) PRIMARY KEY,
  cname varchar(20)
); 

INSERT INTO category
VALUES ('1','手机数码'),
('2','电脑办公'),
('3','运动鞋服'),
('4','图书音像');

-- 商品表
CREATE TABLE product (
  pid varchar(32)  PRIMARY KEY,	-- 商品id
  pname varchar(50) , -- 商品名称 
  price double, -- 商品价格
  pdesc varchar(255), -- 商品描述
  pflag int(11) , -- 商品状态 1 上架 ,0 下架
  cid varchar(32) , -- 外键对应 分类表id
  KEY sfk_0001 (cid), 
  CONSTRAINT sfk_0001 FOREIGN KEY (cid) REFERENCES category (cid)
);

INSERT INTO product 
VALUES 
('1','小米6',2200,'小米 移动联通电信4G手机 双卡双待',0,'1'),
('2','华为Mate9',2599,'华为 双卡双待 高清大屏',0,'1'),
('3','OPPO11',3000,'移动联通 双4G手机',0,'1'),
('4','华为荣耀',1499,'3GB内存标准版 黑色 移动4G手机',0,'1'),
('5','华硕台式电脑',5000,'爆款直降，满千减百',0,'2'),
('6','MacBook',6688,'128GB 闪存',0,'2'),
('7','ThinkPad',4199,'轻薄系列1)',0,'2'),
('8','联想小新',4499,'14英寸超薄笔记本电脑',0,'2'),
('9','李宁音速6',500,'实战篮球鞋',0,'3'),
('10','AJ11',3300,'乔丹实战系列',0,'3'),
('11','AJ1',5800,'精神小伙系列',0,'3');

-- 订单项表
CREATE TABLE orderitem (
  itemid VARCHAR(32) PRIMARY KEY, -- 订单项ID
  pid VARCHAR(32),  -- 外键 对应商品表 id
  oid VARCHAR(32), -- 外键 对应订单表 id
  KEY fk_0001 (pid),
  KEY fk_0002 (oid),
  CONSTRAINT fk_0001 FOREIGN KEY (pid) REFERENCES product (pid),
  CONSTRAINT fk_0002 FOREIGN KEY (oid) REFERENCES orders (oid)
);

-- 向中间表中插入两条数据
INSERT INTO orderitem VALUES('item001','1','order001');
INSERT INTO orderitem VALUES('item002','11','order001');

表与表之间的关系

在这里插入图片描述

环境搭建

项目结构

com.app 测试包用于对DAO代码进行测试
com.dao dao包数据访问层，包含所有对数据库的相关操作的类
com.entity 实体包保存根据数据库表对应创建的JavaBean类
com.utils 工具包
在这里插入图片描述

导入所需jar包

我们只需要导入myjar仓库到项目中就可以了

JavaBean类创建

设计用户与订单

一对多关系分析

在Java一对多的数据关系中，需要遵循一下设计原则：
1.Java类的名称 = 实体表的名称
2.Java类的属性 = 实体表的字段
3.Java类的一个对象 = 表的一行记录
4.外键关系 = 引用配置
一个用户拥有多个订单，所以用户是一的一方，订单是多的一方

Orders类设计分析

第一种方式
根据两张表的关系描述我们可以在订单类中添加一个UID 成员变量，表示订单属于哪个用户
```
private String uid;
```
但这样设计会存在一些问题，比如我要查询的是订单属于哪个用户的用户名，但是我们只有一个uid
第二种方式
Java类表示一对多关系，可以在多的一方添加一个成员变量，这个成员变量的类型就是一的一方的类型
再在订单表中添加一个User对象，User对象中，保存该订单关联的用户的所有信息

private String uid;
private User user;

设计商品与分类

分类与商品同样是一对多关系，我们可以在多的一方进行操作添加一个成员变量类型时一的一方的类型
在这里插入图片描述

User类

package com.entity;


import java.util.Date;

/**
 * user表
 *  uid VARCHAR(32) PRIMARY KEY, -- 用户id
 * 	 username VARCHAR(20), -- 用户名
 * 	 password VARCHAR(20),  -- 密码
 * 	 telephone VARCHAR(20),  -- 电话
 * 	 birthday DATE ,   -- 生日
 * 	 sex VARCHAR(10) -- 性别
 */
public class User {

    private String uid ;
    private String username;
    private String password ;
    private String telephone ;
    private String birthday ;
    private String sex ;

    public User() {
    }

    public User(String uid, String username, String password, String telephone, String birthday, String sex) {
        this.uid = uid;
        this.username = username;
        this.password = password;
        this.telephone = telephone;
        this.birthday = birthday;
        this.sex = sex;
    }

    public String getUid() {
        return uid;
    }

    public void setUid(String uid) {
        this.uid = uid;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getTelephone() {
        return telephone;
    }

    public void setTelephone(String telephone) {
        this.telephone = telephone;
    }

    public String getBirthday() {

        return birthday;
    }

    public void setBirthday(String birthday) {
        this.birthday = birthday;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    @Override
    public String toString() {
        return "User{" +
                "uid='" + uid + '\'' +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", telephone='" + telephone + '\'' +
                ", birthday=" + birthday +
                ", sex='" + sex + '\'' +
                '}';
    }
}

Orders类

package com.entity;


/**
 *
 * 订单表Orders
 * oid varchar(32)   PRIMARY KEY, -- 订单id
 *   ordertime datetime ,	-- 下单时间
 *   total double , -- 总金额
 *   name varchar(20), -- 收货人姓名
 *   telephone varchar(20) , -- 电话
 *   address varchar(30) , -- 地址
 *   state int(11) ,  -- 订单状态
 *   uid varchar(32), -- 外键字段 对应用户表id
 */
public class Orders {

    private String oid ;
    private String ordertime ;
    private double total ;
    private  String name ;
    private  String telephone ;
    private String address ;
    private int state ;
    private String uid ;
    private User user;

    public Orders() {
    }

    public Orders(String oid, String ordertime, double total, String name, String telephone, String address, int state, String uid, User user) {
        this.oid = oid;
        this.ordertime = ordertime;
        this.total = total;
        this.name = name;
        this.telephone = telephone;
        this.address = address;
        this.state = state;
        this.uid = uid;
        this.user = user;
    }

    public String getOid() {
        return oid;
    }

    public void setOid(String oid) {
        this.oid = oid;
    }

    public String getOrdertime() {
        return ordertime;
    }

    public void setOrdertime(String ordertime) {
        this.ordertime = ordertime;
    }

    public double getTotal() {
        return total;
    }

    public void setTotal(double total) {
        this.total = total;
    }

    public String getName() {
        return name;
    }

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

    public String getTelephone() {
        return telephone;
    }

    public void setTelephone(String telephone) {
        this.telephone = telephone;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public int getState() {
        return state;
    }

    public void setState(int state) {
        this.state = state;
    }

    public String getUid() {
        return uid;
    }

    public void setUid(String uid) {
        this.uid = uid;
    }

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    @Override
    public String toString() {
        return "Orders{" +
                "oid='" + oid + '\'' +
                ", ordertime='" + ordertime + '\'' +
                ", total=" + total +
                ", name='" + name + '\'' +
                ", telephone='" + telephone + '\'' +
                ", address='" + address + '\'' +
                ", state=" + state +
                ", uid='" + uid + '\'' +
                '}';
    }
}

Category类

package com.entity;

/**
 * 分类表对应的JavaBean
 *cid varchar(32) PRIMARY KEY,
 *   cname varchar(20)
 */
public class Category {

    private String cid ;
    private String cname ;

    public Category() {
    }

    public Category(String cid, String cname) {
        this.cid = cid;
        this.cname = cname;
    }

    public String getCid() {
        return cid;
    }

    public void setCid(String cid) {
        this.cid = cid;
    }

    public String getCname() {
        return cname;
    }

    public void setCname(String cname) {
        this.cname = cname;
    }

    @Override
    public String toString() {
        return "Category{" +
                "cid='" + cid + '\'' +
                ", cname='" + cname + '\'' +
                '}';
    }
}

Product类

package com.entity;

/**
 *  product表
 *  pid varchar(32)  PRIMARY KEY,	-- 商品id
 *   pname varchar(50) , -- 商品名称
 *   price double, -- 商品价格
 *   pdesc varchar(255), -- 商品描述
 *   pflag int(11) , -- 商品状态 1 上架 ,0 下架
 *   cid varchar(32) , -- 外键对应 分类表id
 */
public class Product {

    private String pid ;
    private String pname ;
    private double price ;
    private String pdesc ;
    private int pflag ;
    private String cid ;
    private Category category ;

    public Product() {
    }

    public Product(String pid, String pname, double price, String pdesc, int pflag, String cid, Category category) {
        this.pid = pid;
        this.pname = pname;
        this.price = price;
        this.pdesc = pdesc;
        this.pflag = pflag;
        this.cid = cid;
        this.category = category;
    }

    public String getPid() {
        return pid;
    }

    public void setPid(String pid) {
        this.pid = pid;
    }

    public String getPname() {
        return pname;
    }

    public void setPname(String pname) {
        this.pname = pname;
    }

    public double getPrice() {
        return price;
    }

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

    public String getPdesc() {
        return pdesc;
    }

    public void setPdesc(String pdesc) {
        this.pdesc = pdesc;
    }

    public int getPflag() {
        return pflag;
    }

    public void setPflag(int pflag) {
        this.pflag = pflag;
    }

    public String getCid() {
        return cid;
    }

    public void setCid(String cid) {
        this.cid = cid;
    }

    public Category getCategory() {
        return category;
    }

    public void setCategory(Category category) {
        this.category = category;
    }

    @Override
    public String toString() {
        return "Product{" +
                "pid='" + pid + '\'' +
                ", pname='" + pname + '\'' +
                ", price=" + price +
                ", pdesc='" + pdesc + '\'' +
                ", pflag=" + pflag +
                ", cid='" + cid + '\'' +
                ", category=" + category +
                '}';
    }



}

设计订单项

多对多关系分析

商品与订单是多对多关系，一个订单上可以有多个商品，一个商品可以出现在多个订单中
多对多建表原则需要一张中间表，中间表中至少有两个字段，作为中间表的外键分别指向另外两张表的主键

在这里插入图片描述
中间表记录了，一个订单中所包含的商品信息

订单项表

package com.entity;


/**
 * orderitem表
 *  itemid VARCHAR(32) PRIMARY KEY, -- 订单项ID
 *   pid VARCHAR(32),  -- 外键 对应商品表 id
 *   oid VARCHAR(32), -- 外键 对应订单表 id
 */
public class OrderItem {
    
    private String itemid ;
    private String pid ;
    private String oid ;
    private Product product ;
    private Orders order ;
    
    
    public OrderItem() {
    }

    public OrderItem(String itemid, String pid, String oid, Product product, Orders order) {
        this.itemid = itemid;
        this.pid = pid;
        this.oid = oid;
        this.product = product;
        this.order = order;
    }

    public String getItemid() {
        return itemid;
    }

    public void setItemid(String itemid) {
        this.itemid = itemid;
    }

    public String getPid() {
        return pid;
    }

    public void setPid(String pid) {
        this.pid = pid;
    }

    public String getOid() {
        return oid;
    }

    public void setOid(String oid) {
        this.oid = oid;
    }

    public Product getProduct() {
        return product;
    }

    public void setProduct(Product product) {
        this.product = product;
    }

    public Orders getOrder() {
        return order;
    }

    public void setOrder(Orders order) {
        this.order = order;
    }

    @Override
    public String toString() {
        return "OrderItem{" +
                "itemid='" + itemid + '\'' +
                ", pid='" + pid + '\'' +
                ", oid='" + oid + '\'' +
                '}';
    }
}

编写DAO类

UserDao

需求一：编写一个注册用户的方法，接收的参数是一个User对象
需求二：编写一个用户登录的方法，接收的参数是用户名和密码，返回值是User对象

编写UserDAO

package com.dao;

import com.entity.User;
import com.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;

import java.sql.SQLException;

public class UserDao {

//    需求一：编写一个注册用户的方法，接收的参数是一个User对象

    public int register(User user) throws SQLException {

//        获取QueryRunner对象
        QueryRunner qr = new QueryRunner(DruidUtils.dataSource) ;

//        编写SQL
        String sql = "insert into user values(?,?,?,?,?,?)" ;

        Object[] param = {user.getUid(),user.getUsername(),user.getPassword(),user.getTelephone(),user.getBirthday(),user.getSex()};

//        执行插入操作
        int update = qr.update(sql, param);

        return update;
    }

//    需求二：编写一个用户登录的方法，接收的参数是用户名和密码 ， 返回值是User对象

    public User login(String username, String password) throws SQLException {

//        获取QueryRunner对象
        QueryRunner qr = new QueryRunner(DruidUtils.dataSource) ;

//        编写SQL
        String sql = "select * from user where username = ? and password = ?" ;

//        执行查询 使用BeanHanler来封装结果集，获取结果集中的第一条数据
        User user = qr.query(sql, new BeanHandler<User>(User.class), username, password);

        return user ;

    }
}

TestUserDAO类

package com.app;

import com.dao.UserDao;
import com.entity.User;
import com.utils.DateUtils;
import com.utils.UUIDUtils;
import org.junit.Test;

import java.sql.SQLException;

public class TestUserDao {

//    测试注册用户
    UserDao userDao = new UserDao();

    @Test
    public void testRegister() throws SQLException {

//        创建user
        User user = new User();
        user.setUid(UUIDUtils.getUUID());
        user.setUsername("武松");
        user.setPassword("123");
        user.setTelephone("13512341234");
        user.setSex("男");
        user.setBirthday(DateUtils.getDateFormart());

//        执行注册
        int register = userDao.register(user);

        if (register > 0){
            System.out.println("注册成功！欢迎您：" + user.getUsername());

        }else {
            System.out.println("注册失败");
        }
    }


//    用户登录测试
    @Test
    public void testLogin() throws SQLException {
//        调用UserDao的Login方法 传入用户名和密码
        User user = userDao.login("武松", "123");

//        判断user是否为空
        if (user != null){
            System.out.println("登录成功！欢迎您：" + user.getUsername());
        }else {
            System.out.println("登录失败，用户名或密码错误！");
        }
    }
}

ProductDao

需求一：根据商品ID获取商品名称，商品价格以及商品所属分类的名称
参数pid，返回值product对象
需求二：根据分类ID获取商品分类信息
参数cid，返回值category对象
需求三：查询指定分类ID下的商品个数
参数cid，返回值int类型商品个数
需求四：查询指定分类ID下的所有商品信息
参数分类ID，返回值List集合集合中保存商品对象

编写ProductDao

package com.dao;

import com.entity.Category;
import com.entity.Product;
import com.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class ProductDao {

//    需求一：根据商品ID获取商品名称 ，商品价格 以及商品所属分类的名称

    public Product findProductById(String pid) throws SQLException {
//       创建QueryRunner
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource()) ;

//        编写SQL
        String sql = "select * from product where pid = ?" ;

//        执行查询
        Product product = qr.query(sql, new BeanHandler<Product>(Product.class), pid);


//        获取外键的值 对应分类的ID
        String cid = product.getCid();

//        商品对应的 分类信息
        Category category = findCategoryById(cid);

        product.setCategory(category);

        return product ;

    }

//    需求二：根据分类ID，查询分类的详细信息
    public Category findCategoryById(String cid) throws SQLException {

        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource()) ;

        String sql = "select * from category where cid = ?" ;

        Category category = qr.query(sql, new BeanHandler<Category>(Category.class), cid);

        return category ;
    }

//    需求三：查询指定分类ID下的商品个数

    public int getCount(String cid) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource()) ;

        String sql = "select count(*) from product  where cid = ?" ;

//        获取单列数据 使用scalarHandler 来封装
        Long count = qr.query(sql, new ScalarHandler<>(), cid);


//        将long类型的包装类 转换为int类型 并返回
        return count.intValue();
    }


//    需求四：查询指定分类ID下的所有商品信息
    public List<Product> findProductByCid(String cid) throws SQLException {

        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource()) ;

        String sql = "select * from product where cid = ?" ;

        List<Product> list = qr.query(sql, new BeanListHandler<Product>(Product.class), cid);

        return list ;
    }

}

TestProductDao类

package com.app;

import com.dao.ProductDao;
import com.entity.Product;
import org.junit.Test;

import java.sql.SQLException;
import java.util.List;

public class TestProductDao {

    ProductDao productDao = new ProductDao() ;
//    测试根据商品ID获取商品名称 ，商品价格 以及商品所属分类的名称

    @Test
    public void testFindProductById() throws SQLException {

        Product product = productDao.findProductById("1");

        System.out.println(product.getPname() + "  " + product.getPrice() + "  " + product.getCategory().getCname());
    }


//    测试 查询指定分类Id 下的商品个数
    @Test
    public void testgetCount() throws SQLException {

//        查询分类ID为3的分类 有几个商品

        int count = productDao.getCount("3") ;

        System.out.println("分类ID为3的商品个数是：" + count);
    }


//    测试 查询指定分类Id下的所有商品信息
    @Test
    public void testfindProductByCid() throws SQLException {

//        查询分类 Id为2 的所有商品信息
        List<Product> list = productDao.findProductByCid("2");

        for (Product product : list){
            System.out.println(product);
        }
    }

}

OrdersDao

多对一分析

OrderItem表与Orders表的关系是多对一
在这里插入图片描述
订单项表中多条数据可能属于同一个订单的
使用Java类描述多对一关系时，可以在一的一方添加一个集合集合中保存的就是多的一方的数据

创建OrdersDao

需求1：获取uid为001的用户的所有订单信息
参数uid，返回值List<Orders>订单集合
需求2：获取订单编号为：order001的订单中的所有商品信息
参数oid,返回值List<Product>商品集合

package com.dao;

import com.entity.OrderItem;
import com.entity.Orders;
import com.entity.Product;
import com.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class OrdersDao {

    //需求1: 获取 uid为 001 的用户的所有订单信息
    public List<Orders> findAllOrders(String uid) throws SQLException {

        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

        String sql = "select * from orders where uid = ?";

        //一个用户所有的订单信息
        List<Orders> ordersList = qr.query(sql, new BeanListHandler<Orders>(Orders.class), uid);

        return ordersList;
    }

    //需求2: 获取订单编号为 order001的订单中的所有商品信息
    public List<Product> findOrderById(String oid) throws SQLException {

        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

        //1.查询订单项表 获取订单项表中 订单ID为order001的数据
        String sql = "SELECT pid FROM orderitem WHERE oid = ? ";

        //2.查询的结果是 多条订单项数据
        List<OrderItem> list = qr.query(sql, new BeanListHandler<OrderItem>(OrderItem.class), oid);

        //3.创建集合保存商品信息
        List<Product> productList = new ArrayList<>();

        ProductDao productDao = new ProductDao();

        //4.遍历订单项集合 获取Pid
        for (OrderItem orderItem : list) {

            //4.1从orderitem中获取 pid
            String pid = orderItem.getPid();

            //4.2 调用productDao
            Product product = productDao.findProductById(pid);

            //4.3 保存到集合
            productList.add(product);
        }

        //返回 订单中对应的商品信息
        return productList;
    }


}

TestOrdersDao 类

package com.app;

import com.dao.OrdersDao;
import com.entity.Orders;
import com.entity.Product;
import org.junit.Test;
import java.sql.SQLException;
import java.util.List;

public class TestOrdersDao {

    OrdersDao ordersDao = new OrdersDao();
    @Test
    public void testFindAllOrders() throws SQLException {

        List<Orders> allOrders = ordersDao.findAllOrders("001");

        for (Orders orders : allOrders){
            System.out.println(orders);
        }
    }

//    测试 获取订单编号为order001的订单中的所有商品信息
    @Test
    public void testFindOrderById() throws SQLException {

        List<Product> order001 = ordersDao.findOrderById("order001");

        for (Product product : order001){

            System.out.println(product);
        }

    }
}