项目需要,把以前分页模板拿出来,改改了,可支持原生的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;
/**
* @param <T> 输入参数的泛型类型
* @param <RT> 输出参数的泛型类型
*/
public abstract class MybatisPageServiceTemplate<T, RT> {
private final Logger logger = LoggerFactory.getLogger(this.getClass());
private final BaseMapper baseMapper;
/**
* @param baseMapper
*/
public MybatisPageServiceTemplate(BaseMapper baseMapper) {
super();
this.baseMapper = baseMapper;
}
/**
* 分页查询模板类,
*
* @param pageParam 翻页、排序参数model
* @return 泛型分页返回值,包含总记录数和当前页List数据
*/
public PageResult<RT> doPagingQuery(PageRequest<T> pageParam) {
//设备mybaties查询分页
logger.info("进行分页参数组装");
Page<RT> page = new Page<RT>();
page.setCurrent(pageParam.getPageNo());
page.setSize(pageParam.getPageSize());
//构建Wrapper
logger.info("分页查询条件构建");
AbstractWrapper wrapper = this.constructWrapper(pageParam.getT());
this.constructOrderByCondition(pageParam, wrapper);
logger.info("执行分页查询");
IPage<RT> pages = selectPage(page, wrapper);
//设置分页返回参数
logger.info("执行分页查询结果组装");
PageResult<RT> pageResult = new PageResult<RT>();
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;
}
/**
* 构建原生sql
*
* @return
*/
protected String constructNativeSql() {
return null;
}
protected boolean isNativeSql() {
if (StringUtils.isNotBlank(this.constructNativeSql())) {
return true;
}
return false;
}
/**
* 查询数据
*
* @param page
* @param wrapper
* @return
*/
private IPage<RT> selectPage(Page<RT> page, AbstractWrapper wrapper) {
if (this.isNativeSql()) {
KeyWinMapper<RT> keyWinMapper = (KeyWinMapper) baseMapper;
return keyWinMapper.selectPageForNativeSql(page, this.constructNativeSql(), wrapper);
} else {
return baseMapper.selectPage(page, wrapper);
}
}
/**
* 构建排序规则
* 找不到对应的表结构,就直接放弃排序
*
* @param pageParam
* @param wrapper
*/
private void constructOrderByCondition(PageRequest<T> pageParam, AbstractWrapper wrapper) {
TableInfo tableInfo = getTableInfo(pageParam);
if (tableInfo == null) {
logger.warn("没有找到数据库表结构,不进行排序操作!");
return;
}
appendSqlOrderByCondition(pageParam, wrapper, tableInfo);
}
/**
* 构建sql排序
* 如果能在映射表中找到字段,就进行排序。
* 否则,就放弃排序
*
* @param pageParam
* @param wrapper
* @param tableInfo
*/
private void appendSqlOrderByCondition(PageRequest<T> pageParam, AbstractWrapper wrapper, TableInfo tableInfo) {
List<MybatisOderByVo> orderList = getOrderByCondition(pageParam);
StringBuilder subSqlOrderBy = new StringBuilder();
Map<String, TableFieldInfo> 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);
TableFieldInfo tableFieldInfo = propertyToTableFieldInfoMap.get(ob.getSortName());
String column = tableFieldInfo == null ? null : tableFieldInfo.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中找不到一个映射字段,本次分页查询将放弃排序!");
}
}
/**
* 检查执行sql中是否已经存在order by排序条件
* 如果没有,则添加一个order by 条件到执行的sql中
*
* @param wrapper
* @return
*/
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;
}
/**
* 获取用户的自定义的排序条件
* 如果用户没有提供,系统将会默认按创建时间降序排列
*
* @param pageParam
* @return
*/
private List<MybatisOderByVo> getOrderByCondition(PageRequest<T> pageParam) {
List<MybatisOderByVo> orderList = new ArrayList();
List<MybatisOderByVo> 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;
}
/**
* 获取参与排序的字段与数据字段映射关系
* 如果找不到对应的表结果,则获取Mybatis中维护的表关系列表中的第一个表结构
* 原因如下:
* 这么做目地,是为解决用户在提交查询分页条件时,没有提交具体的分页条件实体对象,也就没有提交t对象。
* 这种情况下系统将按照默认排序规则进行排序,但又缺少表结构映射信息,所以就取Mybatis中维护的表关系列表中的第一个表结构
* 但这么做,也有可以还是找不到映射到表中的字段,这个情况就直接放弃排序
*
* @param pageParam
* @return
*/
private TableInfo getTableInfo(PageRequest<T> 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<TableInfo> tableInfos = TableInfoHelper.getTableInfos();
if (CollectionUtils.isNotEmpty(tableInfos)) {
tableInfo = tableInfos.get(0);//只为获取公共字段
logger.info("tableInfos的大小为{}个,获取第0个TableInfo的名称:{}", tableInfos.size(), tableInfo.getTableName());
}
}
return tableInfo;
}
/**
* 供子类OverWrite,根据传入的查询条件构造
*
* @param t
* @return AbstractWrapper
*/
abstract protected AbstractWrapper constructWrapper(T t);
/**
* 供子类重写,自定义排序规则;
* 默认返回空,框架会按创建时间createDate降序
*/
protected List<MybatisOderByVo> getQueryOrder(PageRequest<T> 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<T> 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<T> 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<T> data = new ArrayList<T>();
@ApiModelProperty("总页数数")
public long getTotalPage() {
return (count + pageSize - 1) / pageSize;
}
}
- 如果要原生sql进行分页,对应的mapper需要继承KeyWinMapper
package com.key.win.base.mapper;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
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<T> extends BaseMapper<T> {
@Select({"${sql} ${ew.customSqlSegment}"})
IPage<T> selectPageForNativeSql(IPage<T> page, @Param("sql") String sql, @Param("ew") Wrapper<T> queryWrapper);
@Select({"${sql} ${ew.customSqlSegment}"})
ArrayList<T> selectListForNativeSql(@Param("sql") String sql, @Param("ew") Wrapper<T> queryWrapper);
}
- 分页的模板的使用
- 使用LambdaQueryWrapper来构建单表的查询分页
public PageResult<MybatiesTemplate> findMybatiesTemplateByPaged(PageRequest<MybatiesTemplate> pageRequest) {
MybatisPageServiceTemplate<MybatiesTemplate, MybatiesTemplate> page = new MybatisPageServiceTemplate<MybatiesTemplate, MybatiesTemplate>(this.baseMapper) {
@Override
protected AbstractWrapper constructWrapper(MybatiesTemplate mybatiesTemplate) {
LambdaQueryWrapper<MybatiesTemplate> lqw = new LambdaQueryWrapper<MybatiesTemplate>();
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来构建查询分页
- 使用 QueryWrapper来构建查询条件
public PageResult<UserOrganVo> getUserOrganByPaged(PageRequest<UserOrganVo> pageRequest) {
MybatisPageServiceTemplate<UserOrganVo, UserOrganVo> query = new MybatisPageServiceTemplate<UserOrganVo, UserOrganVo>(mybatiesSqlTemplateDao) {
@Override
protected AbstractWrapper constructWrapper(UserOrganVo userOrganVo) {
QueryWrapper<UserOrganVo> queryWrapper = new QueryWrapper<UserOrganVo>();
if (userOrganVo != null) {
if (StringUtils.isNotBlank(userOrganVo.getUserName())) {
queryWrapper.eq("user_name", userOrganVo.getUserName());
}
if (StringUtils.isNotBlank(userOrganVo.getOrganName())) {
queryWrapper.eq("organ_name", userOrganVo.getOrganName());
}
}
return queryWrapper;
}
@Override
protected String constructNativeSql() {
return "SELECT * FROM ( 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 ) user_organ";
}
};
return query.doPagingQuery(pageRequest);
}
- 使用 LambdaQueryWrapper来构建查询条件
public PageResult<UserOrganVo> getUserOrganByPaged(PageRequest<UserOrganVo> pageRequest) {
MybatisPageServiceTemplate<UserOrganVo, UserOrganVo> query = new MybatisPageServiceTemplate<UserOrganVo, UserOrganVo>(mybatiesSqlTemplateDao) {
@Override
protected AbstractWrapper constructWrapper(UserOrganVo userOrganVo) {
LambdaQueryWrapper<UserOrganVo> lqw = new LambdaQueryWrapper<UserOrganVo>();
if (userOrganVo != null) {
if (StringUtils.isNotBlank(userOrganVo.getUserName())) {
lqw.eq(UserOrganVo::getUserName, userOrganVo.getUserName());
}
if (StringUtils.isNotBlank(userOrganVo.getOrganName())) {
lqw.eq(UserOrganVo::getOrganName, userOrganVo.getOrganName());
}
}
return lqw;
}
@Override
protected String constructNativeSql() {
return "SELECT * FROM ( 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 ) user_organ";
}
};
return query.doPagingQuery(pageRequest);
}