1.引入Shiro的Maven依赖
<!-- Spring 整合Shiro需要的依赖 -->
<dependency>
<groupId>org.apache.shiro</groupId>
<artifactId>shiro-core</artifactId>
<version>1.2.1</version>
</dependency>
<dependency>
<groupId>org.apache.shiro</groupId>
<artifactId>shiro-web</artifactId>
<version>1.2.1</version>
</dependency>
<dependency>
<groupId>org.apache.shiro</groupId>
<artifactId>shiro-ehcache</artifactId>
<version>1.2.1</version>
</dependency>
<dependency>
<groupId>org.apache.shiro</groupId>
<artifactId>shiro-spring</artifactId>
<version>1.2.1</version>
</dependency>
<!-- 除此之外还有一些东西也不可少spring, spring-mvc, ibatis等 spring.3.1.2 spring-mvc.3.1.2 ibatis.2.3.4 cglib.2.2 -->
<!-- Spring 整合Shiro需要的依赖 -->
<dependency>
<groupId>org.apache.shiro</groupId>
<artifactId>shiro-core</artifactId>
<version>1.2.1</version>
</dependency>
<dependency>
<groupId>org.apache.shiro</groupId>
<artifactId>shiro-web</artifactId>
<version>1.2.1</version>
</dependency>
<dependency>
<groupId>org.apache.shiro</groupId>
<artifactId>shiro-ehcache</artifactId>
<version>1.2.1</version>
</dependency>
<dependency>
<groupId>org.apache.shiro</groupId>
<artifactId>shiro-spring</artifactId>
<version>1.2.1</version>
</dependency>
<!-- 除此之外还有一些东西也不可少spring, spring-mvc, ibatis等 spring.3.1.2 spring-mvc.3.1.2
ibatis.2.3.4 cglib.2.2 -->
2.web.xml中配置
<!-- 配置shiro的核心拦截器 -->
<filter>
<filter-name>shiroFilter</filter-name>
<filter-class>org.springframework.web.filter.DelegatingFilterProxy</filter-class>
</filter>
<filter-mapping>
<filter-name>shiroFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
<!-- 配置shiro的核心拦截器 -->
<filter>
<filter-name>shiroFilter</filter-name>
<filter-class>org.springframework.web.filter.DelegatingFilterProxy</filter-class>
</filter>
<filter-mapping>
<filter-name>shiroFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
3.编写自己的UserRealm类继承自Realm,主要实现认证和授权的管理操作
package com.jay.demo.shiro;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Set;
import org.apache.shiro.authc.AuthenticationException;
import org.apache.shiro.authc.AuthenticationInfo;
import org.apache.shiro.authc.AuthenticationToken;
import org.apache.shiro.authc.LockedAccountException;
import org.apache.shiro.authc.SimpleAuthenticationInfo;
import org.apache.shiro.authc.UnknownAccountException;
import org.apache.shiro.authz.AuthorizationInfo;
import org.apache.shiro.authz.SimpleAuthorizationInfo;
import org.apache.shiro.realm.AuthorizingRealm;
import org.apache.shiro.subject.PrincipalCollection;
import org.springframework.beans.factory.annotation.Autowired;
import com.jay.demo.bean.Permission;
import com.jay.demo.bean.Role;
import com.jay.demo.bean.User;
import com.jay.demo.service.UserService;
public class UserRealm extends AuthorizingRealm{
@Autowired
private UserService userService;
/**
* 授权操作
*/
@Override
protected AuthorizationInfo doGetAuthorizationInfo(PrincipalCollection principals) {
// String username = (String) getAvailablePrincipal(principals);
String username = (String) principals.getPrimaryPrincipal();
Set<Role> roleSet = userService.findUserByUsername(username).getRoleSet();
//角色名的集合
Set<String> roles = new HashSet<String>();
//权限名的集合
Set<String> permissions = new HashSet<String>();
Iterator<Role> it = roleSet.iterator();
while(it.hasNext()){
roles.add(it.next().getName());
for(Permission per:it.next().getPermissionSet()){
permissions.add(per.getName());
}
}
SimpleAuthorizationInfo authorizationInfo = new SimpleAuthorizationInfo();
authorizationInfo.addRoles(roles);
authorizationInfo.addStringPermissions(permissions);
return authorizationInfo;
}
/**
* 身份验证操作
*/
@Override
protected AuthenticationInfo doGetAuthenticationInfo(
AuthenticationToken token) throws AuthenticationException {
String username = (String) token.getPrincipal();
User user = userService.findUserByUsername(username);
if(user==null){
//木有找到用户
throw new UnknownAccountException("没有找到该账号");
}
/* if(Boolean.TRUE.equals(user.getLocked())) {
throw new LockedAccountException(); //帐号锁定
} */
/**
* 交给AuthenticatingRealm使用CredentialsMatcher进行密码匹配,如果觉得人家的不好可以在此判断或自定义实现
*/
SimpleAuthenticationInfo info = new SimpleAuthenticationInfo(user.getUsername(), user.getPassword(),getName());
return info;
}
@Override
public String getName() {
return getClass().getName();
}
}
package com.jay.demo.shiro;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Set;
import org.apache.shiro.authc.AuthenticationException;
import org.apache.shiro.authc.AuthenticationInfo;
import org.apache.shiro.authc.AuthenticationToken;
import org.apache.shiro.authc.LockedAccountException;
import org.apache.shiro.authc.SimpleAuthenticationInfo;
import org.apache.shiro.authc.UnknownAccountException;
import org.apache.shiro.authz.AuthorizationInfo;
import org.apache.shiro.authz.SimpleAuthorizationInfo;
import org.apache.shiro.realm.AuthorizingRealm;
import org.apache.shiro.subject.PrincipalCollection;
import org.springframework.beans.factory.annotation.Autowired;
import com.jay.demo.bean.Permission;
import com.jay.demo.bean.Role;
import com.jay.demo.bean.User;
import com.jay.demo.service.UserService;
public class UserRealm extends AuthorizingRealm{
@Autowired
private UserService userService;
/**
* 授权操作
*/
@Override
protected AuthorizationInfo doGetAuthorizationInfo(PrincipalCollection principals) {
// String username = (String) getAvailablePrincipal(principals);
String username = (String) principals.getPrimaryPrincipal();
Set<Role> roleSet = userService.findUserByUsername(username).getRoleSet();
//角色名的集合
Set<String> roles = new HashSet<String>();
//权限名的集合
Set<String> permissions = new HashSet<String>();
Iterator<Role> it = roleSet.iterator();
while(it.hasNext()){
roles.add(it.next().getName());
for(Permission per:it.next().getPermissionSet()){
permissions.add(per.getName());
}
}
SimpleAuthorizationInfo authorizationInfo = new SimpleAuthorizationInfo();
authorizationInfo.addRoles(roles);
authorizationInfo.addStringPermissions(permissions);
return authorizationInfo;
}
/**
* 身份验证操作
*/
@Override
protected AuthenticationInfo doGetAuthenticationInfo(
AuthenticationToken token) throws AuthenticationException {
String username = (String) token.getPrincipal();
User user = userService.findUserByUsername(username);
if(user==null){
//木有找到用户
throw new UnknownAccountException("没有找到该账号");
}
/* if(Boolean.TRUE.equals(user.getLocked())) {
throw new LockedAccountException(); //帐号锁定
} */
/**
* 交给AuthenticatingRealm使用CredentialsMatcher进行密码匹配,如果觉得人家的不好可以在此判断或自定义实现
*/
SimpleAuthenticationInfo info = new SimpleAuthenticationInfo(user.getUsername(), user.getPassword(),getName());
return info;
}
@Override
public String getName() {
return getClass().getName();
}
}
4.在Spring的applicationContext.xml中进行Shiro的相关配置
1、添加shiroFilter定义
<!-- Shiro Filter -->
<bean id="shiroFilter" class="org.apache.shiro.spring.web.ShiroFilterFactoryBean">
<property name="securityManager" ref="securityManager" />
<property name="loginUrl" value="/login" />
<property name="successUrl" value="/user/list" />
<property name="unauthorizedUrl" value="/login" />
<property name="filterChainDefinitions">
<value>
/login = anon
/user/** = authc
/role/edit/* = perms[role:edit]
/role/save = perms[role:edit]
/role/list = perms[role:view]
/** = authc
</value>
</property>
</bean>
2、添加securityManager定义
<bean id="securityManager" class="org.apache.shiro.web.mgt.DefaultWebSecurityManager">
<property name="realm" ref="myRealm" />
</bean>
3、添加realm定义
<bean id=" myRealm" class="com.jay.demo.shiro.
UserRealm<span class="attribute-value" style="font-size: 1em; font-family: Monaco, 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', Consolas, 'Courier New', monospace; background-color: rgb(250, 250, 250);">"</span><span style="color: black; font-size: 1em; font-family: Monaco, 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', Consolas, 'Courier New', monospace; background-color: rgb(250, 250, 250);"> </span><span class="tag" style="font-size: 1em; font-family: Monaco, 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', Consolas, 'Courier New', monospace; color: rgb(0, 102, 153); font-weight: bold; background-color: rgb(250, 250, 250);">/></span><span style="color: black; font-size: 1em; font-family: Monaco, 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', Consolas, 'Courier New', monospace; background-color: rgb(250, 250, 250);"> </span>
UserRealm<span class="attribute-value" style="font-size: 1em; font-family: Monaco, 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', Consolas, 'Courier New', monospace; background-color: rgb(250, 250, 250);">"</span><span style="color: black; font-size: 1em; font-family: Monaco, 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', Consolas, 'Courier New', monospace; background-color: rgb(250, 250, 250);"> </span><span class="tag" style="font-size: 1em; font-family: Monaco, 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', Consolas, 'Courier New', monospace; color: rgb(0, 102, 153); font-weight: bold; background-color: rgb(250, 250, 250);">/></span><span style="color: black; font-size: 1em; font-family: Monaco, 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', Consolas, 'Courier New', monospace; background-color: rgb(250, 250, 250);"> </span>
4、配置EhCache
<bean id="cacheManager" class="org.apache.shiro.cache.ehcache.EhCacheManager" />
5、保证实现了Shiro内部lifecycle函数的bean执行
<bean id="lifecycleBeanPostProcessor" class="org.apache.shiro.spring.LifecycleBeanPostProcessor"/>
特别注意:
如果使用Shiro相关的注解,需要在springmvc-servlet.xml中配置一下信息
<bean class="org.springframework.aop.framework.autoproxy.DefaultAdvisorAutoProxyCreator" depends-on="lifecycleBeanPostProcessor"/><bean class="org.apache.shiro.spring.security.interceptor.AuthorizationAttributeSourceAdvisor"> <property name="securityManager" ref="securityManager"/></bean>
备注:Shiro权限管理的过滤器解释:
默认过滤器(10个)
anon -- org.apache.shiro.web.filter.authc.AnonymousFilter
authc -- org.apache.shiro.web.filter.authc.FormAuthenticationFilter
authcBasic -- org.apache.shiro.web.filter.authc.BasicHttpAuthenticationFilter
perms -- org.apache.shiro.web.filter.authz.PermissionsAuthorizationFilter
port -- org.apache.shiro.web.filter.authz.PortFilter
rest -- org.apache.shiro.web.filter.authz.HttpMethodPermissionFilter
roles -- org.apache.shiro.web.filter.authz.RolesAuthorizationFilter
ssl -- org.apache.shiro.web.filter.authz.SslFilter
user -- org.apache.shiro.web.filter.authc.UserFilter
logout -- org.apache.shiro.web.filter.authc.LogoutFilter
anon:例子/admins/**=anon 没有参数,表示可以匿名使用。
authc:例如/admins/user/**=authc表示需要认证(登录)才能使用,没有参数
roles:例子/admins/user/**=roles[admin],参数可以写多个,多个时必须加上引号,并且参数之间用逗号分割,当有多个参数时,例如admins/user/**=roles["admin,guest"],每个参数通过才算通过,相当于hasAllRoles()方法。
perms:例子/admins/user/**=perms[user:add:*],参数可以写多个,多个时必须加上引号,并且参数之间用逗号分割,例如/admins/user/**=perms["user:add:*,user:modify:*"],当有多个参数时必须每个参数都通过才通过,想当于isPermitedAll()方法。
rest:例子/admins/user/**=rest[user],根据请求的方法,相当于/admins/user/**=perms[user:method] ,其中method为post,get,delete等。
port:例子/admins/user/**=port[8081],当请求的url的端口不是8081是跳转到schemal://serverName:8081?queryString,其中schmal是协议http或https等,serverName是你访问的host,8081是url配置里port的端口,queryString是你访问的url里的?后面的参数。
authcBasic:例如/admins/user/**=authcBasic没有参数表示httpBasic认证
ssl:例子/admins/user/**=ssl没有参数,表示安全的url请求,协议为https
user:例如/admins/user/**=user没有参数表示必须存在用户,当登入操作时不做检查
默认过滤器(10个)
anon -- org.apache.shiro.web.filter.authc.AnonymousFilter
authc -- org.apache.shiro.web.filter.authc.FormAuthenticationFilter
authcBasic -- org.apache.shiro.web.filter.authc.BasicHttpAuthenticationFilter
perms -- org.apache.shiro.web.filter.authz.PermissionsAuthorizationFilter
port -- org.apache.shiro.web.filter.authz.PortFilter
rest -- org.apache.shiro.web.filter.authz.HttpMethodPermissionFilter
roles -- org.apache.shiro.web.filter.authz.RolesAuthorizationFilter
ssl -- org.apache.shiro.web.filter.authz.SslFilter
user -- org.apache.shiro.web.filter.authc.UserFilter
logout -- org.apache.shiro.web.filter.authc.LogoutFilter
anon:例子/admins/**=anon 没有参数,表示可以匿名使用。
authc:例如/admins/user/**=authc表示需要认证(登录)才能使用,没有参数
roles:例子/admins/user/**=roles[admin],参数可以写多个,多个时必须加上引号,并且参数之间用逗号分割,当有多个参数时,例如admins/user/**=roles["admin,guest"],每个参数通过才算通过,相当于hasAllRoles()方法。
perms:例子/admins/user/**=perms[user:add:*],参数可以写多个,多个时必须加上引号,并且参数之间用逗号分割,例如/admins/user/**=perms["user:add:*,user:modify:*"],当有多个参数时必须每个参数都通过才通过,想当于isPermitedAll()方法。
rest:例子/admins/user/**=rest[user],根据请求的方法,相当于/admins/user/**=perms[user:method] ,其中method为post,get,delete等。
port:例子/admins/user/**=port[8081],当请求的url的端口不是8081是跳转到schemal://serverName:8081?queryString,其中schmal是协议http或https等,serverName是你访问的host,8081是url配置里port的端口,queryString是你访问的url里的?后面的参数。
authcBasic:例如/admins/user/**=authcBasic没有参数表示httpBasic认证
ssl:例子/admins/user/**=ssl没有参数,表示安全的url请求,协议为https
user:例如/admins/user/**=user没有参数表示必须存在用户,当登入操作时不做检查
关于Shiro的标签应用:
<shiro:authenticated> 登录之后
<shiro:notAuthenticated> 不在登录状态时
<shiro:guest> 用户在没有RememberMe时
<shiro:user> 用户在RememberMe时
<shiro:hasAnyRoles name="abc,123" > 在有abc或者123角色时
<shiro:hasRole name="abc"> 拥有角色abc
<shiro:lacksRole name="abc"> 没有角色abc
<shiro:hasPermission name="abc"> 拥有权限abc
<shiro:lacksPermission name="abc"> 没有权限abc
<shiro:principal> 显示用户登录名
<shiro:authenticated> 登录之后
<shiro:notAuthenticated> 不在登录状态时
<shiro:guest> 用户在没有RememberMe时
<shiro:user> 用户在RememberMe时
<shiro:hasAnyRoles name="abc,123" > 在有abc或者123角色时
<shiro:hasRole name="abc"> 拥有角色abc
<shiro:lacksRole name="abc"> 没有角色abc
<shiro:hasPermission name="abc"> 拥有权限abc
<shiro:lacksPermission name="abc"> 没有权限abc
<shiro:principal> 显示用户登录名
以上是Shiro的相关配置,出于安全的考虑,一般都会使用ACL(基于角色的用户权限管理去控制用户登录后的权限)
ACL详细代码案例如下:
涉及到的表:3+2(User,Role,Permission + user-role,role-permission)
3张实体表+2张关系表
1.关于User类:
package com.jay.demo.bean;
import java.util.HashSet;
import java.util.Set;
public class User {
private String id;
private String username;
private String password;
private Set<Role> roleSet = new HashSet<Role>();
public User() {
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public Set<Role> getRoleSet() {
return roleSet;
}
public void setRoleSet(Set<Role> roleSet) {
this.roleSet = roleSet;
}
}
package com.jay.demo.bean;
import java.util.HashSet;
import java.util.Set;
public class User {
private String id;
private String username;
private String password;
private Set<Role> roleSet = new HashSet<Role>();
public User() {
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public Set<Role> getRoleSet() {
return roleSet;
}
public void setRoleSet(Set<Role> roleSet) {
this.roleSet = roleSet;
}
}
2.关于Role表
package com.jay.demo.bean;
import java.io.Serializable;
import java.util.HashSet;
import java.util.Set;
public class Role implements Serializable {
private static final long serialVersionUID = -4987248128309954399L;
private Integer id;
private String name;
private Set<Permission> permissionSet = new HashSet<Permission>();
public Role() {
super();
}
// --------------------------------------------------------------------------------
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + ((id == null) ? 0 : id.hashCode());
return result;
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
Role other = (Role) obj;
if (id == null) {
if (other.id != null)
return false;
} else if (!id.equals(other.id))
return false;
return true;
}
// --------------------------------------------------------------------------------
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Set<Permission> getPermissionSet() {
return permissionSet;
}
public void setPermissionSet(Set<Permission> permissionSet) {
this.permissionSet = permissionSet;
}
}
package com.jay.demo.bean;
import java.io.Serializable;
import java.util.HashSet;
import java.util.Set;
public class Role implements Serializable {
private static final long serialVersionUID = -4987248128309954399L;
private Integer id;
private String name;
private Set<Permission> permissionSet = new HashSet<Permission>();
public Role() {
super();
}
// --------------------------------------------------------------------------------
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + ((id == null) ? 0 : id.hashCode());
return result;
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
Role other = (Role) obj;
if (id == null) {
if (other.id != null)
return false;
} else if (!id.equals(other.id))
return false;
return true;
}
// --------------------------------------------------------------------------------
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Set<Permission> getPermissionSet() {
return permissionSet;
}
public void setPermissionSet(Set<Permission> permissionSet) {
this.permissionSet = permissionSet;
}
}
3.关于permission表
<pre name="code" class="java">package com.jay.demo.bean;
import java.io.Serializable;
public class Permission implements Serializable {
private static final long serialVersionUID = -8025597823572680802L;
private Integer id;
private String name;
public Permission() {
super();
}
// --------------------------------------------------------------------------------------
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + ((id == null) ? 0 : id.hashCode());
return result;
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
Permission other = (Permission) obj;
if (id == null) {
if (other.id != null)
return false;
} else if (!id.equals(other.id))
return false;
return true;
}
// --------------------------------------------------------------------------------------
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
<pre name="code" class="java">package com.jay.demo.bean;
import java.io.Serializable;
public class Permission implements Serializable {
private static final long serialVersionUID = -8025597823572680802L;
private Integer id;
private String name;
public Permission() {
super();
}
// --------------------------------------------------------------------------------------
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + ((id == null) ? 0 : id.hashCode());
return result;
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
Permission other = (Permission) obj;
if (id == null) {
if (other.id != null)
return false;
} else if (!id.equals(other.id))
return false;
return true;
}
// --------------------------------------------------------------------------------------
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
4.dao层接口
package com.jay.demo.dao;
import com.jay.demo.bean.User;
public interface UserDao {
User findUserByUsername(String username);
}
package com.jay.demo.dao;
import com.jay.demo.bean.User;
public interface UserDao {
User findUserByUsername(String username);
}
4.使用Mybatis完成的Dao层实现
<?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="com.jay.demo.dao.UserDao">
<resultMap id="userMap" type="com.jay.demo.bean.User">
<id property="id" column="USER_ID"/>
<result property="username" column="USER_USERNAME"/>
<result property="password" column="USER_PASSWORD"/>
<!-- 进行 多表关联插叙,先关联user和role -->
<collection property="roleSet" column="roleid" ofType="com.jay.demo.bean.Role">
<id property="id" column="ROLE_ID"/>
<result property="name" column="ROLE_NAME"/>
<!-- 再在role中关联role和permission -->
<collection property="permissionSet" column="permissionid" ofType="com.jay.demo.bean.Permission">
<id property="id" column="permission_id"/>
<result property="name" column="permission_name"/>
</collection>
</collection>
</resultMap>
<!-- 通过User来查找Role -->
<!-- <select id="selectRoleByUser" parameterType="int" resultMap="RoleMap">
select * from tbl_role_user user_id = #{id}
</select>
<resultMap id="roleMap" type="com.jay.demo.bean.User">
<result property="id" column="ROLE_ID" />
<result property="name" column="ROLE_NAME" />
</resultMap>
<resultMap id="permissionMap" type="com.jay.demo.bean.Permission">
<result property="id" column="PERMISSION_ID" />
<result property="name" column="PERMISSION_NAME" />
</resultMap> -->
<sql id="select-base-01">
SELECT
u.USER_ID,
u.USER_USERNAME,
u.USER_PASSWORD,
r.ROLE_ID,
r.ROLE_NAME,
p.PERMISSION_ID,
p.PERMISSION_NAME
FROM
tbl_user as u,
tbl_role as r,
tbl_permission as p,
tbl_permission_role as pr,
tbl_role_user as ru
WHERE
u.USER_ID = ru.USER_ID
AND
r.ROLE_ID = ru.ROLE_ID
AND
p.PERMISSION_ID = pr.PERMISSION_ID
AND
r.ROLE_ID = pr.ROLE_ID
</sql>
<select id="findUserByUsername" parameterType="string" resultMap="userMap">
<include refid="select-base-01" />
AND
u.USER_USERNAME = #{username}
<!-- select * from tbl_user u, tbl_role r, tbl_role_user tu
where u.user_id = tu.user_id and r.role_id = tu.role_id
and user_username=#{username} -->
</select>
</mapper>
<?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="com.jay.demo.dao.UserDao">
<resultMap id="userMap" type="com.jay.demo.bean.User">
<id property="id" column="USER_ID"/>
<result property="username" column="USER_USERNAME"/>
<result property="password" column="USER_PASSWORD"/>
<!-- 进行 多表关联插叙,先关联user和role -->
<collection property="roleSet" column="roleid" ofType="com.jay.demo.bean.Role">
<id property="id" column="ROLE_ID"/>
<result property="name" column="ROLE_NAME"/>
<!-- 再在role中关联role和permission -->
<collection property="permissionSet" column="permissionid" ofType="com.jay.demo.bean.Permission">
<id property="id" column="permission_id"/>
<result property="name" column="permission_name"/>
</collection>
</collection>
</resultMap>
<!-- 通过User来查找Role -->
<!-- <select id="selectRoleByUser" parameterType="int" resultMap="RoleMap">
select * from tbl_role_user user_id = #{id}
</select>
<resultMap id="roleMap" type="com.jay.demo.bean.User">
<result property="id" column="ROLE_ID" />
<result property="name" column="ROLE_NAME" />
</resultMap>
<resultMap id="permissionMap" type="com.jay.demo.bean.Permission">
<result property="id" column="PERMISSION_ID" />
<result property="name" column="PERMISSION_NAME" />
</resultMap> -->
<sql id="select-base-01">
SELECT
u.USER_ID,
u.USER_USERNAME,
u.USER_PASSWORD,
r.ROLE_ID,
r.ROLE_NAME,
p.PERMISSION_ID,
p.PERMISSION_NAME
FROM
tbl_user as u,
tbl_role as r,
tbl_permission as p,
tbl_permission_role as pr,
tbl_role_user as ru
WHERE
u.USER_ID = ru.USER_ID
AND
r.ROLE_ID = ru.ROLE_ID
AND
p.PERMISSION_ID = pr.PERMISSION_ID
AND
r.ROLE_ID = pr.ROLE_ID
</sql>
<select id="findUserByUsername" parameterType="string" resultMap="userMap">
<include refid="select-base-01" />
AND
u.USER_USERNAME = #{username}
<!-- select * from tbl_user u, tbl_role r, tbl_role_user tu
where u.user_id = tu.user_id and r.role_id = tu.role_id
and user_username=#{username} -->
</select>
</mapper>
说明:详细代码和demo见附件
转载于:https://blog.51cto.com/3237526/1652778