mybatis一对多分页查询

今天遇到一个问题，当用户关联角色查询翻页时，如果一个用户有多个角色，会认为是多条记录，页面加载的时候就会发现记录数不对。

为了解决这个问题，我打算分两次查询，第一次只按分页查询出当前页应该展示的用户id列表，然后根据这个idlist去关联查询用户和角色的所有要用到的字段

java

public Map<String, Object> selectPage(AccountVo params, int pageSize) {
        
        if (params.getCurrentPage() == null) {
            params.setCurrentPage(1);
        }
        
        if (pageSize <= 0) {
            pageSize = Const.PageSize;
        }
        
        PageHelper.startPage(params.getCurrentPage(), pageSize);
        
        AccountExample example = new AccountExample();
        Criteria criteria = example.createCriteria();
        
        if (null != params.getName() && !"".equals(params.getName())) {
            criteria.andNameLike("%" + params.getName() + "%");
        }
        
        example.setOrderByClause("a.id");
//        example.setDistinct(true);
        
        List<Long> idList = accountMapper.selectIdListByExample(example);
        PageInfo<Long> pageInfo = new PageInfo<Long>(idList);
        
        List<Account> list = accountMapper.selectWithRoleByIdList(idList);
        
        Map<String, Object> map = new HashMap<>();
        map.put("accountList", list);
        map.put("pageInfo", pageInfo);
        return map;
    }

 

/**
     * selectIdListByExample
     * selectWithRoleByIdList
     */
    List<Long> selectIdListByExample(AccountExample example);
    List<Account> selectWithRoleByIdList(List<Long> idList);

 

 resultMap

<resultMap id="WithRoleResultMap" type="com.cetcht.entity.account.Account">
    <id column="id" jdbcType="BIGINT" property="id" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="password" jdbcType="VARCHAR" property="password" />
    
    <collection property="roleIdList" ofType="java.lang.Long">
        <result column="role_id" jdbcType="BIGINT" />
    </collection>
    
    <collection property="roleList" ofType="com.cetcht.entity.account.Role">
        <id column="role_id" jdbcType="BIGINT" property="id" />
        <result column="role_name" jdbcType="VARCHAR" property="name" />
    </collection>
  </resultMap>
  
  <resultMap id="IdListResultMap" type="java.lang.Long">
    <result column="id" jdbcType="BIGINT" />
  </resultMap>

 

 

select

<select id="selectIdListByExample" parameterType="com.cetcht.entity.account.AccountExample" resultMap="IdListResultMap">
    select
    <if test="distinct">
      distinct
    </if>
    a.id
    from b_account a
    <if test="_parameter != null">
      <include refid="Example_Where_Clause_a" />
    </if>
    <if test="orderByClause != null">
      order by ${orderByClause}
    </if>
    <if test="limit != null">
      <if test="offset != null">
        limit ${offset}, ${limit}
      </if>
      <if test="offset == null">
        limit ${limit}
      </if>
    </if>
  </select>
  
  <select id="selectWithRoleByIdList" parameterType="java.util.ArrayList" resultMap="WithRoleResultMap">
    select
    <include refid="With_Role_Column_List" />
    from b_account a
    left join m_account_role m on a.id = m.account_id
    left join b_role r on m.role_id = r.id
    <where>    
        a.id in (    
          <foreach collection="idList" item="id" index="index" separator=",">   
             #{id}   
          </foreach>    
        )    
    </where>
    order by a.id
  </select>

 

前端

account2page: function() {
      var that = this;

      Account.list(this.search).then(({ data }) => {
        // console.log(data);
        var pageInfo = data.pageInfo;
        var accountList = data.accountList;

        that.accountList = accountList;

        that.pageSize = pageInfo.pageSize;
        that.search.currentPage = pageInfo.pageNum;
        that.rows = pageInfo.pages * pageInfo.pageSize;
      });
    },