來自拉鉤教育-JAVA就業(yè)集訓(xùn)營
1.商城案例表設(shè)計
通過對商城項(xiàng)目的部分表關(guān)系進(jìn)行分析,完成數(shù)據(jù)庫表的設(shè)計
1.1 表關(guān)系分析
01.jpg
1.2 建庫,建表
- 創(chuàng)建名為 store的數(shù)據(jù)庫, 對應(yīng)商城項(xiàng)目
create database db6 character set utf8;
- 創(chuàng)建用戶表
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','男');
- 創(chuàng)建訂單表
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) , -- 訂單狀態(tài)
uid varchar(32), -- 外鍵字段 對應(yīng)用戶表id
CONSTRAINT ofk_0001 FOREIGN KEY (uid) REFERENCES user (uid)
);
-- 插入一條訂單數(shù)據(jù)
INSERT INTO orders
VALUES('order001','2019-10-11',5500,'喬杉','15512342345','皇家洗浴',0,'001');
- 創(chuàng)建商品分類表
CREATE TABLE category (
cid varchar(32) PRIMARY KEY,
cname varchar(20)
);
INSERT INTO `category` VALUES ('1','手機(jī)數(shù)碼'),('2','電腦辦公'),('3','運(yùn)動鞋服'),('4','圖書音像');
- 創(chuàng)建商品表
CREATE TABLE product (
pid varchar(32) PRIMARY KEY, -- 商品id
pname varchar(50) , -- 商品名稱
price double, -- 商品價格
pdesc varchar(255), -- 商品描述
pflag int(11) , -- 商品狀態(tài) 1 上架 ,0 下架
cid varchar(32) , -- 外鍵對應(yīng) 分類表id
KEY sfk_0001 (cid),
CONSTRAINT sfk_0001 FOREIGN KEY (cid) REFERENCES category (cid)
);
INSERT INTO `product` VALUES
('1','小米6',2200,'小米 移動聯(lián)通電信4G手機(jī) 雙卡雙待',0,'1'),
('2','華為Mate9',2599,'華為 雙卡雙待 高清大屏',0,'1'),
('3','OPPO11',3000,'移動聯(lián)通 雙4G手機(jī)',0,'1'),
('4','華為榮耀',1499,'3GB內(nèi)存標(biāo)準(zhǔn)版 黑色 移動4G手機(jī)',0,'1'),
('5','華碩臺式電腦',5000,'爆款直降萎馅，滿千減百',0,'2'),
('6','MacBook',6688,'128GB 閃存',0,'2'),
('7','ThinkPad',4199,'輕薄系列1)',0,'2'),
('8','聯(lián)想小新',4499,'14英寸超薄筆記本電腦',0,'2'),
('9','李寧音速6',500,'實(shí)戰(zhàn)籃球鞋',0,'3'),
('10','AJ11',3300,'喬丹實(shí)戰(zhàn)系列',0,'3'),
('11','AJ1',5800,'精神小伙系列',0,'3');
- 訂單項(xiàng)表 (中間表)
-- 訂單項(xiàng)表
CREATE TABLE orderitem (
itemid VARCHAR(32) PRIMARY KEY, -- 訂單項(xiàng)ID
pid VARCHAR(32), -- 外鍵 對應(yīng)商品表 id
oid VARCHAR(32), -- 外鍵 對應(yīng)訂單表 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)
);
-- 向中間表中插入兩條數(shù)據(jù)
INSERT INTO orderitem VALUES('item001','1','order001');
INSERT INTO orderitem VALUES('item002','11','order001');
2.環(huán)境搭建
2.1 項(xiàng)目結(jié)構(gòu)
com.lagou.app 測試包 用于對DAO代碼進(jìn)行測試
com.lagou.dao dao包 數(shù)據(jù)訪問層,包含所有對數(shù)據(jù)庫的相關(guān)操作的類
com.lagou.entity 實(shí)體包 保存根據(jù)數(shù)據(jù)庫表 對應(yīng)創(chuàng)建的JavaBean類
com.lagou.utils 工具包
03.jpg
2.2 導(dǎo)入所需Jar包
我們只需要導(dǎo)入myjar倉庫到項(xiàng)目中就可以了
04.jpg
2.3 導(dǎo)入配置文件及工具類
05.jpg
3.JavaBean類創(chuàng)建
3.1 設(shè)計用戶與訂單
3.1.1 一對多關(guān)系分析
在Java一對多的數(shù)據(jù)關(guān)系中擂啥，需要遵循以下設(shè)計原則：
- Java類的名稱 = 實(shí)體表的名稱
- Java類的屬性 = 實(shí)體表的字段
- Java類的一個對象 = 表的一行記錄
- 外鍵關(guān)系 = 引用配置
一個用戶擁有多個訂單,所以 用戶是一的一方, 訂單是多的一方
06.jpg
3.1.2 User類
/**
* 用戶表 對應(yīng) 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,
* */
public class User {
private String uid;
private String username;
private String password;
private String telephone;
private String birthday;
private String sex;
//提供 get set toString方法
}
3.1.3 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,
*
* */
public class Orders {
private String oid; //訂單號
private String ordertime; //下單時間
private double total; //訂單的總金額
private String name; //收貨人姓名
private String telephone; //收貨人電話
private String address; //收貨人地址
private int state; //訂單狀態(tài) 1 代表已支付 , 0 代表未支付
//訂單屬于哪個用戶呢 ?
//提供 get set toString方法
}
3.1.4 Orders類設(shè)計分析
-
第一種方式
- 根據(jù)兩張表關(guān)系的描述 我們可以在 訂單類中 添加一個uid 成員變量,表示訂單屬于哪個用戶
private String uid;
但是這樣設(shè)計會存在一些問題,比如 我要查詢的是訂單是屬于哪個用戶的用戶名 ? 但是我們只有一個uid
-
第二種方式
Java類表示一對多關(guān)系,可以在多的一方添加一個成員變量,這個成員變量的類型 就是一的一方的類型.
再在訂單表中 添加一個 User對象,User對象中 ,保存該訂單關(guān)聯(lián)的用戶的所有信息
private String uid;
private User user;
3.1.4 修改Orders類
public class Orders {
private String oid; //訂單號
private String ordertime; //下單時間
private double total; //訂單的總金額
private String name; //收貨人姓名
private String telephone; //收貨人電話
private String address; //收貨人地址
private int state; //訂單狀態(tài) 1 代表已支付 , 0 代表未支付
//訂單屬于哪個用戶呢 ?
private String uid; //表示外鍵
private User user; //用來保存訂單對應(yīng)的詳細(xì)的用戶信息
//提供 get set toString方法
}
3.2 設(shè)計商品與分類
分類與商品 同樣是一對多關(guān)系, 我們可以在多的一方進(jìn)行操作 添加一個成員變量 類型是一的一方的類型
07.jpg
3.2.1 Category類
public class Category {
private String cid;
private String cname;
//提供 get set toString方法
}
3.2.2 Product類
public class Product {
private String pid;
private String pname;
private double price;
private String pdesc;
private int pflag; //是否上架 1 上架 ,0 下架
private String cid; //外鍵 對應(yīng)分類表主鍵
private Category category; //用于保存Category的詳細(xì)數(shù)據(jù)
//提供 get set toString方法
}
3.3 設(shè)計訂單項(xiàng)
3.3.1 多對多關(guān)系分析
商品與訂單是多對多關(guān)系, 一個訂單上可以有多個商品, 一個商品可以出現(xiàn)在多個訂單中.
多對多建表原則 需要一張中間表,中間表中至少有兩個字段,作為中間表的外鍵分別指向另外兩張表的主鍵
11.jpg
3.3.2 創(chuàng)建OrderItem
/**
* 訂單項(xiàng)表(中間表)
* `itemid` VARCHAR(32) NOT NULL,
* `pid` VARCHAR(32) DEFAULT NULL,
* `oid` VARCHAR(32) DEFAULT NULL,
*
* */
public class OrderItem {
//訂單項(xiàng) 指的是中間表中的一條數(shù)據(jù)
private String itemid; //訂單項(xiàng)的id
private String pid; //外鍵 指向商品表主鍵
private String oid; //外鍵 指向訂單表的主鍵
private Product product;//訂單項(xiàng)內(nèi)部的商品詳細(xì)信息
private Orders orders;//訂單項(xiàng)屬于哪個訂單
}
4.編寫DAO類
4.1 UserDao
- 需求一: 編寫一個注冊用戶的方法,接收的參數(shù)是一個User對象
- 需求二: 編寫一個 用戶登錄的方法,接收的參數(shù)是 用戶名 和密碼, 返回值是User對象
4.1.1 編寫UserDao
public class UserDao {
/**
* 注冊用戶
* */
public int register(User user) throws SQLException {
//1.獲取QueryRunner
QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
//2.編寫SQL
String sql = "insert into user values(?,?,?,?,?,?)";
Object[] param = {user.getUid(), user.getUsername(), user.getPassword(),
user.getTelephone(), user.getBirthday(), user.getSex()};
//3.執(zhí)行插入操作
int update = qr.update(sql,param);
//4.返回受影響的行數(shù)
return update;
}
/**
* 用戶注冊
* */
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對象 使用BeanHandler將結(jié)果集的第一條和數(shù)據(jù)封裝到一個Javabean中
User user = qr.query(sql, new BeanHandler<User>(User.class), username, password);
return user;
}
}
4.1.2 測試注冊與登錄功能
public class TestUserDao {
//創(chuàng)建UserDao
UserDao userDao = new UserDao();
//測試注冊功能
@Test
public void testRegister() throws SQLException {
//1. 創(chuàng)建User對象
User user = new User();
//2. 對User對象進(jìn)行賦值
user.setUid(UUIDUtils.getUUID());
user.setUsername("大郎");
user.setPassword("654321");
user.setTelephone("15052005200");
user.setSex("男");
user.setBirthday(DateUtils.getDateFormart());
//3.執(zhí)行注冊
int register = userDao.register(user);
//4.判斷注冊是否成功
if(register > 0){
System.out.println("注冊成功,歡迎您: " + user.getUsername());
}else{
System.out.println("注冊失敗! !");
}
}
//測試登錄功能
@Test
public void testLogin() throws SQLException {
//調(diào)用UserDao的 login方法,傳入用戶名密碼
User user = userDao.login("大郎", "654321");
//判斷user不為空 登錄成功
if(user != null){
System.out.println(user.getUsername() +" 歡迎您!");
}else{
System.out.println("用戶名或者密碼錯誤! !");
}
}
}
4.2 ProductDao
-
需求1: 根據(jù)商品ID 獲取商品名稱 ,商品價格 以及商品所屬分類的名稱
- 參數(shù) pid, 返回值 product對象
-
需求2: 根據(jù)分類ID 獲取商品分類信息
- 參數(shù) cid , 返回值 category對象
-
需求3: 查詢指定分類ID 下的商品個數(shù)
- 參數(shù) cid , 返回值 int類型 商品個數(shù)
-
需求4: 查詢指定分類ID 下的所有商品信息
- 參數(shù)分類ID ,返回值 List集合 集合中保存商品對象
4.2.1 編寫 ProductDao
public class ProductDao {
//1.根據(jù)商品ID 獲取商品名稱 ,商品價格 以及商品所屬分類的名稱
public Product findProductById(String pid) throws SQLException {
QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
String sql = "select * from product where pid = ?";
Product product = qr.query(sql, new BeanHandler<Product>(Product.class), pid);
//調(diào)用 findCategoryById()方法, 傳遞外鍵cid 獲取商品對應(yīng) 的分類信息
Category category = findCategoryById(product.getCid());
//將category保存到商品對象中
product.setCategory(category);
return product;
}
//2.根據(jù)分類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;
}
//3.查詢指定分類ID 下的商品個數(shù)
public int getCount(String cid) throws SQLException {
QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
String sql = "select count(*) from product where cid = ?";
//獲取的單列數(shù)據(jù) ,使用ScalarHandler 封裝
Long count = (Long)qr.query(sql,new ScalarHandler<>(),cid);
//將Lang類型轉(zhuǎn)換為 int 類型,并返回
return count.intValue();
}
//4.查詢指定分類下的所有商品信息
public List<Product> findProductByCid(String cid) throws SQLException {
QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
String sql = "select * from product where cid = ?";
//查詢結(jié)果是一個List集合, 使用BeanListHandler 封裝結(jié)果集
List<Product> list = qr.query(sql, new BeanListHandler<Product>(Product.class), cid);
return list;
}
}
4.2.2 測試 ProductDao
public class TestProductDao {
ProductDao productDao = new ProductDao();
//1.測試 根據(jù)商品ID 獲取商品名稱 ,商品價格 以及商品所屬分類的名稱
@Test
public void testFindProductById() throws SQLException {
Product product = productDao.findProductById("1");
System.out.println("商品名稱: "+product.getPname()+ ", 商品價格: " + product.getPrice() +
", 商品所屬分類: "+ product.getCategory().getCname());
}
//2.測試 查詢指定分類ID下的商品數(shù)
@Test
public void testGetCount() throws SQLException {
//查詢 cid為3的分類下有多少個商品
int count = productDao.getCount("3");
System.out.println("分類ID為3的分類下商品個數(shù): " + count);
}
//3.測試 查詢指定分類下的所有商品信息
@Test
public void testFindProductByCid() throws SQLException {
//查詢cid為 2的分類下 所有的商品信息
List<Product> list = productDao.findProductByCid("2");
for (Product product : list) {
System.out.println(product);
}
}
}
4.3 OrdersDao
4.3.1 多對一分析
OrderItem表與Orders表的關(guān)系是 多對一
09.jpg
之前我們一直是在描述一對多,那么我們再反向描述一下 多對一
方式是在Orders中應(yīng)該有一個 集合用來保存訂單中的訂單項(xiàng)信息
10.jpg
在Orders類中添加 訂單項(xiàng)的集合
//該訂單中有多少訂單項(xiàng)
List<OrderItem> orderItems = new ArrayList<OrderItem>();
public List<OrderItem> getOrderItems() {
return orderItems;
}
public void setOrderItems(List<OrderItem> orderItems) {
this.orderItems = orderItems;
}
4.3.2 創(chuàng)建OrdersDao
需求1: 獲取 uid為 001 的用戶的所有訂單信息
參數(shù) uid, 返回值 LIst<Orders> 訂單集合
需求2: 獲取訂單編號為 order001的訂單中的所有商品信息
參數(shù) oid, 返回值List<Product> 商品集合
-- 獲取訂單編號為: order001的訂單中的所有商品信息
-- 1.查詢訂單項(xiàng)表中 oid是order001的 所有商品信息
SELECT
oi.pid
FROM orderitem oi WHERE oid = 'order001';
-- 2.將上面的查詢語句作為in函數(shù)的條件, 查詢product表
SELECT * FROM product WHERE pid IN
(SELECT
oi.pid
FROM orderitem oi WHERE oid = 'order001');
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.查詢訂單項(xiàng)表 獲取訂單項(xiàng)表中 訂單ID為order001的數(shù)據(jù)
String sql = "SELECT pid FROM orderitem WHERE oid = ? ";
//2.查詢的結(jié)果是 多條訂單項(xiàng)數(shù)據(jù)
List<OrderItem> list = qr.query(sql, new BeanListHandler<OrderItem>(OrderItem.class), oid);
//3.創(chuàng)建集合保存商品信息
List<Product> productList = new ArrayList<>();
ProductDao productDao = new ProductDao();
//4.遍歷訂單項(xiàng)集合 獲取Pid
for (OrderItem orderItem : list) {
//4.1從orderitem中獲取 pid
String pid = orderItem.getPid();
//4.2 調(diào)用productDao
Product product = productDao.findProductById(pid);
//4.3 保存到集合
productList.add(product);
}
//返回 訂單中對應(yīng)的商品信息
return productList;
}
}
4.3.3 測試OrdersDao
public class TestOrderDao {
OrdersDao ordersDao = new OrdersDao();
//1.獲取 uid為 001 的用戶的所有訂單信息
@Test
public void testFindAllOrders() throws SQLException {
List<Orders> allOrders = ordersDao.findAllOrders("001");
//遍歷打印訂單信息
for (Orders order : allOrders) {
System.out.println(order);
}
}
//測試 獲取訂單編號為: order001的訂單中的所有商品信息
@Test
public void testFindOrderById() throws SQLException {
List<Product> list = ordersDao.findOrderById("order001");
System.out.println("訂單編號為order001中的商品有: ");
for (Product product : list) {
System.out.println(product);
}
}
}
