【Mybatis】利用注解实现多对多的多表查询

在上篇博客的基础上在添加一个类Role

package com.leoham.domain;

public class Role {
    private int id;
    private String roleName;
    private String roleDesc;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getRoleName() {
        return roleName;
    }

    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }

    public String getRoleDesc() {
        return roleDesc;
    }

    public void setRoleDesc(String roleDesc) {
        this.roleDesc = roleDesc;
    }

    @Override
    public String toString() {
        return "Role{" +
                "id=" + id +
                ", roleName='" + roleName + '\'' +
                ", roleDesc='" + roleDesc + '\'' +
                '}';
    }
}

然后在User类中添加一个List<User> 属性

package com.leoham.domain;

import org.w3c.dom.ls.LSInput;

import java.util.Date;
import java.util.List;

public class User {
    private int id;
    private String username;
    private String password;
    private Date birthday;
    private List<Role> roleList;

    public List<Role> getRoleList() {
        return roleList;
    }

    public void setRoleList(List<Role> roleList) {
        this.roleList = roleList;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", birthday=" + birthday +
                ", roleList=" + roleList +
                '}';
    }
}

然后创建一个RoleMapper接口，同时实现通过用户id查询信息的功能

package com.leoham.mapper;

import com.leoham.domain.Role;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface RoleMapper {
    @Select("SELECT * FROM sys_user_role ur, sys_role r WHERE ur.roleid = r.id AND ur.userid = #{uid};")
    public List<Role> findAllRole(int uid);
}

 最后在UserMapper中创建一个方法找到所有用户对应的角色信息

    @Select("select * from user")
    @Results({
            @Result(id = true, column = "id", property = "id"),
            @Result(id = true, column = "username", property = "username"),
            @Result(id = true, column = "password", property = "password"),
            @Result(
                    property = "roleList",
                    column = "id",
                    javaType = List.class,
                    many = @Many(select = "com.leoham.mapper.RoleMapper.findAllRole")
            )
    })
    public List<User> findUserAndRoleAll();

最后测试

    public class MapperTest {

        private UserMapper mapper_user;
        private OrderMapper mapper_order;
        @Before
        public void before() throws IOException {
            InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
            SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
            SqlSession sqlSession = sessionFactory.openSession(true);
            mapper_user = sqlSession.getMapper(UserMapper.class);
            mapper_order = sqlSession.getMapper(OrderMapper.class);
        }


        @Test
        public void findUserAndRoleAll(){
            List<User> userList = mapper_user.findUserAndRoleAll();
            for (User user : userList) {
                System.out.println(user);
            }
        }
}