解决MyBatis一对多分页查询不匹配条数的问题

最新推荐文章于 2025-06-20 14:12:20 发布

原创最新推荐文章于 2025-06-20 14:12:20 发布 · 1w 阅读

14 ·

CC 4.0 BY-SA版权

文章标签：

#分页查询

mybatis分页专栏收录该内容

1 篇文章

订阅专栏

本文介绍了一对多关系下MyBatis后台分页的优化方法，通过调整SQL语句解决分页时显示记录数不准确的问题。适用于需要展示主表及从表信息的场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在开发过程中，写mybatis后台分页时（不会后台分页的可以参考我的这篇博客https://blog.youkuaiyun.com/geng2568/article/details/88042834，），要求展示主表信息加从表信息实现分页，并且主表和从表的关系是一对多，这样在写后台分页过程中，每页显示的信息总会少于要求显示的信息，这是因为在一对多左关联查询时，从表中有多少个和主表对应的数据，查询结果中就有几条主表信息和从表对应。总之：

原先的sql是：

<select id="getServerInfoPage" resultMap="BaseResultMap" parameterType="java.util.Map" >
    SELECT
    sm.*,vm.id vmid,vm.v_user_name vusername,vm.password vmpassword,vm.v_big_internet,
    vm.big_mask_code,vm.big_gateway,vm.small_mask_code,
    vm.small_internet,vm.use_person, vm.operate_system,
    vm.application vmapplication,vm.remark vmremark
    FROM server_manager sm
    LEFT JOIN
    virtual_machine_info vm
    ON
    sm.id = vm.server_id
    <where>
      <if test="serverBrand != null and serverBrand != ''" >
        and sm.server_brand like concat('%',#{serverBrand,jdbcType=VARCHAR},'%')
      </if>
      <if test="serverType != null and serverType != ''" >
        and sm.server_type like concat('%',#{serverType,jdbcType=VARCHAR},'%')
      </if>
      <if test="serverPosition != null and serverPosition != ''" >
        and sm.server_position like concat('%',#{serverPosition,jdbcType=VARCHAR},'%')
      </if>
      <if test="userName != null and userName != ''" >
        and sm.user_name = #{userName,jdbcType=VARCHAR}
      </if>
      <if test="password != null and password != ''" >
        and sm.password = #{password,jdbcType=VARCHAR}
      </if>
    </where>
        ORDER BY id asc
        LIMIT #{pageStart},#{pageSize}
    </select>

改正之后的sql：

<select id="getServerInfoPage" resultMap="BaseResultMap" parameterType="java.util.Map" >
    SELECT
    sm.*,vm.id vmid,vm.v_user_name vusername,vm.password vmpassword,vm.v_big_internet,
    vm.big_mask_code,vm.big_gateway,vm.small_mask_code,
    vm.small_internet,vm.use_person, vm.operate_system,
    vm.application vmapplication,vm.remark vmremark
    FROM server_manager sm
    LEFT JOIN
    virtual_machine_info vm
    ON
    sm.id = vm.server_id
    <where>
      sm.id IN (SELECT test.id from (SELECT id FROM server_manager  LIMIT #{pageStart},#{pageSize}) AS test)
      <if test="serverBrand != null and serverBrand != ''" >
        and sm.server_brand like concat('%',#{serverBrand,jdbcType=VARCHAR},'%')
      </if>
      <if test="serverType != null and serverType != ''" >
        and sm.server_type like concat('%',#{serverType,jdbcType=VARCHAR},'%')
      </if>
      <if test="serverPosition != null and serverPosition != ''" >
        and sm.server_position like concat('%',#{serverPosition,jdbcType=VARCHAR},'%')
      </if>
      <if test="userName != null and userName != ''" >
        and sm.user_name = #{userName,jdbcType=VARCHAR}
      </if>
      <if test="password != null and password != ''" >
        and sm.password = #{password,jdbcType=VARCHAR}
      </if>
      <if test="hardpan != null and hardpan !=''" >
        and sm.hardpan = #{hardpan,jdbcType=VARCHAR}
      </if>
      <if test="memory != null and memory != ''" >
        and sm.memory = #{memory,jdbcType=VARCHAR}
      </if>
      <if test="cpu != null and cpu!=''" >
        and sm.cpu = #{cpu,jdbcType=VARCHAR}
      </if>
      <if test="bigInternet != null and bigInternet !=''" >
        and sm.big_internet like concat('%',#{bigInternet,jdbcType=VARCHAR},'%')
      </if>
      <if test="maskCode != null and maskCode !=''" >
        and sm.mask_code like concat('%',#{maskCode,jdbcType=VARCHAR},'%')
      </if>
     </where>
        ORDER BY id asc
     </select>

总之：基本sql需要加强锻炼，以后再遇到这种情况就能够得心应手了，希望可以帮助遇到类似错误的小伙伴，加油！！！！