一.表结构
问题一:不能创建有外键的表
解决:先创建没有依赖的单表,再创建有外键的复杂表
1.会员表信息描述member
CREATE TABLE member(
id varchar2(32) default SYS_GUID() PRIMARY KEY,
NAME VARCHAR2(20),
nickname VARCHAR2(20),
phoneNum VARCHAR2(20),
email VARCHAR2(20)
)
insert into MEMBER (id, name, nickname, phonenum, email)
values ('E61D65F673D54F68B0861025C69773DB', '张三', '小三', '18888888888', 'zs@163.com');
实体类
package com.gxd.ssm.domain;
//会员
public class Member {
private String id;
private String name;
private String nickname;
private String phoneNum;
private String email;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getNickname() {
return nickname;
}
public void setNickname(String nickname) {
this.nickname = nickname;
}
public String getPhoneNum() {
return phoneNum;
}
public void setPhoneNum(String phoneNum) {
this.phoneNum = phoneNum;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
}
2.旅客表信息描述 traveller
C
REATE TABLE traveller(
id varchar2(32) default SYS_GUID() PRIMARY KEY,
NAME VARCHAR2(20),
sex VARCHAR2(20),
phoneNum VARCHAR2(20),
credentialsType INT,
credentialsNum VARCHAR2(50),
travellerType INT
) i
nsert into TRAVELLER (id, name, sex, phonenum, credentialstype, credentialsnum, travellertype)
values ('3FE27DF2A4E44A6DBC5D0FE4651D3D3E', '张龙', '男', '13333333333', 0,
'123456789009876543', 0);
insert into TRAVELLER (id, name, sex, phonenum, credentialstype, credentialsnum, travellertype)
values ('EE7A71FB6945483FBF91543DBE851960', '张小龙', '男', '15555555555', 0,
'987654321123456789', 1);
实体类:
package com.gxd.ssm.domain;
//旅客
public class Traveller {
private String id;
private String name;
private String sex;
private String phoneNum;
private Integer credentialsType; //证件类型 0身份证 1护照 2军官证
private String credentialsTypeStr;
private String credentialsNum;
private Integer travellerType; //旅客类型(人群) 0 成人 1 儿童
private String travellerTypeStr;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getPhoneNum() {
return phoneNum;
}
public void setPhoneNum(String phoneNum) {
this.phoneNum = phoneNum;
}
public Integer getCredentialsType() {
return credentialsType;
}
public void setCredentialsType(Integer credentialsType) {
this.credentialsType = credentialsType;
}
public String getCredentialsTypeStr() {
//证件类型 0身份证 1护照 2军官证
if (credentialsType != null) {
if (credentialsType == 0) {
credentialsTypeStr = "身份证";
} else if (credentialsType == 1) {
credentialsTypeStr = "护照";
} else if (credentialsType == 2) {
credentialsTypeStr = "军官证";
}
}
return credentialsTypeStr;
}
public void setCredentialsTypeStr(String credentialsTypeStr) {
this.credentialsTypeStr = credentialsTypeStr;
}
public String getCredentialsNum() {
return credentialsNum;
}
public void setCredentialsNum(String credentialsNum) {
this.credentialsNum = credentialsNum;
}
public Integer getTravellerType() {
return travellerType;
}
public void setTravellerType(Integer travellerType) {
this.travellerType = travellerType;
}
public String getTravellerTypeStr() {
////旅客类型(人群) 0 成人 1 儿童
if (travellerType != null) {
if (travellerType == 0) {
travellerTypeStr = "成人";
} else if (travellerType == 1) {
travellerTypeStr = "儿童";
}
}
return travellerTypeStr;
}
public void setTravellerTypeStr(String travellerTypeStr) {
this.travellerTypeStr = travellerTypeStr;
}
}
旅客与订单之间是多对多关系，所以我们需要一张中间表（order_traveller）来描述
C
REATE TABLE order_traveller(
orderId varchar2(32),
travellerId varchar2(32),
PRIMARY KEY (orderId,travellerId),
FOREIGN KEY (orderId) REFERENCES orders(id),
FOREIGN KEY (travellerId) REFERENCES traveller(id)
) i
nsert into ORDER_TRAVELLER (orderid, travellerid)
values ('0E7231DC797C486290E8713CA3C6ECCC', '3FE27DF2A4E44A6DBC5D0FE4651D3D3E');
insert into ORDER_TRAVELLER (orderid, travellerid)
values ('2FF351C4AC744E2092DCF08CFD314420', '3FE27DF2A4E44A6DBC5D0FE4651D3D3E');
insert into ORDER_TRAVELLER (orderid, travellerid)
values ('3081770BC3984EF092D9E99760FDABDE', 'EE7A71FB6945483FBF91543DBE851960');
insert into ORDER_TRAVELLER (orderid, travellerid)
values ('55F9AF582D5A4DB28FB4EC3199385762', 'EE7A71FB6945483FBF91543DBE851960');
insert into ORDER_TRAVELLER (orderid, travellerid)
values ('5DC6A48DD4E94592AE904930EA866AFA', '3FE27DF2A4E44A6DBC5D0FE4651D3D3E');
insert into ORDER_TRAVELLER (orderid, travellerid)
values ('96CC8BD43C734CC2ACBFF09501B4DD5D', 'EE7A71FB6945483FBF91543DBE851960');
insert into ORDER_TRAVELLER (orderid, travellerid)
values ('A0657832D93E4B10AE88A2D4B70B1A28', '3FE27DF2A4E44A6DBC5D0FE4651D3D3E');
insert into ORDER_TRAVELLER (orderid, travellerid)
values ('CA005CF1BE3C4EF68F88ABC7DF30E976', 'EE7A71FB6945483FBF91543DBE851960');
insert into ORDER_TRAVELLER (orderid, travellerid)
values ('E4DD4C45EED84870ABA83574A801083E', 'EE7A71FB6945483FBF91543DBE851960');
3.订单表信息描述 orders
CREATE TABLE orders(
id varchar2(32) default SYS_GUID() PRIMARY KEY,
orderNum VARCHAR2(20) NOT NULL UNIQUE,
orderTime timestamp,
peopleCount INT,
orderDesc VARCHAR2(500),
payType INT,
orderStatus INT,
productId varchar2(32),
memberId varchar2(32),
FOREIGN KEY (productId) REFERENCES product(id),
FOREIGN KEY (memberId) REFERENCES member(id)
)
insert into ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus,
productid, memberid)
values ('0E7231DC797C486290E8713CA3C6ECCC', '12345', to_timestamp('02-03-2018 12:00:00.000000',
'dd-mm-yyyy hh24:mi:ss.ff'), 2, '没什么', 0, 1, '676C5BD1D35E429A8C2E114939C5685A',
'E61D65F673D54F68B0861025C69773DB');
insert into ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus,
productid, memberid)
values ('5DC6A48DD4E94592AE904930EA866AFA', '54321', to_timestamp('02-03-2018 12:00:00.000000',
'dd-mm-yyyy hh24:mi:ss.ff'), 2, '没什么', 0, 1, '676C5BD1D35E429A8C2E114939C5685A',
'E61D65F673D54F68B0861025C69773DB');
insert into ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus,
productid, memberid)
values ('2FF351C4AC744E2092DCF08CFD314420', '67890', to_timestamp('02-03-2018 12:00:00.000000',
'dd-mm-yyyy hh24:mi:ss.ff'), 2, '没什么', 0, 1, '12B7ABF2A4C544568B0A7C69F36BF8B7',
'E61D65F673D54F68B0861025C69773DB');
insert into ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus,
productid, memberid)
values ('A0657832D93E4B10AE88A2D4B70B1A28', '98765', to_timestamp('02-03-2018 12:00:00.000000',
'dd-mm-yyyy hh24:mi:ss.ff'), 2, '没什么', 0, 1, '12B7ABF2A4C544568B0A7C69F36BF8B7',
'E61D65F673D54F68B0861025C69773DB');
insert into ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus,
productid, memberid)
values ('E4DD4C45EED84870ABA83574A801083E', '11111', to_timestamp('02-03-2018 12:00:00.000000',
'dd-mm-yyyy hh24:mi:ss.ff'), 2, '没什么', 0, 1, '12B7ABF2A4C544568B0A7C69F36BF8B7',
'E61D65F673D54F68B0861025C69773DB');
insert into ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus,
productid, memberid)
values ('96CC8BD43C734CC2ACBFF09501B4DD5D', '22222', to_timestamp('02-03-2018 12:00:00.000000',
'dd-mm-yyyy hh24:mi:ss.ff'), 2, '没什么', 0, 1, '12B7ABF2A4C544568B0A7C69F36BF8B7',
'E61D65F673D54F68B0861025C69773DB');
insert into ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus,
productid, memberid)
values ('55F9AF582D5A4DB28FB4EC3199385762', '33333', to_timestamp('02-03-2018 12:00:00.000000',
'dd-mm-yyyy hh24:mi:ss.ff'), 2, '没什么', 0, 1, '9F71F01CB448476DAFB309AA6DF9497F',
'E61D65F673D54F68B0861025C69773DB');
insert into ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus,
productid, memberid)
values ('CA005CF1BE3C4EF68F88ABC7DF30E976', '44444', to_timestamp('02-03-2018 12:00:00.000000',
'dd-mm-yyyy hh24:mi:ss.ff'), 2, '没什么', 0, 1, '9F71F01CB448476DAFB309AA6DF9497F',
'E61D65F673D54F68B0861025C69773DB');
insert into ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus,
productid, memberid)
values ('3081770BC3984EF092D9E99760FDABDE', '55555', to_timestamp('02-03-2018 12:00:00.000000',
'dd-mm-yyyy hh24:mi:ss.ff'), 2, '没什么', 0, 1, '9F71F01CB448476DAFB309AA6DF9497F',
'E61D65F673D54F68B0861025C69773DB');
实体类:
package com.gxd.ssm.domain;
import com.gxd.ssm.utils.DateUtils;
import java.util.Date;
import java.util.List;
//订单
public class Orders {
private String id;
private String orderNum;
private Date orderTime;
private String orderTimeStr;
private int orderStatus;
private String orderStatusStr;
private int peopleCount;
private Product product;
private List<Traveller> travellers;
private Member member;
private Integer payType;
private String payTypeStr;
private String orderDesc;
public String getOrderStatusStr() {
//订单状态(0 未支付 1 已支付)
if(orderStatus==0){
orderStatusStr="未支付";
}else if(orderStatus==1){
orderStatusStr="已支付";
}
return orderStatusStr;
}
public void setOrderStatusStr(String orderStatusStr) {
this.orderStatusStr = orderStatusStr;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getOrderNum() {
return orderNum;
}
public void setOrderNum(String orderNum) {
this.orderNum = orderNum;
}
public Date getOrderTime() {
return orderTime;
}
public void setOrderTime(Date orderTime) {
this.orderTime = orderTime;
}
public String getOrderTimeStr() {
if(orderTime!=null){
orderTimeStr= DateUtils.date2String(orderTime,"yyyy-MM-dd HH:mm");
}
return orderTimeStr;
}
public void setOrderTimeStr(String orderTimeStr) {
this.orderTimeStr = orderTimeStr;
}
public int getOrderStatus() {
return orderStatus;
}
public void setOrderStatus(int orderStatus) {
this.orderStatus = orderStatus;
}
public int getPeopleCount() {
return peopleCount;
}
public void setPeopleCount(int peopleCount) {
this.peopleCount = peopleCount;
}
public Product getProduct() {
return product;
}
public void setProduct(Product product) {
this.product = product;
}
public List<Traveller> getTravellers() {
return travellers;
}
public void setTravellers(List<Traveller> travellers) {
this.travellers = travellers;
}
public Member getMember() {
return member;
}
public void setMember(Member member) {
this.member = member;
}
public Integer getPayType() {
return payType;
}
public void setPayType(Integer payType) {
this.payType = payType;
}
public String getPayTypeStr() {
//支付方式(0 支付宝 1 微信 2其它)
if(payType==0){
payTypeStr="支付宝";
}else if(payType==1){
payTypeStr="微信";
}else if(payType==2){
payTypeStr="其它";
}
return payTypeStr;
}
public void setPayTypeStr(String payTypeStr) {
this.payTypeStr = payTypeStr;
}
public String getOrderDesc() {
return orderDesc;
}
public void setOrderDesc(String orderDesc) {
this.orderDesc = orderDesc;
}
}
二.订单查询
问题一:查询所有订单信息时,有一个根据产品id查询单个产品的多表操作
解决:使用注解
public interface IOrdersDao {
@Select("select * from orders")
@Results({
@Result(id=true,column = "id",property = "id"),
@Result(column = "orderNum",property = "orderNum"),
@Result(column = "orderTime",property = "orderTime"),
@Result(column = "orderStatus",property = "orderStatus"),
@Result(column = "peopleCount",property = "peopleCount"),
@Result(column = "payType",property = "payType"),
@Result(column = "orderDesc",property = "orderDesc"),
@Result(column = "productId",property = "product",one = @One(select = "com.gxd.ssm.dao.IProductDao.findById"))
})
List<Orders> findAll() throws Exception;
}
注意:分页PageHelper的配置在applicationContext中进行配置,调用在service层中.
展示所有信息(带分页):
controller:
@RequestMapping("/findAll.do")
public ModelAndView findAll(@RequestParam(name = "page",required = true,defaultValue = "1") int page,@RequestParam(name = "size",required = true,defaultValue = "3")int size) throws Exception {
ModelAndView mv = new ModelAndView();
List<Orders> list = ordersService.findAll(page,size);
PageInfo pageInfo = new PageInfo(list);
mv.addObject("pageInfo",pageInfo);
mv.setViewName("orders-list");
return mv;
}
service(记得加上事务的注解):
//接口
List<Orders> findAll(int page,int size) throws Exception;
//实现类
public List<Orders> findAll(int page,int size) throws Exception {
PageHelper.startPage(page,size);
return ordersDao.findAll();
}
dao:
@Select("select * from orders")
@Results({
@Result(id=true,column = "id",property = "id"),
@Result(column = "orderNum",property = "orderNum"),
@Result(column = "orderTime",property = "orderTime"),
@Result(column = "orderStatus",property = "orderStatus"),
@Result(column = "peopleCount",property = "peopleCount"),
@Result(column = "payType",property = "payType"),
@Result(column = "orderDesc",property = "orderDesc"),
@Result(column = "productId",property = "product",one = @One(select = "com.gxd.ssm.dao.IProductDao.findById"))
})
List<Orders> findAll() throws Exception;
分页的前端代码:
1>页码操作
页码操作
<div class="box-tools pull-right">
<ul class="pagination">
<li>
<a href="${pageContext.request.contextPath}/orders/findAll.do?page=1&size=${pageInfo.pageSize}" aria-label="Previous">首页</a>
</li>
<li><a href="${pageContext.request.contextPath}/orders/findAll.do?page=${pageInfo.pageNum-1}&size=${pageInfo.pageSize}">上一页</a></li>
<c:forEach begin="1" end="${pageInfo.pages}" var="pageNum">
<li><a href="${pageContext.request.contextPath}/orders/findAll.do?page=${pageNum}&size=${pageInfo.pageSize}">${pageNum}</a></li>
</c:forEach>
<li><a href="${pageContext.request.contextPath}/orders/findAll.do?page=${pageInfo.pageNum+1}&size=${pageInfo.pageSize}">下一页</a></li>
<li>
<a href="${pageContext.request.contextPath}/orders/findAll.do?page=${pageInfo.pages}&size=${pageInfo.pageSize}" aria-label="Next">尾页</a>
</li>
</ul>
</div>
2>设置每页显示的条数
设置每页显示的条数
//HTML部分
<div class="pull-left">
<div class="form-group form-inline">
总共2 页，共14 条数据。 每页
<select class="form-control" id="changePageSize" onchange="changePageSize()">
<option>1</option>
<option>2</option>
<option>3</option>
<option>4</option>
<option>5</option>
</select> 条
</div>
</div>
//js部分
function changePageSize() {
//获取下拉框的值
var pageSize = $("#changePageSize").val();
//向服务器发送请求，改变没页显示条数
location.href = "${pageContext.request.contextPath}/orders/findAll.do?page=1&size="
+ pageSize;
}
效果:
展示所有订单信息
三.订单详情
注意:实体层的描述信息
1.controller:
@RequestMapping("/findById.do")
public ModelAndView findById(@RequestParam(name = "id",required = true) String id) throws Exception {
ModelAndView mv = new ModelAndView();
Orders orders = ordersService.findById(id);
mv.addObject("orders",orders);
mv.setViewName("orders-show");
return mv;
}
2.service:
//接口
Orders findById(String id) throws Exception;
//实现类
@Override
public Orders findById(String id) throws Exception {
return ordersDao.findById(id);
}
3.dao:关键在多表查询,one和many
@Select("select * from orders where id=#{id}")
@Results({
@Result(id=true,column = "id",property = "id"),
@Result(column = "orderNum",property = "orderNum"),
@Result(column = "orderTime",property = "orderTime"),
@Result(column = "orderStatus",property = "orderStatus"),
@Result(column = "peopleCount",property = "peopleCount"),
@Result(column = "payType",property = "payType"),
@Result(column = "orderDesc",property = "orderDesc"),
@Result(column = "productId",property = "product",javaType = Product.class,one = @One(select = "com.gxd.ssm.dao.IProductDao.findById")),
@Result(column = "memberId",property = "member",javaType = Member.class,one = @One(select = "com.gxd.ssm.dao.IMemberDao.findById")),
@Result(column = "id",property = "travellers",javaType =java.util.List.class,many = @Many(select = "com.gxd.ssm.dao.ITravellerDao.findByOrdersId"))
})
Orders findById(String id) throws Exception;
效果:
订单详情
网友评论