user role menu funs role_menu

user

CREATE TABLE `user` (
  `id` varchar(200) NOT NULL DEFAULT '',
  `username` varchar(20) DEFAULT NULL,
  `password` varchar(20) DEFAULT NULL,
  `roleid` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `user` VALUES ('00001', 'zhangsan', '1231230', '1');
INSERT INTO `user` VALUES ('00002', 'lisi', '123123', '2');
INSERT INTO `user` VALUES ('00003', 'admin', '123', '1');
INSERT INTO `user` VALUES ('00004', 'wangwu', '123', '3');
INSERT INTO `user` VALUES ('00005', 'zhaoyun', '123', '1');
INSERT INTO `user` VALUES ('00006', 'lixin', '123', '1');
INSERT INTO `user` VALUES ('00007', 'zhuge', '123456', '1');
INSERT INTO `user` VALUES ('00009', 'liangliang', '123123', '1');
INSERT INTO `user` VALUES ('b7baf1f948c64b56b7e56803e14fe41d', 'zhaosi', '123123', '3');

role

CREATE TABLE `role` (
  `roleid` varchar(20) NOT NULL DEFAULT '0',
  `rolename` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`roleid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `role` VALUES ('1', '管理员');
INSERT INTO `role` VALUES ('2', '普通员工');
INSERT INTO `role` VALUES ('3', '普通用户');

menu

CREATE TABLE `menu` (
  `menuid` varchar(20) NOT NULL DEFAULT '0',
  `menuName` varchar(20) DEFAULT NULL,
  `menuURL` varchar(200) DEFAULT NULL,
  `fatherid` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`menuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `menu` VALUES ('1', '系统菜单', 'null', '-1');
INSERT INTO `menu` VALUES ('2', '用户管理', 'null', '1');
INSERT INTO `menu` VALUES ('3', '添加用户', 'register.jsp', '2');
INSERT INTO `menu` VALUES ('4', '查询用户', 'body.jsp', '2');
INSERT INTO `menu` VALUES ('5', '商品管理', 'null', '1');
INSERT INTO `menu` VALUES ('6', '添加商品', 'addGoods.jsp', '5');
INSERT INTO `menu` VALUES ('7', '查询商品', 'findGoods.jsp', '5');
INSERT INTO `menu` VALUES ('8', '查看购物车', 'null', '1');
INSERT INTO `menu` VALUES ('9', '购物车', 'shopping.jsp', '8');

funs

CREATE TABLE `funs` (
  `funid` varchar(20) NOT NULL DEFAULT '0',
  `funName` varchar(20) DEFAULT NULL,
  `funURL` varchar(200) DEFAULT NULL,
  `menuid` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`funid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `funs` VALUES ('1', '主页', 'index.jsp', '1');
INSERT INTO `funs` VALUES ('2', '头页面', 'header.jsp', '1');
INSERT INTO `funs` VALUES ('3', '菜单页面', 'menu.jsp', '1');
INSERT INTO `funs` VALUES ('4', '操作页面', 'body.jsp', '1');
INSERT INTO `funs` VALUES ('5', '底页面', 'footer.jsp', '1');
INSERT INTO `funs` VALUES ('50', '添加用户页面', 'register.jsp', '3');
INSERT INTO `funs` VALUES ('51', '添加用户的动作', 'registerServlet.do', '3');
INSERT INTO `funs` VALUES ('6', '成功页面', 'ok.jsp', '1');
INSERT INTO `funs` VALUES ('60', '查询用户页面', 'body.jsp', '4');
INSERT INTO `funs` VALUES ('61', '查询用户动作', 'registerServlet.do', '4');
INSERT INTO `funs` VALUES ('62', '显示用户页面', 'body.jsp', '4');
INSERT INTO `funs` VALUES ('63', '预更新用户查询动作', 'preUpdata.do', '4');
INSERT INTO `funs` VALUES ('64', '更新用户页面', 'updataUser.jsp', '4');
INSERT INTO `funs` VALUES ('65', '更新用户动作', 'updataUser.do', '4');
INSERT INTO `funs` VALUES ('66', '删除用户动作', 'deleteUser.do', '4');
INSERT INTO `funs` VALUES ('7', '错误页面', 'error.jsp', '1');
INSERT INTO `funs` VALUES ('70', '添加商品页面', 'addProduct.jsp', '6');
INSERT INTO `funs` VALUES ('71', '添加商品动作', 'addProduct.do', '6');
INSERT INTO `funs` VALUES ('8', '角色错误页面', 'norole.jsp', '1');
INSERT INTO `funs` VALUES ('80', '查询商品页面', 'findProduct.jsp', '7');
INSERT INTO `funs` VALUES ('81', '查询商品动作', 'findProduct.do', '7');
INSERT INTO `funs` VALUES ('82', '预更新商品动作', 'preUpdataProduct.do', '7');
INSERT INTO `funs` VALUES ('83', '显示更新商品页面', 'updataProduct.jsp', '7');
INSERT INTO `funs` VALUES ('84', '更新商品动作', 'updataProduct.do', '7');
INSERT INTO `funs` VALUES ('85', '删除商品动作', 'deleteProduct.do', '7');
INSERT INTO `funs` VALUES ('90', '查看购物车页面', 'findcar.jsp', '9');

role_menu

CREATE TABLE `role_menu` (
  `roleid` varchar(20) NOT NULL DEFAULT '0',
  `menuid` varchar(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`roleid`,`menuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `role_menu` VALUES ('1', '1');
INSERT INTO `role_menu` VALUES ('1', '2');
INSERT INTO `role_menu` VALUES ('1', '3');
INSERT INTO `role_menu` VALUES ('1', '4');
INSERT INTO `role_menu` VALUES ('1', '5');
INSERT INTO `role_menu` VALUES ('1', '6');
INSERT INTO `role_menu` VALUES ('1', '7');
INSERT INTO `role_menu` VALUES ('1', '8');
INSERT INTO `role_menu` VALUES ('1', '9');
INSERT INTO `role_menu` VALUES ('2', '1');
INSERT INTO `role_menu` VALUES ('2', '5');
INSERT INTO `role_menu` VALUES ('2', '6');
INSERT INTO `role_menu` VALUES ('2', '7');
INSERT INTO `role_menu` VALUES ('2', '8');
INSERT INTO `role_menu` VALUES ('2', '9');
INSERT INTO `role_menu` VALUES ('3', '1');
INSERT INTO `role_menu` VALUES ('3', '8');
INSERT INTO `role_menu` VALUES ('3', '9');

查询结果

测试类