位置：首页>> 软件编程>> java编程>> mybatis主从表关联查询,返回对象带有集合属性解析

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

作者：瘦_猴　　发布时间：2023-06-15 16:10:59　

标签：mybatis,关联查询,返回对象,集合

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

昨天有同事让我帮着看一个问题，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> 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写法，这个是关键

<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查询语句

<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返回的数据

页面调取接口

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

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

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

数据显示正常：

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

如下：

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

来源：https://blog.csdn.net/weixin_43839457/article/details/100513385

投稿