30 综合案例

1 商城案例表设计

下面是商城项目的部分表关系，见下图：

按照上面的表关系和表字段建数据库和表，代码如下：

create database db6 character set utf8;
-- 用户表
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) , -- 外键对应 分类表cid
  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');

2 JavaBean类创建

设计用户和订单

用户表和订单表是一对多的关系，需要遵循以下设计原则：

• Java类的名称=实体表的名称
• Java类的属性=实体表的字段
• Java类的一个对象=表的一条记录
• 外键关系=引用配置

User类

package entity;

public class User {
    private String uid;
    private String username;
    private String password;
    private String telephone;
    private String birthday;
    private String 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 entity;

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 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 entity;

public class Category {
    private String cid;
    private String 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 entity;

public class Product {
    private String pid;
    private String pname;
    private String price;
    private String pdesc;
    private String pflag;
    private String cid;
    private 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 String getPrice() {
        return price;
    }

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

    public String getPdesc() {
        return pdesc;
    }

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

    public String getPflag() {
        return pflag;
    }

    public void setPflag(String 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 + '\'' +
                '}';
    }
}

设计订单项

商品与订单之间是多对多的关系，对于多对多建表原则就是需要一张中间表，中间表至少有两个字段，作为中间表的外键分别指向两张表的主键。

OrderItem类

package entity;

public class OrderItem {
    private String itemid;
    private String pid;
    private String oid;
    private Product product;
    private Orders orders;

    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 getOrders() {
        return orders;
    }

    public void setOrders(Orders orders) {
        this.orders = orders;
    }

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

3 编写DAO类

UserDao类

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

package dao;

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

import java.sql.SQLException;

public class UserDao {
    public int register(User user) throws SQLException {
        QueryRunner qr=new QueryRunner(DruidUtils.getDataSource());
        String sql="insert into USER values(?,?,?,?,?,?)";
        Object[] ob={user.getUid(),user.getUsername(),user.getPassword(),user.getTelephone(),user.getBirthday(),user.getSex()};
        int i = qr.update(sql, ob);
        return i;

    }
    public User login(String username,String password) throws SQLException {
        QueryRunner qr=new QueryRunner(DruidUtils.getDataSource());
        String sql="select * from USER where username=? and password=?";
        User user = qr.query(sql, new BeanHandler<User>(User.class), username, password);
        return user;
    }
}

ProductDao类

需求：根据商品id获取商品名称，价格，以及商品所属分类名称；根据分类id获取商品分类信息；查询指定分类id下的商品个数；查询指定分类下的所有商品。

package dao;

import entity.Category;
import entity.Product;
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 utils.DruidUtils;

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

public class ProductDao {
    public Product findByPid(String pid) throws SQLException {
        QueryRunner qr=new QueryRunner(DruidUtils.getDataSource());
        String sql="select * from product where pid=?";
        Product res = qr.query(sql, new BeanHandler<Product>(Product.class), pid);
        String cid=res.getCid();
        Category cat = findCategoryById(cid);
        res.setCategory(cat);
        return res;

    }
    public Category findCategoryById(String cid) throws SQLException {
        QueryRunner qr=new QueryRunner(DruidUtils.getDataSource());
        String sql="select * from category where cid=? ";
        Category cat = qr.query(sql, new BeanHandler<Category>(Category.class), cid);
        return cat;
    }
    public int getCount(String cid) throws SQLException {
        QueryRunner qr=new QueryRunner(DruidUtils.getDataSource());
        String sql="select count(*) from product where cid=?";
        Long res = (Long)qr.query(sql, new ScalarHandler<>(), cid);
        return res.intValue();
    }
    public List<Product> getAllProduct(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;
    }
}

OrdersDao类

OrderItem和Orders表至今的关系是多对一

前面一直描述的是一对多，这里是反过来的多对一，方式是在Orders表中应该用一个集合来保存订单中的订单项信息。

OrdersDao类

需求：获取uid为001的用户的所有订单信息；获取订单编号为order001的订单中所有商品信息

package dao;

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

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

public class OrdersDao {
    public List<Orders> findByid(String uid) throws SQLException {
        QueryRunner qr=new QueryRunner(DruidUtils.getDataSource());
        String sql="select * from orders where uid=?";
        List<Orders> lis = qr.query(sql, new BeanListHandler<Orders>(Orders.class), uid);
        return lis;
    }
    public List<Product> findAll(String oid) throws SQLException {
        QueryRunner qr=new QueryRunner(DruidUtils.getDataSource());
        String sql="select pid from orderitem where oid=?";
        List<OrderItem> list = qr.query(sql, new BeanListHandler<OrderItem>(OrderItem.class), oid);
        List<Product> products=new ArrayList<>();
        ProductDao pd=new ProductDao();
        for(OrderItem ot:list){
            Product byPid = pd.findByPid(ot.getPid());
            products.add(byPid);


        }
        return products;

    }
}