IT博客汇
  • 首页
  • 精华
  • 技术
  • 设计
  • 资讯
  • 扯淡
  • 权利声明
    • 登录 注册

    Mybatis实现一对多的查询方式

    John Doe发表于 2023-08-23 02:17:42
    love 0

    mybatis 实现一对多的查询方式

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    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进行分页的情况下。（效率比较快，然是局限性比较强）

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    <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>

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

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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    <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>



沪ICP备19023445号-2号
友情链接