【业务功能篇102】springboot+mybatisPlus分页查询，统一返回封装规范

业务场景： 随着业务代码量增多，很多接口查询的分页写法各种各样，为了使项目工程代码易于维护，我们统一规范，相对没有那么复杂的接口，我们统一都在java的service实现类中，去完成分页查询的接口逻辑，减少dao层大量的分页sql，或者各式各样的分页查询写法，所以写了一些通用工具类，进行重构。

页面插件 

需要引入mybatisPlus的配置类 才能生效分页功能

package com.msb.mall.product.config;import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.plugins.pagination.optimize.JsqlParserCountOptimize;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.transaction.annotation.EnableTransactionManagement;@Configuration
@EnableTransactionManagement // 开启事务
@MapperScan("com.msb.mall.product.dao")
public class MybatisPlusConfig {// 旧版@Beanpublic PaginationInterceptor paginationInterceptor() {PaginationInterceptor paginationInterceptor = new PaginationInterceptor();// 设置请求的页面大于最大页后操作， true调回到首页，false 继续请求  默认falsepaginationInterceptor.setOverflow(true);// 设置最大单页限制数量，默认 500 条，-1 不受限制paginationInterceptor.setLimit(500);// 开启 count 的 join 优化,只针对部分 left joinpaginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));return paginationInterceptor;}// 最新版/*@Beanpublic MybatisPlusInterceptor mybatisPlusInterceptor() {MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.H2));return interceptor;}*/
}

通用分页查询类Query<T>

package com.msb.common.utils;import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.metadata.OrderItem;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.msb.common.xss.SQLFilter;
import org.apache.commons.lang.StringUtils;import java.util.Map;/*** 查询参数**/
public class Query<T> {public IPage<T> getPage(Map<String, Object> params) {return this.getPage(params, null, false);}public IPage<T> getPage(Map<String, Object> params, String defaultOrderField, boolean isAsc) {//分页参数long curPage = 1;long limit = 10;if(params.get(Constant.PAGE) != null){curPage = Long.parseLong((String)params.get(Constant.PAGE));}if(params.get(Constant.LIMIT) != null){limit = Long.parseLong((String)params.get(Constant.LIMIT));}//分页对象Page<T> page = new Page<>(curPage, limit);//分页参数params.put(Constant.PAGE, page);//排序字段//防止SQL注入（因为sidx、order是通过拼接SQL实现排序的，会有SQL注入风险）String orderField = SQLFilter.sqlInject((String)params.get(Constant.ORDER_FIELD));String order = (String)params.get(Constant.ORDER);//前端字段排序if(StringUtils.isNotEmpty(orderField) && StringUtils.isNotEmpty(order)){if(Constant.ASC.equalsIgnoreCase(order)) {return  page.addOrder(OrderItem.asc(orderField));}else {return page.addOrder(OrderItem.desc(orderField));}}//没有排序字段，则不排序if(StringUtils.isBlank(defaultOrderField)){return page;}//默认排序if(isAsc) {page.addOrder(OrderItem.asc(defaultOrderField));}else {page.addOrder(OrderItem.desc(defaultOrderField));}return page;}
}

SQL参数防攻击过滤类SQLFilter 

package com.msb.common.xss;import com.msb.common.exception.RRException;
import org.apache.commons.lang.StringUtils;/*** SQL过滤**/
public class SQLFilter {/*** SQL注入过滤* @param str  待验证的字符串*/public static String sqlInject(String str){if(StringUtils.isBlank(str)){return null;}//去掉'|"|;|\字符str = StringUtils.replace(str, "'", "");str = StringUtils.replace(str, "\"", "");str = StringUtils.replace(str, ";", "");str = StringUtils.replace(str, "\\", "");//转换成小写str = str.toLowerCase();//非法字符String[] keywords = {"master", "truncate", "insert", "select", "delete", "update", "declare", "alter", "drop"};//判断是否包含非法字符for(String keyword : keywords){if(str.indexOf(keyword) != -1){throw new RRException("包含非法字符");}}return str;}
}

自定义参数非法异常类

package com.msb.common.exception;/*** 自定义异常*/
public class RRException extends RuntimeException {private static final long serialVersionUID = 1L;private String msg;private int code = 500;public RRException(String msg) {super(msg);this.msg = msg;}public RRException(String msg, Throwable e) {super(msg, e);this.msg = msg;}public RRException(String msg, int code) {super(msg);this.msg = msg;this.code = code;}public RRException(String msg, int code, Throwable e) {super(msg, e);this.msg = msg;this.code = code;}public String getMsg() {return msg;}public void setMsg(String msg) {this.msg = msg;}public int getCode() {return code;}public void setCode(int code) {this.code = code;}}

相关常量类

package com.msb.common.utils;/*** 常量**/
public class Constant {/** 超级管理员ID */public static final int SUPER_ADMIN = 1;/*** 当前页码*/public static final String PAGE = "page";/*** 每页显示记录数*/public static final String LIMIT = "limit";/*** 排序字段*/public static final String ORDER_FIELD = "sidx";/*** 排序方式*/public static final String ORDER = "order";/***  升序*/public static final String ASC = "asc";}

封装返回PageUtil分页工具类

package com.msb.common.utils;import com.baomidou.mybatisplus.core.metadata.IPage;import java.io.Serializable;
import java.util.List;/*** 分页工具类**/
public class PageUtils implements Serializable {private static final long serialVersionUID = 1L;/*** 总记录数*/private int totalCount;/*** 每页记录数*/private int pageSize;/*** 总页数*/private int totalPage;/*** 当前页数*/private int currPage;/*** 列表数据*/private List<?> list;/*** 分页* @param list        列表数据* @param totalCount  总记录数* @param pageSize    每页记录数* @param currPage    当前页数*/public PageUtils(List<?> list, int totalCount, int pageSize, int currPage) {this.list = list;this.totalCount = totalCount;this.pageSize = pageSize;this.currPage = currPage;this.totalPage = (int)Math.ceil((double)totalCount/pageSize);}/*** 分页*/public PageUtils(IPage<?> page) {this.list = page.getRecords();this.totalCount = (int)page.getTotal();this.pageSize = (int)page.getSize();this.currPage = (int)page.getCurrent();this.totalPage = (int)page.getPages();}public int getTotalCount() {return totalCount;}public void setTotalCount(int totalCount) {this.totalCount = totalCount;}public int getPageSize() {return pageSize;}public void setPageSize(int pageSize) {this.pageSize = pageSize;}public int getTotalPage() {return totalPage;}public void setTotalPage(int totalPage) {this.totalPage = totalPage;}public int getCurrPage() {return currPage;}public void setCurrPage(int currPage) {this.currPage = currPage;}public List<?> getList() {return list;}public void setList(List<?> list) {this.list = list;}}

封装返回前端响应通用类R

package com.msb.common.utils;import org.apache.http.HttpStatus;import java.util.HashMap;
import java.util.Map;/*** 返回数据**/
public class R extends HashMap<String, Object> {private static final long serialVersionUID = 1L;public R() {put("code", 0);put("msg", "success");}public static R error() {return error(HttpStatus.SC_INTERNAL_SERVER_ERROR, "未知异常，请联系管理员");}public static R error(String msg) {return error(HttpStatus.SC_INTERNAL_SERVER_ERROR, msg);}public static R error(int code, String msg) {R r = new R();r.put("code", code);r.put("msg", msg);return r;}public static R ok(String msg) {R r = new R();r.put("msg", msg);return r;}public static R ok(Map<String, Object> map) {R r = new R();r.putAll(map);return r;}public static R ok() {return new R();}public R put(String key, Object value) {super.put(key, value);return this;}public Integer getCode(){return (Integer) this.get("code");}public String getMessage(Integer code){return (String) this.get(code);}
}

实例：分页查询接口

controller层

@RestController
@RequestMapping("product/attrgroup")
public class AttrGroupController {@Autowiredprivate AttrGroupService attrGroupService;/*** 列表* 分页查询* 前端提交请求需要封装对应的分页数据* {*     page:1, // 当前页*     limit:10, // 每页显示的条数*     sidx:"id", // 排序的字段*     order:"asc/desc", // 排序的方式*     key:"小米" // 查询的关键字* }*/@RequestMapping("/list/{catelogId}")public R list(@RequestParam Map<String, Object> params,@PathVariable("catelogId") Long catelogId){// PageUtils page = attrGroupService.queryPage(params);PageUtils page = attrGroupService.queryPage(params,catelogId);return R.ok().put("page", page);}}

 

service层接口

public interface AttrGroupService extends IService<AttrGroupEntity> {PageUtils queryPage(Map<String, Object> params, Long catelogId);}

service层实现类

@Service("attrGroupService")
public class AttrGroupServiceImpl extends ServiceImpl<AttrGroupDao, AttrGroupEntity> implements AttrGroupService {/*** 查询列表数据*    根据列表编号来查询* @param params* @param catelogId 如何catelogId为0 就不根据catelogId来查询* @return*/@Overridepublic PageUtils queryPage(Map<String, Object> params, Long catelogId) {// 获取检索的关键字String key = (String) params.get("key");QueryWrapper<AttrGroupEntity> wrapper = new QueryWrapper<>();if(!StringUtils.isEmpty(key)){// 拼接查询的条件wrapper.and((obj)->{obj.eq("attr_group_id",key).or().like("attr_group_name",key);});}if(catelogId == 0){// 不根据catelogId来查询IPage<AttrGroupEntity> page = this.page(new Query<AttrGroupEntity>().getPage(params),wrapper);return new PageUtils(page);}// 根据类别编号来查询属性信息wrapper.eq("catelog_id",catelogId);IPage<AttrGroupEntity> page = this.page(new Query<AttrGroupEntity>().getPage(params),wrapper);return new PageUtils(page);}
}

dao层接口

@Mapper
public interface AttrGroupDao extends BaseMapper<AttrGroupEntity> {

表单实体类

@Data
@TableName("pms_attr_group")
public class AttrGroupEntity implements Serializable {private static final long serialVersionUID = 1L;/*** 分组id*/@TableIdprivate Long attrGroupId;/*** 组名*/private String attrGroupName;/*** 排序*/private Integer sort;/*** 描述*/private String descript;/*** 组图标*/private String icon;/*** 所属分类id*/private Long catelogId;/*** 修改数据的时候记录类别信息* [2,22,225]*/@TableField(exist = false)private Long[] catelogPath;}