mybatis一对多查询

摘要：查询需求是shiro查询一个用户的同时将其角色和角色的权限一并查出来。每次写一对多都是上网百度记不住，这次写个demo记录下。

1.项目结构，eg：一个用户可以有多个角色，一个角色可以有多个权限。

2.用户实体类，在用户实体类中添加一个roles的列表，用于存储查询出来的roles。

package com.ssm.entity;

import java.util.ArrayList;

public class Users {
	private int userId;
	private String userName;
	private String userPassword;
	//角色引用
	private ArrayList<Roles> roles;
	
	public ArrayList<Roles> getRoles() {
		return roles;
	}
	public void setRoles(ArrayList<Roles> roles) {
		this.roles = roles;
	}
	public int getUserId() {
		return userId;
	}
	public void setUserId(int userId) {
		this.userId = userId;
	}
	public String getUserName() {
		return userName;
	}
	public void setUserName(String userName) {
		this.userName = userName;
	}
	public String getUserPassword() {
		return userPassword;
	}
	public void setUserPassword(String userPassword) {
		this.userPassword = userPassword;
	}
	public Users() {
		super();
	}
	
	public Users(int userId, String userName, String userPassword) {
		super();
		this.userId = userId;
		this.userName = userName;
		this.userPassword = userPassword;
	}
	
	public Users(String userName, String userPassword) {
		super();
		this.userName = userName;
		this.userPassword = userPassword;
	}
}

3.角色实体类，在角色实体类中加入一个权限的列表，用于存储查询出来的每个用户的角色身上的权限。

package com.ssm.entity;

import java.util.ArrayList;

public class Roles {
	private int roleId;
	private String roleName;
	//权限引用
	private ArrayList<Permissions> permissions;

	public ArrayList<Permissions> getPermissions() {
		return permissions;
	}
	public void setPermissions(ArrayList<Permissions> permissions) {
		this.permissions = permissions;
	}
	
	public int getRoleId() {
		return roleId;
	}
	public void setRoleId(int roleId) {
		this.roleId = roleId;
	}
	public String getRoleName() {
		return roleName;
	}
	public void setRoleName(String roleName) {
		this.roleName = roleName;
	}
	public Roles() {
		super();
	}
	
	public Roles(int roleId, String roleName) {
		super();
		this.roleId = roleId;
		this.roleName = roleName;
	}
	
	public Roles(String roleName) {
		super();
		this.roleName = roleName;
	}
}

4.权限实体类。

package com.ssm.entity;

public class Permissions {
	private int permissionId;
	private String permissionName;
	
	public int getPermissionId() {
		return permissionId;
	}
	public void setPermissionId(int permissionId) {
		this.permissionId = permissionId;
	}
	public String getPermissionName() {
		return permissionName;
	}
	public void setPermissionName(String permissionName) {
		this.permissionName = permissionName;
	}
	public Permissions(int permissionId, String permissionName) {
		super();
		this.permissionId = permissionId;
		this.permissionName = permissionName;
	}
	public Permissions() {
		super();
	}
	public Permissions(String permissionName) {
		super();
		this.permissionName = permissionName;
	}
}

5.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.ssm.dao.UsersMapper">

        //这里数据库的设计是，用户表，用户角色关系表，角色表，角色权限关联表，权限表。
	
        //sql代码片段
        <sql id="usersColumn">
		users.user_id,user_name,user_password
	</sql>
	<sql id="rolesColumn">
		roles.role_id,role_name
	</sql>
	<sql id="permissionsColumn">
		permissions.permission_id,permission_name
	</sql>
	<sql id="userRoleColumn">
		user_role_id,user_role.user_id,user_role.role_id
	</sql>
	<sql id="rolePermissionColumn">
		role_permission_id,role_permission.role_id,
		role_permission.permission_id
	</sql>

        //用户基础map，base版本。
	<resultMap type="Users" id="usersBaseMap">
		<id property="userId" column="user_id"/>
		<result property="userName" column="user_name"/>
		<result property="userPassword" column="user_password"/>
	</resultMap>
	
        //继承基础userMap，oftype：引用的java类型
	<resultMap type="Users" id="usersRolesPermissionsMap" extends="usersBaseMap">
		<collection property="roles" ofType="Roles">
			<id property="roleId" column="role_id"/>
			<result property="roleName" column="role_name"/>
			<collection property="permissions" ofType="Permissions">
				<id property="permissionId" column="permission_id"/>
				<result property="permissionName" column="permission_name"/>
			</collection>
		</collection>
	</resultMap>	
	
        //查询出来的角色和权限列表会自动封装到user对象中
	<select id="login" parameterType="Users" resultMap="usersRolesPermissionsMap">
		select 
		<include refid="usersColumn"/>,
		<include refid="rolesColumn"/>,
		<include refid="permissionsColumn"/>,
		<include refid="userRoleColumn"/>,
		<include refid="rolePermissionColumn"/>
		from users,roles,permissions,user_role,role_permission
		where
			 users.user_id = user_role.user_id
	        and  roles.role_id = user_role.role_id
		and  roles.role_id = role_permission.role_id
		and  permissions.permission_id = role_permission.permission_id
		and  users.user_name = #{userName}
	</select>
</mapper>

6.这样我们写dao通过user的name去查询用户时就可以把用户身上的角色和权限一并查出。