resultMap多表查询【MyBatis笔记五】

resultMap  处理列名不一致问题(多表查询)

目录

（1）处理单表关系

（2）处理多表关系

①⼀对多和多对⼀

②多对多

③一对一

---

（1）处理单表关系

通过给列起别名，让别名=属性名，也可以实现数据对应(mybatis 能实现的是单表的⾃动操作)

property 为实体类中的属性名，column 为数据库返回结果的列名，两者不一致的时候需要通过 resultMap 实现数据对应 。

<resultMap id="aaa" type="bean.Dept">
    <!-- 可以⼿动指定列名和属性名的关系 ,⾮主键列使⽤result 标签，主键列使⽤id 标签-->
    <id property="dept_no" column="deptno"></id>
    <result property="d_name" column="dname"/>
</resultMap>

例如：

数据库中的列名为：stuAge
实体类bean中属性名为：age  
名字不同无法完成映射，需要在 resultMap 中完成附加映射

<resultMap id="rs1" type="com.yuyu.bean.Student">
    <result property="age" column="stuage"></result>
</resultMap>
<select id="getall" resultMap="rs1">
     select * from student
</select>

 

（2）处理多表关系

注:如果是单表查询,select 中使⽤resultType 设置返回的类型即可。但是如果是多表联查,那么select 查询的结果需要单独使⽤resultMap 标签来进⾏结果的映射

！！！resultType 和resultMap 属性只能出现⼀个

存的是集合的话使⽤Collection ⼦标签
存的是⼀⽅的话使⽤association ⼦标签

①两表联查:⼀对多和多对⼀

一对多：
<resultMap type="" id="⾃定义名称">
    <id property="id" column="dept_id" /><!--主键列-->
    <result property="java 属性名" column="列名" />
    <collection property="属性名" ofType="java 类型">
        <id property="属性名" column="列名" />
        <result property="属性名" column="列名" />
    </collection>
</resultMap>
多对一：
<resultMap type="" id="">
    <id property="" column="" />
    <result property="" column="" />
    <association property="" javaType="">
        <id property="" column="" />
        <result property="" column="" />
    </association>
</resultMap>

type是⽤来指定project中属性的类型，⽽ofType指定的是映射到list集合属性中project的类型。

学生和年级的关系 实现一对多和多对一的数据库操作

public interface GradeDao {
    //根据年级id查询年级和学生信息
    public Grade findbyGid(int id);
    //查询学生信息以及对应的年级信息
    public List<Student> findAllStudent();
}

根据年级id查询年级和学生信息：（一对多：存的是集合，使⽤Collection ⼦标签）

<select id="findbyGid" resultMap="rs2">
    select * from grade g,student s
    where s.gradeid=g.gid and g.gid=#{id}
</select>

<resultMap id="rs2" type="com.yuyu.bean.Grade">
<!--先描述自身的信息，然后描述关联表的信息,id表述主键列，非主键列使用result描述-->
    <id column="gid" property="gid"></id>
    <result column="gname" property="gname"></result>
    <collection property="studentList" ofType="com.yuyu.bean.Student">
        <id column="studentid" property="studentId"></id>
        <result column="stuname" property="stuName"></result>
        <result column="studentno" property="studentNo"></result>
        <result column="stuage" property="age"></result>
    </collection>
</resultMap>

public class TestGrade {
    public static void main(String[] args) {
        SqlSession session = SqlSessionUtil.getSession();
        GradeDao mapper = session.getMapper(GradeDao.class);
        Grade grade = mapper.findbyGid(2);
        System.out.println(grade.getGname());
        List<Student> studentList = grade.getStudentList();
        for (Student student : studentList) {
            System.out.println(student);
        }
        SqlSessionUtil.closeSession();
    }
}

查询学生信息以及对应的年级信息：（多对一：存的是⼀⽅，使⽤association ⼦标签）

<select id="findAllStudent" resultMap="rs3">
    select * from grade g,student s where s.gradeid=g.gid
</select>

<resultMap id="rs3" type="com.yuyu.bean.Student">
    <id column="studentid" property="studentId"></id>
    <result column="stuname" property="stuName"></result>
    <result column="studentno" property="studentNo"></result>
    <result column="stuage" property="age"></result>
    <association property="grade" javaType="com.yuyu.bean.Grade">
        <id column="gid" property="gid"></id>
        <result column="gname" property="gname"></result>
    </association>
</resultMap>

List<Student> allStudent = mapper.findAllStudent();
for (Student student : allStudent) {
    System.out.println(student+","+student.getGrade().getGname());
}

 

②多对多

角色和菜单的关系 实现多对多的数据库操作

public interface RoleDao {
    //1.查询某个角色以及对应的菜单
    public Role findByRoleId(int roleid);
    //2.查询某个菜单以及对应的角色
    public Menu findByMenuId(int menuid);
}

查询某个角色以及对应的菜单：

<select id="findByRoleId" resultMap="rs4">
    select * from role r,menu m ,middle mid
    where r.roleid=mid.roleid and m.menuid=mid.menuid
    and r.roleid=#{roleid}
</select>

<resultMap id="rs4" type="com.yuyu.bean.Role">
    <id property="roleId" column="roleid"></id>
    <result property="roleName" column="rolename"></result>
    <collection property="menuList" ofType="com.yuyu.bean.Menu">
        <id property="menuId" column="menuid"></id>
        <result property="menuName" column="menuname"></result>
        <result property="menuPath" column="menupath"></result>
    </collection>
</resultMap>

Role role = mapper.findByRoleId(1);
System.out.println(role);
List<Menu> menuList = role.getMenuList();
for (Menu menu : menuList) {
    System.out.println(menu);
}

查询某个菜单以及对应的角色:

<select id="findByMenuId" resultMap="rs5">
    select * from role r,menu m ,middle mid
    where r.roleid=mid.roleid and m.menuid=mid.menuid
    and m.menuid=#{menuid}
</select>

<resultMap id="rs5" type="com.yuyu.bean.Menu">
    <id property="menuId" column="menuid"></id>
    <result property="menuName" column="menuname"></result>
    <result property="menuPath" column="menupath"></result>
    <collection property="roleList" ofType="com.yuyu.bean.Role">
        <id property="roleId" column="roleid"></id>
        <result property="roleName" column="rolename"></result>
    </collection>
</resultMap>

Menu menu = mapper.findByMenuId(28);
System.out.println(menu);
List<Role> roleList = menu.getRoleList();
for (Role role : roleList) {
    System.out.println(role);
}

 

③一对一

丈夫与妻子的关系 实现一对一的数据库操作

public interface WifeDao {
    //根据丈夫查询妻子
    public Husband findByhusId(int husid);
    //根据妻子查询丈夫
    public Wife findByWifeId(int wifeid);
}

根据丈夫查询妻子：

<select id="findByhusId" resultMap="rs6">
    select * from wife w,husband h
    where w.wifeid=h.wid and h.husid=#{husid}
</select>

<resultMap id="rs6" type="com.yuyu.bean.Husband">
    <id property="husId" column="husid"></id>
    <result property="husName" column="husname"></result>
    <association property="wife" javaType="com.yuyu.bean.Wife">
        <id property="wifeId" column="wifeid"></id>
        <result property="wifeName" column="wifename"></result>
    </association>
</resultMap>

Husband husband = mapper.findByhusId(1);
System.out.println(husband.getHusName()+","+husband.getWife().getWifeName());

根据妻子查询丈夫:

<select id="findByWifeId" resultMap="rs7">
     select * from wife w,husband h
     where w.wifeid=h.wid and w.wifeid=#{wifeid}
</select>

<resultMap id="rs7" type="com.yuyu.bean.Wife">
    <id property="wifeId" column="wifeid"></id>
    <result property="wifeName" column="wifename"></result>
    <association property="husband" javaType="com.yuyu.bean.Husband">
        <id property="husId" column="husid"></id>
        <result property="husName" column="husname"></result>
    </association>
</resultMap>

Wife wife = mapper.findByWifeId(1);
System.out.println(wife.getWifeName()+","+wife.getHusband().getHusName());