MyBatis之慎用association

这里先总结一下 association 存在的问题。

一、内嵌查询时存在报错Id找不到及内存溢出隐患
二、一对多关系数据重复问题
三、多层嵌套内层 association 查询结果为null 或 非预期的值

一、内嵌查询时存在报错Id找不到及内存溢出隐患

参考： https://www.lmlphp.com/user/57996/article/item/1356741/
错误示例：

<?xml version="1.0" encoding="UTF-8" ?>

 <!DOCTYPE mapper

 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

 <mapper namespace="com.lizhou.dao.StudentDao">

     <resultMap type="Student" id="studentResult">

         <id property="id" column="id"/>

         <association property="grade" column="gradeId" select="com.lizhou.dao.GradeDao.getGrade"></association>

     </resultMap>

     <select id="getStudent" resultMap="studentResult">

         SELECT * FROM student

         <where>

             <if test="id != null">

                 AND id=#{id}

             </if>

         </where>

     </select>

 </mapper> 

报错：Caused by: org.apache.ibatis.reflection.ReflectionException: There is no getter for property named ‘id’ in ‘class java.lang.Integer’

正确示例：

<mapper namespace="com.lizhou.dao.GradeDao">

     <resultMap type="Grade" id="gradeResult">

         <id property="id" column="id"/>

     </resultMap>

     <select id="getGrade" parameterType="Grade" resultMap="gradeResult">

         SELECT * FROM grade WHERE id=#{id}

     </select>

 </mapper> 

内存溢出隐患示例

错误示例：
StudentMapper.xml：

<mapper namespace="com.lizhou.dao.StudentDao">

     <resultMap type="Student" id="studentResult">

         <id property="id" column="id"/>

         <association property="grade" column="gradeId" select="com.lizhou.dao.GradeDao.getGrade"></association>

     </resultMap>

     <select id="getStudent" resultMap="studentResult">

         SELECT * FROM student WHERE gradeId=#{gradeId}

     </select>

 </mapper> 

GradeMapper.xml：

<mapper namespace="com.lizhou.dao.GradeDao">

     <resultMap type="Grade" id="gradeResult">

         <id property="id" column="id"/>

         <collection property="studentList" column="id" ofType="Student" select="com.lizhou.dao.StudentDao.getStudent"></collection>

     </resultMap>

     <select id="getGrade" parameterType="Grade" resultMap="gradeResult">

         SELECT * FROM grade WHERE id=#{id}

     </select>

 </mapper> 

解决方式：
分离写，分段查询

二、一对多关系数据重复问题

参考：https://blog.csdn.net/wuyezhiyu/article/details/81364974

 <resultMap id="commissionRec" type="CommissionRec" >
        <result property="sourceName" column="cmr_sourceName"/>
        <result property="totalMoney" column="cmr_totalMoney"/>
        <result property="totalCard" column="cmr_totalCard"/>
        <result property="totalCommission" column="cmr_totalCommission"/>
        <result property="commissionTimes" column="cmr_commissionTimes"/>
        <result property="isProvided" column="cmr_isProvided"/>
        
        <result property="userInfo.userId" column="us_userId"/>
        <result property="userInfo.userName" column="us_userName"/>
        
        <association property="memberCard" resultMap="memberCardTemplate" /> 
        <!-- 
        <association property="userInfo" resultMap="userInfo" /> 
         -->
    </resultMap>

 <resultMap id="userInfo" type="cn.sstech.member.marketing.model.base.BaseUserInfo" >
        <id property="userId" column="us_userId"/>
        <result property="userName" column="us_userName"/>
    </resultMap>

如上图所示查询结果中，第一条和第二条只有us_userId是不重复的，而剩下的字段都被注入到CommissionRec对象中。这时

1.用association注入userInfo mybatis会因为数据一样而将前两条数据合为一个CommissionRec对象，us_userId则随便调一个注入到userInfo对象中

2.用 <result property=“userInfo.userId” column=“us_userId”/> 这种制定具体字段名的方式注入，mybatis会因为记录数据有所不同而将前两条数据设为2个CommissionRec对象.

三、多层嵌套内层 association 查询结果为null 或 非预期

非预期

本人遇到的就是这个问题
以组织机构树的单张表为例，一般存的有ID及pre_id父级ID，当内嵌查询时，由于父级ID存在多条记录，导致查询结果并非预期

多层嵌套查询结果为null

参考：https://www.zhuxianfei.com/java/57394.html
错误示例：

<resultMap id="BaseResultMap" type="a.b.c.d.e">
    <id column="id" property="id" />
    <result property="workTime" column="work_time" />
    <result property="model" column="model" />
    <result property="status" column="status" />
    <association property="interfaceUpstream" javaType="interfaceUpstream" columnPrefix="ui_">
      <id column="id" property="id" />
      <result property="interfaceName" column="interface_name" />
      <result property="interfaceType" column="interface_type" />
      <result property="frequency" column="frequency" />
      <result property="address" column="address" />
      <result property="templateOrSql" column="template_or_sql" />
      <result property="status" column="status" />
      <association property="systemInfo" javaType="SystemInfo" columnPrefix="sys_">
        <id column="id" property="id"/>
        <result property="systemName" column="system_name"/>
        <result property="systemNameEN" column="system_name_en"/>
        <result property="belong" column="belong"/>
        <result property="status" column="status"/>
      </association>
      <association property="serverInfo" javaType="ServerInfo" columnPrefix="ser_">
        <id column="id" property="id"/>
        <result property="ftpIp" column="ftp_ip"/>
        <result property="ftpPort" column="ftp_port"/>
        <result property="ftpAccount" column="ftp_account"/>
        <result property="ftpPassword" column="ftp_password"/>
      </association>
    </association>
  </resultMap>

<sql id="base_select">
    SELECT
    ii.Id,
    ii.model,
    ii.status,
    ii.work_time,
    ui.id AS ui_id,
    ui.interface_name AS ui_interface_name,
    ui.interface_type AS ui_interface_type,
    ui.frequency AS ui_frequency,
    ui.address AS ui_address,
    ui.template_or_sql AS ui_template_or_sql,
    ui.status AS ui_status,
    sys.id AS sys_id,
    sys.system_name AS sys_system_name,
    sys.system_name_en AS sys_system_name_en,
    sys.belong AS sys_belong,
    sys.status AS sys_status,
    ser.id AS ser_id,
    ser.ftp_ip AS ser_ftp_ip,
    ser.ftp_port AS ser_ftp_port,
    ser.ftp_account AS ser_ftp_account,
    ser.ftp_password AS ser_ftp_password
  </sql>

结果： 内层的association查询的结果一直为null

原因是association在进行多层嵌套时，mybatis会将外层association的columnPrefix值与内层的进行并合，

如外层columnPrefix值位ui_, 内层为sys_， 那么在SQL中就不能这样 sys.id AS sys_id 了，需要将ui_前缀加上，变成 sys.id AS ui_sys_id ,这样mybatis在匹配的时候才会将数据映射到对应association上

正确示例：

SELECT
    ii.Id,
    ii.model,
    ii.status,
    ii.work_time,
    ui.id AS ui_id,
    ui.interface_name AS ui_interface_name,
    ui.interface_type AS ui_interface_type,
    ui.frequency AS ui_frequency,
    ui.address AS ui_address,
    ui.template_or_sql AS ui_template_or_sql,
    ui.status AS ui_status,
    sys.id AS ui_sys_id,
    sys.system_name AS ui_sys_system_name,
    sys.system_name_en AS ui_sys_system_name_en,
    sys.belong AS ui_sys_belong,
    sys.status AS ui_sys_status,
    ser.id AS ui_ser_id,
    ser.ftp_ip AS ui_ser_ftp_ip,
    ser.ftp_port AS ui_ser_ftp_port,
    ser.ftp_account AS ui_ser_ftp_account,
    ser.ftp_password AS ui_ser_ftp_password

总结

1、能不用就别用
2、如果用，请务必指定对应的字段或id
3、多层嵌套时请注意前缀的使用
4、内嵌sql避免递归