实现要求：
获取拥有“普通用户”角色的所有用户信息，要求查询结果除了包含用户自身信息，还包括角色名和角色创建时间。
实现思路：
在用户实体类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(); } } }
