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

深藏阁楼爱情的钟 2023-01-13 04:21 68阅读 0赞

### 实现要求： ###

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

### 实现思路： ###

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg_size_16_color_FFFFFF_t_70][]

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();
    		}
    	}
    }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg_size_16_color_FFFFFF_t_70]: /images/20221022/ffa491afd7a84120ad6e2888c9d2c05a.png