react-smooth-dnd 拖拽实例（react-smooth-dnd 列表排序）
411
2022-08-22
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
}
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小时内删除侵权内容。
相关文章
发表评论
暂时没有评论，来抢沙发吧~