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

冷不防 2024-03-04 05:58 57阅读 0赞

> 业务场景： 随着业务代码量增多，很多接口查询的分页写法各种各样，为了使项目工程代码易于维护，我们统一规范，相对没有那么复杂的接口，我们统一都在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 {
    
        // 旧版
        @Bean
        public PaginationInterceptor paginationInterceptor() {
            PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
            // 设置请求的页面大于最大页后操作， true调回到首页，false 继续请求  默认false
             paginationInterceptor.setOverflow(true);
            // 设置最大单页限制数量，默认 500 条，-1 不受限制
             paginationInterceptor.setLimit(500);
            // 开启 count 的 join 优化,只针对部分 left join
            paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));
            return paginationInterceptor;
        }
    
    
        // 最新版
        /*@Bean
        public 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 {
        @Autowired
        private 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
         */
        @Override
        public 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
    	 */
    	@TableId
    	private 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;
    
    }