主从表关联查询,返回对象带有集合属性
昨天有同事让我帮着看一个问题，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
