在Mybatis中association标签多层嵌套的问题

在Mybatis中association标签多层嵌套的问题

目录association标签多层嵌套问题排查从代码上看没有什么问题正常代码如下association集合嵌套这个返回集合有什么用呢

association标签多层嵌套问题

mybatis里查询使用嵌套association标签时，发现内层的association查询的结果一直为null

排查

检查sql执行情况，发现有数据返回，排除检查property的值是否和pojo中的对应，值一致，排除检查column的值是否和数据库的相对应，相对应，排除

那么应该是mybatis没有把数据映射到位了，经过排查是association中columnPrefix被不对应

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

从代码上看没有什么问题

原因是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

第三张表里在表示部门和职位的时候只用了上面两张表的主键

但是查询的时候，希望表示下面这样的结果

所以返回值是不止一个对象，这样就用到http://了集合嵌套

javaType="com.advancedc.hrsys.entity.JobDepartment">

javaType="com.advancedc.hrsys.entity.JobDepartment">

javaType="com.advancedc.hrsys.entity.JobPosition">

javaType="com.advancedc.hrsys.entity.JobPosition">

只需要知道：

（1）column表示数据库字段

（2）property表示Java里的值

而且我这里的主键都是id所以会出现重名的情况，在SQL语句里，查询时就要赋予别名才能加以区分，返回结果resultMap就如上图所示

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

and ui.id = #{someone.id}

and ui.gender = #{someone.gender}

and ui.name = #{someone.name}

and ui.id_card = #{someone.idCard}

and ui.is_married = #{someone.isMarried}

and ui.department_id = #{someone.jobDepartment.id}

and ui.position_id = #{someone.jonPosition.id}

and ui.phone = #{someone.phone}

and ui.entry_time = #{someone.entryTime}

and ui.full_time = #{someone.fullTime}

上图用了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;

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。