MyBatis关联查询(对象嵌套对象)
程序员文章站
2022-07-13 17:26:56
...
Mybatis 查询对象中嵌套其他对象的解决方法有两种,一种是用关联另一个resultMap的形式,如
<association property="office" javaType="Office" resultMap="officeMap"/>
<mapper namespace="com.dixn.oa.modules.sys.dao.RoleDao"> <resultMap type="Office" id="officeMap"> <id property="id" column="id" /> <result property="name" column="office.name" /> <result property="code" column="office.code" /> </resultMap> <resultMap id="roleResult" type="Role"> <id property="id" column="id" /> <result property="name" column="name" /> <result property="enname" column="enname" /> <result property="roleType" column="roleType" /> <result property="dataScope" column="dataScope" /> <result property="remarks" column="remarks" /> <result property="useable" column="useable" /> <association property="office" javaType="Office" resultMap="officeMap"/> <collection property="menuList" ofType="Menu"> <id property="id" column="menuList.id" /> </collection> <collection property="officeList" ofType="Office"> <id property="id" column="officeList.id" /> </collection> </resultMap> <sql id="roleColumns"> a.id, a.office_id AS "office.id", a.name, a.enname, a.role_type AS roleType, a.data_scope AS dataScope, a.remarks, a.create_by AS "createBy.id", a.create_date, a.update_by AS "updateBy.id", a.update_date, a.del_flag, o.name AS "office.name", o.code AS "office.code", a.useable AS useable, a.is_sys AS sysData </sql> <select id="get" resultMap="roleResult"> SELECT <include refid="roleColumns"/> rm.menu_id AS "menuList.id", ro.office_id AS "officeList.id" FROM sys_role a JOIN sys_office o ON o.id = a.office_id LEFT JOIN sys_role_menu rm ON rm.role_id = a.id LEFT JOIN sys_role_office ro ON ro.role_id = a.id WHERE a.id = #{id} </select>
另一种联合查询 (一对一)的实现,但是这种方式有“N+1”的问题,不建议使用
<resultMap id="roleResult" type="Role"> <id property="id" column="id" /> <result property="name" column="name" /> <result property="enname" column="enname" /> <result property="roleType" column="roleType" /> <result property="dataScope" column="dataScope" /> <result property="remarks" column="remarks" /> <result property="useable" column="useable" /> <association property="office" javaType="Office" column="id" select="getOfficeById"/> <collection property="menuList" ofType="Menu"> <id property="id" column="menuList.id" /> </collection> <collection property="officeList" ofType="Office"> <id property="id" column="officeList.id" /> </collection> </resultMap>
<select id="getOfficeById" resultType="Office"> select o.name AS "office.name",o.code AS "office.code" from sys_office o where o.id = #{id} </select> 以上就是两种对象内嵌套对象查询的实现。
上一篇: linux下mysql数据库定时备份
下一篇: Java程序员一定知道的优秀第三方库
推荐阅读
-
vue利用v-for嵌套输出多层对象,分别输出到个表的方法
-
实例详解python函数的对象、函数嵌套、名称空间和作用域
-
iOS关联对象示例详解
-
MyBatis从入门到精通(十二):使用collection标签实现嵌套查询
-
序列化表单为json对象,datagrid带额外参提交一次查询 后台用Spring data JPA 实现带条件的分页查询 多表关联查询
-
mybatis 的一对一关联查询association
-
Python函数基础实例详解【函数嵌套,命名空间,函数对象,闭包函数等】
-
django 过滤器-查询集-比较运算符-FQ对象-mysql的命令窗口
-
mybatis_09关联查询_一对一
-
python语法之函数对象/嵌套/名称空间/作用域