Dao第三次作业

最新推荐文章于 2022-11-24 20:02:16 发布

一度教育-月野氿桃

最新推荐文章于 2022-11-24 20:02:16 发布

阅读量200

点赞数

分类专栏： Dao

本文链接：https://blog.csdn.net/weixin_45426610/article/details/100165123

版权

Dao 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

//把酒店的数据设计写在数据库中，然后再把数据库中的酒店Dao在eclipse中

//酒店数据设计图
在这里插入图片描述

– 创建房间表
CREATE TABLE room(
room_id INT PRIMARY KEY AUTO_INCREMENT, – 房间ID
room_name VARCHAR(50), – 房间名称
room_type VARCHAR(50), – 房间类型
room_price VARCHAR(50), – 房间价格
room_state VARCHAR(50) – 房间状态
);

– 创建客户表
CREATE TABLE CLIENT(
client_id INT PRIMARY KEY AUTO_INCREMENT, – 客户ID
client_name VARCHAR(100), – 客户名称
client_sex VARCHAR(10), – 客户性别
client_birthday VARCHAR(50), – 客户生日
client_phone VARCHAR(100) – 客户电话
);

– 创建部门表
CREATE TABLE department(
department_id INT PRIMARY KEY AUTO_INCREMENT, – 部门ID
department_name VARCHAR(100), – 部门名称
department_phone VARCHAR(100) – 部门电话
);

– 创建员工表
CREATE TABLE staff(
staff_id INT PRIMARY KEY AUTO_INCREMENT, – 员工ID
department_id INT, – 部门ID
staff_name VARCHAR(100), – 员工名称
staff_sex VARCHAR(10), – 员工性别
CONSTRAINT fk_department_id FOREIGN KEY (department_id) REFERENCES department(department_id) – 外键
);

– 创建酒店表
CREATE TABLE hotel(
hotel_id INT PRIMARY KEY AUTO_INCREMENT, – 酒店ID
hotel_name VARCHAR(100), – 酒店名称
hotel_address VARCHAR(200), – 酒店地址
hotel_type VARCHAR(100) – 酒店类型
);

– 创建订房表
CREATE TABLE reservation(
reservation_id INT PRIMARY KEY AUTO_INCREMENT, – 订房ID
room_id INT, – 房间ID
client_id INT, – 客户ID
staff_id INT, – 员工ID
reservation_time DATETIME, – 订房时间
reservation_amount INT, – 总金额
reservation_remark VARCHAR(400), – 备注
hotel_id INT, – 酒店ID
CONSTRAINT fk_room_id FOREIGN KEY(room_id) REFERENCES room(room_id), – 添加房间外键
CONSTRAINT fk_client_id FOREIGN KEY(client_id) REFERENCES CLIENT(client_id), – 添加客户外键
CONSTRAINT fk_staff_id FOREIGN KEY(staff_id) REFERENCES staff(staff_id), – 添加员工外键
CONSTRAINT fk_hotel_id FOREIGN KEY(hotel_id) REFERENCES hotel(hotel_id) – 添加酒店外键
);

DROP TABLE reservation;
DROP TABLE hotel;
DROP TABLE CLIENT;
DROP TABLE staff;
DROP TABLE department;
DROP TABLE room;

– 各表中插入你和你朋友的开房记录
INSERT INTO CLIENT VALUES(NULL,“张三”,“女”,“1999-01-01”,“15327276373”);
INSERT INTO CLIENT VALUES(NULL,“李四”,“女”,“1998-05-05”,“15457276373”);
INSERT INTO room VALUES(NULL,“8-13”,“双人间”,“180”,“已开”);
INSERT INTO hotel VALUES(NULL,‘华天大酒店’,‘芙蓉区110号’,‘豪华型’);
INSERT INTO reservation VALUES(NULL,1,1,1,‘2010-01-01’,300,“含早餐”,1);
INSERT INTO department VALUES(NULL,‘1322445554’,‘8599223’);
INSERT INTO staff VALUES(NULL,1,‘王五’,‘女’);

SELECT * FROM room;

– 查询2010-01-01这天,你在哪家酒店,那个房间住了,展示出所有的信息
SELECT h.hotel_name,r.room_name FROM reservation rt,hotel h,room r WHERE h.hotel_id = rt.hotel_id
AND rt.room_id = r.room_id AND rt.reservation_time =‘2010-01-01’;

– 查询客服MM小美为你开房的所有的记录.
SELECT rt.* FROM reservation rt,staff s,room r,CLIENT c,hotel h WHERE rt.hotel_id = h.hotel_id
AND rt.room_id = r.room_id AND rt.client_id = c.client_id AND rt.staff_id = s.staff_id
AND s.staff_sex=‘女’;

– 查询2010年你开房的总金额
SELECT c.client_name,SUM(rt.reservation_amount) FROM reservation rt , CLIENT c WHERE
rt.client_id = c.client_id AND rt.reservation_time>=‘2009-01-01’
AND rt.reservation_time<‘2012-01-01’ AND c.client_name=‘张三’;

– 查询你在哪个房间住的次数最多
SELECT COUNT(*),r.room_name FROM reservation rt,room r,CLIENT c WHERE rt.room_id = r.room_id AND
rt.client_id = c.client_id AND r.room_name= (SELECT MAX(rm.room_name) FROM room rm) AND
c.client_name=‘张三’ GROUP BY r.room_name;

//酒店的模型

//房间
/**
*
*/
package com.yidu.model;

/**

描述：房间类
@author zhouyuxian
@date 2019年8月28日下午5:48:44
@version 1.0
*/
public class Room {
private Integer roomID;//房间ID
private String name;//房间名称
private String type;//房间类型
private String price;//房间类型
private String state;//房间状态
public Integer getRoomID() {
return roomID;
}
public void setRoomID(Integer roomID) {
this.roomID = roomID;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public String getPrice() {
return price;
}
public void setPrice(String price) {
this.price = price;
}
public String getState() {
return state;
}
public void setState(String state) {
this.state = state;
}
}

//客户
/**
*
*/
package com.yidu.model;

/**

描述：客户类
@author zhouyuxian
@date 2019年8月28日下午7:10:12
@version 1.0
*/
public class Client {
private Integer clientID;//客户ID
private String clientName;//客户姓名
private String clientSex;//客户性别
private String clientBirth;//客户生日
private String clientPhone;//客户电话
public Integer getClientID() {
return clientID;
}
public void setClientID(Integer clientID) {
this.clientID = clientID;
}
public String getClientName() {
return clientName;
}
public void setClientName(String clientName) {
this.clientName = clientName;
}
public String getClientSex() {
return clientSex;
}
public void setClientSex(String clientSex) {
this.clientSex = clientSex;
}
public String getClientBirth() {
return clientBirth;
}
public void setClientBirth(String clientBirth) {
this.clientBirth = clientBirth;
}
public String getClientPhone() {
return clientPhone;
}
public void setClientPhone(String clientPhone) {
this.clientPhone = clientPhone;
}

}

// 部门
/**
*
*/
package com.yidu.model;

/**

描述：部门表
@author zhouyuxian
@date 2019年8月28日下午7:41:17
@version 1.0
*/
public class Department {
private Integer dtID;//部门ID
private String dtName;//部门名称
private String dtPhone;//部门电话
public Integer getDtID() {
return dtID;
}
public void setDtID(Integer dtID) {
this.dtID = dtID;
}
public String getDtName() {
return dtName;
}
public void setDtName(String dtName) {
this.dtName = dtName;
}
public String getDtPhone() {
return dtPhone;
}
public void setDtPhone(String dtPhone) {
this.dtPhone = dtPhone;
}

}

//员工
/**
*
*/
package com.yidu.model;

/**

描述：员工表
@author zhouyuxian
@date 2019年8月28日下午7:43:34
@version 1.0
*/
public class Staff {
private Integer staffID;//员工ID
private Integer dtID;//部门ID
private String staffName;//员工名称
private String staffSex;//员工性别
public Integer getStaffID() {
return staffID;
}
public void setStaffID(Integer staffID) {
this.staffID = staffID;
}
public Integer getDtID() {
return dtID;
}
public void setDtID(Integer dtID) {
this.dtID = dtID;
}
public String getStaffName() {
return staffName;
}
public void setStaffName(String staffName) {
this.staffName = staffName;
}
public String getStaffSex() {
return staffSex;
}
public void setStaffSex(String staffSex) {
this.staffSex = staffSex;
}

}

//酒店
/**
*
*/
package com.yidu.model;

/**

描述：酒店类
@author zhouyuxian
@date 2019年8月28日下午11:36:38
@version 1.0
*/
public class Hotel {
private Integer hotelID;
private String hotelName;
private String hotelAddress;
private String hotelType;
public Integer getHotelID() {
return hotelID;
}
public void setHotelID(Integer hotelID) {
this.hotelID = hotelID;
}
public String getHotelName() {
return hotelName;
}
public void setHotelName(String hotelName) {
this.hotelName = hotelName;
}
public String getHotelAddress() {
return hotelAddress;
}
public void setHotelAddress(String hotelAddress) {
this.hotelAddress = hotelAddress;
}
public String getHotelType() {
return hotelType;
}
public void setHotelType(String hotelType) {
this.hotelType = hotelType;
}

}

//订房
/**
*
*/
package com.yidu.model;

import java.sql.Date;

/**

描述：订房表
@author zhouyuxian
@date 2019年8月28日下午7:46:06
@version 1.0
*/
public class Reservation {
// reservation_remark VARCHAR(400),
private Integer rtID;//订房ID
private Integer roomID;//房间ID
private Integer clientID;//客户ID
private Integer staffID;//员工ID
private Date rtTime;//订房时间
private Integer rtAmount;//订房总金额
private String rtRemark;//备注
private Integer rtHotel;

public Integer getRtHotel() {
return rtHotel;
}
public void setRtHotel(Integer rtHotel) {
this.rtHotel = rtHotel;
}
public Integer getRtID() {
return rtID;
}
public void setRtID(Integer rtID) {
this.rtID = rtID;
}
public Integer getRoomID() {
return roomID;
}
public void setRoomID(Integer roomID) {
this.roomID = roomID;
}
public Integer getClientID() {
return clientID;
}
public void setClientID(Integer clientID) {
this.clientID = clientID;
}
public Integer getStaffID() {
return staffID;
}
public void setStaffID(Integer staffID) {
this.staffID = staffID;
}
public Date getRtTime() {
return rtTime;
}
public void setRtTime(Date rtTime) {
this.rtTime = rtTime;
}
public Integer getRtAmount() {
return rtAmount;
}
public void setRtAmount(Integer rtAmount) {
this.rtAmount = rtAmount;
}
public String getRtRemark() {
return rtRemark;
}
public void setRtRemark(String rtRemark) {
this.rtRemark = rtRemark;
}

}

//他们依次的各个Dao