Mybatis多表查询-映射关系配置

resultMap配置

是什么

resultMap处理结果集映射

基础使用

  • 自动方法
    • 如果resultType为POJO且属性名对应数据表中的字段名,会自动生成resultMap处理映射。
    • 如果列名和属性名不匹配也可以使用SQL语句中的as给列名起别名,对应属性名产生映射
  • 手动方法

id和result的不同点在于,在比较对象实例的时候id作为结果集的标识属性。这有助于提高总体性能,特别是应用缓存和嵌套结果映射的时候。

<resultMap id="userinfoResultMap" type="com.test.domain.Userinfo">
    <id property="userid" column="user_id"/>
    <result property="username" column="user_name"/>
    <result property="password" column="password"/>
</resultMap>
  • 拓展resultMap
<resultMap id="userinfoAndAddressResultMap" type="com.test.domain.Userinfo" extends="userinfoResultMap">
    <id property="address.addrid" column="addrid"/>
    <result property="address.city" column="city"/>
    <result property="address.street" column="street"/>
    <result property="address.zip" column="zip"/>
</resultMap>

一对一映射

方法一:拓展resultMap

方法二:嵌套resultMap

<resultMap id="userinfoAndAddressResultMap" type="com.test.domain.Userinfo">
    <id property="userid" column="user_id"/>
    <result property="username" column="user_name"/>
    <result property="password" column="password"/>
    <association property="address" resultMap="addressResultMap"></association>
</resultMap>

association元素处理“一对一”这种类型关系

方法三:嵌套引入select语句

<resultMap id="addressResultMap" type="com.test.domain.Address">
    <id property="addrid" column="addrid"/>
    <result property="city" column="city"/>
    <result property="street" column="street"/>
    <result property="zip" column="zip"/>
</resultMap>
<select id="selectAddressById" parameterType="int" resultMap="addressResultMap">
    SELECT * FROM address WHERE addrid = #{addrid}
</select>
<resultMap id="userinfoAndAddressResultMap" type="com.test.domain.Userinfo">
    <id property="userid" column="user_id"/>
    <result property="username" column="user_name"/>
    <result property="password" column="password"/>
    <association property="address" column="addrid" select="selectAddressByID"/>
</resultMap>

拓展:如果需要启用延迟加载,即真正用到User中的Address的时候才进行对应sql语句的查询操作,可以在mybatis配置文件中把懒加载打开:

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

一对多映射

使用<collection>元素将一对多类型的结果映射到一个对象集合上。

方法一:内嵌结果resultMap

<resultMap id="employeeResultMap" type="com.test.domain.Employee">
    <id property="empid" column="empid"/>
    <result property="empname" column="empname"/>
</resultMap>
<resultMap id="departmentResultMap" type="com.test.domain.Department">
    <id property="depid" column="depid"/>
    <result property="depname" column="depname"/>
    <collection property="employees" resultMap="employeeResultMap"></collection>
</resultMap>

<select id="selectDepartmentAndEmployeeById" parameterType="int" resultMap="departmentResultMap">
    SELECT dep.depid,dep.depname,emp.empid,emp.empname
    FROM
    department dep LEFT OUTER JOIN employee emp ON dep.depid = emp.empid
    WHERE
    dep.depid = #{depid}
</select>

方法二:嵌套select语句

<resultMap id="employeeResultMap" type="com.test.domain.Employee">
    <id property="empid" column="empid"/>
    <result property="empname" column="empname"/>
</resultMap>
<resultMap id="departmentResultMap" type="com.test.domain.Department">
    <id property="depid" column="depid"/>
    <result property="depname" column="depname"/>
    <collection property="employees" select="selectEmployeeById" column="depid"></collection>
</resultMap>

<select id="selectEmployeeById" parameterType="int" resultMap="employeeResultMap">
    SELECT emp.empid,emp.empname
    FROM
    employee emp 
    WHERE
    emp.empid = #{empid}
</select>
<select id="selectDepartmentById" parameterType="int" resultMap="departmentResultMap">
    SELECT dep.depid,dep.depname 
    FROM
    department dep 
    WHERE
    dep.depid = #{depid}
</select>

注意

注意内嵌结果resultMap和嵌套select语句的思路是不同的,内嵌结果resultMap只执行一条sql语句,而嵌套select语句可以分别执行,还支持延迟加载。

原创文章,作者:彭晨涛,如若转载,请注明出处:https://www.codetool.top/article/mybatis%e5%a4%9a%e8%a1%a8%e6%9f%a5%e8%af%a2-%e6%98%a0%e5%b0%84%e5%85%b3%e7%b3%bb%e9%85%8d%e7%bd%ae/

发表评论

电子邮件地址不会被公开。