SpringBoot 适配 MyBatis 分页插件功能(自定义拦截器方式)

文章目录

• ​​1. 自定义分页插件​​

• ​​1.1. application.properties 添加 mybatis 属性​​
• ​​1.2. mybatis-config.xml 配置文件​​
• ​​1.3. 分页拦截器类 PagePlugin​​
• ​​1.4. 分页对象实体类 PageInfo​​
• ​​1.5. mapper 文件 SQL 配置信息​​
• ​​1.6. 实际项目中使用​​

• ​​2. 使用 Github 开源分页插件​​

MyBatis 分页插件原理: 通过拦截器拦截指定方法重写响应SQL语句实现

1. 自定义分页插件

1.1. application.properties 添加 mybatis 属性

# Locations of MyBatis mapper files. 
mybatis.mapper-locations=classpath*:/mybatis/mapper/**.xml

# Packages to search type aliases. (Package delimiters are ",; \t\n")
mybatis.type-aliases-package=com.answer.springboot.mybatis.web.entity

# Location of MyBatis xml config file.
mybatis.config-location=classpath:/mybatis/config/mybatis-config.xml

1.2. mybatis-config.xml 配置文件

<configuration>
    <!-- 引入 MyBatis 分页插件 -->
    <plugins>
        <plugin interceptor="com.answer.springboot.mybatis.web.util.paging.PagePlugin">
            <!-- 数据库方言 -->
            <property name="dialect" value="mysql" />
            <!-- 拦截以 Page 结尾的所有方法 -->
            <!--<property name="pageSqlId" value=".*(Page)$" />-->
            <!-- 拦截以 find 开头的所有方法 -->
            <property name="pageSqlId" value="^find.*" />
            <!-- 分页对象信息对应的key值 -->
            <property name="pageParam" value="pageInfo" />
        </plugin>
    </plugins>
</configuration>

1.3. 分页拦截器类 PagePlugin

// type : 可拦截的类型： Executor、StatementHandler、ParameterHandler 和 ResultSetHandler
// 不同类型拦截器的顺序 Executor -> ParameterHandler -> StatementHandler ->ResultSetHandler
// 拦截 StatementHandler#prepare 方法, args 为方法 prepare  的参数
@Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class, Integer.class}) })
public class PagePlugin implements Interceptor {
    private static final Logger LOGGER = LoggerFactory.getLogger(PagePlugin.class);

    /** 数据库方言 */
    private static String dialect = "mysql";
    /** 拦截以 Page 结尾的所有方法 */
    private static String pageSqlId;
    /** 分页对象信息对应的key值 */
    private static String pageParam;

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();

        MetaObject metaObject = SystemMetaObject.forObject(statementHandler);
        // 获取查询接口映射的相关信息
        MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
        BoundSql boundSql = statementHandler.getBoundSql();
        String sql = boundSql.getSql();
        // 获取方法的全限定名, 或者通过boundSql.getParameterObject()获取
        String methodAllName = mappedStatement.getId();
        // 截取方法名
        String methodName = methodAllName.substring(methodAllName.lastIndexOf(".") + 1, methodAllName.length());
        // 获取方法的类型
        SqlCommandType sqlCmdType = mappedStatement.getSqlCommandType();
        // 如果是查询SQL并且和 pageSqlId 匹配则进行拦截, 进行统一的分页处理
        if (SqlCommandType.SELECT == sqlCmdType && methodName.matches(pageSqlId)) {
            // 获取方法的参数信息
            Map paramsMap = (Map) statementHandler.getParameterHandler().getParameterObject();
            // 如果参数中包含分页对象信息, 则进行重写SQL
            if (paramsMap.containsKey(pageParam)) {
                // 分页信息实体类
                PageInfo pageInfo = (PageInfo) paramsMap.get(pageParam);
                // 当前页码
                int currentPage = pageInfo.getCurrentPage();
                // 每页显示记录数
                int pageSize = pageInfo.getPageSize();
                // 记录偏移量
                int offset = (currentPage - 1) * pageSize;
                // 重写SQL, 加入分页逻辑
                sql = sql.trim() + " LIMIT " + offset + "," + pageSize;
                LOGGER.info("SQL Command Type【{}】, method【{}】 need paing.", sqlCmdType, methodName);
                // 将重写完的分页SQL语句覆盖掉原有的SQL语句
                metaObject.setValue("delegate.boundSql.sql", sql);
            }
        }
        LOGGER.info("mybatis intercept sqlID: {}, sql: \n{}", methodName, sql);

        // 打印 SQL 执行时间
        long startTime = System.currentTimeMillis();
        Object object = invocation.proceed();
        LOGGER.info("{} execute time: {} ms.", methodName, System.currentTimeMillis() - startTime);

        return object;
    }

    /**
     * 获取代理对象
     * */
    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    /**
     * 设置代理对象的参数
     *
     * @param properties config(sql-map-config.xml)中配置的 plugin 对应的 property
     * */
    @Override
    public void setProperties(Properties properties) {
        // 缺省时, 默认mysql
        dialect = properties.getProperty("dialect", "mysql");

        pageSqlId = properties.getProperty("pageSqlId");
        checkParam("pageSqlId", pageSqlId);

        pageParam = properties.getProperty("pageParam");
        checkParam("pageParam", pageParam);

        System.out.println("mybatis intercept dialect: " + dialect + ", pageSqlId: " + pageSqlId + ", pageParam: " + pageParam);
    }


    private void checkParam(String key, String value) {
        if (StringUtils.isEmpty(value)) {
            try {
                throw new PropertyException(key + " property is not found.");
            } catch (PropertyException e) {
                e.printStackTrace();
            }
        }
    }
}

1.4. 分页对象实体类 PageInfo

public class PageInfo {
    private int currentPage;
    private int pageSize;

    public PageInfo(int currentPage, int pageSize) {
        this.currentPage = currentPage;
        this.pageSize = pageSize;
    }

    public int getCurrentPage() {
        return currentPage;
    }

    public void setCurrentPage(int currentPage) {
        this.currentPage = currentPage;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }
}

1.5. mapper 文件 SQL 配置信息

<select id="findUsers" parameterType="Long" resultType="User">
    select *
    from smw_user
</select>

1.6. 实际项目中使用

@Controller
@RequestMapping("/user")
public class UserController {
    
    @RequestMapping("findUsers")
    @ResponseBody
    public AIResponse findUsers() {
        Map<String, Object> params = new HashMap<>();
        PageInfo pageInfo = new PageInfo(1, 5);
        // 引入 MyBatis 分页插件
        params.put("pageInfo", pageInfo);
        List<User> users = userService.findUsers(params);
        logger.info("users size: " + users.size());
        return AIResponse.success(users);
    }
    
}

2. 使用 Github 开源分页插件

• ​​Mybatis-PageHelper​​