1.权限是几乎每个系统都需要的
2.一般在用户请求某个url的时候,都需要验证用户是否拥有该url的访问权限
3.最简单的权限系统需要 用户表,角色表,用户角色表,权限表,角色权限表
# Host: 127.0.0.1 (Version: 5.6.22)
# Date: 2015-04-09 10:52:58
# Generator: MySQL-Front 5.3 (Build 4.13)
/*!40101 SET NAMES utf8 */;
#
# Source for table "privilege"
#
CREATE TABLE `privilege` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`url` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#
# Data for table "privilege"
#
INSERT INTO `privilege` VALUES (1,'产品增加','/product/add'),(2,'产品删除','/product/delete'),(3,'产品修改','/product/update'),(4,'产品查询','/product/query');
#
# Source for table "role"
#
CREATE TABLE `role` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
#
# Data for table "role"
#
INSERT INTO `role` VALUES (1,'admin'),(2,'manager'),(3,'guest');
#
# Source for table "role_priv"
#
CREATE TABLE `role_priv` (
`role_id` int(11) NOT NULL,
`priv_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#
# Data for table "role_priv"
#
INSERT INTO `role_priv` VALUES (1,1),(2,1),(1,3);
#
# Source for table "user"
#
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
#
# Data for table "user"
#
INSERT INTO `user` VALUES (1,'jack'),(2,'jaychang'),(3,'jasaon'),(4,'lucy'),(5,'marry');
#
# Source for table "user_role"
#
CREATE TABLE `user_role` (
`user_id` int(11) NOT NULL DEFAULT '0',
`role_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#
# Data for table "user_role"
#
INSERT INTO `user_role` VALUES (1,1),(1,3),(3,2),(2,2);
4.表对应的实体类
放在了sample包下
public class Privilege {
private Integer id;
private String name;
private String url;
...省略get,set方法
}
public class User {
private Integer id;
private String name;
private List<Role> roles;
...省略get,set方法
}
public class Role {
private Integer id;
private String name;
private List<Privilege> privileges;
...省略get,set方法
}
5.mybatis配置文件
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <typeAliases> <typeAlias type="sample.User" alias="User" /> <typeAlias type="sample.Role" alias="Role" /> </typeAliases> <environments default="development"> <environment id="development"> <transactionManager type="JDBC" /> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver" /> <property name="url" value="jdbc:mysql://127.0.0.1:3306/temp" /> <property name="username" value="root" /> <property name="password" value="123456" /> </dataSource> </environment> </environments> <mappers> <mapper resource="mybatis/mapper/UserMapper.xml" /> </mappers> </configuration>
6.UserMapper.xml
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="sample.User"> <resultMap type="User" id="userResultMap"> <id property="id" column="user_id" /> <result property="name" column="user_name" /> <collection property="roles" ofType="sample.Role"> <result property="id" column="role_id"/> <result property="name" column="role_name"/> <collection property="privileges" ofType="sample.Privilege"> <result property="id" column="priv_id"/> <result property="name" column="priv_name"/> <result property="url" column="priv_url"/> </collection> </collection> </resultMap> <resultMap type="Role" id="roleResultMap"> <id property="id" column="role_id" /> <result property="name" column="role_name" /> <collection property="privileges" ofType="sample.Privilege"> <result property="id" column="priv_id"/> <result property="name" column="priv_name"/> <result property="url" column="priv_url"/> </collection> </resultMap> <select id="selectUserById" parameterType="long" resultMap="userResultMap"> select u.id as user_id,u.name as user_name,r.id as role_id, r.name as role_name,p.id as priv_id,p.name as priv_name,p.url as priv_url from user u left join user_role ur on u.id = ur.user_id left join role r on r.id = ur.role_id left join role_priv rp on r.id = rp.role_id left join privilege p on p.id = rp.priv_id where u.id = #{id} </select> </mapper>
测试
List<User> users = sqlSession.selectList("selectUserById", 1L);
for (User u : users) {
System.out.println("用户名:" + u.getName());
List<Role> roles = u.getRoles();
if (null != roles) {
for (Role role : roles) {
System.out.println(" 拥有角色:" + role.getName());
List<Privilege> privs = role.getPrivileges();
if (null != privs && privs.size() > 0) {
System.out.println(" 该角色【" + role.getName()
+ "】,拥有的权限:");
for (Privilege priv : privs) {
System.out.println(" " + priv.getName()
+ "," + priv.getUrl());
}
}
}
}
}