项目案例：商城Market（1）—— 数据表分析设计与框架搭建

一、项目的数据库与表的分析与设计

  • 使用 PowerDesigner 工具设计模型 PDM
  • 表结构.png
  • PowerDesigner结构.png
    • 设计完成后生成数据库的 .sql文件
    /*
 Navicat MySQL Data Transfer
 Source Server         : MySQL
 Source Server Type    : MySQL
 Source Server Version : 80018
 Source Host           : localhost:3307
 Source Schema         : supermarket
 Target Server Type    : MySQL
 Target Server Version : 80018
 File Encoding         : 65001
 Date: 24/03/2020 15:38:30
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for address
-- ----------------------------
DROP TABLE IF EXISTS `address`;
CREATE TABLE `address`  (
  `did` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `province` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `city` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `township` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `detial` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `types` int(11) NULL DEFAULT 1,
  PRIMARY KEY (`did`) USING BTREE,
  INDEX `FK_u_a`(`user_id`) USING BTREE,
  CONSTRAINT `FK_u_a` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for cart
-- ----------------------------
DROP TABLE IF EXISTS `cart`;
CREATE TABLE `cart`  (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `fdid` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `count` int(11) NOT NULL,
  PRIMARY KEY (`cid`) USING BTREE,
  INDEX `FK_fg_c`(`fdid`) USING BTREE,
  INDEX `FK_u_c`(`user_id`) USING BTREE,
  CONSTRAINT `FK_fg_c` FOREIGN KEY (`fdid`) REFERENCES `fresh_goods` (`fdid`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK_u_c` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for citys
-- ----------------------------
DROP TABLE IF EXISTS `citys`;
CREATE TABLE `citys`  (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `pid` int(11) NOT NULL,
  PRIMARY KEY (`cid`) USING BTREE,
  INDEX `FK_p_c`(`pid`) USING BTREE,
  CONSTRAINT `FK_p_c` FOREIGN KEY (`pid`) REFERENCES `province` (`pid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for courier
-- ----------------------------
DROP TABLE IF EXISTS `courier`;
CREATE TABLE `courier`  (
  `account` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `tel` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`account`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for emp_role
-- ----------------------------
DROP TABLE IF EXISTS `emp_role`;
CREATE TABLE `emp_role`  (
  `eid` int(11) NOT NULL,
  `rid` int(11) NOT NULL,
  PRIMARY KEY (`eid`, `rid`) USING BTREE,
  INDEX `FK_r_er`(`rid`) USING BTREE,
  CONSTRAINT `FK_e_er` FOREIGN KEY (`eid`) REFERENCES `employee` (`eid`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK_r_er` FOREIGN KEY (`rid`) REFERENCES `roles` (`rid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee`  (
  `eid` int(11) NOT NULL AUTO_INCREMENT,
  `ename` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `password` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `tel` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`eid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for evaluation
-- ----------------------------
DROP TABLE IF EXISTS `evaluation`;
CREATE TABLE `evaluation`  (
  `eid` int(11) NOT NULL,
  `user_id` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `fdid` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `detial` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `grade` int(11) NOT NULL DEFAULT 5,
  `eva_date` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`eid`) USING BTREE,
  INDEX `FK_fg_e`(`fdid`) USING BTREE,
  INDEX `FK_u_e`(`user_id`) USING BTREE,
  CONSTRAINT `FK_fg_e` FOREIGN KEY (`fdid`) REFERENCES `fresh_goods` (`fdid`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK_u_e` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for fresh_goods
-- ----------------------------
DROP TABLE IF EXISTS `fresh_goods`;
CREATE TABLE `fresh_goods`  (
  `fdid` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `good_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `gtid` int(11) NOT NULL,
  `img` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `price` double NOT NULL,
  `discount` int(11) NOT NULL DEFAULT 0,
  `preference` int(11) NOT NULL,
  `if_promotion` int(11) NOT NULL DEFAULT 0,
  `last_sales` int(11) NOT NULL,
  `current_sales` int(11) NOT NULL,
  `count_sales` int(11) NOT NULL,
  `summery` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `attr1` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `attr2` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`fdid`) USING BTREE,
  INDEX `FK_gt_fg`(`gtid`) USING BTREE,
  CONSTRAINT `FK_gt_fg` FOREIGN KEY (`gtid`) REFERENCES `good_type` (`gtid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for good_type
-- ----------------------------
DROP TABLE IF EXISTS `good_type`;
CREATE TABLE `good_type`  (
  `gtid` int(11) NOT NULL AUTO_INCREMENT,
  `gtname` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`gtid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for logistics
-- ----------------------------
DROP TABLE IF EXISTS `logistics`;
CREATE TABLE `logistics`  (
  `lid` int(11) NOT NULL,
  `account` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `user_id` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `oid` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `pay_time` datetime(0) NOT NULL,
  `package_time` datetime(0) NULL DEFAULT NULL,
  `begin_time` datetime(0) NULL DEFAULT NULL,
  `end_time` datetime(0) NULL DEFAULT NULL,
  `status` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`lid`) USING BTREE,
  INDEX `FK_c_l`(`account`) USING BTREE,
  INDEX `FK_o_l`(`oid`) USING BTREE,
  INDEX `FK_u_l`(`user_id`) USING BTREE,
  CONSTRAINT `FK_c_l` FOREIGN KEY (`account`) REFERENCES `courier` (`account`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK_o_l` FOREIGN KEY (`oid`) REFERENCES `orders` (`oid`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK_u_l` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for order_detial
-- ----------------------------
DROP TABLE IF EXISTS `order_detial`;
CREATE TABLE `order_detial`  (
  `did` int(11) NOT NULL AUTO_INCREMENT,
  `oid` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `fdid` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `count` int(11) NOT NULL,
  PRIMARY KEY (`did`) USING BTREE,
  INDEX `FK_o_od`(`oid`) USING BTREE,
  CONSTRAINT `FK_o_od` FOREIGN KEY (`oid`) REFERENCES `orders` (`oid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for orders
-- ----------------------------
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders`  (
  `oid` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `user_id` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `order_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `total_price` double NOT NULL,
  `order_status` int(11) NOT NULL DEFAULT 1,
  `pay_type` int(11) NOT NULL,
  PRIMARY KEY (`oid`) USING BTREE,
  INDEX `FK_u_o`(`user_id`) USING BTREE,
  CONSTRAINT `FK_u_o` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for permissions
-- ----------------------------
DROP TABLE IF EXISTS `permissions`;
CREATE TABLE `permissions`  (
  `pid` int(11) NOT NULL,
  `pname` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`pid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for province
-- ----------------------------
DROP TABLE IF EXISTS `province`;
CREATE TABLE `province`  (
  `pid` int(11) NOT NULL AUTO_INCREMENT,
  `pname` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`pid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for role_perm
-- ----------------------------
DROP TABLE IF EXISTS `role_perm`;
CREATE TABLE `role_perm`  (
  `rid` int(11) NOT NULL,
  `pid` int(11) NOT NULL,
  PRIMARY KEY (`rid`, `pid`) USING BTREE,
  INDEX `FK_p_rp`(`pid`) USING BTREE,
  CONSTRAINT `FK_r_rp` FOREIGN KEY (`rid`) REFERENCES `roles` (`rid`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK_p_rp` FOREIGN KEY (`pid`) REFERENCES `permissions` (`pid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for roles
-- ----------------------------
DROP TABLE IF EXISTS `roles`;
CREATE TABLE `roles`  (
  `rid` int(11) NOT NULL,
  `rname` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `detial` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`rid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for township
-- ----------------------------
DROP TABLE IF EXISTS `township`;
CREATE TABLE `township`  (
  `tid` int(11) NOT NULL,
  `tname` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `cid` int(11) NOT NULL,
  PRIMARY KEY (`tid`) USING BTREE,
  INDEX `FK_c_t`(`cid`) USING BTREE,
  CONSTRAINT `FK_c_t` FOREIGN KEY (`cid`) REFERENCES `citys` (`cid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users`  (
  `user_id` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `user_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `password` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `email` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `tel` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `level` int(11) NULL DEFAULT 1,
  `integral` int(11) NULL DEFAULT 0,
  `if_new` int(11) NULL DEFAULT 0,
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;

二、创建项目并搭建框架

  • 打开 IDEA 创建一个Maven项目，并连接数据库与配置SSM框架
  • 使用EasyCode创建相应的controller、dao、entity、service 和 mapper 层
  • 将前端页面移植进项目内：链接：https://pan.baidu.com/s/1Qj-ZE8bW3a3vp2vYLaqwRA
    提取码：fb7v
  • 项目搭建完成
  • 测试项目 
    • @RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:spring-mybatis.xml")
public class TestUserService {
    @Autowired
    private UsersService usersService;
    @Test
    public void testService(){
        Users s = usersService.queryById("100001");
        System.out.println(s);
    }
}

    • 单元测试结果

      INFO [main] - {dataSource-1} inited
 DEBUG [main] - ==>  Preparing: select user_id, user_name, password, email, tel, level, integral, if_new from supermarket.users where user_id = ? 
 DEBUG [main] - ==> Parameters: 100001(String)
 TRACE [main] - <==    Columns: user_id, user_name, password, email, tel, level, integral, if_new
 TRACE [main] - <==        Row: 100001, AAA, 1236987, 123456@abc.com, 18888888888, 1, 0, 0
 DEBUG [main] - <==      Total: 1
com.entity.Users@687ef2e0
  INFO [Thread-1] - {dataSource-1} closed

    • UsersController.java

      @RestController
@RequestMapping("users")
public class UsersController {
    @Resource
    private UsersService usersService;
    /**
     * 通过主键查询单条数据
     * @param id 主键
     * @return 单条数据
     */
    @GetMapping("selectOne/{id}")
    public Users selectOne(@PathVariable String id) {
        return this.usersService.queryById(id);
    }
    @GetMapping("/selectByPage")
    public List<Users> getUsersByPage(@RequestParam(value = "cp",defaultValue = "1") int currentPage ,
                                  @RequestParam(value = "ps",defaultValue = "5") int pageSize){
        currentPage = currentPage <= 1 ? 1 : currentPage;
        pageSize = pageSize <= 0 ? 10 : pageSize;
        List list = usersService.queryAllByLimit((currentPage - 1) * pageSize,pageSize);
        return list;
    }
}
  • 打开浏览器输入：http://localhost:8081/users/selectByPage 测试是否成功
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容