day01_springboot综合案例（一）https://developer.aliyun.com/article/1433639
订单表 （orders）
- orders表信息描述
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Ofbal5CE-1690118686234)(assets/image-20201014063953436.png)]
productId描述了订单与产品之间的关系。
memberid描述了订单与会员之间的关系。 - orders表sql
CREATE TABLE orders( id VARCHAR(32) PRIMARY KEY, orderNum VARCHAR(20) NOT NULL UNIQUE, orderTime TIMESTAMP, peopleCount INT, orderDesc VARCHAR(500), payType INT, orderStatus INT, productId VARCHAR(32), memberId VARCHAR(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', NULL, 2, '没什么', 0, 1, '676C5BD1D35E429A8C2E114939C5685A', 'E61D65F673D54F68B0861025C69773DB'); INSERT INTO ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus, productid, memberid) VALUES ('5DC6A48DD4E94592AE904930EA866AFA', '54321', NULL, 2, '没什么', 0, 1, '676C5BD1D35E429A8C2E114939C5685A', 'E61D65F673D54F68B0861025C69773DB'); INSERT INTO ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus, productid, memberid) VALUES ('2FF351C4AC744E2092DCF08CFD314420', '67890', NULL, 2, '没什么', 0, 1, '12B7ABF2A4C544568B0A7C69F36BF8B7', 'E61D65F673D54F68B0861025C69773DB'); INSERT INTO ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus, productid, memberid) VALUES ('A0657832D93E4B10AE88A2D4B70B1A28', '98765', NULL, 2, '没什么', 0, 1, '12B7ABF2A4C544568B0A7C69F36BF8B7', 'E61D65F673D54F68B0861025C69773DB'); INSERT INTO ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus, productid, memberid) VALUES ('E4DD4C45EED84870ABA83574A801083E', '11111', NULL, 2, '没什么', 0, 1, '12B7ABF2A4C544568B0A7C69F36BF8B7', 'E61D65F673D54F68B0861025C69773DB'); INSERT INTO ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus, productid, memberid) VALUES ('96CC8BD43C734CC2ACBFF09501B4DD5D', '22222', NULL, 2, '没什么', 0, 1, '12B7ABF2A4C544568B0A7C69F36BF8B7', 'E61D65F673D54F68B0861025C69773DB'); INSERT INTO ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus, productid, memberid) VALUES ('55F9AF582D5A4DB28FB4EC3199385762', '33333', NULL, 2, '没什么', 0, 1, '9F71F01CB448476DAFB309AA6DF9497F', 'E61D65F673D54F68B0861025C69773DB'); INSERT INTO ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus, productid, memberid) VALUES ('CA005CF1BE3C4EF68F88ABC7DF30E976', '44444', NULL, 2, '没什么', 0, 1, '9F71F01CB448476DAFB309AA6DF9497F', 'E61D65F673D54F68B0861025C69773DB'); INSERT INTO ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus, productid, memberid) VALUES ('3081770BC3984EF092D9E99760FDABDE', '55555', NULL, 2, '没什么', 0, 1, '9F71F01CB448476DAFB309AA6DF9497F', 'E61D65F673D54F68B0861025C69773DB');
旅客表（ traveller）
- traveller表信息描述
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ikpdABTf-1690118686236)(assets/image-20201014064251188.png)] - traveller表sql
CREATE TABLE traveller( id VARCHAR(32) PRIMARY KEY, NAME VARCHAR(20), sex VARCHAR(20), phoneNum VARCHAR(20), credentialsType INT, credentialsNum VARCHAR(50), travellerType INT ); INSERT 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);
中间表 (order_traveller)
旅客与订单之间是多对多关系，所以我们需要一张中间表（order_traveller）来描述。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qeywgnpD-1690118686237)(assets/image-20201014064431518.png)]
- order_traveller表sql
CREATE TABLE order_traveller( orderId VARCHAR(32), travellerId VARCHAR(32), PRIMARY KEY (orderId,travellerId), FOREIGN KEY (orderId) REFERENCES orders(id), FOREIGN KEY (travellerId) REFERENCES traveller(id) ); INSERT 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');
实体类
Product类
package cn.yanqi.ssm.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import org.springframework.format.annotation.DateTimeFormat; import java.text.SimpleDateFormat; import java.util.Date; @Data public class Product { private String id; private String productNum; private String productName; private String cityName; @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm") private Date departureTime; private String departureTimeStr; private Integer productPrice; private String productDesc; private Integer productStatus; private String productStatusStr; public String getDepartureTimeStr() { // 对日期格式化 SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm"); if (null != departureTime) { departureTimeStr = dateFormat.format(departureTime); } return departureTimeStr; } public String getProductStatusStr() { if (productStatus == null) { return ""; } if (productStatus == 0){ productStatusStr = "关闭"; } else if(productStatus == 1){ productStatusStr = "开启"; } return productStatusStr; } }
Member类
package cn.yanqi.ssm.pojo; import lombok.Data; @Data public class Member { private String id; private String name; private String nickname; private String phoneNum; private String email; }
Orders类
package cn.yanqi.ssm.pojo; import lombok.Data; import org.springframework.format.annotation.DateTimeFormat; import java.text.SimpleDateFormat; import java.util.Date; import java.util.List; @Data public class Orders { private String id; private String orderNum; @DateTimeFormat(pattern="yyyy-MM-dd HH:mm") 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; //用于添加订单时，添加产品，会员 private Integer productId; private Integer memberId; public String getOrderTimeStr() { // 对日期格式化 SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); orderTimeStr = dateFormat.format(orderTime); return orderTimeStr; } public String getPayTypeStr() { if (payType == 0){ payTypeStr = "支付宝"; } else if(payType == 1){ payTypeStr = "微信"; }else if(payType == 2){ payTypeStr = "其他"; } return payTypeStr; } public String getOrderStatusStr() { if (orderStatus == 0){ orderStatusStr = "未支付"; } else if(orderStatus == 1){ orderStatusStr = "已支付"; } return orderStatusStr; } }
Traveller类
package cn.yanqi.ssm.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; @Data public class Traveller { private String id; private String name; private String sex; private String phoneNum; private Integer credentialsType; private String credentialsTypeStr; private String credentialsNum; private Integer travellerType; private String travellerTypeStr; public String getCredentialsTypeStr() { // 证件类型 0：身份证 1：护照 2：军官证 if (credentialsType == 0) { credentialsTypeStr = "身份证"; } else if (credentialsType == 1) { credentialsTypeStr = "护照"; } else if (credentialsType == 2) { credentialsTypeStr = "军官证"; } return credentialsTypeStr; } public String getTravellerTypeStr() { // 证件类型 0：成人 1：儿童 if (travellerType == 0) { travellerTypeStr = "成人"; } else if (travellerType == 1) { travellerTypeStr = "儿童"; } return travellerTypeStr; } }
产品操作
查询所有产品信息
Product实体类
package cn.yanqi.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import org.springframework.format.annotation.DateTimeFormat; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.Date; @NoArgsConstructor @AllArgsConstructor @Data public class Product { private String id; private String productNum; private String productName; private String cityName; @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm") private Date departureTime; private String departureTimeStr; private Integer productPrice; private String productDesc; private Integer productStatus; private String productStatusStr; public void setDepartureTimeStr(String departureTimeStr) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm"); Date date = null; try { date = dateFormat.parse(departureTimeStr); } catch (ParseException e) { e.printStackTrace(); } this.departureTime = date; } public String getDepartureTimeStr() { // 对日期格式化 SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm"); if (null != departureTime) { departureTimeStr = dateFormat.format(departureTime); } return departureTimeStr; } public void setProductStatusStr(String productStatusStr) { if (productStatusStr.equals("关闭")){ this.productStatus = 0; } else if(productStatusStr.equals("开启")){ this.productStatus = 1; } } public String getProductStatusStr() { if (productStatus == null) { return ""; } if (productStatus == 0){ productStatusStr = "关闭"; } else if(productStatus == 1){ productStatusStr = "开启"; } return productStatusStr; } }
编写ProductController
/** * @Author: yanqi * @Date: 12:00 * @Desc: */ @Controller @RequestMapping("product") public class ProductController { @Autowired private ProductService productService; @GetMapping("findAll") public String findAll(@RequestParam(value = "page", defaultValue = "1") Integer age , @RequestParam(value = "size",defaultValue = "5") Integer size, Model model){ PageHelper.startPage(age,size); List<Product> list = this.productService.findAll(); PageInfo pageInfo = new PageInfo(list); model.addAttribute("pageInfo",pageInfo); return "product-list"; } }
编写ProductService
public interface ProductService { /** * 查询所有产品 * @return */ List<Product> findAll(Integer page, Integer size); }
@Service("productService") public class ProductServiceImpl implements ProductService { @Autowired private ProductMapper productMapper; /** * 查询所有产品 * @return */ @Override public List<Product> findAll(Integer page, Integer size) { PageHelper.startPage(page,size); return this.productMapper.findAll(); }
编写ProductMapper
public interface ProductMapper { /** * 查询所有产品 * @return */ List<Product> findAll(); }
编写ProductMapper.xml
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="cn.yanqi.ssm.mapper.ProductMapper"> <!--查询所有产品--> <select id="findAll" resultType="Product"> select * from product order by id desc </select> </mapper>
注意事项
在产品添加时，出发时间是一个日期格式，我们需要对日期进行格式化。日期格式化有两种方式：
- 使用
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")； - 使用
Converter接口来实现日期格式化。
在这里我们使用@DateTimeFormat来进行日期格式化。我们需要在Product类中的departureTime加上该注解
@DateTimeFormat(pattern = "yyyy-MM-dd") private Date departureTime;
day01_springboot综合案例（三）https://developer.aliyun.com/article/1433640
