主从表关联查询,返回对象带有集合属性
昨天有同事让我帮着看一个问题，mybatis主从表联合查询，返回的对象封装集合属性。我先将出现的问题记录一下，然后再讲处理方法也简单说明一下：
VersionResult为接收返回数据对象
get\set方法我这里就省略了。
|
1 2 3 4 5 6 7 8 |
public class VersionResult extends BaseResult implements Serializable{ private Integer id; private String code; @JsonFormat (pattern = "yyyy-MM-dd HH:mm" , timezone = "GMT+8" ) private Date createTimes; //记录内容表的集合对象 private List<UpdateRecordEntity> UpdateRecordEntityList; } |
UpdateRecordEntity为从表数据
同样get\set方法我这里就省略了。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
@Table (name = "z_update_record" ) public class UpdateRecordEntity extends BaseEntity { @Id private Integer id; @Column (name = "version_id" ) private Integer versionId; @Column (name = "module_name" ) private String moduleName; @Column (name = "update_content" ) private String updateContent; @JsonFormat (pattern = "yyyy-MM-dd HH:mm" , timezone = "GMT+8" ) @Column (name = "create_time" ) private Date createTime; @Column (name = "is_delete" ) private Integer isDelete; } |
mapper.xml写法，这个是关键
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
<!--跟新记录表封装的对象--> < resultMap id = "BaseResultMap" type = "com.wangtiansoft.wisdomedu.persistence.result.server.VersionResult" > < id column = "id" property = "id" jdbcType = "INTEGER" /> < result column = "code" property = "code" /> < result column = "create_time" property = "createTimes" /> < collection property = "UpdateRecordEntityList" ofType = "com.wangtiansoft.wisdomedu.persistence.entity.UpdateRecordEntity" > < id property = "id" column = "id" /> < result property = "versionId" column = "version_id" /> < result property = "moduleName" column = "module_name" /> < result property = "updateContent" column = "update_content" /> < result property = "createTime" column = "create_time" /> < result property = "isDelete" column = "is_delete" /> < result property = "tenantId" column = "tenant_id" /> </ collection > </ resultMap > |
sql查询语句
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
< select id= "selectVersionList" parameterType= "map" resultMap= "BaseResultMap" > SELECT z.`code`, z.create_time createTimes, zur.module_name moduleName, zur.update_content updateContent, zur.create_time createTime FROM z_version z LEFT JOIN z_update_record zur ON z.id = zur.version_id WHERE z.tenant_id = #{tenantId} AND z.is_delete = 0 AND z.is_disabled = 0 AND zur.tenant_id = #{tenantId} AND zur.is_delete = 0 AND YEAR (z.create_time)= YEAR (#{ date }) ORDER by z.create_time desc </ select > |
执行sql返回的数据
页面调取接口
下面我将接口数据粘贴下来：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
{ "code": "0", "msg": "", "data": [{ "id": null, "code": "1419", "createTimes": null, "updateRecordEntityList": [] }, { "id": null, "code": "开发修改1111", "createTimes": null, "updateRecordEntityList": [] }, { "id": null, "code": "开发修改1111", "createTimes": null, "updateRecordEntityList": [] }, { "id": null, "code": "开发修改1111", "createTimes": null, "updateRecordEntityList": [] }, { "id": null, "code": "开发修改1111", "createTimes": null, "updateRecordEntityList": [] }] } |
观察code、createTimes、updateRecordEntityList三个属性，会发现只有code字段有值其余的全部为null。分析这个是为啥呢？找点资料粘贴如下：
发现是sql数据和VersionResult的mapper.xml中映射关系有点问题，没有对应起来。resultMap中必须将别名和上面resultMap对的上就行，很明显sql返回数据的列明没有和resultMap一一对应起来，因此有了以下对xml文件的修改：
|
1 2 3 4 5 6 7 8 9 10 11 |
< resultMap id = "BaseResultMap" type = "com.wangtiansoft.wisdomedu.persistence.result.server.VersionResult" > < id column = "id" property = "id" jdbcType = "INTEGER" /> < result column = "code" property = "code" /> < result column = "createTimes" property = "createTimes" /> < collection property = "UpdateRecordEntityList" ofType = "com.wangtiansoft.wisdomedu.persistence.entity.UpdateRecordEntity" > < id property = "id" column = "id" /> < result property = "moduleName" column = "moduleName" /> < result property = "updateContent" column = "updateContent" /> < result property = "createTime" column = "createTime" /> </ collection > </ resultMap > |
数据显示正常：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
{ "code": "0", "msg": "", "data": [{ "code": "1419", "createTimes": "2019-09-02 00:00", "updateRecordEntityList": [{ "moduleName": "安达市大所", "updateContent": "1321321", "createTime": "2019-09-02 10:17" }] }, { "code": "开发修改1111", "createTimes": "2019-05-07 00:00", "updateRecordEntityList": [{ "moduleName": "平台111111", "updateContent": "平台版本第一次更新1", "createTime": "2019-08-15 15:07" }] }, { "code": "开发修改1111", "createTimes": "2019-05-07 00:00", "updateRecordEntityList": [{ "moduleName": "111", "updateContent": "111", "createTime": "2019-08-16 11:16" }] }, { "code": "开发修改1111", "createTimes": "2019-05-07 00:00", "updateRecordEntityList": [{ "moduleName": "515", "updateContent": "5155", "createTime": "2019-08-21 17:29" }] }, { "code": "开发修改1111", "createTimes": "2019-05-07 00:00", "updateRecordEntityList": [{ "moduleName": "2222", "updateContent": "第二次更新", "createTime": "2019-08-22 14:23" }] }] } |
mybatis关联查询（对象嵌套对象）
Mybatis 查询对象中嵌套其他对象的解决方法有两种，
一种是用关联另一个resultMap的形式
如下：
|
1 |
< association property = "office" javaType = "Office" resultMap = "officeMap" /> |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
< 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 > |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
<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> |
|
1 2 3 4 5 6 7 8 9 10 11 |
< 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]的问题，不建议使用
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
< 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 > |
|
1 2 3 |
< 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 > |
以上就是两种对象内嵌套对象查询的实现。仅为个人经验，希望能给大家一个参考，也希望大家多多支持。
原文链接：https://blog.csdn.net/weixin_43839457/article/details/100513385
查看更多关于mybatis主从表关联查询,返回对象带有集合属性解析的详细内容...