Mybatis关联查询之一

MyBatis的关联查询之一对多，多对一

在关系型数据库中，我们经常要处理一对多，多对一和多对多的关系。

一对多，多对一

一、entity 实体类

public class SmbmsRole {
    private long rid;
    private String roleCode;
    private String roleName;
　　　//泛型集合
    private List<User> users;

    public List<User> getUsers() {
        return users;
    }
    public void setUsers(List<User> users) {
        this.users = users;
    }

    public long getRid() {
        return rid;
    }

    public void setRid(long rid) {
        this.rid = rid;
    }

    public String getRoleCode() {
        return roleCode;
    }

    public void setRoleCode(String roleCode) {
        this.roleCode = roleCode;
    }

    public String getRoleName() {
        return roleName;
    }

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

public class User {
    private Integer uid;
    private String userCode;
    private String userName;
     //关联一的一方
    private SmbmsRole  role;

    public SmbmsRole getRole() {
        return role;
    }

    public void setRole(SmbmsRole role) {
        this.role = role;
    }

    public Integer getUid() {
        return uid;
    }

    public void setUid(Integer uid) {
        this.uid = uid;
    }

    public String getUserCode() {
        return userCode;
    }

    public void setUserCode(String userCode) {
        this.userCode = userCode;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

二、dao层

 

//一对多
    //根据角色获取所对应的人
    SmbmsRole getroleId(Integer rid);

    //多对一   查询所有用户信息
    List<User> getUserList();

 

 

 

三、小配置  xml

 

 <!--一对多-->
    <!--由于是关联查询 返回的是多张表中的结果集，必须定义resultMap映射-->
    <resultMap id="SmbmsRoleMaps" type="com.marketsys.entity.SmbmsRole">
        <id column="rid" property="rid"></id>
        <!--此处使用的是collection节点，由于在SmbmsRole类中插入的是List集合
            ofType:为集合中的泛型-->
        <collection property="users" ofType="User" select="getroleIdMutilSQL" column="rid">
            <!--在collection中声明Provincial中的属性与表中列的映射-->
           <!-- <id column="uid" property="uid"></id>-->
        </collection>
    </resultMap>

    <!--一对多  一条select-->
    <!--<select id="getroleId" parameterType="int" resultMap="SmbmsRoleMaps">
        select r.rid,roleName,u.uid,userName from smbms_role r inner join smbms_user u on r.rid=u.userRole where r.rid=#{rid}
    </select>-->
    <!--一对多  两条select-->
    <select id="getroleId" resultMap="SmbmsRoleMaps">
        select * from smbms.smbms_role where rid=#{rid}
    </select>
    <select id="getroleIdMutilSQL" resultType="com.marketsys.entity.User">
        select * from smbms.smbms_user where userRole=#{rid}
    </select>

    <!--多对一-->
   <resultMap id="SmbmsRoleMap" type="com.marketsys.entity.User">
        <id column="uid" property="uid"></id>

        <association property="role" javaType="com.marketsys.entity.SmbmsRole" select="getRole" column="userRole">
            <id column="rid" property="rid"></id>
        </association>
   </resultMap>
    <!-- 多对一  一条select-->
    <!--<select id="getUserList" resultMap="SmbmsRoleMap">
        select rid,roleName,uid,userName from smbms_role,smbms_user where smbms_role.rid=smbms_user.userRole
    </select>-->
    <!-- 多对一  二条select-->
    <select id="getUserList" resultMap="SmbmsRoleMap">
        select uid,userName,userRole from smbms.smbms_user
    </select>
    <select id="getRole" resultType="com.marketsys.entity.SmbmsRole">
        select rid,roleName from smbms.smbms_role where rid=#{userRole}
    </select>

 

 

 

四、test测试类

 

　　 SqlSession sqlSession= MybatisUtil.getSqlSession();
    URoleTestDao mapper=sqlSession.getMapper(URoleTestDao.class);

    //一对多
    @Test
    public void test1(){
        SmbmsRole smbmsRole=mapper.getroleId(3);
        System.out.println("身份为："+smbmsRole.getRoleName()+"\n姓名为：");
        for (User item:smbmsRole.getUsers()) {
            System.out.println(item.getUserName());
        }
    }

    //多对一
    @Test
    public void test2(){
        List<User> userList = mapper.getUserList();
        for (User user:userList) {
            System.out.println("用户："+user.getUserName()+"\t角色："+user.getRole().getRoleName());
        }
    }