第一种 一次性查询出结果然后封装(该方法不能在主表sql语句分页)
- 直接用collection标签映射,一次性查询所有记录,
- 其中tags、roles、files、对应实体类中的成员
- 查询结果是多条记录,然后mybatis根据主表ID封装
注意:这里不能采用分页查询。因为执行sql查询出的所有记录是未封装的记录。而我们想要的是封装后的分页查询
<resultMap type="KlbDocument" id="KlbDocumentResultById">
<id property="documentId" column="document_id" />
<result property="docName" column="doc_Name" />
<result property="categoryId" column="category_id" />
<result property="validity" column="validity" />
<result property="hot" column="hot" />
<result property="comment" column="comment" />
<result property="delFlag" column="del_flag" />
<result property="createTime" column="create_time" />
<result property="updateTime" column="update_time" />
<result property="createBy" column="create_by" />
<result property="updateBy" column="update_by" />
<collection property="tags" ofType="KlbDocTag" >
<id property="id" column="tag_id" />
<result property="tagName" column="tag_name" />
</collection>
<collection property="roles" ofType="KlbDocRole" >
<id property="id" column="role_id" />
<result property="roleName" column="role_name" />
</collection>
<collection property="files" ofType="KlbFile" >
<id property="fileId" column="file_id" />
<result property="fileName" column="file_name" />
<result property="url" column="url" />
</collection>
</resultMap>
<select id="selectDocumentById" parameterType="KlbDocument" resultMap="KlbDocumentResultById">
SELECT d.document_id,d.doc_name,c.category_id,c.cate_name,d.create_time,d.create_by,d.hot,d.validity,dt.id as tag_id,tag.tag_name,dc.id as role_id,r.role_name,file.file_id,file.file_name,file.url
FROM klb_document d
LEFT JOIN klb_doc_tag dt ON dt.document_id = d.document_id
LEFT JOIN klb_tag tag on tag.tag_id = dt.tag_id
LEFT JOIN klb_file file on file.business_id = d.document_id
LEFT JOIN klb_doc_role dc on dc.document_id = d.document_id
LEFT JOIN sys_role r on r.role_id = dc.role_id
LEFT JOIN klb_category c on c.category_id = d.category_id
where d.del_flag = '1' and file.source = 'doc' and d.document_id = #{documentId} and d.status = 1
</select>
第二种 嵌套查询,先查主表,然后根据主表查询结果,查询子表(主表sql可以分页)
- 先用主表查询,查询出记录,然后遍历记录去子表查询。主表查询时可以加分页。
- 其中tags、roles、对应实体类中的成员,
- collection select对应具体某条查询语句,column中前一个值是定义的变量名 后一个值是主表查询出的结果中的字段
- 当主表查询需要和子表联查时需要加GROUP BY对查询结果根据主键分组
<resultMap type="KlbDocument" id="KlbDocumentResult">
<id property="documentId" column="document_id" />
<result property="docName" column="doc_Name" />
<result property="categoryId" column="category_id" />
<result property="validity" column="validity" />
<result property="hot" column="hot" />
<result property="comment" column="comment" />
<result property="delFlag" column="del_flag" />
<result property="createTime" column="create_time" />
<result property="updateTime" column="update_time" />
<result property="createBy" column="create_by" />
<result property="updateBy" column="update_by" />
<collection property="tags" javaType="java.util.ArrayList" ofType="KlbDocTag"
select="com.chinaunicom.system.mapper.base.KlbDocumentMapper.selectTagList" column="{documentId=document_id" />
<collection property="roles" javaType="java.util.ArrayList" ofType="KlbDocRole"
select="com.chinaunicom.system.mapper.base.KlbDocumentMapper.selectRoleList" column="{documentId=document_id" />
</resultMap>
<!--根据主表查询,注意如果主表查询是多表联查,需要用GROUP BY 更具主表主键分组,保证主表查询出的记录是不重复的-->
<select id="selectDocumentList" parameterType="KlbDocument" resultMap="KlbDocumentResult">
SELECT d.document_id,d.doc_name,d.category_id,c.cate_name,d.create_by,d.hot,d.validity,d.create_time
FROM klb_document d
LEFT JOIN klb_doc_tag dt
ON dt.document_id = d.document_id
LEFT JOIN klb_tag tag
ON tag.tag_id = dt.tag_id
LEFT JOIN klb_file file
ON file.business_id = d.document_id
LEFT JOIN klb_doc_role dc
ON dc.document_id = d.document_id
LEFT JOIN sys_role r
ON r.role_id = dc.role_id
LEFT JOIN klb_category c
ON c.category_id = d.category_id
where d.del_flag = '1' and file.source = 'doc' and d.status = 1
<if test="documentId != null and documentId != 0">
AND d.document_id = #{documentId}
</if>
<if test="docName != null and docName != ''">
AND d.doc_name like concat('%', #{docName}, '%')
</if>
<if test="categoryId != null and categoryId != 0">
AND d.category_id = #{categoryId}
</if>
<if test="createBy != null and createBy != ''">
AND d.create_by = #{createBy}
</if>
<if test="selectTagId != null and selectTagId != 0">
AND dt.tag_id = #{selectTagId}
</if>
<if test="fileSuffix != null and fileSuffix != ''">
AND file.file_suffix = #{fileSuffix}
</if>
<if test="hot != null">
AND d.hot = #{hot}
</if>
<if test="roleId != null and roleId.length > 0">
AND r.role_id in
<foreach item="item" collection="roleId" separator="," open="(" close=")" index="">
#{item}
</foreach>
</if>
GROUP BY d.document_id ORDER BY d.create_time
</select>
<!--根据主表传入主键,去子表查询-->
<select id="selectTagList" resultType="KlbDocTag">
SELECT
dt.tag_id,tag.tag_name
FROM
klb_doc_tag dt
LEFT JOIN klb_tag tag ON tag.tag_id = dt.tag_id
WHERE dt.document_id = #{documentId}
</select>
<!--根据主表传入主键,去子表查询-->
<select id="selectRoleList" resultType="KlbDocRole">
SELECT
dc.role_id,
r.role_name
FROM
klb_doc_role dc
LEFT JOIN sys_role r ON r.role_id = dc.role_id
WHERE
dc.document_id = #{documentId}
</select>