MyBatis自定义resultMap以及映射中多对一和一对多

解决字段名和属性名不一致的问题

起别名

• 问题代码

    <select id="getAllEmp" resultType="Emp">
        select eid, emp_name, age, sex, email from t_emp
    </select>

• 结果

Emp{eid=1, empName='null', age=23, sex='男', email='123456@qq.com'}

---

• 起别名

    <select id="getAllEmp" resultType="Emp">
        select eid, emp_name  empName, age, sex, email from t_emp
    </select>

• 结果

Emp{eid=1, empName='张三', age=23, sex='男', email='123456@qq.com'}

全局配置

• mybatis全局配置连接
  

 <select id="getAllEmp" resultType="Emp">
     select * from t_emp
 </select>

Emp{eid=1, empName='张三', age=23, sex='男', email='123456@qq.com'}

自定义ResultMap

• resultMap标签设置自定义映射关系

  id设置唯一标识不能重复
  type设置映射关系中的类型

• id标签设置主键的元素关系， result标签设置映射关系中的实体类型

  property：设置映射关系中的属性名，必须是type属性设置的实体类类型中的属性名
  column：设置映射关系中的字段名，必须是sql语句查询出的字段名

---

• 设置字段与属性之间的对应关系

    <resultMap id="empResultMap" type="emp">
        <id property="eid" column="eid"></id>
        <result property="empName" column="emp_name"></result>
        <result property="age" column="age"></result>
        <result property="sex" column="sex"></result>
        <result property="email" column="email"></result>
    </resultMap>

• xml标签加入resultMap属性

    <select id="getAllEmp" resultMap="empResultMap">
        select * from t_emp
    </select>

解决多对一的映射关系

通过级联属性赋值

<resultMap id="empAndDeptResultMapOne" type="emp">
    <id property="eid" column="eid"></id>
    <result property="empName" column="emp_name"></result>
    <result property="age" column="age"></result>
    <result property="sex" column="sex"></result>
    <result property="email" column="email"></result>
    <result property="dept.did" column="did"/>
    <result property="dept.deptName" column="dept_name"/>
</resultMap>

<select id="getEmpAndDept" resultMap="empAndDeptResultMapOne">
    select * from t_emp,t_dept where t_emp.eid = #{eid} and t_emp.did = t_dept.did
</select>

通过association来解决多对一映射关系

• association：处理多对一的映射关系
• property：需要处理多对的映射关系的属性名
• javaType：该属性的类型

    <resultMap id="empAndDeptResultMapTwo" type="emp">
        <id property="eid" column="eid"></id>
        <result property="empName" column="emp_name"></result>
        <result property="age" column="age"></result>
        <result property="sex" column="sex"></result>
        <result property="email" column="email"></result>
        <association property="dept" javaType="Dept">
            <id property="did" column="did"></id>
            <result property="deptName" column="dept_name"></result>
        </association>
    </resultMap>

    <select id="getEmpAndDept" resultMap="empAndDeptResultMapTwo">
        select * from t_emp,t_dept where t_emp.eid = #{eid} and t_emp.did = t_dept.did
    </select>

分步查询

• select：设置分布查询的sql的唯一标识（namespace.SQLId或mapper接口的全类名.方法名）
• column：设置分步查询的条件

---

• 第一步 EmpMapper.xml

<resultMap id="empAndDeptResultMapStepOne" type="emp">
        <id property="eid" column="eid"></id>
        <result property="empName" column="emp_name"></result>
        <result property="age" column="age"></result>
        <result property="sex" column="sex"></result>
        <result property="email" column="email"></result>
        <association property="dept"
                     select="com.ssc.mybatis.mapper.DeptMapper.getEmpAndDeptStepTwo"
                     column="did">
        </association>
    </resultMap>

    <select id="getEmpAndDeptSteptOne" resultMap="empAndDeptResultMapStepOne">
        select * from t_emp where eid = #{eid}
    </select>

• 第二步 DeptMapper.xml

    <select id="getEmpAndDeptStepTwo" resultType="Dept">
        select did,dept_name deptName from t_dept where did = #{did}
    </select>

• 结果

Emp{eid=3, empName='王五', age=30, sex='男', email='123456@qq.com', dept=Dept{did=3, deptName='器材管理部'}}

分布查询的优点：延迟加载

• 在核心配置文件中设置全局配置信息settings标签，开启全局的延迟加载

    <!--设置mybatis的全局配置-->
    <settings>
        <!--开启延迟加载-->
        <setting name="lazyLoadingEnabled" value="true"/>
        <setting name="aggressiveLazyLoading" value="false"/>
    </settings>

• 通过设置fetchType="lazy"延迟加载，fetchType="eager"立即加载

<resultMap id="empAndDeptResultMapStepOne" type="emp">
   <id property="eid" column="eid"></id>
    <result property="empName" column="emp_name"></result>
    <result property="age" column="age"></result>
    <result property="sex" column="sex"></result>
    <result property="email" column="email"></result>
    <association property="dept"
                 select="com.ssc.mybatis.mapper.DeptMapper.getEmpAndDeptStepTwo"
                 column="did"
                 fetchType="lazy">
    </association>
</resultMap>

解决一对多的映射关系

collection标签

• ofType="Emp"属性设置查询集合中的类型

    <resultMap id="deptAndEmpResultMap" type="dept">
        <id property="did" column="did"></id>
        <result property="deptName" column="dept_name"></result>
        <collection property="emps"  ofType="Emp">
            <id property="eid" column="eid"></id>
            <result property="empName" column="emp_name"></result>
            <result property="age" column="age"></result>
            <result property="sex" column="sex"></result>
            <result property="email" column="email"></result>
        </collection>
    </resultMap>

    <select id="getDeptAndEmp" resultMap="deptAndEmpResultMap">
        select * from t_dept join t_emp  on t_dept.did = t_dept.did where t_dept.did = #{did}
    </select>

分步查询

• 第一步

<resultMap id="deptAndEmpResultMapOne" type="dept">
        <id property="did" column="did"></id>
        <result property="deptName" column="dept_name"></result>
        <collection property="emps"
                    select="com.ssc.mybatis.mapper.EmpMapper.getEmpAndDeptSteptTwo"
                    column="did"
                    fetchType="lazy">
        </collection>
    </resultMap>

    <select id="getDeptAndEmpStepOne" resultMap="deptAndEmpResultMapOne">
        select * from t_dept where did = #{did}
    </select>

• 第二步

    <select id="getEmpAndDeptSteptTwo" resultType="Emp">
        select * from t_emp where did = #{did}
    </select>

• 结果：需要哪句SQL语句就执行哪句SQL语句

DEBUG 06-20 22:10:57,894 ==>  Preparing: select * from t_dept where did = ? (BaseJdbcLogger.java:137) 
DEBUG 06-20 22:10:58,055 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:137) 
DEBUG 06-20 22:10:58,241 <==      Total: 1 (BaseJdbcLogger.java:137) 
财务部
++++++++++++++++
DEBUG 06-20 22:10:58,245 ==>  Preparing: select * from t_emp where did = ? (BaseJdbcLogger.java:137) 
DEBUG 06-20 22:10:58,246 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:137) 
DEBUG 06-20 22:10:58,252 <==      Total: 2 (BaseJdbcLogger.java:137) 
[Emp{eid=1, empName='张三', age=23, sex='男', email='123456@qq.com', dept=null}, Emp{eid=4, empName='赵六', age=25, sex='男', email='123456@qq.com', dept=null}]