实现要求：

获取拥有“普通用户”角色的所有用户信息，要求查询结果除了包含用户自身信息，还包括角色名和角色创建时间。

实现思路：

在用户实体类SysUser中新增角色SysRole成员属性。

package entity;

public class SysUser {
    private long id;
    private String userName;
    private String userPassword;
    private String userEmail;
    private String userInfo;
    private byte headImg;
    private String createTime;
    private int deptId;
    private SysRole role;
    public SysUser() {
        super();
    }
    public SysUser(long id, String userName, String userPassword, String userEmail, String userInfo, byte headImg,
            String createTime, int deptId, SysRole role) {
        super();
        this.id = id;
        this.userName = userName;
        this.userPassword = userPassword;
        this.userEmail = userEmail;
        this.userInfo = userInfo;
        this.headImg = headImg;
        this.createTime = createTime;
        this.deptId = deptId;
        this.role = role;
    }
    @Override
    public String toString() {
        return "SysUser [id=" + id + ", userName=" + userName + ", userPassword=" + userPassword + ", userEmail="
                + userEmail + ", userInfo=" + userInfo + ", headImg=" + headImg + ", createTime=" + createTime
                + ", deptId=" + deptId + ", role=" + role + "]";
    }
    public long getId() {
        return id;
    }
    public void setId(long id) {
        this.id = id;
    }
    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 String getUserEmail() {
        return userEmail;
    }
    public void setUserEmail(String userEmail) {
        this.userEmail = userEmail;
    }
    public String getUserInfo() {
        return userInfo;
    }
    public void setUserInfo(String userInfo) {
        this.userInfo = userInfo;
    }
    public byte getHeadImg() {
        return headImg;
    }
    public void setHeadImg(byte headImg) {
        this.headImg = headImg;
    }
    public String getCreateTime() {
        return createTime;
    }
    public void setCreateTime(String createTime) {
        this.createTime = createTime;
    }
    public int getDeptId() {
        return deptId;
    }
    public void setDeptId(int deptId) {
        this.deptId = deptId;
    }
    public SysRole getRole() {
        return role;
    }
    public void setRole(SysRole role) {
        this.role = role;
    }

}

在UserMapper接口中新增一个方法。

package mapper;

import java.util.List;

import entity.SysUser;

public interface UserMapper {
    List<SysUser> selectUsersRoleName(String roleName);
    int insertInfo(SysUser sysUser);
}

在UserMapper.xml中定义selectUsersByRoleName()方法的SQL，注意sql语句中角色信息列的别名要和SysUser新增角色对象的属性名一致。

    <select id="selectUsersRoleName" resultType="SysUser">
        SELECT r.role_name as "role.roleName",r.create_time as "role.createTime"
        FROM sys_role r,sys_user u,sys_user_role ur
        WHERE ur.user_id=u.id AND ur.role_id=r.id AND role_name=#{roleName}
    </select>

测试selectUsersByRoleName()方法。

package test;

import java.io.IOException;
import java.io.Reader;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.BeforeClass;
import org.junit.Test;

import entity.SysUser;
import mapper.UserMapper;

public class UserMapperTest {
    private static SqlSessionFactory sqlSessionFactory;

    /* 完成mybatis配置的加载，创建得到SqlSessionFactory */
    @BeforeClass
    public static void init() {
        try {
            Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
            reader.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /* 获取SqlSession对象，用于调用方法得到数据 */
    @Test
    public void testSelectAll() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            SysUser sysUser = new SysUser();
            List<SysUser> selectUsersRoleName = userMapper.selectUsersRoleName("普通用户");
            for (SysUser sysUser2 : selectUsersRoleName) {
                System.out.println("数据"+sysUser2.toString());
            }
        } finally {
            // 不要忘记关闭sqlSession
            sqlSession.close();
        }
    }
}