SQL分页查询
前言
Github：https://github.com/HealerJean
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);
}
}
