mybatis 自定义拦截器 分页功能

Java基础

浏览数:93

2019-8-22

AD:资源代下载服务

mybatis介绍

MyBatis 是一款优秀的持久层框架,它支持定制化 SQL、存储过程以及高级映射。MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。MyBatis 可以使用简单的 XML 或注解来配置和映射原生类型、接口和 Java 的 POJO(Plain Old Java Objects,普通老式 Java 对象)为数据库中的记录。

mybatis架构图

mybatis框架结构.png

我们这个demo实现就是基于mybatis的插件模块(主要实现mybatis的Interceptor接口)

Interceptor接口

package org.apache.ibatis.plugin;

import java.util.Properties;

/**
 * @author Clinton Begin
 */
public interface Interceptor {

  Object intercept(Invocation invocation) throws Throwable;

  Object plugin(Object target);

  void setProperties(Properties properties);
}

demo实现

主要技术 spring boot + mybatis

pom.xml

<dependency>
      <groupId>org.mybatis.spring.boot</groupId>
      <artifactId>mybatis-spring-boot-starter</artifactId>
      <version>1.3.2</version>
</dependency>
<dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <scope>runtime</scope>
 </dependency>

数据库 DDL

create table user
(
  id       int         auto_increment primary key,
  username varchar(20) null
);

核心代码

PageVo

需要的分页要继承这个类

public class PageVo {
    
    protected int page = 1;//当前页数,默认为1
    protected int rows = 15;//每页显示条数,默认为15
    private int offset = 0;//每页起始条数
    private int limit = 15;//每页结束条数


    public int getLimit() {
        return (page - 1) * rows;
    }

    public void setLimit(int limit) {
        this.limit = limit;
    }

    public int getOffset() {
        return page * rows;
    }

    public void setOffset(int offset) {
        this.offset = (page - 1) * rows;
    }

    public int getPage() {
        return page;
    }

    public void setPage(int page) {
        this.page = page;
    }

    public int getRows() {
        return rows;
    }

    public void setRows(int rows) {
        this.rows = rows;
    }
}

返回结果集

用于封装返回的结果集

package com.wtn.mybatis_plugs.util;

import java.util.List;


public class PageResult<T> {

    private int pageNo = 1;// 页码,默认是第一页
    private int pageSize = 3;// 每页显示的记录数,默认是15
    private int total;// 总记录数
    private int totalPage;// 总页数
    private List<T> rows;

    private int offset = 0;// 偏移量,当前页起始记录数
    private int limit = 15;

    private boolean count = true;

    public PageResult() {
    }

    public PageResult(int pageNo, int pageSize, int total, List<T> rows) {
        this.pageNo = pageNo;
        this.pageSize = pageSize;
        this.total = total;
        this.setRows(rows);
        int totalPage = total % pageSize == 0 ? total / pageSize : total / pageSize + 1;
        this.setTotalPage(totalPage);
    }

    public List<T> getRows() {
        return rows;
    }

    public void setRows(List<T> rows) {
        this.rows = rows;
    }

    public int getOffset() {
        return offset;
    }

    public void setOffset(int offset) {
//      this.offset = offset;
        this.offset = (this.pageNo - 1) * pageSize;
    }

    public int getLimit() {
        return limit;
    }

    public void setLimit(int limit) {
        this.limit = limit;
    }

    public boolean isCount() {
        return count;
    }

    public void setCount(boolean count) {
        this.count = count;
    }

    @Override
    public String toString() {
        return "Page [pageNo=" + pageNo + ", pageSize=" + pageSize + ", totalRecord=" + total + ", totalPage="
                + totalPage + ", rows=" + rows + ", offset=" + offset + ", limit=" + limit + ", count=" + count + "]";
    }

    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 getTotal() {
        return total;
    }

    public void setTotal(int total) {
        this.total = total;
        int totalPage = total % pageSize == 0 ? total / pageSize : total / pageSize + 1;
        this.setTotalPage(totalPage);
    }

    public int getTotalPage() {
        return totalPage;
    }

    public void setTotalPage(int totalPage) {
        this.totalPage = totalPage;
    }

}

拦截器部分

主要思路是判断是否继承我们的PageVo这个类,如果继承了就进行分页处理,我这里分页重新拼接sql执行

 @Override
    public Object intercept(Invocation invocation) throws Throwable {
        logger.info("进入拦截器");
        Object[] args = invocation.getArgs();
        MappedStatement mappedStatement = (MappedStatement) args[0];

        //获取参数
        Object param = invocation.getArgs()[1];
        BoundSql boundSql = mappedStatement.getBoundSql(param);
        Object parameterObject = boundSql.getParameterObject();

        /**
         * 判断是否是继承PageVo来判断是否需要进行分页
         */
        if (parameterObject instanceof PageVo) {
            //强转 为了拿到分页数据
            PageVo pagevo = (PageVo) param;
            String sql = boundSql.getSql();
            
            //获取相关配置
            Configuration config = mappedStatement.getConfiguration();
            Connection connection = config.getEnvironment().getDataSource().getConnection();

            //拼接查询当前条件的sql的总条数
            String countSql = "select count(*) from (" + sql + ") a";
            PreparedStatement preparedStatement = connection.prepareStatement(countSql);
            BoundSql countBoundSql = new BoundSql(config, countSql, boundSql.getParameterMappings(), boundSql.getParameterObject());
            ParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, parameterObject, countBoundSql);
            parameterHandler.setParameters(preparedStatement);
            //执行获得总条数
            ResultSet rs = preparedStatement.executeQuery();
            int count = 0;
            if (rs.next()) {
                count = rs.getInt(1);
            }
            //拼接分页sql
            String pageSql = sql + " limit " + pagevo.getLimit() + " , " + pagevo.getOffset();
            //重新执行新的sql
            doNewSql(invocation, pageSql);

            Object result = invocation.proceed();
            connection.close();
            //处理新的结构
            PageResult<?> pageResult = new PageResult<List>(pagevo.page, pagevo.rows, count, (List) result);
            List<PageResult> returnResultList = new ArrayList<>();
            returnResultList.add(pageResult);

            return returnResultList;
        }
        return invocation.proceed();
    }

测试结构

 @PostMapping("/findUserPage")
    public List<User> findUserPage(@RequestBody UserDto userDto){
        return userService.findUserPage(userDto);
    }

测试结果

Snip20190403_2.png

github地址

https://github.com/XINYANJIANG/mybaits-plugs

作者:五头牛