今天遇到一个问题,当用户关联角色查询翻页时,如果一个用户有多个角色,会认为是多条记录,页面加载的时候就会发现记录数不对。
为了解决这个问题,我打算分两次查询,第一次只按分页查询出当前页应该展示的用户id列表,然后根据这个idlist去关联查询用户和角色的所有要用到的字段
javapublic Map<String, Object> selectPage(AccountVo params, intpageSize) { if (params.getCurrentPage() == null) { params.setCurrentPage(1); } if (pageSize <= 0) { pageSize =Const.PageSize; } PageHelper.startPage(params.getCurrentPage(), pageSize); AccountExample example = newAccountExample(); 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); returnmap; }
/*** selectIdListByExample * selectWithRoleByIdList */List<Long>selectIdListByExample(AccountExample example); List<Account> selectWithRoleByIdList(List<Long> idList);
<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 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; }); },