mybatis 攔截器和分頁插件demo

mybatis介紹

MyBatis 是一款優秀的持久層架構，它支援定制化 SQL、存儲過程以及進階映射。MyBatis 避免了幾乎所有的 JDBC 代碼和手動設定參數以及擷取結果集。MyBatis 可以使用簡單的 XML 或注解來配置和映射原生類型、接口和 Java 的 POJO（Plain Old Java Objects，普通老式 Java 對象）為資料庫中的記錄。

mybatis架構圖

我們這個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);
    }
           

測試結果

github位址

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