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>
以上就是兩種對象內嵌套對象查詢的實現。僅為個人經驗,希望能給大傢一個參考,也希望大傢多多支持WalkonNet。
推薦閱讀:
- Mybatis一對多查詢列表屬性處理示例詳解
- Mybatis中resultMap的Colum和property屬性詳解
- 關於Mybatis使用collection分頁問題
- 解決mybatis一對多查詢resultMap隻返回瞭一條記錄問題
- Mybatis中resultMap的使用總結