association标签多层嵌套问题
mybatis里查询使用嵌套association标签时，发现内层的association查询的结果一直为null
排查
- 检查sql执行情况，发现有数据返回，排除
- 检查property的值是否和pojo中的对应，值一致，排除
- 检查column的值是否和数据库的相对应，相对应，排除
那么应该是mybatis没有把数据映射到位了，经过排查是association中columnPrefix被不对应
<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在进行多层嵌套时，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问题解决！
association集合嵌套
学了一下mybatis的查询返回值的集合嵌套，先查了查官网：
这个返回集合有什么用呢
举个例子三张表
hr_job_department
hr_job_position
第三张表里在表示部门和职位的时候只用了上面两张表的主键
但是查询的时候，希望表示下面这样的结果
所以返回值是不止一个对象，这样就用到了集合嵌套
<resultMap id="userInfoMap" type="com.advancedc.hrsys.entity.UserInfo">
<id column="id" property="id" />
<result column="name" property="name" />
<result column="gender" property="gender" />
<result column="id_card" property="idCard" />
<result column="is_married" property="isMarried" />
<result column="phone" property="phone" />
<result column="priority" property="priority" />
<result column="entry_time" property="entryTime" />
<result column="full_time" property="fullTime" />
<result column="created_time" property="createdTime" />
<result column="edited_time" property="editedTime" />
<association property="jobDepartment" column="id"
javaType="com.advancedc.hrsys.entity.JobDepartment">
<id column="jdid" property="id" />
<result column="jdname" property="name" />
</association>
<association property="jobPosition" column="id"
javaType="com.advancedc.hrsys.entity.JobPosition">
<id column="jpid" property="id" />
<result column="jpname" property="name" />
</association>
</resultMap>只需要知道：
（1）column表示数据库字段
（2）property表示Java里的值
而且我这里的主键都是id所以会出现重名的情况，在SQL语句里，查询时就要赋予别名才能加以区分，返回结果resultMap就如上图所示
<select id="queryUserInfoBySomeone" resultMap="userInfoMap" resultType="com.advancedc.hrsys.entity.UserInfo">
SELECT
ui.id,
ui.name,
ui.gender,
ui.id_card,
ui.is_married,
ui.department_id,
ui.position_id,
ui.phone,
ui.priority,
ui.entry_time,
ui.full_time,
ui.created_time,
ui.edited_time,
jd.id jdid,
jd.name jdname,
jp.id jpid,
jp.name jpname
FROM
hr_user_info ui
INNER JOIN
hr_job_department jd
ON
ui.department_id=jd.id
INNER JOIN
hr_job_position jp
ON
ui.position_id=jp.id
<where>
<if test="someone.id>0">
and ui.id = #{someone.id}
</if>
<if test="someone.gender!=null">
and ui.gender = #{someone.gender}
</if>
<if test="someone.name!=null">
and ui.name = #{someone.name}
</if>
<if test="someone.idCard!=null">
and ui.id_card = #{someone.idCard}
</if>
<if test="someone.isMarried!=null">
and ui.is_married = #{someone.isMarried}
</if>
<if test="someone.jobDepartment!=null and someone.jobDepartment.id!=null">
and ui.department_id = #{someone.jobDepartment.id}
</if>
<if test="someone.jobPosition!=null and someone.jobPosition.id!=null">
and ui.position_id = #{someone.jonPosition.id}
</if>
<if test="someone.phone!=null">
and ui.phone = #{someone.phone}
</if>
<if test="someone.entryTime!=null">
and ui.entry_time = #{someone.entryTime}
</if>
<if test="someone.fullTime!=null">
and ui.full_time = #{someone.fullTime}
</if>
</where>
</select>上图用了INNER JOIN来查询看上去挺简洁的，有一种不简洁的写法如下，虽然也能得到结果，但是不知道性能对比如何
SELECT
ui.id,
ui.name,
ui.gender,
ui.id_card,
ui.is_married,
ui.department_id,
ui.position_id,
ui.phone,
ui.priority,
ui.entry_time,
ui.full_time,
ui.created_time,
ui.edited_time,
(select id jdid from hr_job_department jd where jd.id=ui.department_id) jdid,
(select name jdname from hr_job_department jd where jd.id=ui.department_id) jdname,
(select id jpid from hr_job_position jp where jp.id=ui.position_id) jpid,
(select name jpname from hr_job_position jp where jp.id=ui.position_id) jpname
FROM
hr_user_info ui;以上为个人经验，希望能给大家一个参考，也希望大家多多支持编程网。
