mybatis实现一对多的两种方法（适用于分页查询）

mybatis 实现一对多的查询方式

实体类代码

package com.sgcc.demo.model.bo;
/**  
 * @author liyalong
 * @description //TODO 
 * @date 13:44 2019/12/2
 **/
import com.sgcc.demo.model.po.DemoFileInfo;
import com.sgcc.demo.model.po.DemoFileManagement;
import lombok.Data;

import java.util.List;
@Data
public class DemoFileManagementBo extends DemoFileManagement {
    // 文件list
    private List<DemoFileInfo> fileInfos;
}

第一种 （适合单条查询一对多）

XML:

 <resultMap id="BaseResultMap" type="com.sgcc.demo.model.po.DemoFileManagement">
        <id column="GUID" jdbcType="VARCHAR" property="guid"/>
        <result column="NAME" jdbcType="VARCHAR" property="name"/>
        <result column="VERSION" jdbcType="VARCHAR" property="version"/>
        <result column="REMARK" jdbcType="VARCHAR" property="remark"/>
        <result column="UPDATE_TIME" jdbcType="TIMESTAMP" property="updateTime"/>
        <result column="DELETE_FLG" jdbcType="CHAR" property="deleteFlg"/>
    </resultMap>
    <resultMap id="fileMap" type="com.sgcc.demo.model.po.DemoFileInfo">
        <id column="GUID" jdbcType="VARCHAR" property="guid"/>
        <result column="INFO_ID" jdbcType="VARCHAR" property="infoId"/>
        <result column="FILE_NAME" jdbcType="VARCHAR" property="fileName"/>
        <result column="FILE_ADDRESS" jdbcType="VARCHAR" property="fileAddress"/>
        <result column="CREATE_TIME" jdbcType="TIMESTAMP" property="createTime"/>
    </resultMap>
    <select id="selectById" resultMap="productsListMap" parameterType="string">
        select
        a.GUID,
        a.VERSION,
        a.REMARK,
        a.UPDATE_TIME,
        a.NAME,
        b.GUID as file_GUID,
        b.FILE_NAME,
        b.FILE_ADDRESS,
        b.CREATE_TIME
        from demo_file_management a
        left join demo_file_info b on a.GUID = b.INFO_ID
        <where>
            a.DELETE_FLG = '0'
            <if test="id != null and id != ''">
                and a.GUID = #{id}
            </if>
        </where>
    </select>

返回参数

这种方式仅适合于 查询一条信息，或者多条信息不使用PageQuery进行分页的情况下。（效率比较快，然是局限性比较强）

第二种方式（适用于分页查询）

 	<resultMap id="productsListMap" type="com.sgcc.demo.model.bo.DemoFileManagementBo" extends="BaseResultMap">
        <collection property="fileInfos"  ofType="com.sgcc.demo.model.po.DemoFileInfo">
            <id column="file_GUID" jdbcType="VARCHAR" property="guid"/>
            <result column="INFO_ID" jdbcType="VARCHAR" property="infoId"/>
            <result column="FILE_NAME" jdbcType="VARCHAR" property="fileName"/>
            <result column="FILE_ADDRESS" jdbcType="VARCHAR" property="fileAddress"/>
            <result column="CREATE_TIME" jdbcType="TIMESTAMP" property="createTime"/>
        </collection>
    </resultMap>

    <resultMap id="productsListMap1" type="com.sgcc.demo.model.bo.DemoFileManagementBo">
        <id column="GUID" jdbcType="VARCHAR" property="guid"/>
        <result column="NAME" jdbcType="VARCHAR" property="name"/>
        <result column="VERSION" jdbcType="VARCHAR" property="version"/>
        <result column="REMARK" jdbcType="VARCHAR" property="remark"/>
        <result column="UPDATE_TIME" jdbcType="TIMESTAMP" property="updateTime"/>
        <result column="DELETE_FLG" jdbcType="CHAR" property="deleteFlg"/>
        <collection property="fileInfos" ofType="com.sgcc.demo.model.po.DemoFileInfo" select="selectFileList" column="GUID"/>
    </resultMap>


    <!--主查询-->
    <select id="select" resultMap="productsListMap1" parameterType="com.sgcc.demo.model.po.DemoFileManagement">
        select
        a.GUID,
        a.VERSION,
        a.REMARK,
        a.UPDATE_TIME,
        a.NAME
        from demo_file_management a
        <where>
            a.DELETE_FLG = '0'
            <if test="name != null and name != ''">
                and a.NAME LIKE concat("%",#{name},"%")
            </if>
        </where>
        Order by  a.UPDATE_TIME desc
    </select>
    <!--  子查询  -->
    <select id="selectFileList" resultType="com.sgcc.demo.model.po.DemoFileInfo">
        select
            b.GUID as guid,
            b.FILE_NAME as fileName,
            b.FILE_ADDRESS as fileAddress,
            b.CREATE_TIME as createTime
        from
            demo_file_info b
        where
            b.INFO_ID = #{guid}

    </select>

采用两套查询，主查询和子查询进行配合（缺点就是，效率不如第一种，但是可以分页使用，不限制连表方式）。

PS：对于自己封装的分页方法的，比如sql直接写limit的，建议第一种。毕竟效率比较高，不过此时需要先分页，然后再链表的嵌套查询。