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

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

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

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

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

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

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