前言

Github:https://github.com/HealerJean

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

1、PageHelper分页插件

1.1、pom依赖

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

1.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;
    }
}

1.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.3.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]
}

2、Mybatis分页查询1

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

2.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.1、Controller

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

2.2、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);
}

2.3、Mapper

2.3.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.3.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>

3、Mybatis分页查询2

小米的时候

3.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;
    }
}

3.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.4、分页查询包装对象

3.4.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;
   }
}

3.4.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.4.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;
    }
}

3.4.1、ScfSysDictionaryTypePage

public class ScfSysDictionaryTypePage  extends PageListResult<ScfSysDictionaryType> {

	private static final long serialVersionUID = 1L;
	
}

3.3、Service分页查询

3.3.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);
}

3.3.1、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.3.1、Mapper

3.3.1.1、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);
	}
	
}

3.3.1.2、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>

3.4、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);
        }
    }

}

4、MybatisPlus分页查询

4.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;
    }

}

4.2、分页

4.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);
}

4.2.1、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>

4.2.2、复杂分页

2.2.2.1、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);
}

2.2.2.2、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>

4.2.3、传入实体对象

4.2.3.1、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);
}

4.2.2.2、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>

4.2、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);
    }
}

ContactAuthor