学了一下mybatis的查询返回值的集合嵌套,先查了查官网:
这个返回集合有什么用呢?举个例子三张表
hr_job_department
hr_job_position
第三张表里在表示部门和职位的时候只用了上面两张表的主键
但是查询的时候,希望表示下面这样的结果
所以返回值是不止一个对象,这样就用到了集合嵌套
javaType="com.advancedc.hrsys.entity.JobDepartment">
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
0">
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;