一凌埂、項目的數(shù)據(jù)庫與表的分析與設(shè)計
- 使用 PowerDesigner 工具設(shè)計模型 PDM
-
- 設(shè)計完成后生成數(shù)據(jù)庫的 .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;
二堂淡、創(chuàng)建項目并搭建框架
- 打開 IDEA 創(chuàng)建一個Maven項目,并連接數(shù)據(jù)庫與配置SSM框架
- 使用EasyCode創(chuàng)建相應(yīng)的controller、dao锥累、entity侍匙、service 和 mapper 層
-
將前端頁面移植進項目內(nèi):鏈接: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); } }
-
單元測試結(jié)果
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; /** * 通過主鍵查詢單條數(shù)據(jù) * @param id 主鍵 * @return 單條數(shù)據(jù) */ @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 測試是否成功