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

实现要求：

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

实现思路：

在用户实体类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();
		}
	}
}