MybatisPlus 一对多分页插件

起源

MybatisPlus分页插件原理:在sql末尾拼接limit,select后面替换count(1)

如果我们的sql是一对多的,比如订单对应订单项,班级对应学生…如果分页大小为10,期望得到的数据往往是大于10条的,此时按照原逻辑是会在sql的结尾拼接limit 10,得不到想要的效果.之前的做法是写一个count的sql,再写一个分页的sql

所以我写了一个MybatisPlus的插件 引入了一个自己的特殊标记[limit],该插件会在[limit]处拼接limit语句,并且通过该标记找到正确的主表sql,并以主表sql拼接正确的count语句.兼容MybatisPlus自带的分页插件和PageHelper[可能,没测试]

插件代码

package com.yami.shop.common;

import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.inner.InnerInterceptor;
import com.baomidou.mybatisplus.extension.toolkit.SqlParserUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.reflection.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.util.HashMap;
import java.util.Map;

/**
 * 一对多语句 正确拼接limit和count
 * 以"[limit]"作为拦截条件
 * <p>
 * 注意[limit]后不可有#{占位符}
 * <p>
 * 使用Demo: {@link com.yami.shop.service.impl.OrderServiceImpl#pageOrdersDetailByOrderParamEdit }
 */
@Slf4j
public class OneToManyPageInterceptor implements InnerInterceptor {

    /**
     * 将最后拼接的limit删除 并根据根[limit]的位置 拼接limit语句
     */
    private static String removeTheLastLimitToTheEndOfMainTable(String sql, BoundSql boundSql) {
        // 获取分页参数
        Map param = (HashMap) boundSql.getParameterObject();
        IPage page = (IPage) param.get("page");
        //将[limit]替换为正确的limit语句 第一页拼接一个? 其他页面拼接两个?
        if (page.getCurrent() == 1) {
            //先去掉自动拼接在结尾的的limit
            sql = sql.replace("limit ?", "");
            sql = sql.replace("[limit]", "limit ?");
        }else {
            sql = sql.replace("limit ?,?", "");
            sql = sql.replace("[limit]", "limit ?,?");
        }
        return sql;
    }

    /**
     * 对于自动生成的count sql
     * 找到主表sql并且将主表select和from之间的内容替换为count(1)
     */
    private static String prepareMainTableCountSql(String sql) {
        // 1. 得到主sql
        // 1.1 获取主sql的结束位置 即特殊标记[limit]的开始位置limitIndex
        int limitIndex = StrUtil.indexOf(sql, '[');
        // 1.2 获取主sql的开始位置 从结束位置往前遍历如果是)就+1 是(就-1 一直到第一次出现-1 则表示是主表sql的select
        int temp = 0;
        int mainSelectIndex = 0;
        for (int i = limitIndex; i >= 0; i--) {
            if (sql.charAt(i)==')') {
                temp++;
            }
            if (sql.charAt(i)=='('){
                temp--;
            }
            if (temp == -1) {
                mainSelectIndex = i;
                break;
            }
        }
        // 1.3 根据开始和结束位置截取得到主表sql
        sql = sql.substring(mainSelectIndex + 1, limitIndex);
        // 2. 寻找主select对应的主from
        // 2.1 根据select出现的次数 判断有没有子查询
        int selectShowNum = StrUtil.count(sql, "select");
        int mainFromIndex = 0;
        if (selectShowNum == 1) {
            // 2.1.1 如果只有一个select 那么from也只有一个说明没有子查询
            mainFromIndex = StrUtil.ordinalIndexOf(sql, "from", 1);
        } else {
            // 2.1.2 如果出现了多个select 说明有子查询 按照from切割
            String[] splitByFromArray = StrUtil.split(sql, "from");
            int temp2 = 0;
            // 2.1.3 遍历每一个以from切割的字符串splitByFrom
            for (String splitByFrom : splitByFromArray) {
                for (int j = 0; j < splitByFrom.length(); j++) {
                    // 如果是(, temp2+1
                    if (splitByFrom.charAt(j) == '(') {
                        temp2++;
                    }
                    // 如果是), temp2-1
                    if (splitByFrom.charAt(j) == ')') {
                        temp2--;
                    }
                }
                // 2.1.4 已经遍历完splitByFrom,主from的位置往后移动
                mainFromIndex += splitByFrom.length();
                // 2.1.5 如果temp2是0 则表示已经遍历结束主sql的select,下一个from语句就是主from,立即结束
                if (temp2 == 0) {
                    break;
                }
            }
        }
        // 3. 根据主from的位置切割 并在前面拼接select count(1)
        sql = "select count(1) " + sql.substring(mainFromIndex);
        // 4. 如果拼接后的sql含有分组,则需要再套一层count语句
        if (sql.contains("group by")) {
            sql = SqlParserUtils.getOriginalCountSql(sql);
        }
        // 4. 删除自定义的特殊标记
        sql = sql.replace("[limit]", "");
        return sql;
    }

    @Override
    public void beforePrepare(StatementHandler sh, Connection connection, Integer transactionTimeout) {
        BoundSql boundSql = sh.getBoundSql();
        String sql = boundSql.getSql().toLowerCase();

        // 通过MetaObject优雅访问对象的属性，这里是访问statementHandler的属性;：MetaObject是Mybatis提供的一个用于方便、
        // 优雅访问对象属性的对象，通过它可以简化代码、不需要try/catch各种reflect异常，同时它支持对JavaBean、Collection、Map三种类型对象的操作。
        MetaObject metaObject = MetaObject.forObject(sh, SystemMetaObject.DEFAULT_OBJECT_FACTORY,
                SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY, new DefaultReflectorFactory());

        // 先拦截到RoutingStatementHandler，里面有个StatementHandler类型的delegate变量，其实现类是BaseStatementHandler，
        // 然后就到BaseStatementHandler的成员变量mappedStatement
        MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");

        // id为执行的mapper方法的全路径名，如com.cq.UserMapper.insertUser
        String id = mappedStatement.getId();
        String modifiedSql;
        if (sql.contains("[limit]")) {
            log.info("一对多自动分页处理开始");
            // 如果是自动生成的count语句(方法名以_mpCount结尾) 查找主sql 重新拼接count(1)
            if (id.endsWith("_mpCount") || id.endsWith("_COUNT")) {
                modifiedSql = prepareMainTableCountSql(sql);
            } else {
                // 将结尾拼接的limit 删除 并在 [limit] 处正确拼接limit
                modifiedSql = removeTheLastLimitToTheEndOfMainTable(sql, boundSql);
            }
            log.info("一对多自动分页处理结束");
            //通过反射修改sql语句
            Field field;
            try {
                field = boundSql.getClass().getDeclaredField("sql");
                field.setAccessible(true);
                field.set(boundSql, modifiedSql);
            } catch (NoSuchFieldException | IllegalAccessException e) {
                throw new RuntimeException(e);
            }
        }
    }
}

使用方式

1.加入插件代码的类

2.在原有的分页配置里增加

/**
 * 这是我的项目中的MybatisPlus的配置类
 */
@Configuration
public class MybatisPlusConfig {

    @Bean
    @ConditionalOnMissingBean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        // 这里是你需要增加的一行
        interceptor.addInnerInterceptor(new OneToManyPageInterceptor());
        return interceptor;
    }
    ...
}

使用Demo

以订单和订单项为例,tz_order为主表,tz_order_item为子表,一个Order对象包含一个OrderItem的List

Controller

 @GetMapping("/page")
    public ResponseEntity<IPage<OrderListResult>> page(OrderParam orderParam, PageParam<OrderListResult> page) {
        IPage<OrderListResult> orderPage = orderService.pageOrdersDetailByOrderParamEdit(page, orderParam);
        return ResponseEntity.ok(orderPage);
    }

Service

直接返回IPage类型

return orderMapper.pageByOrderParam(page, orderParam);

Dao

IPage<OrderListResult> pageByOrderParam(@Param("page") PageParam<OrderListResult> page, @Param("orderParam") OrderParam orderParam);

Mapper

支持在主sql的select写子查询,支持主表分组,支持在where中写子查询
唯一的要求可能是表的字段不能含有from或者select.

<select id="pageByOrderParam" resultMap="orderAndOrderItemAndUserAddrMapEdit">
        select a.*,oi.order_item_id as order_item_id from
        (select * from tz_order o
            <where>
                <if test="orderParam.orderNumber != null">
                    o.order_number = #{orderParam.orderNumber}
                </if>
                <if test="orderParam.status != null">
                    and o.status = #{orderParam.status}
                </if>
                -- 当按照子表筛选时 需要使用exists筛选
                <if test="orderParam.prodName != null and orderParam.prodName != ''">
                and exists(
                  select * from tz_order_item toi where toi.order_number= o.order_number
                  and trim(replace(toi.prod_name, ' ', '')) like trim(replace(concat('%',#{orderParam.prodName}, '%'), ' ', ''))
                )
                </if>
            </where>
            -- 插入此标记 则表示需要将原本分页拼接在末尾的limit语句删除 并在此处进行分页 需要注意不能在该标记后有占位符
            [limit]
        )as a
        left join tz_order_item oi on a.order_number = oi.order_number
        order by a.create_time desc
    </select>

参阅

MybatisPlus官方的插件文档 https://baomidou.com/pages/2976a3/#mybatisplusinterceptor