mybatis主从表关联查询,返回对象带有集合属性解析

mybatis主从表关联查询,返回对象带有集合属性解析

目录主从表关联查询,返回对象带有集合属性VersionResult为接收返回数据对象UpdateRecordEntity为从表数据mapper.xml写法，这个是关键sql查询语句执行sql返回的数据页面调取接口mybatis关联查询（对象嵌套对象）一种是用关联另一个resultMap的形式一种联合查询(一对一)的实现

主从表关联查询,返回对象带有集合属性

昨天有同事让我帮着看一个问题，mybatis主从表联合查询，返回的对象封装集合属性。我先将出现的问题记录一下，然后再讲处理方法也简单说明一下：

VersionResult为接收返回数据对象

get\set方法我这里就省略了。

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 UpdateRecordEntityList;

}

UpdateRecordEntity为从表数据

同样get\set方法我这里就省略了。

@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写法，这个是关键

sql查询语句

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

执行sql返回的数据

页面调取接口

下面我将接口数据粘贴下来：

{

"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文件的修改：

数据显示正常：

{

"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的形式

如下：

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

SELECT

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}

一种联合查询 (一对一)的实现

但是这种方式有“N+1”的问题，不建议使用

select o.name AS "office.name",o.code AS "office.code" from sys_office o where o.id = #{id}

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