MyBatis的三种分页方式

1. RowBounds实现分页
   RowBounds 是 MyBatis提供的 （MyBatis自带的）用于分页的一个简单工具，它通过 Java 代码的方式来实现分页功能。使用 RowBounds 时，只需要在 Mapper 的方法中添加 RowBounds 参数即可。

   List<YourModel> selectByRowBounds(YourExample example, RowBounds rowBounds);
   

   然后，在调用 Mapper 方法时传入 RowBounds 对象，其中包含了偏移量（offset）和限制量（limit）。

   int offset = 0; // 起始位置
   int limit = 10; // 每页显示的数据条数
   RowBounds rowBounds = new RowBounds(offset, limit);
   List<YourModel> list = yourMapper.selectByRowBounds(example, rowBounds);
   

   RowBounds 简单易用，但并不是一种高效的分页方式，因为它是通过在数据库返回全部结果之后，在内存中进行分页处理，这在处理大量数据时会非常低效。

2. 物理分页插件实现分页
   引入 PageHelper 依赖

   <dependency>
       <groupId>com.github.pagehelper</groupId>
       <artifactId>pagehelper-spring-boot-starter</artifactId>
       <version>2.1.0</version>
   </dependency>
   

   使用 PageHelper
   只需要在查询方法前调用 PageHelper.startPage 方法，传入页码和每页数量即可。

   PageHelper.startPage(1, 10); // 页码，每页数量
   List<YourModel> list = yourMapper.selectByExample(example);
   

   PageHelper 会自动的对接下来的第一个 MyBatis 查询进行分页处理。它通过改写原 SQL 语句，在 SQL 执行层面实现分页，它能够实现真正意义上的物理分页，从而大大提高了分页的效率。

3. 编写分页SQL实现分页

   @Select("SELECT * FROM your_table LIMIT #{offset}, #{limit}")
   List<YourModel> selectByPage(@Param("offset") int offset, @Param("limit") int limit);
   

   这种基于 “limit offset,length” 的方式如果offset的值很大时，也会导致性能很差

• 结合mybatis的Interceptor实现分页

/**
 * 自定义分页注解
 */
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface Limit {

    /**
     * 当前页面
     * 
     * @return
     */
    int page() default 0;

    /**
     * 每页显示数量
     * 
     * @return
     */
    int pageSize() default 10;
}

/**
 * 自定义排序注解
 */
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface OrderBy {


    /**
     * 表的别名
     */
    String tableAlias() default "";

    /**
     * 排序字段
     */
    String orderColumn() default "";


    /**
     * ASC/DESC 默认倒序
     * @return
     */
    boolean isAsc() default false;

}

定义基础切面处理类

/**
 * @ClassName BaseAspectAbstract
 * @Description 基础切面处理类，每一个Spring的切面都需要去继承此抽象类
 * @Date
 **/
public abstract class BaseAspectAbstract {

    private static TreeMap<Integer, SQLLanguage> CONTAINERS = new TreeMap<>();

    // 放入sql 切点、sql类型、sql
    public void putSQL(JoinPoint point, SQLEnums sqlEnums, SQLLanguage sqlLanguage) {
        CONTAINERS.put(sqlEnums.getId(), sqlLanguage);
        // 获取方法里的参数
        Object parmas = point.getArgs()[0];
        Map map = (Map)parmas;
        map.put("SQL", getSQL());
    }

    public TreeMap<Integer, SQLLanguage> getSQL() {
        return CONTAINERS;
    }

}

进行分页切面

@Component
@Aspect
@Order(3) //拼接sql时的顺序
public class LimitAspect extends BaseAspectAbstract {

    @Pointcut("@annotation(com.mybatis.interceptor.annotation.Limit)")
    public void limitCut() {}

    @Before("limitCut()")
    public void limit(JoinPoint point) {
        StringBuilder limitBuilder = new StringBuilder(" LIMIT ");
        MethodSignature methodSignature = (MethodSignature)point.getSignature();
        // 获得对应注解
        Limit limit = methodSignature.getMethod().getAnnotation(Limit.class);
        if (!StringUtils.isEmpty(limit)) {
            limitBuilder.append(limit.page()).append(",").append(limit.pageSize());
            putSQL(point, LIMIT, new SQLLanguage(limitBuilder.toString()));
        }

    }
}

进行排序切面

@Component
@Aspect
@Order(2)
public class OrderByAspect extends BaseAspectAbstract {

    // 切点：对注解中的特定注解进行拦截，进行增强
    @Pointcut("@annotation(com.mybatis.interceptor.annotation.OrderBy)")
    public void orderByCut() {}

    // 执行切点操作，将其进行增强，放入排序
    @Before("orderByCut()")
    public void orderBy(JoinPoint point) {
        StringBuilder orderByBuilder = new StringBuilder(" ORDER BY ");
        MethodSignature methodSignature = (MethodSignature)point.getSignature();
        // 获得对应注解
        OrderBy orderBy = methodSignature.getMethod().getAnnotation(OrderBy.class);
        if (!StringUtils.isEmpty(orderBy)) {
            String sort = orderBy.isAsc() ? " asc " : " desc";
            orderByBuilder.append(orderBy.orderColumn()).append(sort);
            putSQL(point, ORDERBY, new SQLLanguage(orderByBuilder.toString()));
        }
    }
}

枚举sql顺序

/**
 * sql类型枚举
 */
public enum SQLEnums {

    /**
     * 数字越靠前 则拼接SQL语句越靠前执行，目前拼接顺序为
     * SELECT * FROM table GROUP BY ORDER BY xxx LIMIT 0, 10
     */
    LIKE(1, "LIKE"), GROUPBY(2, "GROUP BY"), ORDERBY(3, "ORDER BY"), LIMIT(4, "LIMIT");

    private int id;

    private String condition;

    SQLEnums(int id, String condition) {
        this.id = id;
        this.condition = condition;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getCondition() {
        return condition;
    }

    public void setCondition(String condition) {
        this.condition = condition;
    }
}

执行sql增强的注解放置在serviceImpl里面

@RequestMapping("/nba")
public class PlayController {

    @Autowired
    private PlayerService playerService;

    @RequestMapping("/player")
    public List<Player> getList(Map<String, Object> params) {
        List<Player> players = playerService.getList(params);
        return players;
    }
}

执行sql增强

@Service
public class PlayerServiceImpl implements PlayerService {

    @Autowired
    private PlayerMapper playerMapper;

    // 加入自定义注解，方便切点进行增强
    @OrderBy(orderColumn = "height")
    @Limit()
    @Override
    public List<Player> getList(Map<String, Object> params) {
        return playerMapper.getList(params);
    }
}

执行到这里会执行动态代理，然后执行sql拦截

执行sql拦截

@Intercepts(@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class}))
@Component
public class DataFilterInterceptor extends AbstractSqlParserHandler implements Interceptor {

    // 拦截器
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        StatementHandler statementHandler = PluginUtils.realTarget(invocation.getTarget());
        MetaObject metaObject = SystemMetaObject.forObject(statementHandler);
        // SQLLanguage 解析
        sqlParser(metaObject);
        // 非查询操作
        MappedStatement mappedStatement = (MappedStatement)metaObject.getValue("delegate.mappedStatement");
        if (!SqlCommandType.SELECT.equals(mappedStatement.getSqlCommandType())) {
            return invocation.proceed();
        }

        // 取出原始SQL 取出参数
        BoundSql boundSql = (BoundSql)metaObject.getValue("delegate.boundSql");
        String dataSql = boundSql.getSql();
        Object paramObj = boundSql.getParameterObject();
        Map map = (Map)paramObj;
        String sqlLanguage = getSQLLanguage(map);
        String sql = dataSql + sqlLanguage;
        // 重写sql
        metaObject.setValue("delegate.boundSql.sql", sql);
        return invocation.proceed();
    }

    // 插件
    @Override
    public Object plugin(Object target) {
        if (target instanceof StatementHandler) {
            return Plugin.wrap(target, this);
        }
        return target;
    }

    @Override
    public void setProperties(Properties properties) {

    }

    // 获取sql语句
    private String getSQLLanguage(Map<String, Object> map) {
        TreeMap<Integer, SQLLanguage> sqlMap = (TreeMap)map.get("SQL");
        StringBuilder sqlBuilder = new StringBuilder();
        for (Map.Entry treeMap : sqlMap.entrySet()) {
            SQLLanguage sql = (SQLLanguage)treeMap.getValue();
            if (null != sql) {
                sqlBuilder.append(sql);
            }
        }
        return sqlBuilder.toString();
    }
}