这篇“Mybatis中association标签多层嵌套问题怎么解决”文章的知识点大部分人都不太理解,所以小编给大家总结了以下内容,内容详细,步骤清晰,具有一定的借鉴价值,希望大家阅读完这篇文章能有所收获,下面我们一起来看看这篇“Mybatis中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
问题解决!
学了一下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;
以上就是关于“Mybatis中association标签多层嵌套问题怎么解决”这篇文章的内容,相信大家都有了一定的了解,希望小编分享的内容对大家有帮助,若想了解更多相关的知识内容,请关注亿速云行业资讯频道。
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。