Springboot整合MybatisPlus第二期
上期我们搭建好了我们项目的框架,这期我们来进行对数据库的增删改查测试
数据库放下面了,需要的同学请自取
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 /* 2 Navicat MySQL Data Transfer 3 4 Source Server : FirstLian 5 Source Server Version : 80015 6 Source Host : localhost:3306 7 Source Database : smbms02 8 9 Target Server Type : MYSQL 10 Target Server Version : 80015 11 File Encoding : 65001 12 13 Date: 2021-04-16 08:40:05 14 */ 15 16 SET FOREIGN_KEY_CHECKS=0; 17 18 -- ---------------------------- 19 -- Table structure for smbms_account 20 -- ---------------------------- 21 DROP TABLE IF EXISTS `smbms_account`; 22 CREATE TABLE `smbms_account` ( 23 `id` int(11) NOT NULL, 24 `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL, 25 `money` int(10) unsigned DEFAULT NULL, 26 `cardNo` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL, 27 `pwd` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL, 28 PRIMARY KEY (`id`) 29 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 30 31 -- ---------------------------- 32 -- Records of smbms_account 33 -- ---------------------------- 34 INSERT INTO `smbms_account` VALUES ('1001', '张三', '26400', '131999', '12345'); 35 INSERT INTO `smbms_account` VALUES ('1002', '李四', '4000', '131888', '12345'); 36 INSERT INTO `smbms_account` VALUES ('1003', '张安', '10900', '137555', '12345'); 37 INSERT INTO `smbms_account` VALUES ('1004', '张无极', '10900', '137666', '12345'); 38 INSERT INTO `smbms_account` VALUES ('1005', '李楼', '1500', '138555', '12345'); 39 INSERT INTO `smbms_account` VALUES ('1006', '李颖', '2200', '137222', '12345'); 40 INSERT INTO `smbms_account` VALUES ('1100', '王五', '8000', '137333', '12345'); 41 INSERT INTO `smbms_account` VALUES ('1101', '小吴', '1200', '135666', '12345'); 42 INSERT INTO `smbms_account` VALUES ('1102', '小刘', '2300', '151555', '12345'); 43 INSERT INTO `smbms_account` VALUES ('1103', '小六', '4500', '151777', '12345'); 44 INSERT INTO `smbms_account` VALUES ('3000', '小二郎', '10', '5234', '111'); 45 46 -- ---------------------------- 47 -- Table structure for smbms_goods 48 -- ---------------------------- 49 DROP TABLE IF EXISTS `smbms_goods`; 50 CREATE TABLE `smbms_goods` ( 51 `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID', 52 `code` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '商品编码', 53 `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '商品名称', 54 `desc` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '商品描述', 55 `unit` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '商品单位', 56 `purchase_price` decimal(20,2) DEFAULT NULL COMMENT '采购价格', 57 `selling_price` decimal(20,2) DEFAULT NULL COMMENT '销售价格', 58 `producer` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '产地', 59 `created_By` bigint(20) DEFAULT NULL COMMENT '创建者(userId)', 60 `creation_Date` datetime DEFAULT NULL COMMENT '创建时间', 61 `modify_By` bigint(20) DEFAULT NULL COMMENT '更新者(userId)', 62 `modify_date` datetime DEFAULT NULL COMMENT '更新时间', 63 `provider_id` bigint(20) DEFAULT NULL COMMENT '供应商ID', 64 PRIMARY KEY (`id`) 65 ) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 66 67 -- ---------------------------- 68 -- Records of smbms_goods 69 -- ---------------------------- 70 INSERT INTO `smbms_goods` VALUES ('1', 'BILL2016_001', '洗发水、护发素', '日用品-洗发、护发', '瓶', '500.00', '25000.00', '东莞', '1', '2014-12-14 13:02:03', null, null, '13'); 71 INSERT INTO `smbms_goods` VALUES ('2', 'BILL2016_002', '香皂、肥皂、药皂', '日用品-皂类', '块', '1000.00', '10000.00', '东莞', '1', '2016-03-23 04:20:40', null, null, '13'); 72 INSERT INTO `smbms_goods` VALUES ('3', 'BILL2016_003', '大豆油', '食品-食用油', '斤', '300.00', '5890.00', '东莞', '1', '2014-12-14 13:02:03', null, null, null); 73 INSERT INTO `smbms_goods` VALUES ('4', 'BILL2016_004', '橄榄油', '食品-进口食用油', '斤', '200.00', '9800.00', '广州', '1', '2013-10-10 03:12:13', null, null, '7'); 74 INSERT INTO `smbms_goods` VALUES ('5', 'BILL2016_005', '洗洁精', '日用品-厨房清洁', '瓶', '500.00', '7000.00', '广州', '1', '2014-12-14 13:02:03', null, null, '9'); 75 INSERT INTO `smbms_goods` VALUES ('6', 'BILL2016_006', '美国大杏仁', '食品-坚果', '袋', '300.00', '5000.00', '深圳', '1', '2016-04-14 06:08:09', null, null, '4'); 76 INSERT INTO `smbms_goods` VALUES ('7', 'BILL2016_007', '沐浴液、精油', '日用品-沐浴类', '瓶', '500.00', '23000.00', '上海', '1', '2016-07-22 10:10:22', null, null, '14'); 77 INSERT INTO `smbms_goods` VALUES ('8', 'BILL2016_008', '不锈钢盘碗', '日用品-厨房用具', '个', '600.00', '6000.00', '上海', '1', '2016-04-14 05:12:13', null, null, '14'); 78 INSERT INTO `smbms_goods` VALUES ('9', 'BILL2016_009', '塑料杯', '日用品-杯子', '个', '350.00', '1750.00', '宁波', '1', '2016-02-04 11:40:20', null, null, '14'); 79 INSERT INTO `smbms_goods` VALUES ('10', 'BILL2016_010', '豆瓣酱', '食品-调料', '瓶', '200.00', '2000.00', '宁波', '1', '2013-10-29 05:07:03', null, null, '8'); 80 INSERT INTO `smbms_goods` VALUES ('11', 'BILL2016_011', '海之蓝', '饮料-国酒', '瓶', '50.00', '10000.00', '杭州', '1', '2016-04-14 16:16:00', null, null, '1'); 81 INSERT INTO `smbms_goods` VALUES ('12', 'BILL2016_012', '芝华士', '饮料-洋酒', '瓶', '20.00', '6000.00', '杭州', '1', '2016-09-09 17:00:00', null, null, '1'); 82 INSERT INTO `smbms_goods` VALUES ('13', 'BILL2016_013', '长城红葡萄酒', '饮料-红酒', '瓶', '60.00', '800.00', '宁波', '1', '2016-11-14 15:23:00', null, null, '1'); 83 INSERT INTO `smbms_goods` VALUES ('14', 'BILL2016_014', '泰国香米', '食品-大米', '斤', '400.00', '5000.00', '宁波', '1', '2016-10-09 15:20:00', null, null, '3'); 84 INSERT INTO `smbms_goods` VALUES ('15', 'BILL2016_015', '东北大米', '食品-大米', '斤', '600.00', '4000.00', '宁波', '1', '2016-11-14 14:00:00', null, null, '3'); 85 INSERT INTO `smbms_goods` VALUES ('16', 'BILL2016_016', '可口可乐', '饮料', '瓶', '2000.00', '6000.00', '宁波', '1', '2012-03-27 13:03:01', null, null, '2'); 86 INSERT INTO `smbms_goods` VALUES ('17', 'BILL2016_017', '脉动', '饮料', '瓶', '1500.00', '4500.00', '宁波', '1', '2016-05-10 12:00:00', null, null, '2'); 87 INSERT INTO `smbms_goods` VALUES ('18', 'BILL2016_018', '哇哈哈', '饮料', '瓶', '2000.00', '4000.00', '宁波', '1', '2015-11-24 15:12:03', null, null, '2'); 88 89 -- ---------------------------- 90 -- Table structure for smbms_link_user 91 -- ---------------------------- 92 DROP TABLE IF EXISTS `smbms_link_user`; 93 CREATE TABLE `smbms_link_user` ( 94 `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID', 95 `contact` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '联系人姓名', 96 `address_Desc` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '收货地址明细', 97 `post_Code` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '邮编', 98 `tel` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '联系人电话', 99 `created_By` bigint(20) DEFAULT NULL COMMENT '创建者', 100 `creation_Date` datetime DEFAULT NULL COMMENT '创建时间', 101 `modify_By` bigint(20) DEFAULT NULL COMMENT '修改者', 102 `modify_Date` datetime DEFAULT NULL COMMENT '修改时间', 103 `user_Id` bigint(20) DEFAULT NULL COMMENT '用户ID', 104 PRIMARY KEY (`id`) 105 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 106 107 -- ---------------------------- 108 -- Records of smbms_link_user 109 -- ---------------------------- 110 INSERT INTO `smbms_link_user` VALUES ('1', '王凯', '石家庄', '100010', '13678789999', '1', '2016-04-13 14:30:27', null, '2017-12-01 14:23:00', '1'); 111 INSERT INTO `smbms_link_user` VALUES ('2', '张红丽', '北京市海淀区丹棱街3号', '100000', '18567672312', '1', '2016-04-13 00:00:00', null, null, '1'); 112 INSERT INTO `smbms_link_user` VALUES ('3', '任志强', '北京市东城区美术馆后街23号', '100021', '13387906742', '1', '2016-04-13 00:00:00', null, null, '1'); 113 INSERT INTO `smbms_link_user` VALUES ('4', '曹颖', '北京市朝阳区朝阳门南大街14号', '100053', '13568902323', '1', '2016-04-13 00:00:00', null, null, '2'); 114 INSERT INTO `smbms_link_user` VALUES ('5', '李慧', '北京市西城区三里河路南三巷3号', '100032', '18032356666', '1', '2016-04-13 00:00:00', null, null, '15'); 115 INSERT INTO `smbms_link_user` VALUES ('6', '王国强', '北京市顺义区高丽营镇金马工业区18号', '100061', '13787882222', '1', '2016-04-13 00:00:00', null, null, '15'); 116 117 -- ---------------------------- 118 -- Table structure for smbms_order 119 -- ---------------------------- 120 DROP TABLE IF EXISTS `smbms_order`; 121 CREATE TABLE `smbms_order` ( 122 `id` int(11) NOT NULL AUTO_INCREMENT, 123 `CREATE_TIME` datetime DEFAULT NULL COMMENT '生成日期', 124 `CHECK_TIME` datetime DEFAULT NULL COMMENT '审核日期', 125 `purchase_TIME` datetime DEFAULT NULL COMMENT '采购日期', 126 `stock_TIME` datetime DEFAULT NULL COMMENT '入库或出库日期', 127 `TYPE` int(11) DEFAULT NULL COMMENT '1:采购 2:销售', 128 `creator` int(11) DEFAULT NULL COMMENT '下单员', 129 `CHECKER` bigint(20) DEFAULT NULL COMMENT '审核员', 130 `purchaser` bigint(20) DEFAULT NULL COMMENT '采购员', 131 `stock_keeper` bigint(20) DEFAULT NULL COMMENT '库管员', 132 `provider_id` bigint(20) DEFAULT NULL COMMENT '供应商或客户ID', 133 `TOTAL_MONEY` decimal(10,2) DEFAULT NULL COMMENT '合计金额', 134 `state` char(1) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '采购: 0:未审核 1:已审核, 2:已确认, 3:已入库;销售:0:未出库 1:已出库', 135 `way_bill_code` bigint(20) NOT NULL COMMENT '运单号', 136 PRIMARY KEY (`id`) 137 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 138 139 -- ---------------------------- 140 -- Records of smbms_order 141 -- ---------------------------- 142 143 -- ---------------------------- 144 -- Table structure for smbms_order_detail 145 -- ---------------------------- 146 DROP TABLE IF EXISTS `smbms_order_detail`; 147 CREATE TABLE `smbms_order_detail` ( 148 `id` int(11) NOT NULL, 149 `goods_id` int(11) DEFAULT NULL, 150 `GOODS_NAME` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL, 151 `PRICE` decimal(10,2) DEFAULT NULL, 152 `num` int(11) DEFAULT NULL COMMENT '数量', 153 `MONEY` decimal(10,2) DEFAULT NULL COMMENT '小计', 154 `END_TIME` datetime DEFAULT NULL COMMENT '结束日期', 155 `stock_keeper` bigint(20) DEFAULT NULL COMMENT '库管员ID', 156 `storehouse_id` int(11) DEFAULT NULL COMMENT '仓库ID', 157 `STATE` int(11) DEFAULT NULL COMMENT '采购:0=未入库,1=已入库 销售:0=未出库,1=已出库', 158 `order_id` int(11) DEFAULT NULL COMMENT '订单编号', 159 PRIMARY KEY (`id`) 160 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 161 162 -- ---------------------------- 163 -- Records of smbms_order_detail 164 -- ---------------------------- 165 166 -- ---------------------------- 167 -- Table structure for smbms_org 168 -- ---------------------------- 169 DROP TABLE IF EXISTS `smbms_org`; 170 CREATE TABLE `smbms_org` ( 171 `id` int(11) NOT NULL AUTO_INCREMENT, 172 `orgName` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL, 173 `orgDescr` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL, 174 PRIMARY KEY (`id`) 175 ) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 176 177 -- ---------------------------- 178 -- Records of smbms_org 179 -- ---------------------------- 180 INSERT INTO `smbms_org` VALUES ('1', '开发部', '软件开发'); 181 INSERT INTO `smbms_org` VALUES ('7', '市场部', '市场宣传'); 182 INSERT INTO `smbms_org` VALUES ('9', '咨询部', '负责咨询'); 183 INSERT INTO `smbms_org` VALUES ('23', '市场运营部', '阿里市场建设'); 184 INSERT INTO `smbms_org` VALUES ('25', '软件测试部2', '负责测试2'); 185 186 -- ---------------------------- 187 -- Table structure for smbms_permission 188 -- ---------------------------- 189 DROP TABLE IF EXISTS `smbms_permission`; 190 CREATE TABLE `smbms_permission` ( 191 `id` int(11) NOT NULL AUTO_INCREMENT, 192 `name` varchar(255) DEFAULT NULL, 193 `resource_type` enum('menu','button') DEFAULT NULL, 194 `icon` varchar(30) DEFAULT NULL, 195 `perm_code` varchar(255) DEFAULT NULL, 196 `sort` int(11) DEFAULT NULL, 197 `url` varchar(255) DEFAULT NULL, 198 `parent_id` int(11) DEFAULT NULL, 199 PRIMARY KEY (`id`) 200 ) ENGINE=InnoDB AUTO_INCREMENT=117 DEFAULT CHARSET=utf8; 201 202 -- ---------------------------- 203 -- Records of smbms_permission 204 -- ---------------------------- 205 INSERT INTO `smbms_permission` VALUES ('1', '系统设置', 'menu', 'md-keypad', 'sys:*', '10', null, null); 206 INSERT INTO `smbms_permission` VALUES ('2', '销售管理', 'menu', 'md-basket', 'sal:*', '20', null, null); 207 INSERT INTO `smbms_permission` VALUES ('3', '客户管理', 'menu', 'md-body', 'cus:*', '30', null, null); 208 INSERT INTO `smbms_permission` VALUES ('4', '服务管理', 'menu', 'md-help-buoy', 'service:*', '40', null, null); 209 INSERT INTO `smbms_permission` VALUES ('5', '订单管理', 'menu', 'md-ribbon', 'order:*', '50', null, null); 210 INSERT INTO `smbms_permission` VALUES ('10', '机构管理', 'menu', null, 'org:list', '12', 'sys/org/index', '1'); 211 INSERT INTO `smbms_permission` VALUES ('11', '用户管理', 'menu', null, 'user:list', '13', 'sys/user/index', '1'); 212 INSERT INTO `smbms_permission` VALUES ('12', '基础信息', 'menu', null, 'baseInfo:list', '14', 'sys/baseinfo/index', '1'); 213 INSERT INTO `smbms_permission` VALUES ('13', '销售计划', 'menu', null, 'plan:list', '21', 'sal/plain/index', '2'); 214 INSERT INTO `smbms_permission` VALUES ('14', '销售预测', 'menu', null, 'forecast:list', '22', 'sal/forecast/index', '2'); 215 INSERT INTO `smbms_permission` VALUES ('15', '客户资源管理', 'menu', null, 'resource:list', '31', 'cus/resource/index', '3'); 216 INSERT INTO `smbms_permission` VALUES ('16', '客户发展计划', 'menu', null, 'develop:list', '32', 'cus/develop/index', '3'); 217 INSERT INTO `smbms_permission` VALUES ('17', '客户价值管理', 'menu', null, 'price:list', '33', 'cus/price/index', '3'); 218 INSERT INTO `smbms_permission` VALUES ('18', '服务创建', 'menu', null, 'creator:list', '41', 'service/creator/index', '4'); 219 INSERT INTO `smbms_permission` VALUES ('19', '订单统计', 'menu', null, 'counter:list', '51', 'order/counter/index', '5'); 220 INSERT INTO `smbms_permission` VALUES ('20', '角色管理', 'menu', null, 'role:list', '11', 'sys/role/index', '1'); 221 INSERT INTO `smbms_permission` VALUES ('100', '添加角色', 'button', 'md-create', 'role:add', null, null, '20'); 222 INSERT INTO `smbms_permission` VALUES ('101', '删除角色', 'button', 'md-remove', 'role:del', null, null, '20'); 223 INSERT INTO `smbms_permission` VALUES ('102', '修改角色', 'button', 'md-build', 'role:update', null, null, '20'); 224 INSERT INTO `smbms_permission` VALUES ('103', '添加机构', 'button', 'md-create', 'org:add', null, null, '10'); 225 INSERT INTO `smbms_permission` VALUES ('104', '删除机构', 'button', 'md-trash', 'org:del', null, null, '10'); 226 INSERT INTO `smbms_permission` VALUES ('105', '修改机构', 'button', 'md-build', 'org:update', null, null, '10'); 227 INSERT INTO `smbms_permission` VALUES ('106', '添加用户', 'button', 'md-create', 'user:add', null, null, '11'); 228 INSERT INTO `smbms_permission` VALUES ('107', '删除用户', 'button', 'md-trash', 'user:del', null, null, '11'); 229 INSERT INTO `smbms_permission` VALUES ('108', '修改用户', 'button', 'md-build', 'user:update', null, null, '11'); 230 INSERT INTO `smbms_permission` VALUES ('109', '用户授权', 'button', 'md-finger-print', 'user:grant', null, null, '11'); 231 INSERT INTO `smbms_permission` VALUES ('110', '上传附件', 'button', 'md-cloud-upload', 'user:upload', null, null, '11'); 232 INSERT INTO `smbms_permission` VALUES ('111', '添加销售计划', 'button', 'md-create', 'plan:add', null, null, '13'); 233 INSERT INTO `smbms_permission` VALUES ('112', '删除销售计划', 'button', 'md-trash', 'plan:del', null, null, '13'); 234 INSERT INTO `smbms_permission` VALUES ('113', '修改销售计划', 'button', 'md-build', 'plan:update', null, null, '13'); 235 INSERT INTO `smbms_permission` VALUES ('114', '添加客户资源', 'button', 'md-create', 'resource:add', null, null, '15'); 236 INSERT INTO `smbms_permission` VALUES ('115', '删除客户资源', 'button', 'md-trash', 'resource:del', null, null, '15'); 237 INSERT INTO `smbms_permission` VALUES ('116', '修改客户资源', 'button', 'md-build', 'resource:update', null, null, '15'); 238 239 -- ---------------------------- 240 -- Table structure for smbms_provider 241 -- ---------------------------- 242 DROP TABLE IF EXISTS `smbms_provider`; 243 CREATE TABLE `smbms_provider` ( 244 `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID', 245 `pro_Code` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '供应商编码', 246 `pro_Name` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '供应商名称', 247 `pro_Desc` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '供应商详细描述', 248 `pro_Contact` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '供应商联系人', 249 `pro_Phone` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '联系电话', 250 `pro_Address` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '地址', 251 `pro_Fax` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '传真', 252 `created_By` bigint(20) DEFAULT NULL COMMENT '创建者(userId)', 253 `creation_Date` datetime DEFAULT NULL COMMENT '创建时间', 254 `modify_Date` datetime DEFAULT NULL COMMENT '更新时间', 255 `modify_By` bigint(20) DEFAULT NULL COMMENT '更新者(userId)', 256 PRIMARY KEY (`id`) 257 ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 258 259 -- ---------------------------- 260 -- Records of smbms_provider 261 -- ---------------------------- 262 INSERT INTO `smbms_provider` VALUES ('1', 'BJ_GYS001', '北京三木堂商贸有限公司', '长期合作伙伴,主营产品:茅台、五粮液、郎酒、酒鬼酒、泸州老窖、赖茅酒、法国红酒等', '张国强', '13566667777', '北京市丰台区育芳园北路', '010-58858787', '1', '2013-03-21 16:52:07', null, null); 263 INSERT INTO `smbms_provider` VALUES ('2', 'HB_GYS001', '石家庄帅益食品贸易有限公司', '长期合作伙伴,主营产品:饮料、水饮料、植物蛋白饮料、休闲食品、果汁饮料、功能饮料等', '王军', '13309094212', '河北省石家庄新华区', '0311-67738876', '1', '2016-04-13 04:20:40', null, null); 264 INSERT INTO `smbms_provider` VALUES ('3', 'GZ_GYS001', '深圳市泰香米业有限公司', '初次合作伙伴,主营产品:良记金轮米,龙轮香米等', '郑程瀚', '13402013312', '广东省深圳市福田区深南大道6006华丰大厦', '0755-67776212', '1', '2014-03-21 16:56:07', null, null); 265 INSERT INTO `smbms_provider` VALUES ('5', 'JS_GYS001', '兴化佳美调味品厂', '长期合作伙伴,主营产品:天然香辛料、鸡精、复合调味料', '徐国洋', '13754444221', '江苏省兴化市林湖工业区', '0523-21299098', '1', '2015-11-22 16:52:07', null, null); 266 INSERT INTO `smbms_provider` VALUES ('6', 'BJ_GYS002', '北京纳福尔食用油有限公司', '长期合作伙伴,主营产品:山茶油、大豆油、花生油、橄榄油等', '马莺', '13422235678', '北京市朝阳区珠江帝景1号楼', '010-588634233', '1', '2012-03-21 17:52:07', null, null); 267 INSERT INTO `smbms_provider` VALUES ('7', 'BJ_GYS003', '北京国粮食用油有限公司', '初次合作伙伴,主营产品:花生油、大豆油、小磨油等', '王驰', '13344441135', '北京大兴青云店开发区', '010-588134111', '1', '2016-04-13 00:00:00', null, null); 268 INSERT INTO `smbms_provider` VALUES ('8', 'ZJ_GYS001', '慈溪市广和绿色食品厂', '长期合作伙伴,主营产品:豆瓣酱、黄豆酱、甜面酱,辣椒,大蒜等农产品', '薛圣丹', '18099953223', '浙江省宁波市慈溪周巷小安村', '0574-34449090', '1', '2013-11-21 06:02:07', null, null); 269 INSERT INTO `smbms_provider` VALUES ('9', 'GX_GYS001', '优百商贸有限公司', '长期合作伙伴,主营产品:日化产品', '李立国', '13323566543', '广西南宁市秀厢大道42-1号', '0771-98861134', '1', '2013-03-21 19:52:07', null, null); 270 INSERT INTO `smbms_provider` VALUES ('10', 'JS_GYS002', '南京火头军信息技术有限公司', '长期合作伙伴,主营产品:不锈钢厨具等', '陈女士', '13098992113', '江苏省南京市浦口区浦口大道1号新城总部大厦A座903室', '025-86223345', '1', '2013-03-25 16:52:07', null, null); 271 INSERT INTO `smbms_provider` VALUES ('11', 'GZ_GYS003', '广州市白云区美星五金制品厂', '长期合作伙伴,主营产品:海绵床垫、坐垫、靠垫、海绵枕头、头枕等', '梁天', '13562276775', '广州市白云区钟落潭镇福龙路20号', '020-85542231', '1', '2016-12-21 06:12:17', null, null); 272 INSERT INTO `smbms_provider` VALUES ('12', 'BJ_GYS004', '北京隆盛日化科技', '长期合作伙伴,主营产品:日化环保清洗剂,家居洗涤专卖、洗涤用品网、墙体除霉剂、墙面霉菌清除剂等', '孙欣', '13689865678', '北京市大兴区旧宫', '010-35576786', '1', '2014-11-21 12:51:11', null, null); 273 INSERT INTO `smbms_provider` VALUES ('13', 'SD_GYS001', '山东豪克华光联合发展有限公司', '长期合作伙伴,主营产品:洗衣皂、洗衣粉、洗衣液、洗洁精、消杀类、香皂等', '吴洪转', '13245468787', '山东济阳济北工业区仁和街21号', '0531-53362445', '1', '2015-01-28 10:52:07', null, null); 274 INSERT INTO `smbms_provider` VALUES ('14', 'JS_GYS003', '无锡喜源坤商行', '长期合作伙伴,主营产品:日化品批销', '周一清', '18567674532', '江苏无锡盛岸西路', '0510-32274422', '1', '2016-04-23 11:11:11', null, null); 275 INSERT INTO `smbms_provider` VALUES ('15', 'HD-zg001', '邯郸日化', '长期合作伙伴,主营产品:各种中、高档塑料杯,塑料乐扣水杯(密封杯)、保鲜杯(保鲜盒)、广告杯、礼品杯', '王世杰', '13212331567', '浙江省金华市义乌市义东路', '0579-34452321', '1', '2016-08-22 10:01:30', null, null); 276 INSERT INTO `smbms_provider` VALUES ('16', 'HD-gk1008', '邯郸胡合肥', null, '电话:123456', null, '学院北路15号', null, '1', '2018-06-12 12:06:47', null, null); 277 278 -- ---------------------------- 279 -- Table structure for smbms_role 280 -- ---------------------------- 281 DROP TABLE IF EXISTS `smbms_role`; 282 CREATE TABLE `smbms_role` ( 283 `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID', 284 `role_Code` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '角色编码', 285 `role_Name` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '角色名称', 286 `created_By` bigint(20) DEFAULT NULL COMMENT '创建者', 287 `creation_Date` datetime DEFAULT NULL COMMENT '创建时间', 288 `modify_By` bigint(20) DEFAULT NULL COMMENT '修改者', 289 `modify_Date` datetime DEFAULT NULL COMMENT '修改时间', 290 PRIMARY KEY (`id`) 291 ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 292 293 -- ---------------------------- 294 -- Records of smbms_role 295 -- ---------------------------- 296 INSERT INTO `smbms_role` VALUES ('1', 'SMBMS_ADMIN', '系统管理员', '2', '2019-08-25 00:00:00', null, null); 297 INSERT INTO `smbms_role` VALUES ('2', 'SMBMS_JL', '经理', '1', '2016-04-13 00:00:00', null, '2019-08-28 12:01:25'); 298 INSERT INTO `smbms_role` VALUES ('3', 'SMBMS_PTYG', '普通员工', '1', '2016-04-13 00:00:00', null, '2019-09-02 12:30:44'); 299 INSERT INTO `smbms_role` VALUES ('11', 'SMSMS_XSZY', '销售专员2', '1', '2019-01-26 10:52:33', null, '2019-09-02 12:30:19'); 300 INSERT INTO `smbms_role` VALUES ('12', 'SMBMS_XSJL', '销售经理', null, '2019-08-28 10:19:09', null, null); 301 INSERT INTO `smbms_role` VALUES ('13', 'SMBMS_ZXJL', '咨询经理', null, '2019-08-28 10:33:02', null, null); 302 INSERT INTO `smbms_role` VALUES ('14', null, '张三', null, null, null, null); 303 304 -- ---------------------------- 305 -- Table structure for smbms_role_permission 306 -- ---------------------------- 307 DROP TABLE IF EXISTS `smbms_role_permission`; 308 CREATE TABLE `smbms_role_permission` ( 309 `role_id` int(11) DEFAULT NULL, 310 `perm_id` int(11) DEFAULT NULL 311 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 312 313 -- ---------------------------- 314 -- Records of smbms_role_permission 315 -- ---------------------------- 316 INSERT INTO `smbms_role_permission` VALUES ('1', '1'); 317 INSERT INTO `smbms_role_permission` VALUES ('1', '8'); 318 INSERT INTO `smbms_role_permission` VALUES ('1', '9'); 319 INSERT INTO `smbms_role_permission` VALUES ('1', '11'); 320 INSERT INTO `smbms_role_permission` VALUES ('1', '12'); 321 INSERT INTO `smbms_role_permission` VALUES ('1', '2'); 322 INSERT INTO `smbms_role_permission` VALUES ('1', '13'); 323 INSERT INTO `smbms_role_permission` VALUES ('1', '14'); 324 INSERT INTO `smbms_role_permission` VALUES ('1', '3'); 325 INSERT INTO `smbms_role_permission` VALUES ('1', '15'); 326 INSERT INTO `smbms_role_permission` VALUES ('1', '16'); 327 INSERT INTO `smbms_role_permission` VALUES ('1', '17'); 328 INSERT INTO `smbms_role_permission` VALUES ('1', '4'); 329 INSERT INTO `smbms_role_permission` VALUES ('1', '18'); 330 INSERT INTO `smbms_role_permission` VALUES ('1', '5'); 331 INSERT INTO `smbms_role_permission` VALUES ('1', '19'); 332 INSERT INTO `smbms_role_permission` VALUES ('1', '20'); 333 INSERT INTO `smbms_role_permission` VALUES ('1', '107'); 334 INSERT INTO `smbms_role_permission` VALUES ('3', '2'); 335 INSERT INTO `smbms_role_permission` VALUES ('3', '13'); 336 INSERT INTO `smbms_role_permission` VALUES ('3', '111'); 337 INSERT INTO `smbms_role_permission` VALUES ('3', '3'); 338 INSERT INTO `smbms_role_permission` VALUES ('3', '15'); 339 INSERT INTO `smbms_role_permission` VALUES ('3', '114'); 340 INSERT INTO `smbms_role_permission` VALUES ('3', '115'); 341 INSERT INTO `smbms_role_permission` VALUES ('3', '116'); 342 INSERT INTO `smbms_role_permission` VALUES ('1', '109'); 343 INSERT INTO `smbms_role_permission` VALUES ('2', '10'); 344 INSERT INTO `smbms_role_permission` VALUES ('2', '103'); 345 INSERT INTO `smbms_role_permission` VALUES ('2', '2'); 346 INSERT INTO `smbms_role_permission` VALUES ('2', '13'); 347 INSERT INTO `smbms_role_permission` VALUES ('2', '111'); 348 INSERT INTO `smbms_role_permission` VALUES ('2', '112'); 349 INSERT INTO `smbms_role_permission` VALUES ('2', '113'); 350 INSERT INTO `smbms_role_permission` VALUES ('2', '14'); 351 INSERT INTO `smbms_role_permission` VALUES ('2', '3'); 352 INSERT INTO `smbms_role_permission` VALUES ('2', '15'); 353 INSERT INTO `smbms_role_permission` VALUES ('2', '114'); 354 INSERT INTO `smbms_role_permission` VALUES ('2', '115'); 355 INSERT INTO `smbms_role_permission` VALUES ('2', '116'); 356 357 -- ---------------------------- 358 -- Table structure for smbms_user 359 -- ---------------------------- 360 DROP TABLE IF EXISTS `smbms_user`; 361 CREATE TABLE `smbms_user` ( 362 `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID', 363 `user_code` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '用户编码', 364 `username` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '用户名称', 365 `password` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '用户密码', 366 `salt` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL, 367 `gender` int(1) DEFAULT NULL COMMENT '性别(1:女、 2:男)', 368 `birthday` date DEFAULT NULL COMMENT '出生日期', 369 `phone` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '手机', 370 `address` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '地址', 371 `user_role` bigint(20) DEFAULT NULL COMMENT '用户角色(取自角色表-角色id)', 372 `created_by` bigint(20) DEFAULT NULL COMMENT '创建者(userId)', 373 `creation_date` datetime DEFAULT NULL COMMENT '创建时间', 374 `modify_by` bigint(20) DEFAULT NULL COMMENT '更新者(userId)', 375 `modify_date` datetime DEFAULT NULL COMMENT '更新时间', 376 `attach_path` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL, 377 `attach_descr` varchar(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL, 378 PRIMARY KEY (`id`) 379 ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 380 381 -- ---------------------------- 382 -- Records of smbms_user 383 -- ---------------------------- 384 INSERT INTO `smbms_user` VALUES ('1', 'admin', '系统管理员', 'bacbc96d57da0555dc8b58beadbc2d93', 'qwert', '1', '1983-10-10', '18988889999', '北京市海淀区成府路207号', '1', '1', '2013-03-21 16:52:07', null, '2019-06-20 09:08:48', null, null); 385 INSERT INTO `smbms_user` VALUES ('2', 'liming', '李明2', 'bacbc96d57da0555dc8b58beadbc2d93', 'qwert', '2', '2019-06-01', '13688884457', '北京市东城区前门东大街9号', '12', '1', '2014-12-31 19:52:09', '1', '2019-06-28 17:51:04', null, null); 386 INSERT INTO `smbms_user` VALUES ('5', 'hanlubiao', '韩路彪', 'bacbc96d57da0555dc8b58beadbc2d93', 'qwert', '1', '1984-07-12', '18567542321', '北京市朝阳区北辰中心12号', '14', '1', '2014-12-31 19:52:09', '1', '2019-06-28 17:49:35', null, null); 387 INSERT INTO `smbms_user` VALUES ('6', 'zhanghua', '张华', 'bacbc96d57da0555dc8b58beadbc2d93', 'qwert', '1', '1983-06-19', '13544561111', '北京市海淀区学院路61号', '11', '1', '2013-02-11 10:51:17', null, '2019-06-20 09:08:58', null, null); 388 INSERT INTO `smbms_user` VALUES ('7', 'wangyang', '王洋', 'bacbc96d57da0555dc8b58beadbc2d93', 'qwert', '2', '1982-12-31', '13444561124', '北京市海淀区西二旗辉煌国际16层', '11', '1', '2014-06-11 19:09:07', null, '2019-06-20 09:56:53', null, null); 389 INSERT INTO `smbms_user` VALUES ('8', 'zhaoyan', '赵燕', 'bacbc96d57da0555dc8b58beadbc2d93', 'qwert', '1', '1986-03-07', '18098764545', '北京市海淀区回龙观小区10号楼', '3', '1', '2016-04-21 13:54:07', null, '2019-06-20 09:56:56', null, null); 390 INSERT INTO `smbms_user` VALUES ('10', 'sunlei', '孙磊', 'bacbc96d57da0555dc8b58beadbc2d93', 'qwert', '2', '1981-01-04', '13387676765', '北京市朝阳区管庄新月小区12楼', '3', '1', '2015-05-06 10:52:07', null, '2019-06-20 09:56:58', null, null); 391 INSERT INTO `smbms_user` VALUES ('11', 'sunxing', '孙兴', 'bacbc96d57da0555dc8b58beadbc2d93', 'qwert', '2', '1978-03-12', '13367890900', '北京市朝阳区建国门南大街10号', '3', '1', '2016-11-09 16:51:17', null, '2019-06-20 09:57:00', null, null); 392 INSERT INTO `smbms_user` VALUES ('12', 'zhangchen', '张晨', 'bacbc96d57da0555dc8b58beadbc2d93', 'qwert', '1', '1986-03-28', '18098765434', '朝阳区管庄路口北柏林爱乐三期13号楼', '3', '1', '2016-08-09 05:52:37', '1', '2016-04-14 14:15:36', null, null); 393 INSERT INTO `smbms_user` VALUES ('13', 'dengchao', '邓超', 'bacbc96d57da0555dc8b58beadbc2d93', 'qwert', '2', '1981-11-04', '13689674534', '北京市海淀区北航家属院10号楼', '3', '1', '2016-07-11 08:02:47', null, null, null, null); 394 INSERT INTO `smbms_user` VALUES ('14', 'yangguo', '杨过', 'bacbc96d57da0555dc8b58beadbc2d93', 'qwert', '2', '1980-01-01', '13388886623', '北京市朝阳区北苑家园茉莉园20号楼', '3', '1', '2015-02-01 03:52:07', null, null, null, null); 395 INSERT INTO `smbms_user` VALUES ('15', 'zhaomin', '赵敏', 'bacbc96d57da0555dc8b58beadbc2d93', 'qwert', '1', '1987-12-04', '18099897657', '北京市昌平区天通苑3区12号楼', '2', '1', '2015-09-12 12:02:12', null, null, null, null);
整好数据库后我们来到idea,右边maven上面应该会有一个datasource(数据库),如果没有找到的同学去视图中的工具窗口中找
可以测试连接,如果报错serverTimezone就在高级里面添加一个serverTimezone=UTC然后继续测试直到成功
生成后我们直接用Lombok插件来让idea自己生成pojo实体类,没有Lombok插件的同学去设置里面下载,右击你想自动生成的数据库表格
选择lombo,然后出现选择路径,选你项目路径下的pojo然后点击确定
我们会发现实体类出现了 一个@Data注解,这个注解中包含了我们的Setter跟Getter方法以及toString简单来说就是不用再手动添加get/set等方法了,大大提高了写代码的速度。
在@Data下添加一个@TableName(value = "smbms_role")这个注解的意思就是指定的数据库名,设置@TableId(type=IdType.AUTO)//id主键递增注解
@Data
@TableName(value = "smbms_role")
public class Role {
/**
* 主键ID
*/
@TableId(type=IdType.AUTO)//id主键递增
private Integer id;
/**
* 角色编码
*/
private String roleCode;
/**
* 角色名称
*/
private String roleName;
/**
* 创建者
*/
private Integer createdBy;
/**
* 创建时间
*/
private java.time.LocalDateTime creationDate;
/**
* 修改者
*/
private Integer modifyBy;
/**
* 修改时间
*/
private java.time.LocalDateTime modifyDate;
}
写入我们的mapper,这里继承一个BaseMapper,实现BaseMapper用来实现Mybatis-Plus的增强功能。增删改查等一系列方法都包含在BaseMapper中
public interface RoleMapper extends BaseMapper<Role> { }
mapper写完后开始写我们的Service业务逻辑层
public interface RoleService { //获取所有角色数据 public List<Role> getRoleList(); //根据id获取角色数据 public Role getRoleById(Integer id); //添加一条数据 public int addRole(Role role); //修改数据 public int updateRole(Role role); //根据id删除角色数据 public int deleteById(Integer id); }
业务逻辑层写完后我们开始写ServiceImpl实现类
@Service("roleService") public class RoleServiceImpl implements RoleService{ @Resource private RoleMapper roleMapper; //获取所有角色数据 @Override public List<Role> getRoleList() { return roleMapper.selectList(null); } //根据id获取角色数据 @Override public Role getRoleById(Integer id) { return roleMapper.selectById(id); } //添加一条数据 @Override public int addRole(Role role) { return roleMapper.insert(role); } //修改数据 @Override public int updateRole(Role role) { return roleMapper.updateById(role); } //根据id删除角色数据 @Override public int deleteById(Integer id) { return roleMapper.deleteById(id); } }
然后开始测试我们上面写的一些增删改
@RunWith注解作用是运行器
@SpringBootTest注解 标记需要测试的类
@RunWith(SpringRunner.class) @SpringBootTest//测试类,来测试我们的service业务逻辑层 public class TestService { @Resource private RoleService roleService; //查询所有 @Test public void getRolelist(){ List<Role> list= roleService.getRoleList(); list.forEach(System.out::println); } //根据id查询 @Test public void getRoleById(){ Role role=roleService.getRoleById(2); System.out.println(role); } //添加一条数据 @Test public void addRole(){ Role role=new Role(); role.setRoleCode("1111"); role.setRoleName("杨某"); System.out.println(roleService.addRole(role)); } //根据id修改数据 @Test public void getupdateRole(){ Role role= roleService.getRoleById(15); role.setRoleName("张思"); System.out.println(roleService.updateRole(role)); } //根据id删除 @Test public void getdeleteRole(){ int unm=0; unm=roleService.deleteById(14); System.out.println(unm); } }
测试完成后可以看出我们的数据库发生了变化,测试成功!