商城案例

1. 建立用户表，订单表，订单项表，商品表，分类表

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

-- 创建名为 store的数据库, 对应商城项目  
CREATE DATABASE store CHARACTER SET utf8;

创建用户表

-- 创建用户表 
CREATE TABLE IF NOT EXISTS 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 IF NOT EXISTS 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');`user``orders`

创建商品分类表

-- 创建商品分类表
CREATE TABLE IF NOT EXISTS 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');

结构关系图

2. 项目结构

com.app 测试包用于对DAO代码进行测试
com.dao dao包数据访问层,包含所有对数据库的相关操作的类
com.lagou.entity 实体包保存根据数据库表对应创建的JavaBean类
com.lagou.utils 工具包

导入jar包

导入配置文件

3. 创建javabean 类对应数据

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

package entity;

/**  User表
 *  `uid` varchar(32) NOT NULL,
 *   `username` varchar(20) DEFAULT NULL,
 *   `password` varchar(20) DEFAULT NULL,
 *   `telephone` varchar(20) DEFAULT NULL,
 *   `birthday` date DEFAULT NULL,
 *   `sex` varchar(10) DEFAULT NULL,
 *   PRIMARY KEY (`uid`)
 */
public class User {
    private String uid;  //用户ID

    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 "Orders{" +
                "uid='" + uid + '\'' +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", telephone='" + telephone + '\'' +
                ", birthday='" + birthday + '\'' +
                ", sex='" + sex + '\'' +
                '}';
    }
}

与此类似建立以下表

外键cid product 和category 是多对一的关系， category 是一， product是多，外键绑定是在多的实体类中绑定少的，找准一对多

package entity;

import java.util.ArrayList;
import java.util.List;

/** Orders表
 * `oid` varchar(32) NOT NULL,
 *   `ordertime` datetime DEFAULT NULL,
 *   `total` double DEFAULT NULL,
 *   `NAME` varchar(20) DEFAULT NULL,
 *   `telephone` varchar(20) DEFAULT NULL,
 *   `address` varchar(30) DEFAULT NULL,
 *   `state` int(11) DEFAULT NULL,
 *   `uid` varchar(32) DEFAULT NULL,
 *   PRIMARY KEY (`oid`),
 *   KEY `ofk_0001` (`uid`),
 *   CONSTRAINT `ofk_0001` FOREIGN KEY (`uid`) REFERENCES `user` (`uid`)
 */
public class Orders {
    private String oid;  //订单ID

    private String ordertime;  //订单时间

    private double total;//订单总金额

    private String name; //订单名

    private String telephone;  //订单联系号码

    private String address;//地址

    private  int state;//订单状态  1、已支付  0、未支付

    //外键  user 和 orders 是一对多的关系，user是1， orders是多
    private String uid; //绑定的用户ID
    private User user;//绑定的用户信息

    public List<Orderitem> getOrderitemList() {
        return orderitemList;
    }

    public void setOrderitemList(List<Orderitem> orderitemList) {
        this.orderitemList = orderitemList;
    }

    //描述多对一关系
    private List<Orderitem> orderitemList= new ArrayList<Orderitem>();

    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 + '\'' +
                '}';
    }
}

多对多情况下要建立中间表转为一对多

4. 编写dao类并测试

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 {
    //需求1: 获取 uid为 001 的用户的所有订单信息
    //参数 uid, 返回值 LIst 订单集合
    public List<Orders> findListById(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的订单中的所有商品信息
    //参数 oid, 返回值List 商品集合
    public List<Product> findPListById(String oid) throws SQLException {

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

        String sql= " select * from orderitem where oid=?";

        List<Orderitem> orderitemList = qr.query(sql, new BeanListHandler<Orderitem>(Orderitem.class), oid);

        List<Product> productList= new ArrayList<>();

        ProductDao productDao=new ProductDao();

        for (Orderitem orderitem : orderitemList) {
            String pid = orderitem.getPid();
            Product product = productDao.findProductById(pid);
            productList.add(product);
        }
        return productList;
    }
}

package app;

import dao.OrdersDao;
import entity.Orders;
import entity.Product;
import org.junit.Test;

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

public class TestOrdersDao {
    OrdersDao ordersDao=new OrdersDao();
    //需求1: 获取 uid为 001 的用户的所有订单信息
    @Test
    public void testFindListById() throws SQLException {
        List<Orders> list = ordersDao.findListById("001");
        for (Orders orders : list) {
            System.out.println(orders.toString());
        }
    }
    //需求2: 获取订单编号为 order001的订单中的所有商品信息
    @Test
    public void testFindPListById() throws SQLException {
        List<Product> products = ordersDao.findPListById("order001");
        for (Product product : products) {
            System.out.println(product.toString());
        }
    }
}

posted @ 2020-12-24 11:34 forever_fate 阅读(165) 评论(0) 收藏举报

刷新页面返回顶部

商城案例

商城案例

公告