mybatis-plus分页模板，可支持原生sql

mybatis-plus分页模板，可支持原生sql

项目需要，把以前分页模板拿出来，改改了，可支持原生的sql进行分页

MybatisPageServiceTemplate分页模板类

package com.key.win.base.page;

import com.baomidou.mybatisplus.core.conditions.AbstractWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.segments.OrderBySegmentList;
import com.baomidou.mybatisplus.core.mapper.baseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.metadata.TableFieldInfo;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import com.baomidou.mybatisplus.core.metadata.TableInfoHelper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.key.win.base.auth.AuthenticationUtil;
import com.key.win.base.mapper.KeyWinMapper;
import com.key.win.base.util.SingleSoldierConstantUtils;
import com.key.win.base.web.CodeEnum;
import com.key.win.base.web.OrderDir;
import com.key.win.base.web.PageRequest;
import com.key.win.base.web.PageResult;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;


public abstract class MybatisPageServiceTemplate {

    private static final Logger logger = LoggerFactory.getLogger(MybatisPageServiceTemplate.class);

    private final baseMapper baseMapper;

    
    public MybatisPageServiceTemplate(baseMapper baseMapper) {
        super();
        this.baseMapper = baseMapper;
    }

    
    public PageResult doPagingQuery(PageRequest pageParam) {

        //设备mybaties查询分页
        logger.info("进行分页参数组装");
        Page page = new Page();
        page.setCurrent(pageParam.getPageNo());
        page.setSize(pageParam.getPageSize());
        //构建Wrapper
        logger.info("分页查询条件构建");
        AbstractWrapper wrapper = this.constructWrapper(pageParam.getT());
        this.constructOrderByCondition(pageParam, wrapper);
        logger.info("执行分页查询");
        IPage pages = selectPage(page, wrapper);
        //设置分页返回参数
        logger.info("执行分页查询结果组装");
        PageResult pageResult = new PageResult();
        pageResult.setCount(pages.getTotal());
        pageResult.setPageNo(pageParam.getPageNo());
        pageResult.setPageSize(pageParam.getPageSize());
        pageResult.setData(page.getRecords());
        pageResult.setCode(CodeEnum.SUCCESS.getCode());
        logger.info("执行分页查询结果组装完成，返回PageResult");
        return pageResult;
    }

    
    protected String constructNativeSql() {
        return null;
    }

    protected boolean isNativeSql() {
        if (StringUtils.isNotBlank(this.constructNativeSql())) {
            return true;
        }
        return false;
    }

    
    private IPage selectPage(Page page, AbstractWrapper wrapper) {
        if (this.isNativeSql()) {
            KeyWinMapper keyWinMapper = (KeyWinMapper) baseMapper;
            QueryWrapper queryWrapper = (QueryWrapper) wrapper;
            return keyWinMapper.selectPageForNativeSql(page, this.constructNativeSql(), queryWrapper);
        } else {
            return baseMapper.selectPage(page, wrapper);
        }
    }

    
    private void constructOrderByCondition(PageRequest pageParam, AbstractWrapper wrapper) {
        TableInfo tableInfo = getTableInfo(pageParam);
        if (tableInfo == null) {
            logger.warn("没有找到数据库表结构，不进行排序 *** 作！");
            return;
        }
        appendSqlOrderByCondition(pageParam, wrapper, tableInfo);
    }

    
    private void appendSqlOrderByCondition(PageRequest pageParam, AbstractWrapper wrapper, TableInfo tableInfo) {
        List orderList = getOrderByCondition(pageParam);
        StringBuilder subSqlOrderBy = new StringBuilder();
        Map propertyToTableFieldInfoMap = tableInfo.getFieldList().stream().collect(Collectors.toMap(TableFieldInfo::getProperty, a -> a, (k1, k2) -> k1));
        for (int i = 0; i < orderList.size(); i++) {
            MybatisOderByVo ob = orderList.get(i);
            String column = propertyToTableFieldInfoMap.get(ob.getSortName()).getColumn();
            if (StringUtils.isNotBlank(column)) {
                subSqlOrderBy.append(SingleSoldierConstantUtils.SQL_SEPARATOR).append(column).append(SingleSoldierConstantUtils.SQL_SEPARATOR).append(ob.getSortDir().name());
            } else {
                logger.warn("{}在propertyToTableFieldInfoMap中找不到映射字段！", ob.getSortName());
            }
            if (i < orderList.size() - 1 && subSqlOrderBy.length() > 0) {
                subSqlOrderBy.append(SingleSoldierConstantUtils.SQL_COMMA_SEPARATOR);
            }
        }
        if (subSqlOrderBy.length() > 0) {
            StringBuilder sqlOrderBy = getSqlOrderBy(wrapper);
            sqlOrderBy.append(subSqlOrderBy);
            wrapper.last(sqlOrderBy.toString());
            logger.info("分页查询排序条件:{}", sqlOrderBy.toString());
        } else {
            logger.warn("最终在propertyToTableFieldInfoMap中找不到一个映射字段，本次分页查询将放弃排序！");
        }

    }

    
    private StringBuilder getSqlOrderBy(AbstractWrapper wrapper) {
        OrderBySegmentList orderBySegmentList = wrapper.getexpression().getOrderBy();
        StringBuilder sqlOrderBy = new StringBuilder();
        if (CollectionUtils.isEmpty(orderBySegmentList)) {
            sqlOrderBy.append(SingleSoldierConstantUtils.ORDER_BY);
            logger.info("执行sql中没有order by条件，将为此sql添加order by条件");
        } else {
            sqlOrderBy.append(SingleSoldierConstantUtils.SQL_COMMA_SEPARATOR);
            logger.info("执行sql中已有order by条件，将直接为此sql添加具体排序语句。");
        }
        return sqlOrderBy;
    }

    
    private List getOrderByCondition(PageRequest pageParam) {
        List orderList = new ArrayList();
        List queryOrder = this.getQueryOrder(pageParam);
        if (queryOrder != null && queryOrder.size() > 0) {
            orderList.addAll(queryOrder);
            logger.info("执行自定义分页排序条件");
        } else {
            if (StringUtils.isNotBlank(pageParam.getSortName())) {
                orderList.add(new MybatisOderByVo(pageParam.getSortName(), pageParam.getSortDir()));
                logger.info("执行用户传入的分页条件{}->{}", pageParam.getSortName(), pageParam.getSortDir());
            } else {
                if (!this.isNativeSql()) {
                    orderList.add(new MybatisOderByVo(SingleSoldierConstantUtils.QUERY_DEFAULT_ORDER_NAME, OrderDir.DESC));
                    logger.info("执行默认分页排序条件");
                } else {
                    logger.info("原生sql,不添加默认分页排序条件");
                }

            }


        }
        return orderList;
    }

    
    private TableInfo getTableInfo(PageRequest pageParam) {
        TableInfo tableInfo = null;
        if (pageParam.getT() != null) {
            tableInfo = TableInfoHelper.getTableInfo(pageParam.getT().getClass());
            logger.info("{}用户查询分页时提交了对应的Model对象:{}", AuthenticationUtil.getUserName(), pageParam.getT().getClass());
        }
        if (tableInfo == null) {
            List tableInfos = TableInfoHelper.getTableInfos();
            if (CollectionUtils.isNotEmpty(tableInfos)) {
                tableInfo = tableInfos.get(0);//只为获取公共字段
                logger.info("tableInfos的大小为{}个，获取第0个TableInfo的名称：{}", tableInfos.size(), tableInfo.getTableName());
            }
        }
        return tableInfo;
    }

    
    abstract protected AbstractWrapper constructWrapper(T t);


    
    protected List getQueryOrder(PageRequest pageParam) {
        return null;
    }


}

MybatisOderByVo 排序VO

package com.key.win.base.page;

import com.key.win.base.web.OrderDir;
import io.swagger.annotations.ApiModel;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;

@ApiModel("Mybatis排序VO")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class MybatisOderByVo implements Serializable {

    private String sortName;
    private OrderDir sortDir;


}

分页输入对象

package com.key.win.base.web;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;


@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class PageRequest implements Serializable {

    
    private static final long serialVersionUID = -7320421323343652634L;
    @ApiModelProperty("当前页")
    private int pageNo = 1;
    @ApiModelProperty("每页条数")
    private int pageSize = 10;
    @ApiModelProperty("排序字段")
    private String sortName = "";
    @ApiModelProperty("排序方向[ ASC-升序, DESC-降序 ]")
    private OrderDir sortDir = OrderDir.DESC;
    @ApiModelProperty("对应实体对象")
    private T t;

    @ApiModelProperty("每页条数")
    public int getPageSize() {
        return (0 == pageSize) ? 10 : pageSize;
    }

    @ApiModelProperty("获取条数")
    public int getFirstResult() {
        return (pageNo - 1) * pageSize;
    }


}

分页输出对象

package com.key.win.base.web;

import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.NoArgsConstructor;

import java.io.Serializable;

@Data
@EqualsAndHashCode(callSuper = false)
@NoArgsConstructor
@AllArgsConstructor
public class baseResult implements Serializable {
    @ApiModelProperty("code")
    protected Integer code;
    @ApiModelProperty("提示信息")
    protected String msg;
}

package com.key.win.base.web;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.ArrayList;
import java.util.List;


@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class PageResult extends baseResult {
    @ApiModelProperty("当前页数")
    @Builder.Default
    private int pageNo = 1;
    @ApiModelProperty("每页条数")
    @Builder.Default
    private int pageSize = 10;
    @ApiModelProperty("总条数")
    @Builder.Default
    private long count = 0;

    //总页数
    //private int					totalPage;

    @ApiModelProperty("分页数据列表")
    private List data = new ArrayList();

    @ApiModelProperty("总页数数")
    public long getTotalPage() {
        return (count + pageSize - 1) / pageSize;
    }


}

如果要原生sql进行分页，对应的mapper需要继承KeyWinMapper

package com.key.win.base.mapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.baseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.ArrayList;

public interface KeyWinMapper extends baseMapper {

    @Select({"${sql} ${ew.customSqlSegment}"})
    IPage selectPageForNativeSql(IPage page, @Param("sql") String sql, @Param("ew") QueryWrapper queryWrapper);

    @Select({"${sql} ${ew.customSqlSegment}"})
    ArrayList selectListForNativeSql(@Param("sql") String sql, @Param("ew") QueryWrapper queryWrapper);
}

分页的模板的使用

使用LambdaQueryWrapper来构建查询分页

    public PageResult findMybatiesTemplateByPaged(PageRequest pageRequest) {
        MybatisPageServiceTemplate page = new MybatisPageServiceTemplate(this.baseMapper) {
            @Override
            protected AbstractWrapper constructWrapper(MybatiesTemplate mybatiesTemplate) {
                LambdaQueryWrapper lqw = new LambdaQueryWrapper();
                if (mybatiesTemplate != null && StringUtils.isNotBlank(mybatiesTemplate.getName())) {
                    lqw.like(MybatiesTemplate::getName, mybatiesTemplate.getName() == null ? "" : mybatiesTemplate.getName());
                }
                if (mybatiesTemplate != null && StringUtils.isNotBlank(mybatiesTemplate.getCode())) {
                    lqw.like(MybatiesTemplate::getCode, mybatiesTemplate.getCode() == null ? "" : mybatiesTemplate.getCode().toUpperCase());
                }

                lqw.orderByDesc(MybatiesTemplate::getCreateDate);
                return lqw;
            }
        };
        return page.doPagingQuery(pageRequest);
    }

使用原生sql来构建查询分页

public PageResult getUserOrganByPaged(PageRequest pageRequest) {
        MybatisPageServiceTemplate query = new MybatisPageServiceTemplate(mybatiesSqlTemplateDao) {
            @Override
            protected AbstractWrapper constructWrapper(UserOrganVo userOrganVo) {
                QueryWrapper queryWrapper = new QueryWrapper();
                if (userOrganVo != null) {
                    if (StringUtils.isNotBlank(userOrganVo.getUserName())) {
                        queryWrapper.eq("u.user_name", userOrganVo.getUserName());
                    }
                    if (StringUtils.isNotBlank(userOrganVo.getOrganName())) {
                        queryWrapper.eq("o.name", userOrganVo.getOrganName());
                    }
                }
                return queryWrapper;
            }

            @Override
            protected String constructNativeSql() {
                return "SELECT u.user_name ,o.`name` as organ_name FROM sys_user u INNER JOIN sys_user_organ uo on u.id = uo.user_id INNER JOIN sys_organ o on uo.organ_id = o.id";
            }
        };
        return query.doPagingQuery(pageRequest);
    }

最后源码地址：https://gitee.com/fengkuangshl_admin/single-soldier-wireless