mybatis-多对多查询

用户表和角色表的关系为，一个用户有多个角色，一个角色被多个用户使用多对多查询的需求：查询用户同时查询出该用户的所有角色

对应的sql语句： SELECT * FROM USER u,sys_user_role ur,sys_role r WHERE u.id=ur.userId AND ur.roleId=r.id
查询的结果：

1）创建Role实体，修改User实体

public class User {
    private int id;
    private String username;
    private String password;
    private Date birthday;
    //代表当前用户具备哪些角色
    private List roleList;
}

public class Role {
    private int id;
    private String rolename;

}

2) 添加UserMapper接口方法

List findAllUserAndRole();

3)配置UserMapper.xml

    
        
        
        
        
        
        
        
        
            
            
            
        
    

    
        SELECT * FROM USER u,sys_user_role ur,sys_role r WHERE u.id=ur.userId AND ur.roleId=r.id
    

4)测试结果

@Test
    public void test3() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List userAndRoleAll = mapper.findUserAndRoleAll();
        for (User user : userAndRoleAll) {
            System.out.println(user);
        }

        sqlSession.close();
    }