1、数据库
1. # 用户表 2. create table sys_user( 3. `user_id` int primary key auto_increment comment '用户ID', 4. `user_name` varchar(50) comment '用户名', 5. `password` varchar(32) comment '用户密码' , 6. `desc` varchar(200) comment '个人介绍' 7. ); 8. 9. insert into sys_user(`user_id`,`user_name`,`password`,`desc`) values(1,'jack','1234','这是一个男生'); 10. insert into sys_user(`user_id`,`user_name`,`password`,`desc`) values(2,'rose','1234','这是一个女生'); 11. insert into sys_user(`user_id`,`user_name`,`password`,`desc`) values(3,'rose','1234','这是一个女生'); 12. 13. # 角色表 14. create table sys_role( 15. `role_id` varchar(32) primary key comment '角色ID', 16. `role_name` varchar(50) comment '角色名', 17. `desc` varchar(200) comment '角色介绍' 18. ); 19. 20. insert into sys_role(`role_id`,`role_name`,`desc`) values('r001','管理员','拥有所有的权限'); 21. insert into sys_role(`role_id`,`role_name`,`desc`) values('r002','财务经理','拥有所有财务权限'); 22. insert into sys_role(`role_id`,`role_name`,`desc`) values('r003','开发经理','拥有所有开发权限'); 23. insert into sys_role(`role_id`,`role_name`,`desc`) values('r004','销售经理','拥有所有销售权限'); 24. 25. 26. CREATE TABLE `sys_permission` ( 27. `perm_id` varchar(32) primary key comment '权限ID', 28. `perm_name` varchar(50) comment '权限名字' , 29. `parent_id` varchar(32) comment '父权限ID', 30. `path` varchar(255) comment '权限路径' 31. ); 32. 33. INSERT INTO `sys_permission` VALUES ('p001', '财务管理', '0', '/finance/'); 34. INSERT INTO `sys_permission` VALUES ('p002', '查询财务', 'p001', '/finance/list'); 35. INSERT INTO `sys_permission` VALUES ('p003', '添加财务', 'p001', '/finance/add'); 36. INSERT INTO `sys_permission` VALUES ('p004', '修改财务', 'p001', '/finance/update'); 37. INSERT INTO `sys_permission` VALUES ('p005', '删除财务', 'p001', '/finance/delete'); 38. INSERT INTO `sys_permission` VALUES ('p006', '开发管理', '0', '/develop/'); 39. INSERT INTO `sys_permission` VALUES ('p007', '查询开发', 'p006', '/develop/list'); 40. INSERT INTO `sys_permission` VALUES ('p008', '添加开发', 'p006', '/develop/add'); 41. INSERT INTO `sys_permission` VALUES ('p009', '修改开发', 'p006', '/develop/update'); 42. INSERT INTO `sys_permission` VALUES ('p010', '删除开发', 'p006', '/develop/delete'); 43. INSERT INTO `sys_permission` VALUES ('p011', '销售管理', '0', '/sale/'); 44. INSERT INTO `sys_permission` VALUES ('p012', '查询销售', 'p011', '/sale/list'); 45. INSERT INTO `sys_permission` VALUES ('p013', '添加销售', 'p011', '/sale/add'); 46. INSERT INTO `sys_permission` VALUES ('p014', '修改销售', 'p011', '/sale/update'); 47. 48. 49. CREATE TABLE `sys_user_role` ( 50. `user_id` int, 51. `role_id` varchar(32), 52. CONSTRAINT `sys_ur_role` FOREIGN KEY (`role_id`) REFERENCES `sys_role` (`role_id`), 53. CONSTRAINT `sys_ur_user` FOREIGN KEY (`user_id`) REFERENCES `sys_user` (`user_id`) 54. ) ; 55. 56. INSERT INTO `sys_user_role` VALUES (1, 'r001'); 57. INSERT INTO `sys_user_role` VALUES (2, 'r002'); 58. INSERT INTO `sys_user_role` VALUES (2, 'r003'); 59. INSERT INTO `sys_user_role` VALUES (2, 'r004'); 60. INSERT INTO `sys_user_role` VALUES (3, 'r003'); 61. 62. 63. 64. CREATE TABLE `sys_role_permission` ( 65. `role_id` varchar(32), 66. `perm_id` varchar(32), 67. CONSTRAINT `sys_rp_permission` FOREIGN KEY (`perm_id`) REFERENCES `sys_permission` (`perm_id`), 68. CONSTRAINT `sys_rp_role` FOREIGN KEY (`role_id`) REFERENCES `sys_role` (`role_id`) 69. ) ; 70. 71. INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r001', 'p001'); 72. INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r001', 'p002'); 73. INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r001', 'p003'); 74. INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r001', 'p004'); 75. INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r001', 'p005'); 76. INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r001', 'p006'); 77. INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r001', 'p007'); 78. INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r001', 'p008'); 79. INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r001', 'p009'); 80. INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r001', 'p010'); 81. INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r001', 'p011'); 82. INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r001', 'p012'); 83. INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r001', 'p013'); 84. INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r001', 'p014'); 85. 86. INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r002', 'p001'); 87. INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r002', 'p002'); 88. INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r002', 'p003'); 89. INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r002', 'p004'); 90. INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r002', 'p005'); 91. 92. INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r003', 'p006'); 93. INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r003', 'p007'); 94. INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r003', 'p008'); 95. INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r003', 'p009'); 96. INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r003', 'p010'); 97. 98. INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r004', 'p011'); 99. INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r004', 'p012'); 100. INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r004', 'p013'); 101. INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r004', 'p014'); 102.
2、需求
- 查询指定若干用户，同时查询
- 每个用户对应的角色，同时查询
- 查询每一个角色对应的一级权限，同时查询
- 每一个一级权限对应的惹急权限
3、实现
UserMapper
1. @Mapper 2. public interface UserMapper { 3. 4. /** 5. * 查询所有用户 6. * @return 7. */ 8. public List<User> selectById(@Param("ulist") List<Integer> ulist); 9. }
UserMapper.xml
1. <?xml version="1.0" encoding="UTF-8"?> 2. <!DOCTYPE mapper 3. PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 4. "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 5. <mapper namespace="com.czxy.mapper.UserMapper"> 6. 7. <resultMap id="UserResultMap" type="user"> 8. <result property="userId" column="user_id"></result> 9. 10. <association property="roleList" column="user_id" select="com.czxy.mapper.RoleMapper.selectByUserId"></association> 11. </resultMap> 12. 13. 14. <select id="selectById" resultMap="UserResultMap"> 15. select * from sys_user where user_id in 16. <foreach collection="ulist" index="index" item="userId" open="(" separator="," close=")"> 17. #{userId} 18. </foreach> 19. </select> 20. 21. 22. </mapper>
RoleMapper.xml
1. <?xml version="1.0" encoding="UTF-8"?> 2. <!DOCTYPE mapper 3. PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 4. "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 5. <mapper namespace="com.czxy.mapper.RoleMapper"> 6. 7. <resultMap id="RoleResultMap" type="role"> 8. <result property="roleId" column="role_id"></result> 9. 10. <collection property="permissionList" column="role_id" select="com.czxy.mapper.PermissionMapper.selectByRoleId"></collection> 11. </resultMap> 12. 13. <select id="selectByUserId" resultMap="RoleResultMap"> 14. select r.* from sys_role r inner join sys_user_role ur on r.role_id = ur.role_id where user_id = #{userId} 15. </select> 16. 17. </mapper>
PermissionMapper.xml
1. <?xml version="1.0" encoding="UTF-8"?> 2. <!DOCTYPE mapper 3. PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 4. "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 5. <mapper namespace="com.czxy.mapper.PermissionMapper"> 6. 7. <resultMap id="PermissionResultMap" type="permission"> 8. <result property="permId" column="perm_id"></result> 9. 10. <association property="permissionList" column="perm_id" select="com.czxy.mapper.PermissionMapper.selectParentId"></association> 11. </resultMap> 12. 13. <select id="selectByRoleId" resultMap="PermissionResultMap"> 14. select p.* from sys_permission p inner join sys_role_permission rp on rp.perm_id = p.perm_id where rp.role_id = #{roleId} and parent_id = '0' 15. </select> 16. 17. <select id="selectParentId" resultType="permission"> 18. select * from sys_permission where parent_id = #{parentId} 19. </select> 20. 21. </mapper>
