既要关联多张数据表,又要分页怎么办呢?

可以先根据一张数据表先进行分页,再从分页工具类中取出列表数据 list 。之后再用 DTO 进行组装即可。

下面用一个 demo 进行演示:


Service 层:

getPageResult() 方法得到分页信息:
public PageResult getPageResult(int page) {
    Map<String,Object> params = new HashMap<>();
    params.put("page",page);
    params.put("limit",2);
    PageQueryUtil pageUtil = new PageQueryUtil(params);

    List<Question> questionList = questionMapper.selectQuestionList(pageUtil);
    int total = questionMapper.getTotalQuestion();
    return new PageResult(questionList,total,pageUtil.getLimit(),pageUtil.getPage());
}

返回一个分页工具类的实例,得到了 列表数据,总记录数,每页记录数和当前页数。


getQuestionDTOList() 方法得到关联多张表后的组装数据:
public List<QuestionDTO> getQuestionDTOList(int page){
    PageResult result = getPageResult(page);

    List<QuestionDTO> questionDTOList = new ArrayList<>();
    List<Question> questions = (List<Question>) result.getList();
    for (Question question : questions) {
        User user = userMapper.selectByPrimaryKey(question.getCreator());
        QuestionDTO questionDTO = new QuestionDTO();
        questionDTO.setQuestion(question);
        questionDTO.setUser(user);
        questionDTOList.add(questionDTO);
    }
    return questionDTOList;
}

List<Question> questions = (List<Question>) result.getList(); 从分页工具类中取出列表数据 list ,list 里是 question 表的数据。之后就是通过 DTO 将 user 和 question 表组装到一起返回一个装载 questionDTO 数据类型的 list 。


Controller 层:

@RequestMapping("/index")
public String login(@RequestParam(name = "pageNum",defaultValue = "1") int pageNum, Model model){

    List<QuestionDTO> questionDTOList = questionService.getQuestionDTOList(pageNum);
    PageResult result = questionService.getPageResult(pageNum);
    model.addAttribute("list",questionDTOList);
    model.addAttribute("result",result);
    return "index";
}

要取数据到 getQuestionDTOList() 方法中取,而要取分页信息到 getPageResult() 方法中取。


DAO 层:

QuestionMapper.java:
public interface QuestionMapper {
	List<Question> selectQuestionList(PageQueryUtil pageQueryUtil);

    int getTotalQuestion();
}

QuestionMapper.xml:
<select id="selectQuestionList" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List"/>
    from question
    order by id desc
    <if test="start!=null and limit!=null">
        limit #{start},#{limit}
    </if>
</select>

<select id="getTotalQuestion" resultType="int">
    select count(1) from question
</select>

index.html:

数据展示:
<div class="media" th:each="list : ${list}">
    <div class="media-left">
        <a href="#">
            <img class="media-object img-rounded" th:src="${list.user.avatarUrl}" alt="...">
        </a>
    </div>
    <div class="media-body">
        <h4 class="media-heading">
            <a th:href="@{/question/{questionId}(questionId=${list.question.id})}" th:text="${list.question.title}">Media heading</a>
        </h4>
        <span th:text="${list.question.description}"></span><br/>
        <span class="text-desc" th:text="${list.question.commentCount}"></span>个回复 •
        <span class="text-desc" th:text="${list.question.viewCount}"></span>次浏览 •
        <span th:text="${#dates.format(list.question.gmtCreate,'yyyy-MM-dd HH:mm')}"></span>
    </div>
</div>

分页展示:
<nav>
<ul class="pagination pagination-lg" >
    <li th:class="${result.currPage == 1}?'disabled' : ''"><a th:href="@{'/index?pageNum=' + ${result.currPage -1}}">上一页</a></li>
    <li th:if="${result.currPage -3 >=1}" ><a th:href="@{'/index?pageNum=' + ${result.currPage -3}}" th:text="${result.currPage -3}" >1</a></li>
    <li th:if="${result.currPage -2 >=1}" ><a th:href="@{'/index?pageNum=' + ${result.currPage -2}}" th:text="${result.currPage -2}" >1</a></li>
    <li th:if="${result.currPage -1 >=1}" ><a th:href="@{'/index?pageNum=' + ${result.currPage -1}}" th:text="${result.currPage -1}" >1</a></li>
    <li class="active"><a href="#" th:text="${result.currPage}" >1</a></li>
    <li th:if="${result.currPage+1 <=result.totalPage}" ><a th:href="@{'/index?pageNum=' + ${result.currPage +1}}" th:text="${result.currPage +1}" >1</a></li>
    <li th:if="${result.currPage+2 <=result.totalPage}" ><a th:href="@{'/index?pageNum=' + ${result.currPage +2}}" th:text="${result.currPage +2}" >1</a></li>
    <li th:if="${result.currPage+3 <=result.totalPage}" ><a th:href="@{'/index?pageNum=' + ${result.currPage +3}}" th:text="${result.currPage +3}" >1</a></li>

    <li th:class="${result.currPage==result.totalPage}?'disabled' : ''" ><a th:href="@{'/index?pageNum=' + ${result.currPage +1}}">下一页</a></li>
</ul>
</nav>

因为之前的分页都是没有限定条件的分页,那么如果需要根据限定条件进行分页呢?

假设需要根据 id 进行分页,下面给出 demo 进行演示:


Service 层:

public PageResult getPageResultByUserId(Long userId,int page){
    Map<String,Object> params = new HashMap<>();
    params.put("page",page);
    params.put("limit",2);
    params.put("id",userId);
    PageQueryUtil pageUtil = new PageQueryUtil(params);

    List<Question> questionList = questionMapper.selectQuestionListById(pageUtil);
    int totalQuestionById = questionMapper.getTotalQuestionById(userId);
    return new PageResult(questionList,totalQuestionById,pageUtil.getLimit(),pageUtil.getPage());
}

重点在于往 params 中添加了一个 用户id 字段 params.put("id",userId); ,这样的话分页查询参数类 PageQueryUtil 中就会有用户id。


DAO 层:

QuestionMapper.java:
public interface QuestionMapper {
	List<Question> selectQuestionListById(PageQueryUtil pageQueryUtil);

    int getTotalQuestionById(Long id);
}

QuestionMapper.xml:
<select id="selectQuestionListById" resultMap="BaseResultMap">
  select
  <include refid="Base_Column_List"/>
  from question
  where creator=#{id}
  order by id desc
  <if test="start!=null and limit!=null">
      limit #{start} ,#{limit}
  </if>
</select>

<select id="getTotalQuestionById" resultType="int">
  select count(1) from question
  where creator=#{id,jdbcType=BIGINT}
</select>