前言

博客：http://blog.healerjean.com

一、`PageHelper` 分页插件

1、`pom`依赖

<!--分页插件-->
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>4.1.6</version>
</dependency>

2、`configuration`配置

package com.hlj.mybatisxml.pagehelper;

import com.github.pagehelper.PageHelper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import java.util.Properties;


@Configuration
public class PageHelperConfiguration {
    private static final Logger log = LoggerFactory.getLogger(PageHelperConfiguration.class);
    @Bean
    public PageHelper pageHelper() {
        log.info("------Register MyBatis PageHelper");
        PageHelper pageHelper = new PageHelper();
        Properties p = new Properties();
        p.setProperty("offsetAsPageNum", "true");
        p.setProperty("rowBoundsWithCount", "true");
        p.setProperty("reasonable", "true");
        //通过设置pageSize=0或者RowBounds.limit = 0就会查询出全部的结果。
        p.setProperty("pageSizeZero", "true");
        pageHelper.setProperties(p);
        return pageHelper;
    }
}

3、分页查询

@RestController
public class PageHelperController {

    @Resource
    private BasesetUserMapper basesetUserMapper;

    @RequestMapping(value = "page")
    public PageInfo<BasesetUser> queryAll(
        @RequestParam(value = "pageNum", required = false, defaultValue="1") Integer pageNum,
        @RequestParam(value = "pageSize", required = false, defaultValue="10") Integer pageSize) {

        //传入第几页和大小
        PageHelper.startPage(pageNum, pageSize);
        List<BasesetUser> list = basesetUserMapper.findMyall();
        PageInfo<BasesetUser> pageInfo = new PageInfo<BasesetUser>(list);
        return  pageInfo;
    }
}

1）分页查询结果

http://localhost:8888/page?pageNum=1&pageSize=2


{
    "pageNum": 1,
    "pageSize": 2,
    "size": 2,
    "orderBy": null,
    "startRow": 1,
    "endRow": 2,
    "total": 6,
    "pages": 3,
    "list": [{
        "id": 1,
        "username": "HealerJean",
        "password": "213456",
        "enable": 1
    }, {
        "id": 2,
        "username": "HealerJean",
        "password": "213456",
        "enable": 1
    }],
    "firstPage": 1,
    "prePage": 0,
    "nextPage": 2,
    "lastPage": 3,
    "isFirstPage": true,
    "isLastPage": false,
    "hasPreviousPage": false,
    "hasNextPage": true,
    "navigatePages": 8,
    "navigatepageNums": [1, 2, 3]
}

二、`Mybatis` 分页查询1

我在多点的时候使用的下面的分页方式

1、`PageQuery`

@Data
public class PageQuery {

    private Integer pageNum = 0;
    private Integer pageSize = 15;

    public Integer getPageSize() {
        return pageSize == null ? 15 : pageSize;
    }

    public Integer getPageNum() {
        return pageNum == null || pageNum == 0 ? 0 : pageNum - 1;
    }
}

2、`Controller`

@PostMapping("getInviteList")
public ResponseBean getInviteList(InviteListQuery listQuery, PageQuery pageQuery){
    InviteQuery inviteQuery = new InviteQuery();
    Page<UserInfo> invitees = userInfoService.getInvitees(inviteQuery, pageQuery);
    return invitees;
}

3、`Service`

@Override
public Page<UserInfo> getInvitees(InviteQuery inviteQuery, PageQuery pageQuery) {
    Pageable pageable = new PageRequest(pageQuery.getPageNum(), pageQuery.getPageSize());
    inviteQuery.setOffset(pageable.getOffset());
    inviteQuery.setLimit(pageQuery.getPageSize());
    List<UserInfo> list = userInfoMapper.getInvitee(inviteQuery);
    Long count = userInfoMapper.getInviteeCount(inviteQuery);
    return new PageImpl<>(list, pageable, count);
}

4、`Mapper`

1）`Mapper.java`

package com.duodian.youhui.dao.mybatis.user;

import com.duodian.youhui.data.invite.InviteQuery;
import com.duodian.youhui.data.user.UserInfoToAdminData;
import com.duodian.youhui.entity.db.user.UserInfo;
import org.apache.ibatis.annotations.Param;
import org.springframework.data.repository.CrudRepository;

import java.util.List;
import java.util.Map;

/**
 * @Desc:
 * @Author HealerJean
 * @Date 2018/9/19  下午6:24.
 */
public interface UserInfoMapper  {



    List<UserInfo> getInvitee(InviteQuery inviteQuery);

    Long getInviteeCount(InviteQuery query);
}

2）`Mapper.xml`

<select id="getInviteeCount" resultType="java.lang.Long">
    select count(*) from user_info a
    <where>
        <if test="pid != null">
            and a.pId=#{pid}
        </if>
        <if test="keyword != null and keyword != ''">
            and(
            <if test="@org.apache.commons.lang3.StringUtils@isNumeric(keyword)">
                a.id=#{keyword} or
            </if>
            a.nickName like concat('%', #{keyword}, '%')
            )
        </if>
    </where>
</select>

<select id="getInvitee" resultType="com.duodian.youhui.entity.db.user.UserInfo">
    select <include refid="BaseColumnList"/> from user_info a
    <where>
        <if test="pid != null">
            and a.pId=#{pid}
        </if>
        <if test="keyword != null and keyword != ''">
            and(
            <if test="@org.apache.commons.lang3.StringUtils@isNumeric(keyword)">
                a.id=#{keyword} or
            </if>
            a.nickName like concat('%', #{keyword}, '%')
            )
        </if>
    </where>
    order by a.cdate desc
</select>

三、`Mybatis` 分页查询2

小米的时候

1、数据库查询`PagingQuery`

package com.fintech.scf.common.query;

import java.io.Serializable;

public abstract class PagingQuery implements Serializable{
	
	private static final long serialVersionUID = 1L;
	
	private int pageNo;
	private int pageSize;
	
	/**
	 * 数据记录总数
	 */
	private int itemCount;
	private int startRow;
	private int endRow;
	
	public PagingQuery(int pageNo, int pageSize){
		this.pageNo=pageNo;
		this.pageSize=pageSize;
	}
	
	public int getPageNo() {
		return pageNo;
	}
	
	public void setPageNo(int pageNo) {
		this.pageNo = pageNo;
	}
	
	public int getPageSize() {
		return pageSize;
	}
	
	public void setPageSize(int pageSize) {
		this.pageSize = pageSize;
	}
	
    public int getItemCount() {
		return itemCount;
	}
    
    public void setItemCount(int itemCount){
    	this.itemCount = itemCount;
		/*替换oracle分页为mysql  2017-11-14 15:19:13  by shuai.hao
		startRow = this.getStartIndex()< 1 ? 0 : this.getStartIndex() - 1;
		endRow = this.getEndIndex();*/
    	startRow = (pageNo - 1) * pageSize;
    	endRow = pageSize;
    }

	public int getStartRow() {
		return startRow;
	}

	public int getEndRow() {
		return endRow;
	}

	private int getStartIndex() {
        return (pageNo - 1) * pageSize + 1;
    }
    
	private int getEndIndex() {
        int end = pageNo * pageSize;
        if (end > itemCount) {
            end = itemCount;
        }
        return end;
    }
}

2、自定义`Query`

package com.fintech.scf.data.pojo.system;

import com.fintech.scf.common.query.PagingQuery;
import lombok.Data;


@Data
public class ScfSysDictionaryTypeQuery extends PagingQuery {
	private static final long serialVersionUID = 1L;

	public ScfSysDictionaryTypeQuery(){
		super(1, 10);
	}

	public ScfSysDictionaryTypeQuery(int pageNo, int pageSize){
		super(pageNo, pageSize);
	}

	/** 字典类型 键 */
	private String typeKey;
	/** 字典类型 描述 */
	private String typeDesc;
	/** 状态 */
	private String status;
	/** 创建人 */
	private Long createUser;
	/** 创建人名称 */
	private String createName;
	/** 更新人 */
	private Long updateUser;
	/** 更新人名称 */
	private String updateName;

}

3、分页查询包装对象

1）分页结果

package com.fintech.scf.common.paging;

import java.io.Serializable;

/**
 * 辅助分页查询结果
 * @author YuYue
 *
 */
public class Pagenation implements Serializable{

   private static final long serialVersionUID = -4653953957682657183L;

   public Pagenation(int pageNo,int pageSize, int itemCount) {
      this.pageNo = pageNo;
      this.pageSize = pageSize;
      this.itemCount = itemCount;
      this.pageCount = (int) Math.ceil(((double) itemCount / (double) pageSize));
    }
    
   /**
     * @return 每页条目数量
     */
   private int pageSize;
   
   /**
     * @return 当前页码
     */
   private int pageNo;
   
   /**
     * @return 条目总数
     */
   private int itemCount;
   
   /**
    * @return 总页数
    */
   private int pageCount;
   
   public int getPageCount() {
      return pageCount;
   }

   public void setPageCount(int pageCount) {
      this.pageCount = pageCount;
   }

   public int getPageSize() {
      return pageSize;
   }

   public void setPageSize(int pageSize) {
      this.pageSize = pageSize;
   }

   public int getPageNo() {
      return pageNo;
   }

   public void setPageNo(int pageNo) {
      this.pageNo = pageNo;
   }

   public int getItemCount() {
      return itemCount;
   }

   public void setItemCount(int itemCount) {
      this.itemCount = itemCount;
   }
}

2）`ListResult`

package com.fintech.scf.common.result;

import com.fintech.scf.data.pojo.flow.ScfFlowAuditRecordTemp;

import java.util.List;


public class ListResult<T> {

   private static final long serialVersionUID = 1641284062873197036L;

   public ListResult() {}

   public static <E> ListResult<E> newListResult() {
      return new ListResult<E>();
   }

   /**
    * 集合值结果
    */
   private List<T> values;

   public List<T> getValues() {
      return values;
   }

   public ListResult<T> setValues(List<T> values) {
      this.values = values;
      return this;
   }
}

3）`PageListResult`

package com.fintech.scf.common.result;


import com.fintech.scf.common.paging.Pagenation;


public class PageListResult<T> extends ListResult<T> {

    private static final long serialVersionUID = -5229900490913912833L;

    public static <E> PageListResult<E> newPageListResult() {
        return new PageListResult<E>();
    }

    /**
	 * @return 辅助分页查询结果
	 */
    private Pagenation pagenation;

    public Pagenation getPagenation() {
        return pagenation;
    }

    public PageListResult<T> setPagenation(Pagenation pagenation) {
        this.pagenation = pagenation;
        return this;
    }
}

4）`ScfSysDictionaryTypePage`

public class ScfSysDictionaryTypePage  extends PageListResult<ScfSysDictionaryType> {

	private static final long serialVersionUID = 1L;
	
}

4、`Service` 分页查询

1）`Service`

public PageDTO<DictionaryTypeDTO> queryDictTypesPageLikes(DictionaryTypeDTO type) {
    List<DictionaryTypeDTO> collect = null;
    ScfSysDictionaryTypeQuery query = new ScfSysDictionaryTypeQuery();
    query.setTypeKey(type.getTypeKey());
    query.setStatus(SystemEnum.StatusEnum.EFFECT.getCode());
    query.setPageNo(type.getPageNo());
    query.setPageSize(type.getPageSize());
    
    
    ScfSysDictionaryTypePage page = scfSysDictionaryTypeManager.queryDictTypePageLikes(query);
    List<ScfSysDictionaryType> types = page.getValues();
    if (types != null) {
        collect = types.stream().map(item -> BeanUtils.dictionaryTypeToDTO(item)).collect(toList());
    }
    return BeanUtils.toPageDTO(page, collect);
}

2）`Manager`

public ScfSysDictionaryTypePage queryDictTypePageLikes(ScfSysDictionaryTypeQuery query) {
    ScfSysDictionaryTypePage typePage = new ScfSysDictionaryTypePage();
    Integer itemCount = scfSysDictionaryTypeDao.countDictTypeLikes(query);
    query.setItemCount(itemCount);

    if (itemCount == 0) {
        typePage.setValues(null);
    } else {
        typePage.setValues(scfSysDictionaryTypeDao.queryDictTypePageLikes(query));
    }
    typePage.setPagenation(new Pagenation(query.getPageNo(), query.getPageSize(), query.getItemCount()));
    return typePage;
}

3）`Mapper`

a、`Mapper.java`

@Repository("scfSysDictionaryTypeDao")
public class ScfSysDictionaryTypeDao extends BaseDao {

	public Integer countDictTypeLikes(ScfSysDictionaryTypeQuery query){
		return super.getSqlSession().selectOne("ScfSysDictionaryTypeMapper.countDictTypeLikes", query);
	}
	public List<ScfSysDictionaryType> queryDictTypePageLikes(ScfSysDictionaryTypeQuery query){
		return super.getSqlSession().selectList("ScfSysDictionaryTypeMapper.queryDictTypePageLikes", query);
	}
	
}

b、`Mapper.xml`

<select id="countDictTypeLikes" parameterType="ScfSysDictionaryTypeQuery" resultType="java.lang.Integer">
    select count(*) from scf_sys_dictionary_type
    <include refid="Example_Where_Like_Clause" />
</select>

<select id="queryDictTypePageLikes" parameterType="ScfSysDictionaryTypeQuery" resultMap="BaseResultMap">
    select
    <trim suffixOverrides=",">
        <include refid="Base_Column_List" />
    </trim>
    from scf_sys_dictionary_type
    <include refid="Example_Where_Like_Clause" />
    <![CDATA[ limit #{startRow},#{endRow} ]]>
</select>

<sql id="Example_Where_Like_Clause">
    <where>
        <trim prefix="(" prefixOverrides="and" suffix=")">
            <if test="typeKey != null">
                and type_key    LIKE CONCAT('%', #{typeKey,jdbcType=VARCHAR},'%')
            </if>
            <if test="status != null">
                and status = #{status,jdbcType=VARCHAR}
            </if>
        </trim>
    </where>
</sql>

5、`BeanUtils`封装

public class BeanUtils {

    public static <T> PageDTO<T> toPageDTO(PageListResult pageView, List<T> datas) {
        if (pageView == null && (datas == null || datas.isEmpty())) {
            return null;
        } else if (pageView == null) {
            return new PageDTO<>(datas);
        } else if (pageView.getPagenation() == null) {
            return new PageDTO<>(datas);
        } else {
            Pagenation pagenation = pageView.getPagenation();
            return new PageDTO(pagenation.getPageNo(), pagenation.getPageSize(), pagenation.getItemCount(),
                               pagenation.getPageCount(), datas);
        }
    }

}

四、`MybatisPlus` 分页查询

1、`@Configuration`

@Configuration
@MapperScan("com.healerjean.proj.data.mapper")
public class MybatisPlusConfig {

    @Bean
    public PaginationInterceptor paginationInterceptor() {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
        // 设置请求的页面大于最大页后操作， true调回到首页，false 继续请求  默认false
        // paginationInterceptor.setOverflow(false);
        // 设置最大单页限制数量，默认 500 条，-1 不受限制
        // paginationInterceptor.setLimit(500);
        // 开启 count 的 join 优化,只针对部分 left join
        paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));
        return paginationInterceptor;
    }

}

2、分页

1）简单分页

/** 传入QueryWrapper分页 */
@Test
public void page1 (){    
    Page<User> page = new Page<>(1, 2);
    Wrapper<User> userWrapper = new QueryWrapper<User>().lambda();
    IPage<User> userIPage = userMapper.selectPage(page, userWrapper);
    System.out.println(userIPage);
}

/** 传入普通参数分页 */
@Test
public void page2(){
    Page<UserDTO> page = new Page<>(1, 2);
    String name = "name";
    IPage<UserDTO> users = userMapper.selectMapperXmlPage(page, name);
    System.out.println(users);
}

a、`Mapper`

public interface UserMapper extends BaseMapper<User> {

    IPage<UserDTO> selectMapperXmlPage(Page<UserDTO> page , String name);

}

<select id="selectMapperXmlPage" resultType="com.healerjean.proj.dto.UserDTO">
    select * from user where name = #{name}
</select>

2）复杂分页

a、`Service`

@Test
public void page3() {
    Page page = new Page<>(1, 2);
    QueryWrapper wrapper = Wrappers.<User>query().eq("name", "name");
    IPage<UserDTO> users = userMapper.selectMapperXmlFZPage(page, wrapper);
    System.out.println(users);
}

b、`Mapper`

public interface UserMapper extends BaseMapper<User> {

    IPage<UserDTO> selectMapperXmlFZPage(Page page,   @Param(Constants.WRAPPER) QueryWrapper<UserDTO> queryWrapper);
}

<select id="selectMapperXmlFZPage" resultType="com.healerjean.proj.dto.UserDTO">
    select * from user  ${ew.customSqlSegment}
</select>

3）传入实体对象

a、`Service`

@Test
public void page4() {
    Page page = new Page<>(1, 2);
    UserDTO userDTO = new UserDTO();
    userDTO.setName("name");
    //分页查询
    IPage<UserDTO>  users = userMapper.selectMapperXmlEntity(page, userDTO);
    System.out.println(users);

    //list查询
    List<UserDTO>  user2s = userMapper.selectMapperXmlEntity( userDTO);
    System.out.println(user2s);
}

b、`Mapper`

public interface UserMapper extends BaseMapper<User> {

    IPage<UserDTO> selectMapperXmlEntity(Page page,   @Param("userDTO") UserDTO userDTO);
    List<UserDTO> selectMapperXmlEntity( @Param("userDTO") UserDTO userDTO);
}

<select id="selectMapperXmlEntity" resultType="com.healerjean.proj.dto.UserDTO">
    select * from user where  name = #{userDTO.name}
</select>

3、`BeanUtils`封装

public static <T> PageDTO<T> toPageDTO(IPage iPage, List<T> datas) {
    if (datas == null || datas.isEmpty() || iPage == null) {
        return null;
    } else {
        return new PageDTO(iPage.getCurrent(), iPage.getSize(), iPage.getTotal(), iPage.getPages(), datas);
    }
}

五、分页问题

1、不查询 `count` 分页

1）`PageHelper` 不查询

PageHelper.startPage(req.getPageNo(), req.getPageSize(), false);


// 以下返回 -1
 pageResult.setTotalPage(pageInfo.getPages());
 pageResult.setTotal(pageInfo.getTotal());

2）`MybatsPlus`

Page<MerchantPolicySkuWhite> page = new Page<>(pageQuery.getPageNo(), pageQuery.getPageSize());
            //返回结果
IPage<MerchantPolicySkuWhite> pageResult = merchantPolicySkuWhiteDao.page(page, queryWrapper);

// 返回的Page<T>对象中的total字段将不会包含实际的记录总数，而是可能包含特定的值（如0、-1或null，具体取决于你的实现和MyBatis-Plus的版本
// --  mybatis-plus-extension-3.3.1.jar 版本返回 0

ContactAuthor

一、PageHelper 分页插件

1、pom依赖

2、configuration配置

3、分页查询

1）分页查询结果

二、Mybatis 分页查询1

1、PageQuery

2、Controller

3、Service

4、Mapper

1）Mapper.java

2）Mapper.xml

三、Mybatis 分页查询2

1、数据库查询PagingQuery

2、自定义Query

3、分页查询包装对象

1）分页结果

2）ListResult

3）PageListResult

4）ScfSysDictionaryTypePage

4、Service 分页查询

1）Service

2）Manager

3）Mapper

a、Mapper.java

b、Mapper.xml

5、BeanUtils封装

四、MybatisPlus 分页查询

1、@Configuration

2、分页

1）简单分页

a、Mapper

2）复杂分页

a、Service

b、Mapper

3）传入实体对象

a、Service

b、Mapper

3、BeanUtils封装

五、分页问题

1、不查询 count 分页

1）PageHelper 不查询

2）MybatsPlus

一、`PageHelper` 分页插件

1、`pom`依赖

2、`configuration`配置

二、`Mybatis` 分页查询1

1、`PageQuery`

2、`Controller`

3、`Service`

4、`Mapper`

1）`Mapper.java`

2）`Mapper.xml`

三、`Mybatis` 分页查询2

1、数据库查询`PagingQuery`

2、自定义`Query`

2）`ListResult`

3）`PageListResult`

4）`ScfSysDictionaryTypePage`

4、`Service` 分页查询

1）`Service`

2）`Manager`

3）`Mapper`

a、`Mapper.java`

b、`Mapper.xml`

5、`BeanUtils`封装

四、`MybatisPlus` 分页查询

1、`@Configuration`

a、`Mapper`

a、`Service`

b、`Mapper`

a、`Service`

b、`Mapper`

3、`BeanUtils`封装

1、不查询 `count` 分页

1）`PageHelper` 不查询

2）`MybatsPlus`